What is Roles in Oracle

• A role in oracle is a named groups of related privilege. Suppose the privilege CREATE TABLE,CREATE SESSION can be assigned to a role name. After assign whenever we give any access via that role the underlying privilege will be assigned.

• A role can be granted to a user or to another role.

• Within a database, each role name must be unique, different from all user names and all other
role names.

• Roles are not contained in any schema. So if the user who created the role is deleted, the role created by him remain in tact.

• Roles significantly helps to ease of administration tasks. Rather than assign a lists of privilege to a user we can create role with these privilege and just assign this role to the specified user.

• Oracle Predefined roles are,
CONNECT
RESOURCE
DBA
EXP_FULL_DATABASE
IMP_FULL_DATABASE

• In order to know all your database roles query from DBA_ROLES.

In order to know which roles are granted to each user or role in the database query from dba_role_privs view. Like,
SQL> select grantee, granted_role from dba_role_privs order by grantee;

In order to know system privileges granted to users and roles query from DBA_SYS_PRIVS.
So to know about which privileges the role CONNECT and RESOURCE contains just query,

SQL> select * from dba_sys_privs where grantee='CONNECT';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
CONNECT                        CREATE SESSION                           NO

SQL> select * from dba_sys_privs where grantee='RESOURCE';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE TYPE                              NO
RESOURCE                       CREATE PROCEDURE                         NO
RESOURCE                       CREATE CLUSTER                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE TABLE                             NO

8 rows selected.

Related Documents:
Privilege required to create a view