Oracle User Management

Creating a User:-

CREATE USER <username> IDENTIFIED BY <password>;



Change the password of already created user:-

ALTER USER <username> IDENTIFIED BY <password>;



View Created User Information:-

SELECT username, password, created FROM dba_users WHERE username='<username>';


Give user a Default access to particular tablespace during user creation time:-

CREATE USER gaurav identified BY gaurav@1 DEFAULT TABLESPACE temp1;


View the Default Tablespace to a particular user:-

SELECT username, default_tablespace FROM dba_users WHERE username='GAURAV';



Give a default tablespace already created user:-

ALTER USER gaurav DEFAULT TABLESPACE temp1;



Include Access to a Temporary Tablespace during user creation time:-

CREATE USER gaurav IDENTIFIED BY gaurav@1 TEMPORARY TABLESPACE temp;



Include Access to a Temporary Tablespace already  created user:-

ALTER USER gaurav TEMPORARY TABLESPACE temp;

Include Quota On Tablespaces during user creation time:-

CREATE USER gaurav IDENTIFIED by gaurav@1 DEFAULT TABLESPACE user TEMPORARY TABLESPACE temp QUOTA 5M ON users;

 View Access in a particular Tablespace information for a particular user:-

SELECT username, tablespace_name, max_bytes, max_blocks FROM dba_ts_quotas WHERE username='GAURAV';

Give Tablespace Quota to a already created user:-

ALTER USER gaurav QUOTA 3M ON users;

Assign profile to a user during creation time:

CREATE USER gaurav IDENTIFIED BY gaurav@1 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 2M ON users PROFILE DEFAULT;


Provide Profile to a already created user:-

ALTER USER gaurav PROFILE monitoring_profile;



View Assign Profile name of a particular user:-

SELECT username, profile FROM dba_users WHERE username='GAURAV';

Use of EXPIRE keyword:-

CREATE USER gaurav IDENTIFIED BY gaurav@1 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 2M on users PROFILE DEFAULT PASSWORD EXPIRE;

NOTE - PASSWORD EXPIRE: forces the user to reset the password in the first time login.


View Account Status and Expiry date of users:-

SELECT username, expiry_date, account_status FROM dba_users WHERE username='GAURAV';

Drop User:-

DROP USER GAURAV;


Drop User with Objects:-

DROP USER gaurav CASCADE;

Note: Current Connected User Can Not be dropped


LOCK a User Account:

ALTER USER gaurav ACCOUNT lock;



UNLOCK a User Account:

ALTER USER gaurav ACCOUNT unlock;