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.)
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.
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:
SQL> connect c##maja@cdb1
c##maja@CDB1> grant create session to c##john container=all;
c##maja@CDB1> grant select any table to c##role1 container=all;
c##maja@CDB1> grant c##role1 to c##role2 container=all;
c##maja@CDB1> grant c##role2 to c##john container=all;
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
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).
To complete this recipe, you will need:
SQL> connect sys@cdb1 as sysdba
SQL> alter pluggable database pdb1 unplug into '/u02/oradata/pdb1.xml';
SQL> drop pluggable database pdb1 keep datafiles;
SQL> connect sys@cdb2 as sysdba
SQL> create pluggable database pdb1 using '/u02/oradata/pdb1.xml' nocopy;
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.
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.
Further resources on this subject:
I remember deciding to pursue my first IT certification, the CompTIA A+. I had signed…
Key takeaways The transformer architecture has proved to be revolutionary in outperforming the classical RNN…
Once we learn how to deploy an Ubuntu server, how to manage users, and how…
Key-takeaways: Clean code isn’t just a nice thing to have or a luxury in software projects; it's a necessity. If we…
While developing a web application, or setting dynamic pages and meta tags we need to deal with…
Software architecture is one of the most discussed topics in the software industry today, and…