Oracle Database User Management.

USER CREATION AND DELETION.

SQL> create user tom identified by jerry
  2  default tablespace users
  3  temporary tablespace temp 
  4  quota 20m on users;

User created.

Above command creates a user tom with password jerry.
The tablespace in which tom will store his data is "USERS".
The tablespace used for storing temporary segments will be "TEMP".
And amount of space which tom can use on "USERS" tablespace is 20M.

SQL> drop user tom cascade;


SQL> alter user scott identified by tiger account unlock;

User altered.

The above command unlocks the "SCOTT" user with password "TIGER".


SQL> select username, account_status, default_tablespace,
 temporary_tablespace, profile from dba_users
 where username = 'TOM'


USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE TEMPORARY_TABLESPACE PROFILE  
-------- -------------- ------------------ -------------------- ---------
TOM      OPEN           USERS              TEMP                 DEFAULT


The above query shows the account information related to "TOM".


ALTERING TABLEPSACE QUOTA

SQL> select * from dba_ts_quotas where username = 'TOM';

TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
--------------- -------- ----- --------- ------ ---------- ---
USERS           TOM      0     20971520  0      2560       NO



 
SQL> alter user tom quota 30m on users;

User altered.

SQL> select * from dba_ts_quotas where username = 'TOM';

TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
--------------- -------- ----- --------- ------ ---------- ---
USERS           TOM      0     31457280  0      3840       NO


GRANTING AND REVOKING PRIVILEGES. 


SQL> grant create table to tom;   

Grant succeeded.

SQL> grant create session to tom;

Grant succeeded.

SQL> grant create any table, create tablespace to tom;

Grant succeeded.

SQL> revoke create any table from tom;

Revoke succeeded.

SQL> revoke create tablespace from tom;

Revoke succeeded.

SQL> grant select, insert, update, delete on scott.emp to tom;

Grant succeeded.

SQL> revoke update,delete on scott.emp from tom;

Revoke succeeded.


ROLES

SQL> create role manager;

SQL> grant create any table,
  2  alter any table,
  3  drop any table,
  4  select any table,
  5  update any table,
  6  delete any table
  7  to manager;



SQL> grant create any index,
  2  alter any index,
  3  drop any index
  4  to manager;

Grant succeeded.

SQL> grant alter session,
  2  restricted session
  3  to manager;

Grant succeeded.

SQL> grant create tablespace,
  2  alter tablespace,
  3  drop tablespace,
  4  unlimited tablespace
  5  to manager;

Grant succeeded.

SQL> grant select, insert, update, delete on scott.dept to manager;

Grant succeeded.

SQL> grant manager to tom;

Grant succeeded.


SQL> select * from dba_sys_privs where grantee = 'TOM';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
TOM                            CREATE TABLE                             NO
TOM                            CREATE SESSION                           NO

SQL> select * from dba_sys_privs where grantee = 'MANAGER'


GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
MANAGER                        DELETE ANY TABLE                         NO
MANAGER                        CREATE ANY TABLE                         NO
MANAGER                        DROP TABLESPACE                          NO
MANAGER                        ALTER TABLESPACE                         NO
MANAGER                        ALTER ANY INDEX                          NO
MANAGER                        DROP ANY TABLE                           NO
MANAGER                        DROP ANY INDEX                           NO
MANAGER                        UPDATE ANY TABLE                         NO
MANAGER                        ALTER SESSION                            NO
MANAGER                        SELECT ANY TABLE                         NO
MANAGER                        RESTRICTED SESSION                       NO
MANAGER                        CREATE ANY INDEX                         NO
MANAGER                        ALTER ANY TABLE                          NO
MANAGER                        UNLIMITED TABLESPACE                     NO
MANAGER                        CREATE TABLESPACE                        NO

15 rows selected.




SQL> select grantee, owner, table_name, grantor, privilege from dba_tab_privs
  2  where grantee = 'TOM';

GRANTEE  OWNER  TABLE_NAME   GRANTOR    PRIVILEGE
-------- ------ ------------ ---------  ---------  
TOM      SCOTT  EMP          SCOTT      SELECT

TOM      SCOTT  EMP          SCOTT      INSERT


SQL> select grantee, owner, table_name, grantor, privilege from dba_tab_privs
     where grantee = 'MANAGER'

