About Privileges and Roles

Authorization permits users to access, process, or alter data; it also creates limitations on user access or actions.

The limitations placed on (or removed from) users can apply to objects such as schemas, entire tables, or table rows.

A user privilege is the right to run a particular type of SQL statement, or the right to access an object that belongs to another user, run a PL/SQL package, and so on. The types of privileges are defined by Oracle Database.

Roles are created by users (usually administrators) to group together privileges or other roles. They are a way to facilitate the granting of multiple privileges or roles to users. In addition to granting roles to users and other roles, you can assign roles to programs by using code based access control (CBAC).

Privileges can fall into the following general categories:

Related Topics

Privilege and Role Grants in a CDB

The scope of a privilege and role grant in a CDB depends on where the role is being used.

About Privilege and Role Grants in a CDB

User accounts in a CDB can grant and be granted roles and privileges. Roles and privileges in a CDB, however, are either locally or commonly granted.

A privilege or role granted locally is exercisable only in the PDB in which it was granted. A privilege or role granted commonly is exercisable in every existing and future PDB in the container-either the CDB or an application container-in which it was granted.

Users and roles may be common or local. However, a privilege is in itself neither common nor local. If a user grants a privilege locally using the CONTAINER=CURRENT clause, then the grantee has a privilege exercisable only in the current container. If a user connects to either the CDB root or an application root, and if this user grants a privilege commonly using the CONTAINER=ALL clause, then the grantee has this privilege in any existing or future PDB within the current container.

Principles of Privilege and Role Grants in a CDB

In a CDB, every act of granting, whether local or common, occurs within a container. The container may be the CDB root, an application root, or a PDB.

If the current container is the CDB root, then granting commonly means granting to all containers in the CDB. If the current container is an application root, however, then granting commonly means granting to all PDBs in the current application container.

The basic principles of granting are as follows:

Local users, roles, and privileges are restricted to a particular PDB. Thus, local users may not grant roles and privileges commonly, and local roles and privileges may not be granted commonly.

The following sections describe the implications of the preceding principles.

Privileges and Roles Granted Locally in a CDB

Roles and privileges may be granted locally to users and roles regardless of whether the grantees, grantors, or roles being granted are local or common.

The following table explains the valid possibilities for locally granted roles and privileges.

Phenomenon May Grant Locally May Be Granted Locally May Receive a Role or Privilege Granted Locally
Common User Yes N/A Yes
Local User Yes N/A Yes
Common Role N/A Yes (but privileges in this role are available to the grantee only in the container in which the role was granted, regardless of whether the privileges were granted to the role locally or commonly) Yes
Local Role N/A Yes (but privileges in this role are available to the grantee only in the container in which the role was granted and created) Yes
Privilege N/A Yes N/A

What Makes a Privilege or Role Grant Local

To grant a role or privilege locally, use the GRANT statement with the CONTAINER=CURRENT clause, which is the default.

Specifically, a role or privilege is granted locally only when the following criteria are met:

The following example shows how to grant a privilege locally. Both SYSTEM and c##hr_admin are common users. The example connects to hrpdb as SYSTEM (which has administrator privileges), and then locally grants read privileges on the employees table to c##hr_admin. This grant applies only to c##hr_admin within hrpdb, not within any other PDBs.

CONNECT SYSTEM@hrpdb
Enter password: password
Connected.

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

Roles and Privileges Granted Locally

A user or role may be locally granted a privilege (CONTAINER=CURRENT).

For example, a READ ANY TABLE privilege granted locally to a local or common user in hrpdb applies only to this user in this PDB.

A user or role may be locally granted a role (CONTAINER=CURRENT). A common role may receive a privilege granted locally. For example, the common role c##dba may be granted the READ ANY TABLE privilege locally in hrpdb. If the c##cdb common role has local privileges, then these privileges apply only in the container in which the role is granted. In this example, a common user who has the c##cdba role does not, because of a privilege granted locally to this role in hrpdb, have the right to exercise this privilege in any PDB other than hrpdb.

Roles and Privileges Granted Commonly in a CDB

Privileges and common roles may be granted commonly.

User accounts or roles may be granted roles and privileges commonly only if the grantees and grantors are both common. If a role is being granted commonly, then the role itself must be common. The following table explains the possibilities for common grants.

Phenomenon May Grant Commonly May Be Granted Commonly May Receive Roles and Privileges Granted Commonly
Common User Account Yes N/A Yes
Local User Account No N/A No
Common Role N/A Yes1 Yes
Local Role N/A No No
Privilege N/A Yes N/A

What Makes a Grant Common

The CONTAINER=ALL clause specifies that the privilege or role is being granted commonly.

A role or privilege is granted commonly when the following criteria are met:

The following example shows how to grant a privilege commonly. Both SYSTEM and c##hr_admin are common users. SYSTEM connects to the CDB root, and then grants the CREATE ANY TABLE privilege commonly to c##hr_admin. In this case, c##hr_admin can now create a table in any PDB in the CDB.

CONNECT SYSTEM@root
Enter password: password
Connected.

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

Roles and Privileges Granted Commonly

A common user account or role may be granted a privilege commonly (CONTAINER=ALL).

Within the context of either the CDB root or an application root, the privilege is granted to this common user account or role in all existing and future PDBs within the current container. For example, if SYSTEM connects to the CDB root and grants a SELECT ANY TABLE privilege commonly to CDB common user account c##dba, then the c##dba user has this privilege in all PDBs in the CDB. A role or privilege granted commonly cannot be revoked locally.

