Next: Other Permissioning Problems Up: Users and Permissions Previous: Adding New Users Contents

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>

Next: Other Permissioning Problems Up: Users and Permissions Previous: Adding New Users Contents

2005-02-10