Oracle7 Server Concepts
Roles
Oracle provides for easy and controlled privilege management through roles. Roles are named groups of related privileges that you grant to users or other roles. Roles are designed to ease the administration of end-user system and object privileges. However, roles are not meant to be used for application developers, because the privileges to access objects within stored programmatic constructs need to be granted directly. See the section "Data Definition Language Statements and Roles" ![[*]](jump.gif)
for more information about restrictions for procedures.
These properties of roles allow for easier privilege management within a database:
- Reduced privilege administration Rather than explicitly granting the same set of privileges to several users, you can grant the privileges for a group of related users to a role, and then only the role needs to be granted to each member of the group.
- Dynamic privilege management If the privileges of a group must change, only the privileges of the role need to be modified. The security domains of all users granted the group's role automatically reflect the changes made to the role.
- Selective availability of privileges You can selectively enable or disable the roles granted to a user. This allows specific control of a user's privileges in any given situation.
- Application awareness Because the data dictionary records which roles exist, you can design database applications to query the dictionary and automatically enable (and disable) selective roles when a user attempts to execute the application via a given username.
- Application-specific security You can protect role use with a password. Applications can be created specifically to enable a role when supplied the correct password. Users cannot enable the role if they do not know the password. Instructions for enabling roles from an application are included in the Oracle7 Server Application Developer's Guide.
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 18 - 1 and the sections that follow describe the two uses of roles.
Figure 18 - 1. Common Uses for Roles
Application Roles
You grant an application role all privileges necessary to run a given database application. Then, you grant an 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
The functionality of database roles includes the following:
- A role can be granted system or 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.
- 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 Add Role to User dialog box and Remove Privileges from Role dialog box of Server 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.
More detailed instructions on role management are included in the Oracle7 Server Administrator's Guide.
Who Can Grant or Revoke Roles?
Who Can Grant or Revoke Roles?
Who Can Grant or Revoke Roles?
Any user with the GRANT ANY ROLE system privilege can grant or revoke any role to or from other users or roles of the database. You should grant this system privilege conservatively because it is very powerful. Additionally, 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 a Role and a User Granted Roles
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 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 user's security domain also includes the privileges and roles granted to the user group PUBLIC. A role can be simultaneously enabled for one user and disabled for another.
Data Definition Language Statements and Roles
Depending on the statement, a user requires one or more privileges to successfully execute a DDL 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 privilege for the table or the SELECT ANY TABLE system privilege.
Oracle avoids the dependencies on privileges received via 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 object privileges that permit a user to perform a DDL operation are usable when received via a role.
Examples System Privileges: the CREATE TABLE, CREATE VIEW and CREATE PROCEDURE privileges. Object Privileges: the ALTER and INDEX privileges for a table.
Exception The REFERENCES object privilege for a table cannot be used for definition of a table's foreign key if the privilege is received via 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 via a role.
Example If a user receives the SELECT ANY TABLE system privilege or the SELECT object privilege for a table via a role, he/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 via 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
- is not directly granted the SELECT 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 either the EMP or DEPT tables.
- Although the user has both the CREATE VIEW and SELECT privilege for the EMP table (both via a role), the user cannot create a usable view on the EMP table, because the SELECT object privilege for the EMP table was granted via 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 (via 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 grants (and revokes) of database roles and/or manage their password authentication.
Additional Information: This capability might not be available on all operating systems. 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 make sure that all needed roles are set as the default roles for a distributed 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 which attempts to enable a role at the remote site. To use roles in a distributed environment, you must make the required roles the default role for the remote session. For more information about distributed database environments, see Oracle7 Server Distributed Systems, Volume I.