4 Configuring Privilege and Role Authorization

About Administrative Privileges

For better separation of duty, Oracle Database provides a set of administrative privileges that are tailored for specific administrative tasks: backup and recovery, Oracle Data Guard, and encryption key management for transparent data encryption management (TDE).

You can find the administrative privileges that a user has by querying the V$PWFILE_USERS dynamic view, which lists users in the password file.

In previous releases, you needed to have the SYSDBA administrative privilege to perform these tasks. To support backward compatibility, you still can use the SYSDBA privilege for these tasks, but Oracle recommends that you use the administrative privileges described in this section.

The use of administrative privileges is mandatorily audited. See "Auditing Administrative Users" for more information.

Grants of Administrative Privileges to Users

As with all powerful privileges, only grant administrative privileges to trusted users.

However, be aware that there is a restriction for users whose names have non-ASCII characters (for example, the umlaut in the name HÜBER). You can grant administrative privileges to these users, but if the Oracle database instance is down, the authentication using the granted privilege is not supported if the user name has non-ASCII characters. If the database instance is up, then the authentication is supported.

SYSDBA and SYSOPER Administrative Privileges for Standard Database Operations

The SYSDBA and SYSOPER administrative privileges enable you to perform a variety of standard database operations.

These database operations can include tasks such as database startups and shutdowns, creating the server parameter file (SPFILE), or altering the database archive log.

SYSBACKUP Administrative Privilege for Backup and Recovery Operations

To perform backup and recovery operations from either Oracle Recovery Manager (RMAN) and or through SQL*Plus, log in with the SYSBACKUP administrative privilege.

To connect to the database as SYSBACKUP using a password, you must create a password file for it. See Oracle Database Administrator's Guide for more information about creating password files.

This privilege enables you to perform the following operations:

• STARTUP

• SHUTDOWN

• ALTER DATABASE

• ALTER SYSTEM

• ALTER SESSION

• ALTER TABLESPACE

• CREATE CONTROLFILE

• CREATE ANY DIRECTORY

• CREATE ANY TABLE

• CREATE ANY CLUSTER

• CREATE PFILE

• CREATE RESTORE POINT (including GUARANTEED restore points)

• CREATE SESSION

• CREATE SPFILE

• DROP DATABASE

• DROP TABLESPACE

• DROP RESTORE POINT (including GUARANTEED restore points)

• FLASHBACK DATABASE

• RESUMABLE

• UNLIMITED TABLESPACE

• SELECT ANY DICTIONARY

• SELECT ANY TRANSACTION

• SELECT

  • X$ tables (that is, the fixed tables)

  • V$ and GV$ views (that is, the dynamic performance views)

  • APPQOSSYS.WLM_CLASSIFIER_PLAN

  • SYSTEM.LOGSTDBY$PARAMETERS

• DELETE/INSERT

  • SYS.APPLY$_SOURCE_SCHEMA

  • SYSTEM.LOGSTDBY$PARAMETERS

• EXECUTE

  • SYS.DBMS_BACKUP_RESTORE

  • SYS.DBMS_RCVMAN

  • SYS.DBMS_DATAPUMP

  • SYS.DBMS_IR

  • SYS.DBMS_PIPE

  • SYS.SYS_ERROR

  • SYS.DBMS_TTS

  • SYS.DBMS_TDB

  • SYS.DBMS_PLUGTS

  • SYS.DBMS_PLUGTSP

• SELECT_CATALOG_ROLE

In addition, the SYSBACKUP privilege enables you to connect to the database even if the database is not open.

SYSDG Administrative Privilege for Oracle Data Guard Operations

You can log in as user SYSDG with the SYSDG administrative privilege to perform Data Guard operations.

You can use this privilege with either Data Guard Broker or the DGMGRL command-line interface. In order to connect to the database as SYSDG using a password, you must create a password file for it. See Oracle Database Administrator's Guide for more information about creating password files.

The SYSDG privilege enables the following operations:

• STARTUP

• SHUTDOWN

• ALTER DATABASE

• ALTER SESSION

• ALTER SYSTEM

• CREATE RESTORE POINT (including GUARANTEED restore points)

• CREATE SESSION

• DROP RESTORE POINT (including GUARANTEED restore points)

• FLASHBACK DATABASE

• SELECT ANY DICTIONARY

• SELECT

  • X$ tables (that is, the fixed tables)

  • V$ and GV$ views (that is, the dynamic performance views)

  • APPQOSSYS.WLM_CLASSIFIER_PLAN

• DELETE

  • APPQOSSYS.WLM_CLASSIFIER_PLAN

• EXECUTE

  • SYS.DBMS_DRS

In addition, the SYSDG privilege enables you to connect to the database even if it is not open.

SYSKM Administrative Privilege for Transparent Data Encryption

The SYSKM administrative privilege enables the SYSKM user to manage Transparent Data Encryption wallet operations.

In order to connect to the database as SYSKM using a password, you must create a password file for it. See Oracle Database Administrator's Guide for more information about creating password files.

The SYSKM administrative privilege enables the following operations:

• ADMINISTER KEY MANAGEMENT

• CREATE SESSION

• SELECT (only when database is open)

  • SYS.V$ENCRYPTED_TABLESPACES

  • SYS.V$ENCRYPTION_WALLET

  • SYS.V$WALLET

  • SYS.V$ENCRYPTION_KEYS

  • SYS.V$CLIENT_SECRETS

  • SYS.DBA_ENCRYPTION_KEY_USAGE

In addition, the SYSKM privilege enables you to connect to the database even if it is not open.

About 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 100 distinct system privileges. Each system privilege allows a user to perform a particular database operation or class of database operations. Remember that system privileges are very powerful. Only grant them when necessary to roles and trusted users of the database. You can find a complete list of system privileges and their descriptions in Oracle Database SQL Language Reference. To find the system privileges that have been granted to a user, you can query the DBA_SYS_PRIVS data dictionary view.

Why Is It Important to Restrict System Privileges?

System privileges are very powerful, so you must only grant them to trusted users. You should also secure the data dictionary and restrict objects in the SYS schema.

Topics:

• About the Importance of Restricting System Privileges

• Restriction of System Privileges by Securing the Data Dictionary

• User Access to Objects in the SYS Schema

ALTER SYSTEM SET O7_DICTIONARY_ACCESSIBILITY=FALSE SCOPE=SPFILE;

Grants and Revokes of System Privileges

You can grant or revoke system privileges to users and roles.

If you grant system privileges to roles, then you can use the roles to exercise system privileges. For example, roles permit privileges to be made selectively available. Ensure that you follow the separation of duty guidelines described in "Guidelines for Securing Roles".

Use either of the following methods to grant or revoke system privileges to or from users and roles:

• GRANT and REVOKE SQL statements

• Oracle Enterprise Manager Cloud Control

Who Can Grant or Revoke System Privileges?

Only two types of users can grant system privileges to other users or revoke those privileges from them.

• Users who were granted a specific system privilege with the ADMIN OPTION

• Users with the system privilege GRANT ANY PRIVILEGE

For this reason, only grant these privileges to trusted users.

About ANY Privileges and the PUBLIC Role

System privileges that use the ANY keyword enable you to set privileges for an entire category of objects in the database.

