ORACLE DBA USER MANAGEMENT OVER ALL QUERIES

USER MANAGEMENT OVER ALL QUERIES
To create user with default tablespace, temporary tablespace and quotas.
SQL> create user olduser identified by freeman
default tablespace users_tbs
temporary tablespace temp
quota 100m on users_tbs
quota unlimited on data_tbs;
Alter User: Password and Account Management
SQL> alter user olduser password expire;
SQL> alter user olduser account lock;
SQL> alter user olduser account unlock;
Alter User: Profile
SQL> alter user olduser profile admin_profile;
Alter User: Quotas
SQL> alter user olduser quota unlimited on users;
SQL> alter user olduser quota 10000m on users;
Alter User: Roles
SQL> alter user olduser default role admin_role;
SQL> alter user olduser default role none;
SQL> alter user olduser default role all except admin_role;
SQL> alter user olduser default tablespace users;
SQL> alter user olduser temporary tablespace temp;
Drop User
SQL> drop user my_user cascade;

Grants: Object Grants
SQL> grant select on scott.my_tab to my_user;
SQL> grant insert, update, select on scott.my_tab to my_user;
SQL> grant select on scott.my_tab to my_user with grant option;
SQL> grant select on scott.my_tab to public with grant option;
Grants: System Grants
SQL> grant create table to my_user;
SQL> grant create any table to my_user with admin option;
SQL> grant all privileges to my_user with admin option;