Oracle8 Concepts Release 8.0 A58227-01 |
Library | Product | Contents | Index

# 26
Privileges and Roles

My right and my privilege to stand here before you has been won - won in my lifetime - by the blood and the sweat of the innocent.

Jesse Jackson: Speech at the Democratic National Convention, 1988

This chapter explains how you can control users' ability to execute system operations and to access schema objects by using privileges and roles. The chapter includes:

- Privileges
  - System Privileges
  - Schema Object Privileges
- Roles

  Additional Information: If you are using Trusted Oracle, see your Trusted Oracle documentation for information about roles and privileges in that environment.

## Privileges

A privilege is a right to execute a particular type of SQL statement or to access another user's object. Some examples of privileges include the right to

- connect to the database (create a session)
- create a table
- select rows from another user's table
- execute another user's stored procedure

You grant privileges to users so these users can accomplish tasks required for their job. You should grant a privilege only to a user who absolutely requires the privilege to accomplish necessary work. Excessive granting of unnecessary privileges can compromise security. A user can receive a privilege in two different ways:

- You can grant privileges to users explicitly. For example, you can explicitly grant the privilege to insert records into the EMP table to the user SCOTT.
- You can also grant privileges to a role (a named group of privileges), and then grant the role to one or more users. For example, you can grant the privileges to select, insert, update, and delete records from the EMP table to the role named CLERK, which in turn you can grant to the users SCOTT and BRIAN.

Because roles allow for easier and better management of privileges, you should normally grant privileges to roles and not to specific users.

There are two distinct categories of privileges:

- system privileges
- schema object privileges

  Additional Information: Complete listings of all system and schema object privileges, as well as instructions for privilege management, appear in the Oracle8 Administrator's Guide.

### System Privileges

A system privilege is the right to perform a particular action, or to perform an action on any schema objects of a particular type. For example, the privileges to create tablespaces and to delete the rows of any table in a database are system privileges. There are over 60 distinct system privileges.

#### Granting and Revoking System Privileges

You can grant or revoke system privileges to users and roles. If you grant system privileges to roles, you can use the roles to manage system privileges (for example, roles permit privileges to be made selectively available).

Note: Usually, you should grant system privileges only to administrative personnel and application developers, because end users normally do not require the associated capabilities.

System privileges are granted to or revoked from users and roles using either of the following:

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

#### Who Can Grant or Revoke System Privileges?

Only users who have been granted a specific system privilege with the ADMIN OPTION or users with the GRANT ANY PRIVILEGE system privilege (typically database or security administrators) can grant or revoke system privileges to other users.

### Schema Object Privileges

A schema object privilege ("object privilege") is a privilege or right to perform a particular action on a specific table, view, sequence, procedure, function, or package. Different object privileges are available for different types of schema objects. For example, the privilege to delete rows from the table DEPT is an object privilege.

Some schema objects (such as clusters, indexes, triggers, and database links) do not have associated object privileges; their use is controlled with system privileges. For example, to alter a cluster, a user must own the cluster or have the ALTER ANY CLUSTER system privilege.

A schema object and its synonym are equivalent with respect to privileges; that is, the object privileges granted for a table, view, sequence, procedure, function, or package apply whether referencing the base object by name or using a synonym.

For example, assume there is a table JWARD.EMP with a synonym named JWARD.EMPLOYEE and the user JWARD issues the following statement:

    GRANT SELECT ON emp TO swilliams;

The user SWILLIAMS can query JWARD.EMP by referencing the table by name or using the synonym JWARD.EMPLOYEE:

    SELECT * FROM jward.emp;
    SELECT * FROM jward.employee;

If you grant object privileges on a table, view, sequence, procedure, function, or package to a synonym for the object, the effect is the same as if no synonym were used. For example, if JWARD wanted to grant the SELECT privilege for the EMP table to SWILLIAMS, JWARD could issue either of the following statements:

    GRANT SELECT ON emp TO swilliams;
    GRANT SELECT ON employee TO swilliams;

If a synonym is dropped, all grants for the underlying schema object remain in effect, even if the privileges were granted by specifying the dropped synonym.

#### Granting and Revoking Schema Object Privileges

Schema object privileges can be granted to and revoked from users and roles. If you grant object privileges to roles, you can make the privileges selectively available. Object privileges for users and roles can be granted or revoked using the SQL commands GRANT and REVOKE, respectively, or the Add Privilege to Role/User dialog box and Revoke Privilege from Role/User dialog box of Oracle Enterprise Manger.

#### Who Can Grant Schema Object Privileges?

A user automatically has all object privileges for schema objects contained in his or her schema. A user can grant any object privilege on any schema object he or she owns to any other user or role. If the grant includes the GRANT OPTION (of the GRANT command), the grantee can further grant the object privilege to other users; otherwise, the grantee can use the privilege but cannot grant it to other users.

#### Table Security Topics