For example, the CREATE ANY PROCEDURE system privilege permits a user to create a procedure anywhere in the database. The behavior of an object created by users with the ANY privilege is not restricted to the schema in which it was created. For example, if user JSMITH has the CREATE ANY PROCEDURE privilege and creates a procedure in the schema JONES, then the procedure will run as JONES. However, JONES may not be aware that the procedure JSMITH created is running as him (JONES). If JONES has DBA privileges, letting JSMITH run a procedure as JONES could pose a security violation.

The PUBLIC role is a special role that every database user account automatically has when the account is created. By default, it has no privileges granted to it, but it does have numerous grants, mostly to Java objects. You cannot drop the PUBLIC role, and a manual grant or revoke of this role has no meaning, because the user account will always assume this role. Because all database user accounts assume the PUBLIC role, it does not appear in the DBA_ROLES and SESSION_ROLES data dictionary views.

You can grant privileges to the PUBLIC role, but remember that this makes the privileges available to every user in the Oracle database. For this reason, be careful about granting privileges to the PUBLIC role, particularly powerful privileges such as the ANY privileges and system privileges. For example, if JSMITH has the CREATE PUBLIC SYNONYM system privilege, he could redefine an interface that he knows everyone else uses, and then point to it with the PUBLIC SYNONYM that he created. Instead of accessing the correct interface, users would access the interface of JSMITH, which could possibly perform illegal activities such as stealing the login credentials of users.

These types of privileges are very powerful and could pose a security risk if given to the wrong person. Be careful about granting privileges using ANY or PUBLIC. As with all privileges, you should follow the principles of "least privilege" when granting these privileges to users.

To protect the data dictionary (the contents of the SYS schema) against users who have one or more of the powerful ANY system privileges, set the O7_DICTIONARY_ACCESSIBILITY initialization parameter to FALSE. You can set this parameter by using an ALTER SYSTEM statement or by modifying the initSID.ora file.

About Commonly and Locally Granted Privileges

In a multitenant environment, both common users and local users can grant privileges to one another. Privileges by themselves are neither common nor local. How the privileges are applied depends on whether the privilege is granted commonly or granted locally.

For commonly granted privileges:

• A privilege that is granted commonly can be used in every existing and future container.

• Only common users can grant privileges commonly, and only if the grantee is common.

• A common user can grant privileges to another common user or to a common role.

• The grantor must be connected to the root and must specify CONTAINER=ALL in the GRANT statement.

• Both system and object privileges can be commonly granted. (Object privileges become actual only with regard to the specified object.)

• When a common user connects to or switches to a given container, this user's ability to perform various activities (such as creating a table) is controlled by both the commonly granted and locally granted privileges this user has.

• Do not grant privileges to PUBLIC commonly.

For locally granted privileges:

• A privilege granted locally can be used only in the container in which it was granted. When the privilege is granted in the root, it applies only to the root.

• Both common users and local users can grant privileges locally.

• A common user and a local user can grant privileges to other common or local roles.

• The grantor must be connected to the container and must specify CONTAINER=CURRENT in the GRANT statement.

• Any user can grant a privilege locally to any other user or role (both common and local) or to the PUBLIC role.

How Commonly Granted System Privileges Work

Users can exercise system privileges only within the PDB in which they were granted. For example, if a system privilege is locally granted to a common user A in a PDB B, user A can exercise that privilege only while connected to PDB B.

System privileges can apply in the root and in all existing and future PDBs if the following requirements are met:

• The system privilege grantor is a common user and the grantee is a common user, a common role, or the PUBLIC role. Do not commonly grant system privileges to the PUBLIC role, because this in effect makes the system privilege available to all users.

• The system privilege grantor possesses the ADMIN OPTION for the commonly granted privilege

• The GRANT statement must contain the CONTAINER=ALL clause.

Example 4-2 shows how to perform a common privilege grant for the common user c##hr_admin so that he can create tables in any PDB in the CDB.

CONNECT SYSTEM@root 
Enter password: password
Connected.

GRANT CREATE ANY TABLE TO c##hr_admin CONTAINER=ALL;

How Commonly Granted Object Privileges Work

An object privilege on a common object applies only to that object as well as all metadata links or object links that are associated with it in the root and in all PDBs to which the grantor can connect (including future PDBs) if the following requirements are met:

• The object privilege grantor is a common user and the grantee is a common user, a common role, or the PUBLIC role.

• The object privilege grantor possesses the commonly granted GRANT OPTION for the commonly granted privilege

• The GRANT statement contains the CONTAINER=ALL clause.

Example 4-3 shows how to grant an object privilege to the common user c##hr_admin so that he can select from the user_data table in the current PDB.

CONNECT SYSTEM@hr_pdg
Enter password: password
Connected.

GRANT READ ON user_data TO c##hr_admin CONTAINER=CURRENT;

Granting or Revoking Privileges to Access a PDB

To grant a privilege in a multitenant environment, you should include the CONTAINER clause in the GRANT or REVOKE statement.

Setting CONTAINER to ALL applies the privilege to all existing and future containers; setting it to CURRENT applies the privilege to the local container only. Except for the root, omitting the CONTAINER clause applies the privilege to the local container. If you issue the GRANT statement from the root and omit the CONTAINER clause, then the privilege is applied locally.

Example 4-4 shows how to commonly grant the CREATE TABLE privilege to common user c##hr_admin so that this user can use this privilege in all existing and future containers.

CONNECT SYSTEM@root
Enter password: password
Connected.

GRANT CREATE TABLE TO c##hr_admin CONTAINER=ALL;

Enabling Common Users to View Information About Container Objects

Common users can view information about container objects in the root or for data in specific PDBs.

Topics:

• Viewing Data Pertaining to Root, CDB, and PDBs While Connected to Root

• Enabling Common Users to Query Data in Specific PDBs

COLUMN USERNAME FORMAT A15
COLUMN DEFAULT_ATTR FORMAT A7
COLUMN OWNER FORMAT A15
COLUMN OBJECT_NAME FORMAT A15
COLUMN ALL_CONTAINERS FORMAT A3
COLUMN CONTAINER_NAME FORMAT A10
COLUMN CON_ID FORMAT A6

SELECT USERNAME, DEFAULT_ATTR, OWNER, OBJECT_NAME, ALL_CONTAINERS, CONTAINER_NAME, CON_ID FROM CDB_CONTAINER_DATA ORDER BY OBJECT_NAME;

USERNAME        DEFAULT OWNER           OBJECT_NAME     ALL CONTAINERS CON_ID
--------------- ------- --------------- --------------- --- ---------- ------
C##HR_ADMIN     N       SYS             V$SESSION       N   CDB$ROOT        1
C##HR_ADMIN     N       SYS             V$SESSION       N   SALESPDB        1
C##HR_ADMIN     Y                                       N   HRPDB           1
C##HR_ADMIN     Y                                       N   CDB$ROOT        1
DBSNMP          Y                                       Y                   1
SYSTEM          Y                                       Y                   1

CONNECT SYSTEM@root
Enter password: password
Connected.

ALTER USER c##hr_admin
SET CONTAINER_DATA = (CDB$ROOT, SALESPDB, HRPDB) 
FOR V$SESSION CONTAINER=CURRENT;