A user or role may receive a common role granted commonly. A common role may receive a privilege granted locally. Thus, a common user can be granted a common role, and this role may contain locally granted privileges.

For example, the common role c##admin may be granted the SELECT ANY TABLE privilege that is local to hrpdb. Locally granted privileges in a common role apply only in the container in which the privilege was granted. Thus, the common user with the c##admin role does not have the right to exercise an hrpdb-contained privilege in salespdb or any PDB other than hrpdb.

Grants to PUBLIC in a CDB

In a CDB, PUBLIC is a common role. In a PDB, privileges granted locally to PUBLIC enable all local and common user account to exercise these privileges in this PDB only.

Every privilege and role granted to Oracle-supplied users and roles is granted commonly except for system privileges granted to PUBLIC, which are granted locally. This exception exists because you may want to revoke some grants included by default in Oracle Database, such as EXECUTE on the SYS.UTL_FILE package.

Assume that local user account hr exists in hrpdb. This user locally grants the SELECT privilege on hr.employees to PUBLIC. Common and local users in hrpdb may exercise the privilege granted to PUBLIC. User accounts in salespdb or any other PDB do not have the privilege to query hr.employees in hrpdb.

Privileges granted commonly to PUBLIC enable all local users to exercise the granted privilege in their respective PDBs and enable all common users to exercise this privilege in the PDBs to which they have access. Oracle recommends that users do not commonly grant privileges and roles to PUBLIC.

Grants of Privileges and Roles: Scenario

In this scenario, SYSTEM creates common user c##dba and tries to give this user privileges to query a table in the hr schema in hrpdb.

The scenario shows how the CONTAINER clause affects grants of roles and privileges. The first column shows operations in CDB$ROOT. The second column shows operations in hrpdb.

t Operations in CDB$ROOT Operations in hrpdb Explanation
t1 SQL> CONNECT SYSTEM@root Enter password: ******* Connected. n/a Common user SYSTEM connects to the root container.
t2 SQL> CREATE USER c##dba IDENTIFIED BY *password* CONTAINER=ALL; n/a SYSTEM creates common user c##dba. The clause CONTAINER=ALL makes the user a common user.
t3 SQL> GRANT CREATE SESSION TO c##dba; n/a SYSTEM grants the CREATE SESSION system privilege to c##dba. Because the clause CONTAINER=ALL is absent, this privilege is granted locally and thus applies only to the root, which is the current container.
t4 SQL> CREATE ROLE c##admin CONTAINER=ALL; n/a SYSTEM creates a common role named c##admin. The clause CONTAINER=ALL makes the role a common role.
t5 SQL> GRANT SELECT ANY TABLE TO c##admin; Grant succeeded. n/a SYSTEM grants the SELECT ANY TABLE privilege to the c##admin role. The absence of the CONTAINER=ALL clause makes the privilege local to the root. Thus, this common role contains a privilege that is exercisable only in the root.
t6 SQL> GRANT c##admin TO c##dba; SQL> EXIT; n/a SYSTEM grants the c##admin role to c##dba. Because the CONTAINER=ALL clause is absent, the role applies only to the current container, even though it is a common role. If c##dba connects to a PDB, then c##dba does not have this role.
t7 n/a SQL> CONNECT c##dba@hrpdb Enter password: ******* ERROR: ORA-01045: user c##dba lacks CREATE SESSION privilege; logon denied c##dba fails to connect to hrpdb because the grant at t3 was local to the root.
t8 n/a SQL> CONNECT SYSTEM@hrpdb Enter password: ******* Connected. SYSTEM connects to hrpdb.
t9 n/a SQL> GRANT CONNECT, RESOURCE TO c##dba; Grant succeeded. SQL> EXIT SYSTEM grants the CONNECT and RESOURCE roles to common user c##dba. Because the clause CONTAINER=ALL is absent, the grant is local to hrpdb.
t10 n/a SQL> CONNECT c##dba@hrpdb Enter password: ******* Connected. Common user c##dba connects to hrpdb.
t11 n/a SQL> SELECT COUNT(*) FROM hr.employees; select * from hr.employees * ERROR at line 1: ORA-00942: table or view does not exist The query of hr.employees still returns an error because c##dba does not have select privileges on tables in hrpdb. The SELECT ANY TABLE privilege granted locally at t5 is restricted to the root and thus does not apply to hrpdb.
t12 SQL> CONNECT SYSTEM@root Enter password: ******* Connected. n/a Common user SYSTEM connects to the root container.
t13 SQL> GRANT SELECT ANY TABLE TO c##admin CONTAINER=ALL; Grant succeeded. n/a SYSTEM grants the SELECT ANY TABLE privilege to the c##admin role. The presence of CONTAINER=ALL means the privilege is being granted commonly.
t14 n/a SQL> SELECT COUNT(*) FROM hr.employees; select * from hr.employees * ERROR at line 1: ORA-00942: table or view does not exist A query of hr.employees still returns an error. The reason is that at t6 the c##admin common role was granted to c##dba in the root only.
t15 SQL> GRANT c##admin TO c##dba CONTAINER=ALL; Grant succeeded. n/a SYSTEM grants the common role named c##admin to c##dba, specifying CONTAINER=ALL. Now user c##dba has the role in all containers, not just the root.
t17 n/a SQL> SELECT COUNT(*) FROM hr.employees; COUNT(*) ---------- 107 The query succeeds.

  1. Privileges that were granted commonly to a common role are available to the grantee across all containers. In addition, any privilege granted locally to a common role is available to the grantee only in the container in which that privilege was granted to the common role. ↩