Database Security

Creating a user:

SQL> create user test identified by testpass default tablespace users temporary tablespace temp;
SQL> grant create session to test;

Check to see what roles are granted for a user:

SQL> select * from user_role_privs;
[OR]
SQL> select * from session_roles;

SQL> conn scott/tiger;

SQL> password

[OR]

Reset to old password:

SQL> alter user uname identified by values 'GSKBHU8768BKJ';

get the value before from dba_users

ALTER USER:

Drop a user:

SQL> conn system/manager;

SQL> drop user scott;

Check to see what system privileges are granted to user:

SQL> conn scott/tiger;

SQL> select * from user_sys_privs;

Revoking system privileges:
You can revoke system privileges using REVOKE command.
Ex: revoke create table from scott;

Creating Roles:
SQL> create role dept_manager;
SQL> grant select, insert, update, delete on dept_pay to dept_manager;

Granting Roles:
SQL> grant dept_manager to scott;

Check roles granted to a user:
SQL> select * from user_role_privs;

Revoking privileges from a role:
SQL> revoke all on dept_pay from dept_manager;

Dropping a role:
SQL> drop role dept_manager;