Managing user roles - Oracle DBA

This section describes aspects of managing roles, and includes the following topics:

• Creating role.
• Predefined role.

A ROLE groups several PRIVILEGES AND ROLES, so that they can be granted and revoked simultaneously from users. Roles can be enabled and disabled per user.

Creating role:

You can create a role using either the sql command create role, or the create role property sheet of enterprise manager.
You must have the create role system privilege to create a role. Typically, only security administrators have this system privilege.
The following statement creates the clerk role, which is authorized by the database using the password bicentennial:
Create role clerk
Identified by bicentennial;

Role names

You must give each role you create a unique name among existing usernames and role names of the database. Roles are not contained in the schema of any user.

Predefined roles:

The roles listed in table are automatically defined for oracle databases. These roles are provided for backward compatibility to earlier versions of oracle. You can grant and revoke privileges and roles to these predefined roles, much the way you do with any role you define.

Predefined roles

Role name	Privileges granted to role
Connect 1	Alter session, create cluster, create database link, create sequence, create session, create synonym, create table, create view
Create type 7	Create type, execute, execute any type, admin option, grant option
Resource 1,2	Create cluster, create procedure, create sequence, create table, create trigger
Dba 1,3, 4	All system privileges with admin option
Exp_full_database 5	Select any table, backup any table, insert, delete, and update on the tables sys.incvid, sys.incfil, and sys.incexp
Imp_full_database 5	Become user
Delete_catalog_role 6	Delete privileges on all dictionary packages for this role.
Execute_catalog_role	Execute privilege on all dictionary packages for this role.
Select_catalog_role 6	Select privilege on all catalog tables and views for this role.

1. created by sql.bsq. 
2. grantees of the resource role also receive the unlimited tablespace system privilege as an  explicitly grant (not as part of the resource role). 
3. grantees of the dba role also receive the unlimited tablespace system privilege with the admin option as an explicit grant (not as part of the dba role). Therefore when the dba role is revoked, any explicit grant of unlimited tablespace is also revoked. 
4. also includes the exp_full_database and imp_full_database roles if catexp.sql has been run. 
5. created by catexp.sql. 
6. these roles must be granted to users who do not have the dba role, but require access to the views and tables in the data dictionary. 
7. the create type command is only available if the oracle objects option is installed on your database server. 
     

Checkout Oracle DBA Interview Questions

Role authorization:

 

• A database role can optionally require authorization when a user attempts to enable the role. Role authorization can be maintained by the database (using passwords), by the operating system, or by a network service.
• To alter the authorization method for a role, you must have the alter any role system privilege or have been granted the role with the admin option.

Role authorization by the database:

The use of a role can be protected by an associated password. If you are granted a role protected by a password, you can enable or disable the role only by supplying the proper password for the role in a set role command.

Role authorization by the operating system:

The following statement creates a role named accts_rec and requires that the operating system must authorize its use:

Sql>create role role identified externally;

• Role authentication via the operating system is useful only when the operating system must be able to dynamically link operating system privileges with applications. When a user starts an application, the operating system grants an operating system privilege to the user. The granted operating system privilege corresponds to the role associated with the application. At this point, the application can enable the application role. When the application is terminated, the previously granted operating system privilege is revoked from the user’s operating system account.
• If a role is authorized by the operating system, you must configure information for each user at the operating system level. This operation is operating system dependent.
• If roles are granted by the operating system, you do not need to have the operating system authorize them also; this is redundant.

Changing a role’s authorization:

You can set and change the authorization method for a role using either the alter role property sheet of enterprise manager/GUI or the sql command alter role.

The following statement alters the clerk role to be authorized externally:

Sql>alter role clerk  identified externally;

Changing a user’s default roles:

A user’s list of default roles can be set and altered using either the alter user dialog box of enterprise manager or the sql command alter user.

Using the all keyword:

If the user’s list of default roles is specified as all, every role granted to a user is automatically added to the user’s list of default roles. Only subsequent modification of a user’s default role list can remove newly granted roles from a user’s list of default roles.

Dropping roles:

• To drop a role, you must have the drop any role system privilege or have been granted the role with the admin option.
• You can drop a role using either the drop menu item of enterprise manager or the sql command drop role.

The following statement drops the role clerk:

Sql>drop role clerk;