Schema object privileges for tables allow table security at the level of DML and DDL operations.

##### Data Manipulation Language (DML) Operations

The DELETE, INSERT, SELECT, and UPDATE privileges allow the DELETE, INSERT, SELECT, and UPDATE DML operations, respectively, on a table or view. You should grant these privileges only to users and roles that need to query or manipulate a table's data.

Additional Information: See the Oracle8 SQL Reference for more information on these DML operations.

You can restrict INSERT and UPDATE privileges for a table to specific columns of the table. With selective INSERT, a privileged user can insert a row with values for the selected columns; all other columns receive NULL or the column's default value. With selective UPDATE, a user can update only specific column values of a row. Selective INSERT and UPDATE privileges are used to restrict a user's access to sensitive data.

For example, if you do not want data entry users to alter the SAL column of the employee table, selective INSERT and/or UPDATE privileges can be granted that exclude the SAL column. (Alternatively, a view that excludes the SAL column could satisfy this need for additional security.)

##### Data Definition Language (DDL) Operations

The ALTER, INDEX, and REFERENCES privileges allow DDL operations to be performed on a table. Because these privileges allow other users to alter or create dependencies on a table, you should grant privileges conservatively. A user attempting to perform a DDL operation on a table may need additional system or object privileges (for example, to create a trigger on a table, the user requires both the ALTER TABLE object privilege for the table and the CREATE TRIGGER system privilege).

As with the INSERT and UPDATE privileges, the REFERENCES privilege can be granted on specific columns of a table. The REFERENCES privilege enables the grantee to use the table on which the grant is made as a parent key to any foreign keys that the grantee wishes to create in his or her own tables. This action is controlled with a special privilege because the presence of foreign keys restricts the data manipulation and table alterations that can be done to the parent key. A column-specific REFERENCES privilege restricts the grantee to using the named columns (which, of course, must include at least one primary or unique key of the parent table). See Chapter 24, "Data Integrity" for more information about primary keys, unique keys, and integrity constraints.

#### View Security Topics

Schema object privileges for views allow various DML operations, which actually affect the base tables from which the view is derived. DML object privileges for tables can be applied similarly to views.

##### Privileges Required to Create Views

To create a view, you must meet the following requirements:

- You must have been granted the CREATE VIEW (to create a view in your schema) or CREATE ANY VIEW (to create a view in another user's schema) system privilege, either explicitly or through a role.
- You must have been explicitly granted the SELECT, INSERT, UPDATE, or DELETE object privileges on all base objects underlying the view or the SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, or DELETE ANY TABLE system privileges. You may not have obtained these privileges through roles.
- Additionally, in order to grant other users access to your view, you must have received object privilege(s) to the base objects with the GRANT OPTION option or appropriate system privileges with the ADMIN OPTION option. If you have not, grantees cannot access your view.

##### Increasing Table Security Using Views

To use a view, you require appropriate privileges only for the view itself. You do not require privileges on base object(s) underlying the view.

Views add two more levels of security for tables, column-level security and value-based security:

- A view can provide access to selected columns of base table(s). For example, you can define a view on the EMP table to show only the EMPNO, ENAME, and MGR columns:

      CREATE VIEW emp_mgr AS
          SELECT ename, empno, mgr FROM emp;

- A view can provide value-based security for the information in a table. A WHERE clause in the definition of a view displays only selected rows of base tables. Consider the following two examples:

      CREATE VIEW lowsal AS
          SELECT * FROM emp
          WHERE sal < 10000;

  The LOWSAL view allows access to all rows of the EMP table that have a salary value less than 10000. Notice that all columns of the EMP table are accessible in the LOWSAL view.

      CREATE VIEW own_salary AS
          SELECT ename, sal
          FROM emp
          WHERE ename = USER;

  In the OWN_SALARY view, only the rows with an ENAME that matches the current user of the view are accessible. The OWN_SALARY view uses the USER pseudocolumn, whose values always refer to the current user. This view combines both column-level security and value-based security.

#### Procedure Security Topics

The one schema object privilege for procedures (including standalone procedures and functions, and packages) is EXECUTE. You should grant this privilege only to users who need to execute a procedure.

You can use procedures to add a level of database security. A user requires only the privilege to execute a procedure and no privileges on the underlying objects that a procedure accesses. By writing a procedure and granting only EXECUTE privilege to a user, the user can be forced to access the referenced objects only through the procedure (that is, the user cannot submit ad hoc SQL statements to the database).

##### Procedure Execution and Security Domains

A user with the EXECUTE object privilege for a specific procedure can execute the procedure. A user with the EXECUTE ANY PROCEDURE system privilege can execute any procedure in the database. A user can be granted privileges through roles to execute procedures.

When you execute a procedure, it operates under the security domain of the user who owns the procedure, regardless of who is executing it. Therefore, a user does not need privileges on referenced objects to execute a procedure. Because the owner of a procedure must have the necessary object privileges for referenced objects, fewer privileges have to be granted to users of the procedure, resulting in tighter control of database access.

The current privileges of the owner of a stored procedure are always checked before the procedure is executed. If a necessary privilege on a referenced object is revoked from the owner of a procedure, the procedure cannot be executed by the owner or any other user.

Note: Trigger execution follows these same patterns. The user executes a SQL statement, which that user is privileged to execute. As a result of the SQL statement, a trigger is fired. The statements within the triggered action temporarily execute under the security domain of the user that owns the trigger.

##### System Privileges Needed to Create or Alter a Procedure

To create a procedure, a user must have the CREATE PROCEDURE or CREATE ANY PROCEDURE system privilege. To alter a procedure, that is, to manually recompile a procedure, a user must own the procedure or have the ALTER ANY PROCEDURE system privilege.

The user who owns the procedure also must hav privileges for schema objects referenced in the procedure body. To create a procedure, you must have been explicitly granted the necessary privileges (system or object) on all objects referenced by the procedure; you cannot have obtained the required privileges through roles. This includes the EXECUTE privilege for any procedures that are called inside the procedure being created.

Triggers also require that privileges to referenced objects be granted explicitly to the trigger owner. Anonymous PL/SQL blocks can use any privilege, whether the privilege is granted explicitly or via a role.

##### Packages and Package Objects

A user with the EXECUTE object privilege for a package can execute any (public) procedure or function in the package and access or modify the value of any (public) package variable. Specific EXECUTE privileges cannot be granted for a package's constructs. Therefore, you may find it useful to consider two alternatives for establishing security when developing procedures, functions, and packages for a database application. These alternatives are described in the following examples.

Example 1: This example shows four procedures created in the bodies of two packages.

    CREATE PACKAGE BODY hire_fire AS
      PROCEDURE hire(...) IS
        BEGIN
          INSERT INTO emp . . .
        END hire;
      PROCEDURE fire(...) IS
        BEGIN
          DELETE FROM emp . . .
        END fire;
    END hire_fire;

    CREATE PACKAGE BODY raise_bonus AS
      PROCEDURE give_raise(...) IS
        BEGIN
          UPDATE EMP SET sal = . . .
        END give_raise;
      PROCEDURE give_bonus(...) IS
        BEGIN
          UPDATE EMP SET bonus = . . .
        END give_bonus;
    END raise_bonus;

Access to execute the procedures is given by granting the EXECUTE privilege for the package, using the following statements:

    GRANT EXECUTE ON hire_fire TO big_bosses;
    GRANT EXECUTE ON raise_bonus TO little_bosses;

Granting EXECUTE privilege granted for a package provides uniform access to all package objects.

Example 2: This example shows four procedure definitions within the body of a single package. Two additional standalone procedures and a package are created specifically to provide access to the procedures defined in the main package.

    CREATE PACKAGE BODY employee_changes AS
      PROCEDURE change_salary(...) IS BEGIN ... END;
      PROCEDURE change_bonus(...) IS BEGIN ... END;
      PROCEDURE insert_employee(...) IS BEGIN ... END;
      PROCEDURE delete_employee(...) IS BEGIN ... END;
    END employee_changes;

    CREATE PROCEDURE hire
      BEGIN
        employee_changes.insert_employee(...)
      END hire;

    CREATE PROCEDURE fire
      BEGIN
        employee_changes.delete_employee(...)
      END fire;

    PACKAGE raise_bonus IS
      PROCEDURE give_raise(...) AS
        BEGIN
          employee_changes.change_salary(...)
        END give_raise;

      PROCEDURE give_bonus(...)
        BEGIN
          employee_changes.change_bonus(...)
        END give_bonus;

Using this method, the procedures that actually do the work (the procedures in the EMPLOYEE_CHANGES package) are defined in a single package and can share declared global variables, cursors, on so on. By declaring top-level procedures HIRE and FIRE, and an additional package RAISE_BONUS, you can grant selective EXECUTE privileges on procedures in the main package:

    GRANT EXECUTE ON hire, fire TO big_bosses;
    GRANT EXECUTE ON raise_bonus TO little_bosses;

## 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 schema object privileges. However, roles are not meant to be used for application developers, because the privileges to access schema objects within stored programmatic constructs need to be granted directly. See "Data Definition Language Statements and Roles" on page 26-14 for more information about restrictions for procedures.

These properties of roles allow for easier privilege management within a database:

reduced privilege administration | Rather than granting the same set of privileges explicitly 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 | The data dictionary records which roles exist, so you can design applications to query the dictionary and automatically enable (or disable) selective roles when a user attempts to execute the application by way of 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.

Additional Information: Instructions for enabling roles from an application are included in the Oracle8 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 26-1 and the sections that follow describe the two uses of roles.

#### Figure 26-1 Common Uses for 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.

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index