About Common Roles and Local Roles

In a multitenant environment, a common role role is a role whose identity and password are created in the root and is known in all existing and future containers. All Oracle-supplied predefined roles are common roles.

A local role exists in only one PDB and can only be used within this PDB. It does not have any commonly granted privileges.

Note the following:

• Common users can both create and grant common roles to other common and local users.

• You can grant a common role to a common user either commonly or locally.

• If you grant a common role to a local user, then the privileges of that common role apply only to the local user's PDB.

• Local users cannot create common roles, but they can grant them to common and other local users.

How Common Roles Work

Common roles are visible in every PDB in a multitenant environment. Commonly granted privileges that have been made to the common role apply in the root and all PDBs to which the grantor can connect, including PDBs that may be added later on, if the following requirements are met:

• Both the grantor and the grantee are common users.

• The grantor possesses the ADMIN OPTION for the common role that was granted.

• The GRANT statement contains the CONTAINER=ALL clause.

If the common role contains locally granted privileges, then these privileges apply only within the PDB in which they were granted to the common role. A local role cannot be granted commonly.

For example, suppose the common user c##hr_mgr has been commonly granted the DBA role. This means that user c##hr_mgr can use the privileges associated with the DBA role in the root and in every PDB in the multitenant environment. However, if the common user c##hr_mgr has only been locally granted the DBA role for the hr_pdb PDB, then this user can only use the DBA role's privileges in the hr_pdb PDB.

How the PUBLIC Role Works in a Multitenant Environment

All grants that Oracle makes to the PUBLIC role are made locally to the PDB.

This feature enables you to revoke privileges or roles that have granted to the PUBLIC role individually in each PDB as needed. If you must grant any privileges to the PUBLIC role, then grant them locally. Never grant privileges to PUBLIC commonly.

Privileges Required to Create, Modify, or Drop a Common Role

Only common users who have the commonly granted CREATE ROLE, ALTER ROLE, and DROP ROLE privileges can create, alter, or drop common roles.

Common users can create local roles, too, but these roles are available only in the PDB in which they were created.

Rules for Creating Common Roles

When you create the common role, you must follow special rules.

• Ensure that you are in the root. You cannot create common roles from a PDB. To check if you are in the root, run the show con_name command. The output should be CDB$ROOT.

• Ensure that the name that you give the common role starts with C## or c## and contains only ASCII or EDCDIC characters. Note that this requirement does not apply to the names of existing Oracle-supplied roles, such as DBA or RESOURCE.

• Optionally, set the CONTAINER clause to ALL. As long as you are in the root, if you omit the CONTAINER = ALL clause, then by default the role is created as a common role.

Creating a Common Role

You can use the CREATE USER statement to create a common role,.

1. Connect to the root of the CDB in which you want to create the common role.

   For example:

   CONNECT SYSTEM@root
   Enter password: password
   Connected.
   

2. Run the CREATE ROLE statement with the CONTAINER clause set to ALL.

   For example:

   CREATE ROLE c##sec_admin IDENTIFIED BY password CONTAINER=ALL; 
   

Rules for Creating Local Roles

To create a local role, you must follow special rules.

• To create a local role, you must be connected to the PDB in which you want to create the role, and have the CREATE ROLE privilege.

• The name that you give the local role must not start with C## or c##.

• You can include CONTAINER=CURRENT in the CREATE ROLE statement to specify the role as a local role. If you are connected to a PDB and omit this clause, then the CONTAINER=CURRENT clause is implied.

• You cannot have common roles and local roles with the same name. However, you can use the same name for local roles in different PDBs. To find the names of existing roles, query the CDB_ROLES and DBA_ROLES data dictionary views.

Creating a Local Role

You can use the CREATE ROLE statement to create a role.

1. Connect to the PDB in which you want to create the local role.

   For example:

   CONNECT SYSTEM@hrpdb
   Enter password: password
   Connected.
   

2. Run the CREATE ROLE statement with the CONTAINER clause set to CURRENT.

   For example:

   CREATE ROLE sec_admin CONTAINER=CURRENT;
   

Grants to or Revokes from Common Roles and Local Roles

Common users can grant and revoke common roles to and from other common users. A local user can grant a common role to any user in a PDB, including common users, but this grant applies only within the PDB.

Example 4-7 shows how to grant the common user c##sec_admin the AUDIT_ADMIN common role for use in all containers.

CONNECT SYSTEM@root
Enter password: password
Connected.

GRANT AUDIT_ADMIN TO c##sec_admin CONTAINER=ALL;

Similarly, Example 4-8 shows how local user aud_admin can grant the common user c##sec_admin the AUDIT_ADMIN common role for use within the hrpdb PDB.

CONNECT aud_admin@hrpdb
Enter password: password
Connected.

GRANT AUDIT_ADMIN TO c##sec_admin CONTAINER=CURRENT;

Example 4-9 shows how a local user aud_admin can revoke a role from a PDB. If you omit the CONTAINER clause, then CURRENT is implied.

CONNECT aud_admin@hrpdb
Enter password: password
Connected.

REVOKE sec_admin FROM psmith CONTAINER=CURRENT;

About User Roles

A user role, which is a named set of privileges that you can grant to other users, provide many advantages to user management. Roles can affect the scope of a user's privileges, work with PL/SQL blocks, and aid or restrict DDL usage. Operating systems can help roles, and roles work in specific ways in a distributed environment.

Topics:

• What Are User Roles?

• The Functionality of Roles

• Properties of Roles and Why They Are Advantageous

• Typical Uses of Roles

• How Roles Affect the Scope of a User's Privileges

• How Roles Work in PL/SQL Blocks

• How Roles Aid or Restrict DDL Usage

• How Operating Systems Can Aid Roles

• How Roles Work in a Distributed Environment

Typical Uses of Roles

In general, you create a role to serve one of two purposes.

• To manage the privileges for a database application (see "Common Uses of Application Roles")

• To manage the privileges for a user group (see "Common Uses of User Roles")

Figure 4-1 and the sections that follow describe the two uses of roles.

Figure 4-1 Common Uses for Roles

Description of "Figure 4-1 Common Uses for Roles"

Common Uses of Application Roles

Grant an application role all privileges necessary to run a given database application. Then, grant the secure 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.

Common Uses of User Roles

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

Predefined Roles in an Oracle Database Installation

Oracle Database provides a set of predefined roles to help in database administration.

These predefined roles, listed in Table 4-3, are automatically defined for Oracle databases when you run the standard scripts that are part of database creation, and they are considered common roles. If you install other options or products, then other predefined roles may be created.

Creating a Role

You can create a role that is authenticated with or without a password. You also can create external or global roles. After you create a role, you can alter it.

Topics:

• About the Creation of Roles

• Creating a Role That Is Authenticated With a Password

• Creating a Role That Has No Password Authentication

• Creating a Role That Is External or Global

• Altering a Role

Specifying the Type of Role Authorization

You can configure a role to be authorized through the database, an application, an external source, the operating system, a network client, or through an enterprise directory service.

Topics:

• Authorization of a Role by Using the Database

• Authorizing a Role by Using an Application

• Authorizing a Role by Using an External Source

• Authorization a Role by Using the Operating System

