For better separation of duty, Oracle Database provides administrative privileges that are tailored for commonly performed specific administrative tasks.

These tasks include operations for backup and recovery, Oracle Data Guard, and encryption key management for Transparent Data Encryption (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.

Users who have been granted administrative privileges can be altered to be schema-only accounts.

The use of administrative privileges is mandatorily audited.

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.

The SYSDBA and SYSOPER administrative privileges enable you to perform 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. You can grant the SYSDBA and SYSOPER administrative privileges to application common users (but not to CDB common users).

You can find if a user has been granted an administrative privilege on a local (PDB) level, for a CDB root, or for an application root by querying the SCOPE column of the V$PWFILE_USERS dynamic view.

You can grant the SYSDBA or SYSOPER administrative privilege to users who have been created with no authentication.

The SYSBACKUP administrative privilege is used to perform backup and recovery operations from either Oracle Recovery Manager (RMAN) and or through SQL*Plus.

To connect to the database as SYSBACKUP using a password, you must create a password file for it.

You cannot grant the SYSBACKUP administrative privilege to users who have been created with no authentication.

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.

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.

You cannot grant the SYSYSDG administrative privilege to users who have been created with no authentication.

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.

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

In order to connect to the database as SYSKM using a password, you must create a password file for it.

You cannot grant the SYSKM administrative privilege to users who have been created with no authentication.

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.

A system privilege is the right to perform an action or to perform actions on schema objects.

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. To find the system privileges that have been granted to a user, you can query the DBA_SYS_PRIVS data dictionary view.

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

• About the Importance of Restricting System Privileges
  System privileges are very powerful, so by default the database is configured to prevent typical (non-administrative) users from exercising the ANY system privileges.
• User Access to Objects in the SYS Schema
  Users with explicit object privileges or those who connect with administrative privileges (SYSDBA) can access objects in the SYS schema.

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 separation of duty 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

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

These users are as follows:

• 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.

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.

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 privileges granted commonly as well as privileges granted locally in the given container.

• 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.

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.

The following example shows how to commonly grant a privilege to the common user c##hr_admin.

CONNECT SYSTEM 
Enter password: password
Connected.

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

Object privileges on common objects applies to the object as well as all associated links on this common object.

These links include all metadata links, data links (previously called object links), or extended data links that are associated with it in the root and in all PDBs belonging to the container (including future PDBs) if certain requirements are met.

These requirements are as follows:

• 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 privilege

• The GRANT statement contains the CONTAINER=ALL clause.

The following example shows how to grant an object privilege to the common user c##hr_admin so that he can select from the DBA_PDBS view in the CDB root or in any of the associated PDBs that he can access.

CONNECT SYSTEM
Enter password: password
Connected.

GRANT SELECT ON DBA_OBJECTS TO c##hr_admin 
CONTAINER=ALL;

You can grant and revoke privileges for PDB access.

You must use the GRANT statement in the root to grant privileges to a common user.

Example 4-3 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
Enter password: password
Connected.

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

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

• Viewing Data About the Root, CDB, and PDBs While Connected to the Root
  You can restrict view information for the X$ table and the V$, GV$ and CDB_* views when common users perform queries.
• Enabling Common Users to Query Data in Specific PDBs
  You can enable common users to access data pertaining to specific PDBs by adjusting the users’ CONTAINER_DATA attribute.

Database roles can be specific to a PDB or used throughout the entire system container or application container.

A common role is a role whose identity and (optional) password are created in the root of a container and will be known in the root and in all existing and future PDBs belonging to that container.

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 role (local or common) to a local user or role only locally.

• If you grant a common role locally, then the privileges of that common role apply only in the container where the role is granted.

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

• The CONTAINER = ALL clause is the default when you create a common role in the CDB root or an application root.

• Every Oracle-supplied role is common, for example, the predefined DBA role. In Oracle-supplied scripts, every privilege or role granted to Oracle-supplied users and roles is granted commonly, with one exception: system privileges are granted locally to the common role PUBLIC.

A common role exists either in the CDB root or an application root, and applies to every PDB within the root container (either the CDB or the application container).

Common roles are useful for cross-container operations, ensuring that a common user has a role in every PDB. Every common role is one of the following types:

• Oracle-supplied

  All Oracle-supplied roles, such as DBA and PUBLIC, are common to the CDB.

• User-created

  Create a common role by executing CREATE ROLE ... CONTAINER=ALL in either the CDB root or application root, which determines the container to which the role is common. The standard naming conventions apply. Additionally, the names of CDB common roles must begin with the characters specified by the COMMON_USER_PREFIX initialization parameter, which are c## or C## by default.

The scope of the role is the scope of the root within which it is defined. If you define the role in CDB$ROOT, then its scope is the entire CDB. If you define the role within application root, then its scope is the application container.

Common roles are visible in the root and in every PDB of a container within which they are defined.

A privilege can be granted commonly to a common role if:

• The grantor is a common user.

• The grantor possesses the commonly granted ADMIN OPTION for the privilege that is being 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 CDB 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 container. However, if the CDB 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.

All privileges that Oracle grants to the PUBLIC role are granted locally.

This feature enables you to revoke privileges or roles that have been 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.

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 also create local roles, but these roles are available only in the PDB in which they were created.

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

The rules are as follows:

• Ensure that you are in the correct root. For the creation of common roles, you must be in the correct root, either the CDB root or the application root. You cannot create common roles from a PDB. To check if you are in the correct root, run one of the following:

  • To confirm that you are in the CDB root, you can issue the show_con_name command. The output should be CDB$ROOT.

  • To confirm that you are in an application root, verify that the following query returns YES:

    SELECT APPLICATION_ROOT FROM V$PDBS WHERE CON_ID=SYS_CONTEXT('USERENV', 'CON_ID');

  • Ensure that the name that you give the common role starts with the value of the COMMON_USER_PREFIX parameter (which defaults to C##). 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 for the CDB root or the application root.

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

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

   For example:

   CONNECT SYSTEM
   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; 

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

These rules are as follows:

• 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 the value of the COMMON_USER_PREFIX parameter (which defaults to 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.

A local role exists only in a single PDB, and is thus completely independent of local roles in any other PDBs.

A local role can only contain roles and privileges that apply within the container in which the role exists. For example, if you create the local role pdbadmin in hrpdb, then the scope of this role is restricted to this PDB.

PDBs in the same CDB, or in the same application container, may contain local roles with the same name. For example, the user-created role pdbadmin may exist in both hrpdb and salespdb. However, these roles are completely independent of each other.

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 sec_admin@pdb_name
   Enter password: password
   Connected.
   

   To find the available PDBs in a CDB, log in to the CDB root container and then query the PDB_NAME column of the DBA_PDBS data dictionary view. To check the current container, run the show con_name command.

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

   For example:

   CREATE ROLE sec_admin CONTAINER=CURRENT;

Role grants and revokes apply only to the scope of access of the common user or the local user.

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.

The following example shows how to grant the common user c##sec_admin the AUDIT_ADMIN common role for use in all containers.

CONNECT SYSTEM
Enter password: password
Connected.

GRANT AUDIT_ADMIN TO c##sec_admin CONTAINER=ALL;

Similarly, the next example 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;

This example shows how a local user aud_admin can revoke a role from another user in 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;

User roles are useful in a variety of situations, such as restricting DDL usage.

• What Are User Roles?
  A user role is a named group of related privileges that you can grant as a group to users or other roles.
• The Functionality of Roles
  Roles are useful for quickly and easily granting permissions to users.
• Properties of Roles and Why They Are Advantageous
  Roles have special properties that make their management very easy, such reduced privilege administration.
• Typical Uses of Roles
  In general, you create a role to manage privileges.
• Common Uses of Application Roles
  You can use application roles to control privileges to use applications.
• Common Uses of User Roles
  You can create a user role for a group of database users with common privilege grant requirements.
• How Roles Affect the Scope of a User's Privileges
  Each role and user has its own unique security domain.
• How Roles Work in PL/SQL Blocks
  Role behavior in a PL/SQL block is determined by the type of block and by definer's rights or invoker's rights.
• How Roles Aid or Restrict DDL Usage
  A user requires one or more privileges to successfully execute a DDL statement, depending on the statement.
• How Operating Systems Can Aid Roles
  In some environments, you can administer database security using the operating system.
• How Roles Work in a Distributed Environment
  In a distributed database environment, all necessary roles must be set as the default role for a distributed (remote) session.

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

These predefined role are automatically defined for Oracle databases when you run the standard scripts (such as catalog.sql and catproc.sql) 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. You can find roles that are created and maintained by Oracle by querying the ROLE and ORACLE_MAINTAINED columns of the DBA_ROLES data dictionary view. If the output for ORACLE_MAINTAINED is Y, then you must not modify the role except by running the script that was used to create it.

You can create a role that is authenticated with or without a password. You also can create external or global roles.

• About the Creation of Roles
  You can create a role by using the CREATE ROLE statement.
• Creating a Role That Is Authenticated With a Password
  You can create a password authenticated role by using the IDENTIFIED BY clause.
• Creating a Role That Has No Password Authentication
  You can create a role that does not require a password by omitting the IDENTIFIED BY clause.
• Creating a Role That Is External or Global
  External or global roles allow services that are outside the database to associate database roles to authenticated users.
• Altering a Role
  The ALTER ROLE statement can modify the authorization method for a role.

You can configure a role to be authorized through different sources, such the database or an external source.

• Authorizing a Role by Using the Database
  You can protect a role authorized by the database by assigning the role a password.
• Authorizing a Role by Using an Application
  An application role can be enabled only by applications that use an authorized PL/SQL package.
• Authorizing a Role by Using an External Source
  Oracle Database supports the use of external roles but with certain limitations.
• Authorizing a Role by Using the Operating System
  Oracle Database supports role authentication through the operating system but with certain limitations.
• Authorizing a Role by Using a Network Client
  Oracle Database supports role authentication by a network client but you must be aware of security risks.
• Authorizing a Global Role by an Enterprise Directory Service
  A global role enables a global user to be authorized only by an enterprise directory service.

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

• About Granting and Revoking Roles
  You can grant system or object privileges to a role, and grant any role to any database user or to another role.
• Who Can Grant or Revoke Roles?
  The GRANT ANY ROLE system privilege enables users to grant or revoke any role except global roles to or from other users or roles.
• Granting and Revoking Roles to and from Program Units
  You can grant roles to function, procedure, and PL/SQL package program units.

Dropping a role affects the security domains of users or roles who had been granted the role.

DROP ROLE clerk;

You should restrict SQL*Plus users from using database roles, which helps to safeguard the database from intruder attacks.

• Potential Security Problems of Using Ad Hoc Tools
  Ad hoc tools can pose problems if malicious users have access to such tools.
• How the PRODUCT_USER_PROFILE System Table Can Limit Roles
  The SYSTEM schema PRODUCT_USER_PROFILE table can disable SQL and SQL*Plus commands in the SQL*Plus environment for each user.
• How Stored Procedures Can Encapsulate Business Logic
  Stored procedures encapsulate privileges use with business logic so that privileges are only exercised in the context of a well-formed business transaction.

A secure application role can be enabled only by an authorized PL/SQL package or procedure.

The PL/SQL package itself reflects the security policies that are necessary 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.

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

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.

You can grant privileges to or revoke privileges from objects either directly to a user or through roles.

• About Granting and Revoking Object Privileges
  Object privileges can be granted to and revoked from users and roles.
• How the ALL Clause Grants or Revokes All Available Object Privileges
  Each type of object has different privileges associated with it, which can be controlled by the ALL clause.

The READ and SELECT privileges provide different layers of query privileges.

• About Managing READ and SELECT Object Privileges
  You can grant users either the READ or the SELECT object privilege.
• Enabling Users to Use the READ Object Privilege to Query Any Table in the Database
  The READ ANY TABLE system privilege provides the READ object privilege for querying any table in the database.
• Restrictions on the READ and READ ANY TABLE Privileges
  There are special restrictions on the READ and READ ANY TABLE privileges.

The CREATE SYNONYM statement create synonyms for database objects.

You can create synonyms for the following objects: 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

You can grant privileges to use the DELETE, INSERT, SELECT, and UPDATE DML operations on tables and views.

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.

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).

To create a view, you must have specific privileges.

Object privileges for a view allow various DML operations, which affect the base tables from which the view is derived.

These privileges to create a view are as follows:

• You must be 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 be 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.

Database views can increase table security by restricting the data that users can see.

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.

The EXECUTE privilege is a very powerful privilege that should be handled with caution.

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

You should grant this privilege only to users who must run a procedure or compile another procedure that calls a desired procedure. You can find the privileges that a user has been granted by querying the DBA_SYS_PRIVS data dictionary view.

The EXECUTE object privilege for a procedure can be used to execute a 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.

You must have specific privileges to create or replace a procedure in your own schema or in another user’s schema.

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.

You must have specific privileges to compile both standalone procedures and procedures that are part of a package.

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

The following example 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.

The powerful EXECUTE privilege enables users to run any public procedures or functions within a package.

• About the Effect of Procedure Privileges on Packages and Package Objects
  The EXECUTE object privilege for a package applies to any procedure or function within this package.
• Example: Procedure Privileges Used in One Package
  The CREATE PACKAGE BODY statement can create a package body that contains procedures to manage procedure privileges used in one package.
• Example: Procedure Privileges and Package Objects
  The CREATE PACKAGE BODY statement can create a package body containing procedure definitions to manage procedure privileges and package objects.

System privileges for named types can enable users to perform actions such as creating named types in their own schemas.

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

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.

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

Users must be granted the appropriate privileges for using the named types, such as the EXECUTE privilege. As with all privilege grants, only grant these privileges to trusted users. You can find the privileges that a user has been granted by querying the DBA_SYS_PRIVS data dictionary view.

To create a type, you must have the appropriate privileges.

These privileges are as follows:

• 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.

The EXECUTE privilege with the GRANT OPTION is required for users to grant the EXECUTE privilege on a type to other users.

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;

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

Table 4-8 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.

As with stored objects, such as procedures and tables, types that are 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.

Before you grant system privileges and roles to users and roles, be aware of how privileges for these types of grants work.

• Privileges for Grants of 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.
• Example: Granting a System Privilege and a Role to a User
  You can use the GRANT statement to grant system privileges and roles to users.
• Example: Granting the EXECUTE Privilege on a Directory Object
  You can use the GRANT statement to grant the EXECUTE privilege on a directory object.
• Use of the ADMIN Option to Enable Grantee Users to Grant the Privilege
  The WITH ADMIN OPTION clause can be used to expand the capabilities of a privilege grant.
• Creating a New User with the GRANT Statement
  You can create a new user and grant this user a privilege in one GRANT SQL statement.

You can grant object privileges to users and roles, and enable the grantee to grant the privilege to other users.

• About Granting Object Privileges to Users and Roles
  You can use the GRANT statement to grant object privileges to roles and users.
• How the WITH GRANT OPTION Clause Works
  The WITH GRANT OPTION clause with the GRANT statement can enable a grantee to grant object privileges to other users.
• Grants of Object Privileges on Behalf of the Object Owner
  The GRANT ANY OBJECT PRIVILEGE system privilege enables users to grant and revoke any object privilege on behalf of the object owner.
• Grants of Privileges on Columns
  You can grant INSERT, UPDATE, or REFERENCES privileges on individual columns in a table.
• Row-Level Access Control
  You can provide access control at the row level, that is, within objects, but not with the GRANT statement.

The REVOKE SQL statement revokes system privileges and roles.

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-15 revokes the CREATE TABLE system privilege and the accts_rec role from user psmith:

REVOKE CREATE TABLE, accts_rec FROM psmith;

You can revoke multiple object privileges, object privileges on behalf of an object owner, column-selective object privileges, and the REFERENCES object privilege.

• About Revokes of Object Privileges
  To revoke an object privilege, you must meet the appropriate requirements.
• Revokes of Multiple Object Privileges
  The REVOKE statement can revoke multiple privileges on one object.
• Revokes of Object Privileges on Behalf of the Object Owner
  The GRANT ANY OBJECT PRIVILEGE system privilege can be used to revoke any object privilege where the object owner is the grantor.
• Revokes of Column-Selective Object Privileges
  GRANT and REVOKE operations for column-specific operations have different privileges and restrictions.
• Revokes of the REFERENCES Object Privilege
  When you revoke the REFERENCES object privilege, it affects foreign key constraints.

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

• Cascading Effects When Revoking System Privileges
  There are no cascading effects when you revoke a system privilege that is related to DDL operations.
• Cascading Effects When Revoking Object Privileges
  Revoking an object privilege can have cascading effects.

The operating system on which Oracle Database runs can be used to grant roles to users at connect time.

This feature is an alternative to a security administrator explicitly having to granting and revoking database roles to and from users using GRANT and REVOKE statements.

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.

You can use operating system authentication for a database administrator only for the CDB root. You cannot use it for PDBs, the application root, or application PDBs.

The OS_ROLES initialization parameter can be used to control how the operating system identifies roles.

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 must 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.

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.

Setting the OS_ROLES initialization parameter to TRUE enables the operating system to manage role grants and revokes 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.

Setting the OS_ROLES initialization parameter to TRUE enables the SET ROLE statement to dynamically enable roles granted by the operating system.

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.

By default, users cannot connect to the database through a shared server if the operating system manages roles.

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.

Depending on the privilege that is granted or revoked, a grant or revoke takes effect at different times.

The grants and revokes take effect as follows:

• All grants and revokes of system and object privileges to anything (users, roles, and PUBLIC) take immediate effect.

• All grants and revokes of roles to anything (users, other roles, PUBLIC) take effect only when a current user session issues a SET ROLE statement to reenable the role after the grant and revoke, or when a new user session is created after the grant or revoke.

You can see which roles are currently enabled by examining the SESSION_ROLES data dictionary view.

During a user session, a user or an application can use the SET ROLE statement multiple times to change the roles enabled for the session.

The user must already be granted the roles that are named in the SET ROLE statement.

The following example 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.

The following example shows how to use SET ROLE to disable all roles.

SET ROLE NONE;

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

1. Ensure that the user who you want to set the default role for has been directly granted the role with a GRANT statement, or that the role was created by the user with the CREATE ROLE privilege.
2. Use the ALTER USER statement with the DEFAULT ROLE clause to specify the default role for the user.

ALTER USER jane DEFAULT ROLE payclerk, pettycash;

You can grant a user as many roles as you want, but no more than 148 roles can be enabled for a logged-in 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.

Oracle Database provides data dictionary views that describe privilege and role grants.

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;

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

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

The DBA_TAB_PRIVS and DBA_COL_PRIVS data dictionary views list object privileges that have bee granted to users.

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

The SESSION_ROLES and SESSION_PRIVS data dictionary views list the current privilege domain of a database 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.

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

The ROLE_ROLE_PRIVS, ROLE_SYS_PRIVS, and ROLE_TAB_PRIVS data dictionary views list 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