1) How to create a role
Create role role_name
Create role satish_role
2) Create Password Protected Role
Create role role name identified by password
3) How to Assign Privilege To A Role
GRANT privilege_name TO role_name;
GRANT create session TO role_name
You can give grant role to role
Grant
4) How to Assigning Roles To Users
GRANT roles_name TO user_name;
5) How to Revoking Privileges From Roles
REVOKE
6) How to Revoke a role from a user
REVOKE role_name FROM user_name;
7) How Activating A Role
SET ROLE role_name;
8) How Activating A Password Protected Role
SET ROLE role_name IDENTIFIED BY role_password;
9) How to Activating All Roles
SET ROLE all;
10) How to Activating All Roles Except One
SET ROLE all EXCEPT role_name;
11) How to Deactivating A Role
Can not be done on an individual basis
12) How Deactivating All Roles
SET ROLE none;
13) How to Dropping A Role
DROP ROLE role_name;
14) How to Creating And Assigning The PLUSTRACE Role Used By AUTOTRACE
This role must be created by SYS and grants SELECT on the following v_$ views:
· V$SESSTAT
· V$STATNAME
· V$MYSTAT
SQL> @c:\oracle\product\ora10\sqlplus\admin\plustrce.sql
GRANT plustrace TO uwclass;
15) User$
In this user$ table we can see all roles in our database
16) role_sys_privs
In this role_sys_privs table we can see which Privileges Granted To A Role
17) dba_role_privs
In this dba_role_privs we can see which Roles Granted To Schemas
18) session_roles
In this session_roles we can see the present session have which roles
19) session_prives
In this session_prives table we can see the present session have which privelags
20) dba_roles
In this dba_roles table we can see the dba user contain which roles
21) dba_sys_privs
In this dba_sys_privs table we can see all the system privileges
ORA-01045) this error will be come due to insufficient privs
all_tab_privs_made in this table you can see all tables privs
user_tab_privs_made