Privileges, Roles and Profiles

At first the three can be confusing to differentiate. Lets define them first:

Privileges

A user privilege is a right to execute a particular type of SQL statement, or a right to access another user's object, execute a PL/SQL package, and so on. The types of privileges are defined by Oracle.

Roles

Roles are created by users (usually administrators) to group together privileges or other roles. They are a means of facilitating the granting of multiple privileges or roles to users.

Profiles

Profiles define resource limits imposed upon a user account. The "default" profile sets all resource limits to unlimited.

So we can assert a good ammount of control over the user here, by bundling privileges into roles and then granting those roles to user accounts, and then further controlling the resource usage of the account with a profile. In almost all cases the "default" profile will be used, so lets look at roles in more depth.

Lets list some of the predefined roles that Oracle makes avalible to us:

CONNECT

Includes the following system privileges: ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW

RESOURCE

Includes the following system privileges: CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE

DBA

All system privileges WITH ADMIN OPTION

EXP_FULL_DATABASE

Provides the privileges required to perform full and incremental database exports. Includes: SELECT ANY TABLE, BACKUP ANY TABLE, EXECUTE ANY PROCEDURE, EXECUTE ANY TYPE, ADMINISTER RESOURCE MANAGER, and INSERT, DELETE, and UPDATE on the tables SYS.INCVID, SYS.INCFIL, and SYS.INCEXP. Also the following roles: EXECUTE_CATALOG_ROLE and SELECT_CATALOG_ROLE.

IMP_FULL_DATABASE

Provides the privileges required to perform full database imports. Includes an extensive list of system privileges (use view DBA_SYS_PRIVS to view privileges) and the following roles: EXECUTE_CATALOG_ROLE and SELECT_CATALOG_ROLE.

By utilizing the data dictionary (Oracle configuration tables) we can check the current set of roles and privileges. The DBA_ROLES table contains all the roles avalible and DBA_ROLE_PRIVS contains the user-to-role mappings. If your not sure which roles are assigned to a user, check this table.

SQL> select * from DBA_ROLES;

ROLE                           PASSWORD
------------------------------ --------
CONNECT                        NO
RESOURCE                       NO
DBA                            NO
SELECT_CATALOG_ROLE            NO
EXECUTE_CATALOG_ROLE           NO
...
SQL> select * from DBA_ROLE_PRIVS;

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
BEN                            CONNECT                        NO  YES
DBA                            OLAP_DBA                       NO  YES
DBA                            XDBADMIN                       NO  YES
....

Like we used when creating the user, we can use the GRANT SQL statement to grant new roles to a user. We can also use the REVOKE statement to remove a role from the user.

SQL> grant resource to ben;
Grant succeeded.

SQL> select * from DBA_ROLE_PRIVS where grantee = 'BEN';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
BEN                            CONNECT                        NO  YES
BEN                            RESOURCE                       NO  YES

SQL> revoke resource from ben;
Revoke succeeded.

SQL> select * from DBA_ROLE_PRIVS where grantee = 'BEN';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
BEN                            CONNECT                        NO  YES

SQL>