• Authorization a Role by Using a Network Client

• Authorizing a Global Role by an Enterprise Directory Service

SET ROLE clerk IDENTIFIED BY password;

Granting and Revoking Roles

You can grant privileges to roles, and then grant these roles to users or to other roles. Similarly, you can revoke privileges from roles and users.

Topics:

• About Granting and Revoking Roles

• Who Can Grant or Revoke Roles?

• Granting and Revoking Roles to and from Program Units

GRANT clerk_admin TO procedure psmith.checkstats_proc;

GRANT clerk_admin TO package psmith.checkstats_pkg;

REVOKE clerk_admin FROM package psmith.checkstats_pkg;

Dropping Roles

When you drop a role, the security domains of all users and roles granted a dropped role are immediately changed to reflect the absence of the dropped role 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 user default role lists.

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

You can drop a role using the SQL statement DROP ROLE. To drop a role, you must have the DROP ANY ROLE system privilege or have been granted the role with the ADMIN option.

The following statement drops the role CLERK:

DROP ROLE clerk;

Restricting SQL*Plus Users from Using Database Roles

You should be aware of possible security problems that may arise from users using their roles in SQL*Plus, how you can use the PRODUCT_USER_PROFILE system table to limit roles, and how stored procedures can encapsulate business logic.

Topics:

• Potential Security Problems of Using Ad Hoc Tools

• How the PRODUCT_USER_PROFILE System Table Can Limit Roles

• How Stored Procedures Can Encapsulate Business Logic

Securing Role Privileges by Using Secure Application Roles

A secure application role is a role that can be enabled only by an authorized PL/SQL package (or procedure). The PL/SQL package itself reflects the security policies needed to control access to the application.

This method of role creation restricts the enabling of this type of role to the invoking application. For example, the application can perform authentication and customized authorization, such as checking whether the user has connected through a proxy.

This type of role strengthens security because passwords are not embedded in application source code or stored in a table. This way, the actions the database performs are based on the implementation of your security policies, and these definitions are stored in one place, the database, rather than in your applications. If you need to modify the policy, you do so in one place without having to modify your applications. No matter how users connect to the database, the result is always the same, because the policy is bound to the role.

To enable the secure application role, you must execute its underlying package by invoking it directly from the application when the user logs in, before the user exercises the privileges granted by the secure application role. You cannot use a logon trigger to enable a secure application role, nor can you have this type of role be a default role.

When you enable the secure application role, Oracle Database verifies that the authorized PL/SQL package is on the calling stack, that is, it verifies that the authorized PL/SQL package is issuing the command to enable the role.

You can use secure application roles to ensure the existence of a database connection. Because a secure application role is a role implemented by a package, the package can validate that users can connect to the database through a middle tier or from a specific IP address. In this way, the secure application role prevents users from accessing data outside an application. They are forced to work within the framework of the application privileges that they have been granted.

About Object Privileges

An object privilege grants permission to perform a particular action on a specific schema object.

Different object privileges are available for different types of schema objects. The privilege to delete rows from the departments table is an example of 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.

Some examples of object privileges include the right to:

• Use an edition

• Update a table

• Select rows from another user's table

• Execute a stored procedure of another user

Who Can Grant Object Privileges?

A user automatically has all object privileges for schema objects contained in his or her schema.

A user with the GRANT ANY OBJECT PRIVILEGE system privilege can grant any specified object privilege to another user with or without the WITH GRANT OPTION clause of the GRANT statement. A user with the GRANT ANY OBJECT PRIVILEGE privilege can also use that privilege to revoke any object privilege that was granted either by the object owner or by some other user with the GRANT ANY OBJECT PRIVILEGE privilege.

If the grantee does not have the GRANT ANY OBJECT PRIVILEGE privilege or had been granted the privilege without the WITH GRANT OPTION clause of the GRANT statement, then this user cannot grant the privilege to other users.

The WITH GRANT OPTION can be used only with object privilege grants to users. It cannot be used for object privilege grants to roles.

Grants and Revokes of Object Privileges

You can grant privileges to or revoke privileges from objects either directly to a user or through roles. The ALL clause in the GRANT or REVOKE statement affects all objects.

Topics:

• About Granting and Revoking Object Privileges

• How the ALL Clause Grants or Revokes All Available Object Privileges

REVOKE ALL 
 ON ORDERS FROM HR
 CASCADE CONSTRAINTS;

READ and SELECT Object Privileges

The READ privilege enables users to perform queries only on a table or a view. The SELECT privilege provides users with extra privileges, in addition to the ability to query tables and views.

Topics:

• About Managing READ and SELECT Object Privileges

• The READ Object Privilege for Any Table in the Database

• Restrictions on the READ and READ ANY TABLE Privileges

GRANT READ ANY TABLE TO psmith;

Using Object Privileges with Synonyms

You can use the CREATE SYNONYM statement to create synonyms for tables, views, sequences, operators, procedures, stored functions, packages, materialized views, Java class schema objects, user-defined object types, or other synonyms.

If you grant users the privilege to use the synonym, then the object privileges granted on the underlying objects apply whether the user references the base object by name or by using the synonym.

For example, suppose user OE creates the following synonym for the CUSTOMERS table:

CREATE SYNONYM customer_syn FOR CUSTOMERS;

Then OE grants the READ privilege on the customer_syn synonym to user HR.

GRANT READ ON customer_syn TO HR;

User HR then tries either of the following queries:

SELECT COUNT(*) FROM OE.customer_syn;

SELECT COUNT(*) FROM OE.CUSTOMERS;

Both queries will yield the same result:

  COUNT(*)
----------
       319

Be aware that when you grant the synonym to another user, the grant applies to the underlying object that the synonym represents, not to the synonym itself. For example, if user HR queries the ALL_TAB_PRIVS data dictionary view for his privileges, he will learn the following:

SELECT TABLE_SCHEMA, TABLE_NAME, PRIVILEGE 
FROM ALL_TAB_PRIVS 
WHERE TABLE_SCHEMA = 'OE';

TABLE_SCHEMA  TABLE_NAME  PRIVILEGE
------------  ----------  ------------------
OE            CUSTOMER    READ
OE            OE          INHERIT PRIVILEGES

The results show that in addition to other privileges, he has the READ privilege for the underlying object of the customer_syn synonym, which is the OE.CUSTOMER table.

At this point, if user OE then revokes the READ privilege on the customer_syn synonym from HR, here are the results if HR checks his privileges again:

TABLE_SCHEMA  TABLE_NAME  PRIVILEGE
------------  ----------  ------------------
OE            OE          INHERIT PRIVILEGES

User HR no longer has the READ privilege for the OE.CUSTOMER table. If he tries to query the OE.CUSTOMERS table, then the following error appears:

SELECT COUNT(*) FROM OE.CUSTOMERS;

ERROR at line 1:
ORA-00942: table or view does not exist

How Table Privileges Affect Data Manipulation Language Operations

You can grant privileges to use the DELETE, INSERT, SELECT, and UPDATE DML operations on a table or view. Grant these privileges only to users and roles that need to query or manipulate data in a table.

