WHAT ARE THE DIFFERENT ROLES IN DATABASE ENVIRONMENT?

WHAT ARE THE DIFFERENT ROLES IN DATABASE ENVIRONMENT?

Common Uses for Roles

In general, you create a role to serve one of two purposes: to manage the privileges for a database application or to manage the privileges for a user group. Figure 26-1 and the sections that follow describe the two uses of roles.

Application Roles

You grant an application role all privileges necessary to run a given database application. Then, you grant the application role to other roles or to specific users. An application can have several different roles, with each role assigned a different set of privileges that allow for more or less data access while using the application.

User Roles

You create a user role for a group of database users with common privilege requirements. You manage user privileges by granting application roles and privileges to the user role and then granting the user role to appropriate users.

The Mechanisms of Roles

Database roles have the following functionality:

• A role can be granted system or schema object privileges.
• A role can be granted to other roles. However, a role cannot be granted to itself and cannot be granted circularly (for example, role A cannot be granted to role B if role B has previously been granted to role A).
• Any role can be granted to any database user.
• Each role granted to a user is, at a given time, either enabled or disabled. A user's security domain includes the privileges of all roles currently enabled for the user and excludes the privileges of any roles currently disabled for the user. Oracle allows database applications and users to enable and disable roles to provide selective availability of privileges.
• An indirectly granted role (a role granted to a role) can be explicitly enabled or disabled for a user. However, by enabling a role that contains other roles, you implicitly enable all indirectly granted roles of the directly granted role.

Granting and Revoking Roles

You grant or revoke roles from users or other roles using the following options:

• the Grant System Privileges/Roles dialog box and Revoke System Privileges/Roles dialog box of Oracle Enterprise Manager
• the SQL commands GRANT and REVOKE

Privileges are granted to and revoked from roles using the same options. Roles can also be granted to and revoked from users using the operating system that executes Oracle, or through network services.

Additional Information: Detailed instructions on role management are included in the Oracle8 Administrator's Guide.

Who Can Grant or Revoke Roles?

Any user with the GRANT ANY ROLE system privilege can grant or revoke any role (except a global role) to or from other users or roles of the database. You should grant this system privilege conservatively because it is very powerful.

Additional Information: See Oracle8 Distributed Database Systems for information about global roles.

Any user granted a role with the ADMIN OPTION can grant or revoke that role to or from other users or roles of the database. This option allows administrative powers for roles on a selective basis.

Naming Roles

Within a database, each role name must be unique, and no username and role name can be the same. Unlike schema objects, roles are not "contained" in any schema. Therefore, a user who creates a role can be dropped with no effect on the role.

Security Domains of Roles and Users

Each role and user has its own unique security domain. A role's security domain includes the privileges granted to the role plus those privileges granted to any roles that are granted to the role.

A user's security domain includes privileges on all schema objects in the corresponding schema, the privileges granted to the user, and the privileges of roles granted to the user that are currently enabled. (A role can be simultaneously enabled for one user and disabled for another.) A user's security domain also includes the privileges and roles granted to the user group PUBLIC.

Named PL/SQL Blocks and Roles

All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that

• is created in a user schema that does not own the object being referenced in the PL/SQL block
• can be executed as a user other than the owner of the PL/SQL block

Anonymous PL/SQL blocks, however, are executed based on privileges granted through enabled roles.

The SESSION_ROLES view shows all roles that are currently enabled. If a named PL/SQL block queries SESSION_ROLES, the query does not return any rows.

Data Definition Language Statements and Roles

A user requires one or more privileges to successfully execute a data definition language (DDL) statement, depending on the statement. For example, to create a table, the user must have the CREATE TABLE or CREATE ANY TABLE system privilege. To create a view of another user's table, the creator requires the CREATE VIEW or CREATE ANY VIEW system privilege and either the SELECT object privilege for the table or the SELECT ANY TABLE system privilege.

Oracle avoids the dependencies on privileges received by way of roles by restricting the use of specific privileges in certain DDL statements. The following rules outline these privilege restrictions concerning DDL statements:

• All system privileges and schema object privileges that permit a user to perform a DDL operation are usable when received through a role.

Examples:

• System Privileges: the CREATE TABLE, CREATE VIEW and CREATE PROCEDURE privileges.
• Schema Object Privileges: the ALTER and INDEX privileges for a table.

Exception: The REFERENCES object privilege for a table cannot be used to define a table's foreign key if the privilege is received through a role.

• All system privileges and object privileges that allow a user to perform a DML operation that is required to issue a DDL statement are not usable when received through a role.

Example: If a user receives the SELECT ANY TABLE system privilege or the SELECT object privilege for a table through a role, he or she can use neither privilege to create a view on another user's table.

The following example further clarifies the permitted and restricted uses of privileges received through roles:

Example: Assume that a user

• is granted a role that has the CREATE VIEW system privilege
• is granted a role that has the SELECT object privilege for the EMP table, but the user is indirectly granted the SELECT object privilege for the EMP table
• is directly granted the SELECT object privilege for the DEPT table

Given these directly and indirectly granted privileges:

• The user can issue SELECT statements on both the EMP and DEPT tables.
• Although the user has both the CREATE VIEW and SELECT privilege for the EMP table (both through a role), the user cannot create a usable view on the EMP table, because the SELECT object privilege for the EMP table was granted through a role. Any views created will produce errors when accessed.
• The user can create a view on the DEPT table, because the user has the CREATE VIEW privilege (through a role) and the SELECT privilege for the DEPT table (directly).

Predefined Roles

The roles CONNECT, RESOURCE, DBA, EXP_FULL_DATABASE, and IMP_FULL_DATABASE are defined automatically for Oracle databases. These roles are provided for backward compatibility to earlier versions of Oracle and can be modified in the same manner as any other role in an Oracle database.

The Operating System and Roles

In some environments, you can administer database security using the operating system. The operating system can be used to manage the granting (and revoking) of database roles and to manage their password authentication.

This capability is not available on all operating systems.

Additional Information: See your operating system-specific Oracle documentation for details on managing roles through the operating system.

Roles in a Distributed Environment

When you use roles in a distributed database environment, you must ensure that all needed roles are set as the default roles for a distributed (remote) session. You cannot enable roles when connecting to a remote database from within a local database session. For example, you cannot execute a remote procedure that attempts to enable a role at the remote site.

Additional Information: For more information about distributed database environments, see Oracle8 Distributed Database Systems.