Archive for the ‘Users’ Category

Standard User Creation Procedure

CREATE USER FZAFAR
  IDENTIFIED BY VALUES ‘2BC657B017E6006B’
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  — 5 Roles for ITSM
  GRANT SELECT_CATALOG_ROLE TO FZAFAR;
  GRANT EXECUTE_CATALOG_ROLE TO FZAFAR;
  GRANT DELETE_CATALOG_ROLE TO FZAFAR;
  GRANT RESOURCE TO FZAFAR;
  GRANT CONNECT TO FZAFAR;
  ALTER USER FZAFAR DEFAULT ROLE ALL;    –Dont forget to enable default role all
  — 2 System Privileges for FZAFAR
  GRANT CREATE SESSION TO FZAFAR;
  GRANT SELECT ANY TABLE TO FZAFAR;
  — 1 Tablespace Quota for FZAFAR
  ALTER USER FZAFAR QUOTA UNLIMITED ON USERS;

 

Creating User in TOAD 

1. User Info
Select User Name Password  Resource Profile(Default)

2. TableSpace
Default TableSpace and Temporary Tablespace

3. ROLE
Select CONNECT, RESOURCE, EXECUTE_CATALOG_ROLE, SELECT_CATALOG_ROLE

4. SYSTEM Privileges
Leave Blank

5. Object Grants
Leave Blank (if user asks for specific grants then check the checkboxes)

6. Quotas
Leave Blank

7. Resource Groups
Leave Blank

Advertisements

Dropping User

To drop a user you must have the DROP USER system privilege

To check if user has privilege to drop user.

select * from session_privs where privilege=’DROP USER’;

 

Dropping User without having objects:

SQL> drop user test;

 

SQL> drop user a cascade;

Dropping a Connected User

Find out the connected user sid and serial# by,

SQL> select sid, serial# from v$session where username = 'TEST';



SID SERIAL#
---------- ----------
268 1268
315 1223

Before killing the user , lock its account so that it cannot attempt
further connections.

SQL> Alter user test account lock;

Now kill the connected session.

SQL> alter system kill session ‘268,1268’;

SQL> alter system kill session ‘315,1223’;

Finally drop the user.

SQL> drop user test cascade;