In this article by Zoran Pavlović and Maja Veselica, authors of the book, Oracle Database 12c Security Cookbook, we will be introduced to common privileges and learn how to grant privileges and roles commonly. We’ll also study the effects of plugging and unplugging operations on users, roles, and privileges.

(For more resources related to this topic, see here.)

Granting privileges and roles commonly

Common privilege is a privilege that can be exercised across all containers in a container database. Depending only on the way it is granted, a privilege becomes common or local. When you grant privilege commonly (across all containers) it becomes common privilege. Only common users or roles can have common privileges. Only common role can be granted commonly.

Getting ready

For this recipe, you will need to connect to the root container as an existing common user who is able to grant a specific privilege or existing role (in our case – create session, select any table, c##role1, c##role2) to another existing common user (c##john). If you want to try out examples in the How it works section given ahead, you should open pdb1 and pdb2.

You will use:

• Common users c##maja and c##zoran with dba role granted commonly
• Common user c##john
• Common roles c##role1 and c##role2

How to do it…

1. You should connect to the root container as a common user who can grant these privileges and roles (for example, c##maja or system user).

   SQL> connect c##maja@cdb1

2. Grant a privilege (for example, create session) to a common user (for example, c##john) commonly

   c##maja@CDB1> grant create session to c##john container=all;

3. Grant a privilege (for example, select any table) to a common role (for example, c##role1) commonly

   c##maja@CDB1> grant select any table to c##role1 container=all;

4. Grant a common role (for example, c##role1) to a common role (for example, c##role2) commonly

   c##maja@CDB1> grant c##role1 to c##role2 container=all;

5. Grant a common role (for example, c##role2) to a common user (for example, c##john) commonly

   c##maja@CDB1> grant c##role2 to c##john container=all;

How it works…

Figure 16

You can grant privileges or common roles commonly only to a common user. You need to connect to the root container as a common user who is able to grant a specific privilege or role.

In step 2, system privilege, create session, is granted to common user c##john commonly, by adding a container=all clause to the grant statement. This means that user c##john can connect (create session) to root or any pluggable database in this container database (including all pluggable databases that will be plugged-in in the future).

N.B. container = all clause is NOT optional, even though you are connected to the root. Unlike during creation of common users and roles (if you omit container=all, user or role will be created in all containers – commonly), If you omit this clause during privilege or role grant, privilege or role will be granted locally and it can be exercised only in root container.

SQL> connect c##john/oracle@cdb1

c##john@CDB1> connect c##john/oracle@pdb1

c##john@PDB1> connect c##john/oracle@pdb2

c##john@PDB2>

In the step 3, system privilege, select any table, is granted to common role c##role1 commonly. This means that role c##role1 contains select any table privilege in all containers (root and pluggable databases).

c##zoran@CDB1> select * from role_sys_privs where role='C##ROLE1';

ROLE               PRIVILEGE               ADM   COM
-------------       -----------------       ---   ---
C##ROLE1           SELECT ANY TABLE       NO     YES

c##zoran@CDB1> connect c##zoran/oracle@pdb1

c##zoran@PDB1> select * from role_sys_privs where role='C##ROLE1';

ROLE               PRIVILEGE             ADM    COM
--------------     ------------------     ---   ---
C##ROLE1           SELECT ANY TABLE       NO     YES

c##zoran@PDB1> connect c##zoran/oracle@pdb2

c##zoran@PDB2> select * from role_sys_privs where role='C##ROLE1';

ROLE              PRIVILEGE             ADM     COM
--------------     ----------------       ---     ---
C##ROLE1           SELECT ANY TABLE       NO       YES

In step 4, common role c##role1, is granted to another common role c##role2 commonly. This means that role c##role2 has granted role c##role1 in all containers.

c##zoran@CDB1> select * from role_role_privs where role='C##ROLE2';

ROLE               GRANTED ROLE           ADM     COM
---------------     ---------------       ---     ---
C##ROLE2           C##ROLE1               NO       YES

c##zoran@CDB1> connect c##zoran/oracle@pdb1

c##zoran@PDB1> select * from role_role_privs where role='C##ROLE2';

ROLE               GRANTED_ROLE           ADM   COM
-------------       -----------------      ---   ---
C##ROLE2           C##ROLE1               NO     YES

c##zoran@PDB1> connect c##zoran/oracle@pdb2

c##zoran@PDB2> select * from role_role_privs where role='C##ROLE2';

ROLE           GRANTED_ROLE           ADM     COM
-------------       -------------     ---     ---
C##ROLE2           C##ROLE1           NO       YES

In step 5, common role c##role2, is granted to common user c##john commonly. This means that user c##john has c##role2 in all containers.

Consequently, user c##john can use select any table privilege in all containers in this container database.

c##john@CDB1> select count(*) from c##zoran.t1;

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

         4

c##john@CDB1> connect c##john/oracle@pdb1

c##john@PDB1> select count(*) from hr.employees;

COUNT(*)
----------
       107

c##john@PDB1> connect c##john/oracle@pdb2

c##john@PDB2> select count(*) from sh.sales;

COUNT(*)
----------
   918843

Effects of plugging/unplugging operations on users, roles, and privileges

Purpose of this recipe is to show what is going to happen to users, roles, and privileges when you unplug a pluggable database from one container database (cdb1) and plug it into some other container database (cdb2).

Getting ready

To complete this recipe, you will need:

• Two container databases (cdb1 and cdb2)
• One pluggable database (pdb1) in container database cdb1
• Local user mike in pluggable database pdb1 with local create session privilege
• Common user c##john with create session common privilege and create synonym local privilege on pluggable database pdb1

How to do it…

1. Connect to the root container of cdb1 as user sys:

   SQL> connect sys@cdb1 as sysdba

2. Unplug pdb1 by creating XML metadata file:

   SQL> alter pluggable database pdb1 unplug into '/u02/oradata/pdb1.xml';

3. Drop pdb1 and keep datafiles:

   SQL> drop pluggable database pdb1 keep datafiles;

4. Connect to the root container of cdb2 as user sys:

   SQL> connect sys@cdb2 as sysdba

5. Create (plug) pdb1 to cdb2 by using previously created metadata file:

   SQL> create pluggable database pdb1 using '/u02/oradata/pdb1.xml' nocopy;

How it works…

By completing previous steps, you unplugged pdb1 from cdb1 and plugged it into cdb2. After this operation, all local users and roles (in pdb1) are migrated with pdb1 database.

If you try to connect to pdb1 as a local user:

SQL> connect mike@pdb1

It will succeed. All local privileges are migrated, even if they are granted to common users/roles. However, if you try to connect to pdb1 as a previously created common user c##john, you’ll get an error

SQL> connect c##john@pdb1

ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.

This happened because after migration, common users are migrated in a pluggable database as locked accounts. You can continue to use objects in these users’ schemas, or you can create these users in root container of a new CDB. To do this, we first need to close pdb1:

sys@CDB2> alter pluggable database pdb1 close;

Pluggable database altered.

sys@CDB2> create user c##john identified by oracle container=all;

User created.

sys@CDB2> alter pluggable database pdb1 open;

Pluggable database altered.

If we try to connect to pdb1 as user c##john, we will get an error:
SQL> conn c##john/oracle@pdb1

ERROR:
ORA-01045: user C##JOHN lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.

Even though c##john had create session common privilege in cdb1, he cannot connect to the migrated PDB. This is because common privileges are not migrated! So we need to give create session privilege (either common or local) to user c##john.

sys@CDB2> grant create session to c##john container=all;

Grant succeeded.

Let’s try granting a create synonym local privilege to the migrated pdb2:

c##john@PDB1> create synonym emp for hr.employees;

Synonym created.

This proves that local privileges are always migrated.

Summary

In this article, we learned about common privileges and the methods to grant common privileges and roles to users. We also studied what happens to users, roles, and privileges when you unplug a pluggable database from one container database and plug it into some other container database.

Resources for Article:

---

Further resources on this subject:

• Oracle 12c SQL and PL/SQL New Features[article]
• Oracle GoldenGate 12c — An Overview[article]
• Backup and Recovery for Oracle SOA Suite 12C[article]

---