You can restrict INSERT and UPDATE privileges for a table to specific columns of the table. With a selective INSERT privilege, a privileged user can insert a row with values for the selected columns. All other columns receive NULL or the default value of the column. With a selective UPDATE privilege, a user can update only specific column values of a row. You can use selective INSERT and UPDATE privileges to restrict user access to sensitive data.

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

How Table Privileges Affect Data Definition Language 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 these 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, you can grant the REFERENCES privilege 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).

Privileges Required to Create Views

To create a view, you must meet the a set of requirements.

• You must have been granted one of the following system privileges, either explicitly or through a role:

  • The CREATE VIEW system privilege (to create a view in your schema)

  • The CREATE ANY VIEW system privilege (to create a view in the schema of another user)

• You must have been explicitly granted one of the following privileges:

  • The SELECT, INSERT, UPDATE, or DELETE object privileges on all base objects underlying the view

  • The SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, or DELETE ANY TABLE system privileges

• In addition, before you can grant other users access to you view, you must have object privileges to the base objects with the GRANT OPTION clause or appropriate system privileges with the ADMIN OPTION clause. If you do not have these privileges, then you cannot to grant other users access to your view. If you try, an ORA-01720: grant option does not exist for object_name error is raised, with object_name referring to the view's underlying object for which you do not have the sufficient privilege.

  See Also:

  Oracle Database SQL Language Reference

Increasing Table Security with Views

To use a view, the user must have the appropriate privileges but only for the view itself, not its underlying objects. However, if access privileges for the underlying objects of the view are removed, then the user no longer has access.

This behavior occurs because the security domain that is used when a user queries the view is that of the definer of the view. If the privileges on the underlying objects are revoked from the view's definer, then the view becomes invalid, and no one can use the view. Therefore, even if a user has been granted access to the view, the user may not be able to use the view if the definer's rights have been revoked from the view's underlying objects.

For example, suppose User A creates a view. User A has definer's rights on the underlying objects of the view. User A then grants the SELECT privilege on that view to User B so that User B can query the view. But if User A no longer has access to the underlying objects of that view, then User B no longer has access either.

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

• A view can provide access to selected columns of base tables. For example, you can define a view on the employees table to show only the employee_id, last_name, and manager_id columns:

  CREATE VIEW employees_manager AS 
      SELECT last_name, employee_id, manager_id FROM employees; 
  

• 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 employees 
      WHERE salary < 10000; 
  

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

  CREATE VIEW own_salary AS 
      SELECT last_name, salary 
      FROM employees 
      WHERE last_name = USER; 
  

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

Use of the EXECUTE Privilege for Procedure Privileges

The EXECUTE privilege is the only object privilege for procedures, including standalone procedures and functions, and for those within packages.

Grant this privilege only to users who need to run a procedure or to compile another procedure that calls a desired procedure.

Procedure Execution and Security Domains

A user with the EXECUTE object privilege for a specific procedure can execute the procedure or compile a program unit that references the procedure.

Oracle Database performs a run-time privilege check when any PL/SQL unit is called. A user with the EXECUTE ANY PROCEDURE system privilege can execute any procedure in the database. Privileges to run procedures can be granted to a user through roles.

System Privileges Required to Create or Replace a Procedure

To create or replace a procedure in your own schema, you must have the CREATE PROCEDURE system privilege. To create or replace a procedure in another user's schema, you must have the CREATE ANY PROCEDURE system privilege.

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

System Privileges Required to Compile a Procedure

To compile a standalone procedure, run the ALTER PROCEDURE statement with the COMPILE clause. To compile a procedure that is part of a package, run the ALTER PACKAGE statement.

Example 4-13 shows how to compile a standalone procedure.

ALTER PROCEDURE psmith.remove_emp COMPILE;

If the standalone or packaged procedure is in another user's schema, you must have the ALTER ANY PROCEDURE privilege to recompile it. You can recompile procedures in your own schema without any privileges.

How Procedure Privileges Affect Packages and Package Objects

The EXECUTE privilege is a powerful privilege that enables users to run any public procedures or functions within a package. Oracle Database provides two ways that you can ensure security when they develop procedures, functions, and packages for database applications.

Topics:

• About the Effect of Procedure Privileges on Packages and Package Objects

• Procedure Privileges and Packages and Package Objects: Example 1

• Procedure Privileges and Packages and Package Objects: Example 2

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

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

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

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; 

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

System Privileges for Named Types

Table 4-4 lists system privileges for named types (object types, VARRAYs, and nested tables).

The RESOURCE role includes the CREATE TYPE system privilege. The DBA role includes all of these privileges.

Object Privileges for Named Types

The only object privilege that applies to named types is EXECUTE.

If the EXECUTE privilege exists on a named type, then a user can use the named type to:

• Define a table

• Define a column in a relational table

• Declare a variable or parameter of the named type

The EXECUTE privilege permits a user to invoke the methods in the type, including the type constructor. This is similar to the EXECUTE privilege on a stored PL/SQL procedure.

Method Execution Model for Named Types

The method execution for named types is the same as any other stored PL/SQL procedure.

Privileges Required to Create Types and Tables Using Types

To create a type, you must meet several requirements.

• You must have the CREATE TYPE system privilege to create a type in your schema or the CREATE ANY TYPE system privilege to create a type in the schema of another user. These privileges can be acquired explicitly or through a role.

• The owner of the type must be explicitly granted the EXECUTE object privileges to access all other types referenced within the definition of the type, or have been granted the EXECUTE ANY TYPE system privilege. The owner cannot obtain the required privileges through roles.

• If the type owner intends to grant access to the type to other users, then the owner must receive the EXECUTE privileges to the referenced types with the GRANT OPTION or the EXECUTE ANY TYPE system privilege with the ADMIN OPTION. If not, then the type owner has insufficient privileges to grant access on the type to other users.

To create a table using types, you must meet the requirements for creating a table and the following additional requirements:

• The owner of the table must have been directly granted the EXECUTE object privilege to access all types referenced by the table, or has been granted the EXECUTE ANY TYPE system privilege. The owner cannot exercise the required privileges if these privileges were granted through roles.

• If the table owner intends to grant access to the table to other users, then the owner must have the EXECUTE privilege to the referenced types with the GRANT OPTION or the EXECUTE ANY TYPE system privilege with the ADMIN OPTION. If not, then the table owner has insufficient privileges to grant access on the table.

Example of Privileges for Creating Types and Tables Using Types

Before users can grant the EXECUTE privilege on types to other users, they should be granted the EXECUTE privilege with the GRANT OPTION on the type.

Assume that three users exist with the CONNECT and RESOURCE roles:

• user1

• user2

• user3

The following DDL is run in the schema of user1:

CREATE TYPE type1 AS OBJECT (
  attr1 NUMBER);

CREATE TYPE type2 AS OBJECT (
  attr2 NUMBER);

GRANT EXECUTE ON type1 TO user2;
GRANT EXECUTE ON type2 TO user2 WITH GRANT OPTION;

The following DDL is performed in the schema of user2:

CREATE TABLE tab1 OF user1.type1;
CREATE TYPE type3 AS OBJECT (
  attr3 user1.type2);
CREATE TABLE tab2 (
  col1 user1.type2);

The following statements succeed because user2 has EXECUTE privilege on user1.type2 with the GRANT OPTION:

