Oracle7 Server Administrator's Guide

Contents Index Home Previous Next

Managing User Roles

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

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.

See Also: For information about roles, see the Oracle7 Server Concepts manual.

Creating a Role

You can create a role using either the SQL command CREATE ROLE, or the Create Role property sheet of Server Manager.

You must have the CREATE ROLE system privilege to create a role. Typically, only security administrators have this system privilege.

Note: Immediately after creation, a role has no privileges associated with it. To associate privileges with a new role, you must grant privileges or other roles to the new role.

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.

Role Names in Multi-Byte Character Sets

In a database that uses a multi-byte character set, Oracle Corporation recommends that each role name contain at least one single-byte character. If a role name contains only multi-byte characters, the encrypted role name/password combination is considerably less secure.

Predefined Roles

The roles listed in Table 20 - 4 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.

Role Name Privileges Granted To Role
CONNECT1 ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW
RESOURCE1,2 CREATE CLUSTER, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER
DBA1,3,4 All system privileges WITH ADMIN OPTION
EXP_FULL_DATABASE5 SELECT ANY TABLE, BACKUP ANY TABLE, INSERT, DELETE, AND UPDATE ON THE TABLES SYS.INCVID, SYS.INCFIL, AND SYS.INCEXP
IMP_FULL_DATABASE5 BECOME USER, WRITEDOWN6
Table 20 - 4. Predefined Roles

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 A Trusted Oracle7 privilege only; see the Trusted Oracle7 Server Administrator's Guide.

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.

See Also: For more information about network roles, see Oracle7 Server Distributed Systems, Volume I.

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.

Note: In a database that uses a multi-byte character set, passwords for roles must include only single-byte characters. Multi-byte characters are not accepted in passwords.

See Also: For more information about valid passwords, see the Oracle7 Server Reference.

Role Authorization by the Operating System

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

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.

See Also: For more information about roles granted by the operating system, see [*].

Role Authorization and Network Clients

If users connect to the database over SQL*Net, by default their roles cannot be authenticated by the operating system. This includes connections through a multi-threaded server, as this connection requires SQL*Net. This restriction is the default because a remote user could impersonate another operating system user over a network connection.

If you are not concerned with this security risk and want to use operating system role authentication for network clients, set the parameter REMOTE_OS_ROLES in the database's parameter file to TRUE. The change will take effect the next time you start the instance and mount the database. (The parameter is FALSE by default.)

Witholding Authorization

A role can also be created without authorization. If a role is created without any protection, the role can be enabled or disabled by any grantee.

Changing a Role's Authorization

You can set and change the authorization method for a role using either the Alter Role property sheet of Server Manager/GUI or the SQL command ALTER ROLE.

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

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 Server Manager or the SQL command ALTER USER.

See Also: See "Altering Users" [*] for more information about these options.

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. Using the MAX_ENABLED_ROLES Parameter A user can enable as many roles as specified by the initialization parameter MAX_ENABLED_ROLES. All indirectly granted roles enabled as a result of enabling a primary role are included in this count. The database administrator can alter this limitation by modifying the value for this parameter. Higher values permit each user session to have more concurrently enabled roles. However, the larger the value for this parameter, the more memory space is required on behalf of each user session; this is because the PGA size is affected for each user session, and requires four bytes per role. Determine the highest number of roles that will be concurrently enabled by any one user and use this value for the MAX_ENABLED_ROLES parameter.

Dropping Roles

In some cases, it may be applicable to drop a role from the database. The security domains of all users and roles granted a dropped role are immediately changed to reflect the absence of the dropped role's privileges. All indirectly granted roles of the dropped role are also removed from affected security domains. Dropping a role automatically removes the role from all users' default role lists.

Because the creation of objects is not dependent on the privileges received via a role, tables and other objects are not dropped when a role is dropped.

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 Server Manager, or the SQL command DROP ROLE.

The following statement drops the role CLERK:

DROP ROLE clerk;

Contents Index Home Previous Next