Conn Sys as SYSDBA;
Create role TestRole;
Grant create session, CREATE TABLE, unlimited tablespace to TestRole;
Create user test identified by test;
Grant TestRole to test;
Conn Test/test;
Roles that are commonly used in Oracle:
Connect Resource
Conn Sys as SYSDBA;
Create user test identified by test;
Grant Connect, resource to test;
See which roles are in the system:
SELECT * from Dba_roles;
To view the permissions that a role has:
SELECT * from Dba_sys_privs D where d.grantee= ' RESOURCE ';
SELECT * from Dba_sys_privs D where d.grantee= ' CONNECT ';
To view the system permissions that a role has:
SELECT * from Role_sys_privs d where d.role = ' testrole ';
To view the object permissions that a role has:
SELECT * from Dba_tab_privs D where rownum < 5;
To view the object permissions that the user has:
SELECT * from Dba_tab_privs d where d.grantee = ' test ';
Roles in Oracle