GRANT EXECUTE ON type3 TO user3;
GRANT SELECT ON tab2 TO user3;

However, the following grant fails because user2 does not have EXECUTE privilege on user1.type1 with the GRANT OPTION:

GRANT SELECT ON tab1 TO user3;

The following statements can be successfully run by user3:

CREATE TYPE type4 AS OBJECT (
  attr4 user2.type3);
CREATE TABLE tab3 OF type4;

Privileges on Type Access and Object Access

Existing column-level and table-level privileges for DML statements apply to both column objects and row objects.

Table 4-5 lists the privileges for object tables.

Similar table privileges and column privileges apply to column objects. Retrieving instances does not in itself reveal type information. However, clients must access named type information to interpret the type instance images. When a client requests type information, Oracle Database checks for the EXECUTE privilege on the type.

Consider the following schema:

CREATE TYPE emp_type (
    eno NUMBER, ename CHAR(31), eaddr addr_t);
CREATE TABLE emp OF emp_t;

In addition, consider the following two queries:

SELECT VALUE(emp) FROM emp;
SELECT eno, ename FROM emp;

For either query, Oracle Database checks the SELECT privilege of the user for the emp table. For the first query, the user must obtain the emp_type type information to interpret the data. When the query accesses the emp_type type, Oracle Database checks the EXECUTE privilege of the user.

The second query, however, does not involve named types, so Oracle Database does not check type privileges.

In addition, by using the schema from the previous section, user3 can perform the following queries:

SELECT tab1.col1.attr2 FROM user2.tab1 tab1;
SELECT attr4.attr3.attr2 FROM tab3;

Note that in both SELECT statements, user3 does not have explicit privileges on the underlying types, but the statement succeeds because the type and table owners have the necessary privileges with the GRANT OPTION.

Oracle Database checks privileges on the following events, and returns an error if the client does not have the privilege for the action:

• Pinning an object in the object cache using its REF value causes Oracle Database to check for the SELECT privilege on the containing object table.

• Modifying an existing object or flushing an object from the object cache causes Oracle Database to check for the UPDATE privilege on the destination object table.

• Flushing a new object causes Oracle Database to check for the INSERT privilege on the destination object table.

• Deleting an object causes Oracle Database to check for the DELETE privilege on the destination table.

• Pinning an object of a named type causes Oracle Database to check EXECUTE privilege on the object.

Modifying the attributes of an object in a client third-generation language application causes Oracle Database to update the entire object. Therefore, the user needs the UPDATE privilege on the object table. Having the UPDATE privilege on only certain columns of the object table is not sufficient, even if the application only modifies attributes corresponding to those columns. Therefore, Oracle Database does not support column-level privileges for object tables.

Type Dependencies

As with stored objects, such as procedures and tables, types being referenced by other objects are called dependencies.

There are some special issues for types on which tables depend. Because a table contains data that relies on the type definition for access, any change to the type causes all stored data to become inaccessible. Changes that can cause this are when necessary privileges required to use the type are revoked, or the type or dependent types are dropped. If these actions occur, then the table becomes invalid and cannot be accessed.

A table that is invalid because of missing privileges can automatically become valid and accessible if the required privileges are granted again. A table that is invalid because a dependent type was dropped can never be accessed again, and the only permissible action is to drop the table.

Because of the severe effects that revoking a privilege on a type or dropping a type can cause, the SQL statements REVOKE and DROP TYPE, by default, implement restricted semantics. This means that if the named type in either statement has table or type dependents, then an error is received and the statement cancels. However, if the FORCE clause for either statement is used, then the statement always succeeds. If there are depended-upon tables, then they are invalidated.

Granting System Privileges and Roles to Users and Roles

You can use the GRANT SQL statement to grant system privileges and roles to users and roles.

The following privileges are required:

• To grant a system privilege, a user must be granted the system privilege with the ADMIN option or must be granted the GRANT ANY PRIVILEGE system privilege.

• To grant a role, a user must be granted the role with the ADMIN option or was granted the GRANT ANY ROLE system privilege.

Example 4-16 grants the system privilege CREATE SESSION and the accts_pay role to the user jward.

GRANT CREATE SESSION, accts_pay TO jward;

Example 4-16 grants the EXECUTE privilege on the exec_dir directory object to the user jward.

GRANT EXECUTE ON DIRECTORY exec_dir TO jward;

GRANT new_dba TO michael WITH ADMIN OPTION;

GRANT CREATE SESSION TO psmith IDENTIFIED BY password;

Granting Object Privileges to Users and Roles

You can grant object privileges to users and roles. To enable the grantee to grant the privilege to other users, you can specify the WITH GRANT OPTION clause in the GRANT statement. You can make grants to object privileges on behalf of an object owner. You can use the GRANT statement to grant access to columns, but not to rows.

Topics:

• About Granting Object Privileges to Users and Roles

• How the WITH GRANT OPTION Clause Works

• Grants of Object Privileges on Behalf of the Object Owner

• Grants of Privileges on Columns

• Row-Level Access Control

GRANT READ, INSERT, DELETE ON emp TO jfee, tsmith;

GRANT ALL ON salary TO jfee;

GRANT SELECT ON HR.EMPLOYEES TO blake WITH GRANT OPTION;

SELECT GRANTEE, GRANTOR, PRIVILEGE, GRANTABLE
  FROM DBA_TAB_PRIVS 
  WHERE TABLE_NAME = 'EMPLOYEES' and OWNER = 'HR';

GRANTEE  GRANTOR PRIVILEGE    GRANTABLE
-------- ------- -----------  ----------
BLAKE    HR       SELECT      YES       

GRANT SELECT ON HR.EMPLOYEES TO clark;

GRANTEE  GRANTOR  PRIVILEGE  GRANTABLE
-------- -------- ---------  ----------
BLAKE    HR       SELECT     YES       
CLARK    BLAKE    SELECT     NO        

GRANT INSERT (acct_no) ON accounts TO psmith;

GRANT INSERT(ename, job) ON emp TO jfee, tsmith;

Revokes of System Privileges and Roles

You can revoke system privileges and roles using the SQL statement REVOKE.

Any user with the ADMIN option for a system privilege or role can revoke the privilege or role from any other database user or role. The revoker does not have to be the user that originally granted the privilege or role. Users with GRANT ANY ROLE can revoke any role.

Example 4-21 revokes the CREATE TABLE system privilege and the accts_rec role from user psmith:

REVOKE CREATE TABLE, accts_rec FROM psmith;

Revokes of Object Privileges

Topics:

• About Revokes of Object Privileges

• Revokes of Multiple Object Privileges

• Revokes of Object Privileges on Behalf of the Object Owner

• Revokes of Column-Selective Object Privileges

• Revokes of the REFERENCES Object Privilege

REVOKE SELECT, INSERT ON emp FROM jfee, psmith;

REVOKE ALL ON dept FROM human_resources;

GRANTEE  GRANTOR PRIVILEGE    GRANTABLE
-------- ------- -----------  ----------
BLAKE    HR       SELECT       YES       
CLARK    BLAKE    SELECT       NO        
CLARK    HR       SELECT       NO        

REVOKE  SELECT ON HR.EMPLOYEES FROM clark;

