Creating Roles

SQL>select * from dba_roles;

                To see all the roles.

                connect,resource,dba.....etc are roles.

SQL>create role role1;

SQL>select * from dba_roles;

 

Grant all the privileges to a role and assign the role to the users.

 

SQL>grant create session,create table to role1;

SQL>desc role_sys_privs;

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

                To see the privileges of the roles.

SQL>grant role1 to venu;             

                Assigning the role to user.

SQL>desc dba_role_privs;

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

                To see the roles assigned to the user.

SQL>revoke role1 from venu;

SQL>conn venu/venu

                ORA-01045 : user venu lacks create session privilege : login denied