GRANTEE  OWNER  TABLE_NAME   GRANTOR    PRIVILEGE
-------- ------ ------------ ---------  --------- 
MANAGER  SCOTT  DEPT         SCOTT      UPDATE

MANAGER  SCOTT  DEPT         SCOTT      SELECT

MANAGER  SCOTT  DEPT         SCOTT      INSERT

MANAGER  SCOTT  DEPT         SCOTT      DELETE




SQL> select * from dba_roles where role = 'MANAGER';

ROLE                           PASSWORD AUTHENTICAT
------------------------------ -------- -----------
MANAGER                        NO       NONE




SQL> select * from dba_role_privs where grantee = 'TOM';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
TOM                            MANAGER                        NO  YES



SQL> select * from role_sys_privs where role = 'MANAGER';

ROLE                           PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
MANAGER                        DROP TABLESPACE                          NO
MANAGER                        CREATE ANY TABLE                         NO
MANAGER                        DELETE ANY TABLE                         NO
MANAGER                        ALTER TABLESPACE                         NO
MANAGER                        DROP ANY TABLE                           NO
MANAGER                        ALTER ANY INDEX                          NO
MANAGER                        UPDATE ANY TABLE                         NO
MANAGER                        DROP ANY INDEX                           NO
MANAGER                        ALTER SESSION                            NO
MANAGER                        RESTRICTED SESSION                       NO
MANAGER                        SELECT ANY TABLE                         NO
MANAGER                        CREATE TABLESPACE                        NO
MANAGER                        UNLIMITED TABLESPACE                     NO
MANAGER                        ALTER ANY TABLE                          NO
MANAGER                        CREATE ANY INDEX                         NO

15 rows selected.





SQL> select * from role_tab_privs where role = 'MANAGER';

ROLE     OWNER   TABLE_NAME   COLUMN_NAME   PRIVILEGE   GRA
-------- ------- ------------ ------------- ----------- ---
MANAGER  SCOTT   DEPT                       DELETE       NO

MANAGER  SCOTT   DEPT                       UPDATE       NO

MANAGER  SCOTT   DEPT                       SELECT       NO

MANAGER  SCOTT   DEPT                       INSERT       NO



SQL> revoke manager from tom;

Revoke succeeded.



PROFILES

SQL> create profile developer limit
    failed_login_attempts 3
    password_lock_time unlimited
    password_life_time 30
    password_reuse_time 30
    password_grace_time 5
    idle_time 30;


Profile created.

SQL> alter user tom profile developer;

User altered.


SQL> select * from dba_profiles where profile = 'DEVELOPER';

PROFILE     RESOURCE_NAME                    RESOURCE LIMIT
----------- --------------                   -------- -----
DEVELOPER   COMPOSITE_LIMIT                  KERNEL   DEFAULT
DEVELOPER   SESSIONS_PER_USER                KERNEL   DEFAULT
DEVELOPER   CPU_PER_SESSION                  KERNEL   DEFAULT
DEVELOPER   CPU_PER_CALL                     KERNEL   DEFAULT
DEVELOPER   LOGICAL_READS_PER_SESSION        KERNEL   DEFAULT
DEVELOPER   LOGICAL_READS_PER_CALL           KERNEL   DEFAULT
DEVELOPER   IDLE_TIME                        KERNEL   30
DEVELOPER   CONNECT_TIME                     KERNEL   DEFAULT
DEVELOPER   PRIVATE_SGA                      KERNEL   DEFAULT
DEVELOPER   FAILED_LOGIN_ATTEMPTS            PASSWORD 3
DEVELOPER   PASSWORD_LIFE_TIME               PASSWORD 30
DEVELOPER   PASSWORD_REUSE_TIME              PASSWORD 30
DEVELOPER   PASSWORD_REUSE_MAX               PASSWORD DEFAULT
DEVELOPER   PASSWORD_VERIFY_FUNCTION         PASSWORD DEFAULT
DEVELOPER   PASSWORD_LOCK_TIME               PASSWORD UNLIMITED
DEVELOPER   PASSWORD_GRACE_TIME              PASSWORD 5

16 rows selected.

SQL> drop profile developer cascade;

Profile dropped.