GRANTEE  GRANTOR PRIVILEGE    GRANTABLE
-------- ------- -----------  ----------
BLAKE    HR       SELECT      YES       
CLARK    HR       SELECT      NO        

REVOKE UPDATE ON dept FROM human_resources;
GRANT UPDATE (dname) ON dept TO human_resources;

REVOKE REFERENCES ON dept FROM jward CASCADE CONSTRAINTS;

Cascading Effects of Revoking Privileges

There are no cascading effects when you revoke object privileges related to DDL operations, but there are cascading effects for object privilege revocations.

Topics:

• Cascading Effects When Revoking System Privileges

• Cascading Effects When Revoking Object Privileges

About Granting Roles Using the Operating System or Network

Instead of a security administrator explicitly granting and revoking database roles to and from users using GRANT and REVOKE statements, the operating system on which Oracle Database runs can grant roles to users at connect time.

Roles can be administered using the operating system and passed to Oracle Database when a user creates a session. As part of this mechanism, the default roles of a user and the roles granted to a user with the ADMIN option can be identified. If the operating system is used to authorize users for roles, then all roles must be created in the database and privileges assigned to the role with GRANT statements.

Roles can also be granted through a network service.

The advantage of using the operating system to identify the database roles of a user is that privilege management for an Oracle database can be externalized. The security facilities offered by the operating system control user privileges. This option may offer advantages of centralizing security for several system activities, such as the following situation:

• MVS Oracle administrators want RACF groups to identify database user roles.

• UNIX Oracle administrators want UNIX groups to identify database user roles.

• VMS Oracle administrators want to use rights identifiers to identify database user roles.

The main disadvantage of using the operating system to identify the database roles of a user is that privilege management can only be performed at the role level. Individual privileges cannot be granted using the operating system, but they can still be granted inside the database using GRANT statements.

A second disadvantage of using this feature is that, by default, users cannot connect to the database through the shared server or any other network connection if the operating system is managing roles. However, you can change this default as described in "Network Connections with Operating System Role Management".

Operating System Role Identification

To have the database use the operating system to identify the database roles of each user when a session is created, you can set the initialization parameter OS_ROLES to TRUE. If the instance is current running, you would need to restart the instance. When a user tries to create a session with the database, Oracle Database initializes the user security domain using the database roles identified by the operating system.

To identify database roles for a user, the operating system account for each Oracle Database user must have operating system identifiers (these may be called groups, rights identifiers, or other similar names) that indicate which database roles are to be available for the user. Role specification can also indicate which roles are the default roles of a user and which roles are available with the ADMIN option. No matter which operating system is used, the role specification at the operating system level follows the format:

ora_ID_ROLE[[_d][_a][_da]]

In this specification:

• ID has a definition that varies on different operating systems. For example, on VMS, ID is the instance identifier of the database; on VMS, it is the computer type; and on UNIX, it is the system ID.

  ID is case-sensitive to match your ORACLE_SID. ROLE is not case-sensitive.

• ROLE is the name of the database role.

• d is an optional character that indicates this role is to be a default role of the database user.

• a is an optional character that indicates this role is to be granted to the user with the ADMIN option. This allows the user to grant the role to other roles only. Roles cannot be granted to users if the operating system is used to manage roles.

  If either the d or a character is specified, then precede that character by an underscore (_).

For example, suppose an operating system account has the following roles identified in its profile:

ora_PAYROLL_ROLE1
ora_PAYROLL_ROLE2_a
ora_PAYROLL_ROLE3_d
ora_PAYROLL_ROLE4_da

When the corresponding user connects to the payroll instance of Oracle Database, role3 and role4 are defaults, while role2 and role4 are available with the ADMIN option.

Operating System Role Management

When you use operating system-managed roles, remember that database roles are being granted to an operating system user. Any database user to which the operating system user is able to connect will have the authorized database roles enabled.

For this reason, you should consider defining all Oracle Database users as IDENTIFIED EXTERNALLY if you are using OS_ROLES = TRUE, so that the database accounts are tied to the operating system account that was granted privileges.

Role Grants and Revokes When OS_ROLES Is Set to TRUE

If the OS_ROLES parameter is set to TRUE, then the operating system completely manages the granting and revoking of roles to users.

Any previous granting of roles to users using GRANT statements do not apply. However, they are still listed in the data dictionary. Only the role grants to users made at the operating system level apply. Users can still grant privileges to roles and users.

Role Enablements and Disablements When OS_ROLES Is Set to TRUE

If the OS_ROLES initialization parameter is set to TRUE, then any role granted by the operating system can be dynamically enabled using the SET ROLE statement. This still applies, even if the role was defined to require a password or operating system authorization.

However, any role not identified in the operating system account of a user cannot be specified in a SET ROLE statement, even if a role was granted using a GRANT statement when OS_ROLES = FALSE. (If you specify such a role, then Oracle Database ignores it.)

When OS_ROLES is set to TRUE, then the user can enable up to 148 roles. Remember that this number includes other roles that may have been granted to the role.

Network Connections with Operating System Role Management

If you have the operating system manage roles, then, by default, users cannot connect to the database through the shared server. This restriction is the default because a remote user could impersonate another operating system user over an unsecure connection.

If you are not concerned with this security risk and want to use operating system role management with the shared server, or any other network connection, then set the initialization parameter REMOTE_OS_ROLES to TRUE. The change takes effect the next time you start the instance and mount the database. The default setting of this parameter is FALSE.

How the SET ROLE Statement Affects Grants and Revokes

During the user session, the user or an application can use the SET ROLE statement any number of times to change the roles currently enabled for the session. The user must already be granted the roles that are named in the SET ROLE statement.

Example 4-22 enables the role clerk, which you have already been granted, and specifies the password.

SET ROLE clerk IDENTIFIED BY password;

Replace password with a password that is secure. "Minimum Requirements for Passwords" describes the minimum requirements for passwords.

Example 4-23 shows how to use SET ROLE to disable all roles.

SET ROLE NONE;

Specifying Default Roles

When a user logs on, Oracle Database enables all privileges granted explicitly to the user and all privileges in the default roles of the user.

You can set and alter a list of default roles for a user by using the ALTER USER SQL statement. The ALTER USER statement specifies roles that are to be enabled when a user connects to the database. The user must have been directly granted the roles with a GRANT statement, or the roles must have been created by the user with the CREATE ROLE privilege. For information about the restrictions of the DEFAULT ROLE clause of the ALTER USER statement, see Oracle Database SQL Language Reference.

Example 4-24 sets the default roles payclerk and pettycash for user jane:

ALTER USER jane DEFAULT ROLE payclerk, pettycash;

You cannot set default roles for a user in the CREATE USER statement. When you first create a user, the default user role setting is ALL, which causes all roles subsequently granted to the user to be default roles. Use the ALTER USER statement to limit the default user roles.

The Maximum Number of Roles That a User Can Have Enabled

You can grant a user as many roles as you want, but be aware that even though the user login succeeds, no more than 148 roles can be enabled for the user at any given time. Therefore, not all privileges will be available to this user during the user session. As a best practice, restrict the number of roles granted to a user to the minimum roles the user needs. See "Guidelines for Securing Roles" for additional guidelines on granting roles to users.

Data Dictionary Views to Find Information about Privilege and Role Grants

Table 4-6 lists data dictionary views that you can query to access information about grants of privileges and roles.

This section provides some examples of using these views. For these examples, assume the following statements were issued:

CREATE ROLE security_admin IDENTIFIED BY password;

GRANT CREATE PROFILE, ALTER PROFILE, DROP PROFILE,
    CREATE ROLE, DROP ANY ROLE, GRANT ANY ROLE, AUDIT ANY,
    AUDIT SYSTEM, CREATE USER, BECOME USER, ALTER USER, DROP USER
    TO security_admin WITH ADMIN OPTION;

GRANT READ, DELETE ON SYS.AUD$ TO security_admin;

GRANT security_admin, CREATE SESSION TO swilliams;

GRANT security_admin TO system_administrator;

GRANT CREATE SESSION TO jward;

GRANT READ, DELETE ON emp TO jward;

GRANT INSERT (ename, job) ON emp TO swilliams, jward;

Query to List All System Privilege Grants

The DBA_SYS_PRIVS data dictionary view returns all system privilege grants made to roles and users.

For example:

SELECT GRANTEE, PRIVILEGE, ADM FROM DBA_SYS_PRIVS;

GRANTEE            PRIVILEGE                         ADM
--------------     --------------------------------- ---
SECURITY_ADMIN     ALTER PROFILE                     YES
SECURITY_ADMIN     ALTER USER                        YES
SECURITY_ADMIN     AUDIT ANY                         YES
SECURITY_ADMIN     AUDIT SYSTEM                      YES
SECURITY_ADMIN     BECOME USER                       YES
SECURITY_ADMIN     CREATE PROFILE                    YES
SECURITY_ADMIN     CREATE ROLE                       YES
SECURITY_ADMIN     CREATE USER                       YES
SECURITY_ADMIN     DROP ANY ROLE                     YES
SECURITY_ADMIN     DROP PROFILE                      YES
SECURITY_ADMIN     DROP USER                         YES
SECURITY_ADMIN     GRANT ANY ROLE                    YES
SWILLIAMS          CREATE SESSION                    NO
JWARD              CREATE SESSION                    NO

See Oracle Database Reference for detailed information about the DBA_SYS_PRIVS view.

Query to List All Role Grants

The DBA_ROLE_PRIVS query returns all the roles granted to users and other roles.

For example:

SELECT * FROM DBA_ROLE_PRIVS;

GRANTEE            GRANTED_ROLE                         ADM
------------------ ------------------------------------ ---
SWILLIAMS          SECURITY_ADMIN                       NO

See Oracle Database Reference for detailed information about the DBA_ROLE_PRIVS view.

Query to List Object Privileges Granted to a User

The DBA_TAB_PRIVS data dictionary view returns all object privileges (not including column-specific privileges) granted to the specified user.

For example:

SELECT TABLE_NAME, PRIVILEGE, GRANTABLE FROM DBA_TAB_PRIVS
    WHERE GRANTEE = 'jward';

TABLE_NAME   PRIVILEGE    GRANTABLE
-----------  ------------ ----------
EMP          SELECT       NO
EMP          DELETE       NO

To list all the column-specific privileges that have been granted, you can use the following query:

SELECT GRANTEE, TABLE_NAME, COLUMN_NAME, PRIVILEGE
    FROM DBA_COL_PRIVS;

GRANTEE      TABLE_NAME     COLUMN_NAME      PRIVILEGE
-----------  ------------   -------------    --------------
SWILLIAMS    EMP            ENAME            INSERT
SWILLIAMS    EMP            JOB              INSERT
JWARD        EMP            NAME             INSERT
JWARD        EMP            JOB              INSERT

See Oracle Database Reference for detailed information about the DBA_TAB_PRIVS view.

Query to List the Current Privilege Domain of Your Session

The SESSION_ROLES view lists all roles currently enabled for the issuer.

For example:

SELECT * FROM SESSION_ROLES;

If user swilliams has the security_admin role enabled and issues the previous query, then Oracle Database returns the following information:

ROLE
------------------------------
SECURITY_ADMIN

The following query lists all system privileges currently available in the security domain of the issuer, both from explicit privilege grants and from enabled roles:

SELECT * FROM SESSION_PRIVS;

If user swilliams has the security_admin role enabled and issues the previous query, then Oracle Database returns the following results:

PRIVILEGE
----------------------------------------
AUDIT SYSTEM
CREATE SESSION
CREATE USER
BECOME USER
ALTER USER
DROP USER
CREATE ROLE
DROP ANY ROLE
GRANT ANY ROLE
AUDIT ANY
CREATE PROFILE
ALTER PROFILE
DROP PROFILE

If the security_admin role is disabled for user swilliams, then the first query would return no rows, while the second query would only return a row for the CREATE SESSION privilege grant.

See Oracle Database Reference for detailed information about the SESSION_ROLES view.

Query to List Roles of the Database

The DBA_ROLES data dictionary view lists all roles of a database and the authentication used for each role.

For example:

SELECT * FROM DBA_ROLES;

ROLE                  PASSWORD
----------------      --------
CONNECT               NO
RESOURCE              NO
DBA                   NO
SECURITY_ADMIN        YES

See Oracle Database Reference for detailed information about the DBA_ROLES view.

Query to List Information About the Privilege Domains of Roles

The ROLE_ROLE_PRIVS, ROLE_SYS_PRIVS, and ROLE_TAB_PRIVS data dictionary views contain information about the privilege domains of roles.

For example:

SELECT GRANTED_ROLE, ADMIN_OPTION
   FROM ROLE_ROLE_PRIVS
   WHERE ROLE = 'SYSTEM_ADMIN';

GRANTED_ROLE              ADM
----------------          ----
SECURITY_ADMIN            NO

The following query lists all the system privileges granted to the security_admin role:

SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE = 'SECURITY_ADMIN';

ROLE                    PRIVILEGE                      ADM
----------------------- -----------------------------  ---
SECURITY_ADMIN           ALTER PROFILE                 YES
SECURITY_ADMIN           ALTER USER                    YES
SECURITY_ADMIN           AUDIT ANY                     YES
SECURITY_ADMIN           AUDIT SYSTEM                  YES
SECURITY_ADMIN           BECOME USER                   YES
SECURITY_ADMIN           CREATE PROFILE                YES
SECURITY_ADMIN           CREATE ROLE                   YES
SECURITY_ADMIN           CREATE USER                   YES
SECURITY_ADMIN           DROP ANY ROLE                 YES
SECURITY_ADMIN           DROP PROFILE                  YES
SECURITY_ADMIN           DROP USER                     YES
SECURITY_ADMIN           GRANT ANY ROLE                YES

The following query lists all the object privileges granted to the security_admin role:

SELECT TABLE_NAME, PRIVILEGE FROM ROLE_TAB_PRIVS
    WHERE ROLE = 'SECURITY_ADMIN';

TABLE_NAME                     PRIVILEGE
---------------------------    ----------------
AUD$                           DELETE
AUD$                           SELECT

See Oracle Database Reference for detailed information about the ROLE_ROLE_PRIVS, ROLE_SYS_PRIVS, and ROLE_TAB_PRIVS views.