What happens when you unplug a pluggable database that has local users who have been granted common roles? They get copied upon plug-in of the PDB to the target container database!
The picture above shows the situation before the unplug command. It has been implemented with these commands:
SQL> connect / as sysdba Connected. SQL> create role c##role container=all; Role created. SQL> grant select any table to c##role container=all; Grant succeeded. SQL> connect sys/oracle_4U@pdb1 as sysdba Connected. SQL> grant c##role to app; Grant succeeded. SQL> grant create session to app; Grant succeeded.
The local user app has now been granted the common role c##role. Let’s assume that the application depends on the privileges inside the common role. Now the pdb1 is unplugged and plugged in to cdb2:
SQL> shutdown immediate Pluggable Database closed. SQL> connect / as sysdba Connected. SQL> alter pluggable database pdb1 unplug into '/home/oracle/pdb1.xml'; Pluggable database altered. SQL> drop pluggable database pdb1; Pluggable database dropped. SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@EDE5R2P0 ~]$ . oraenv ORACLE_SID = [cdb1] ? cdb2 The Oracle base for ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1 is /u01/app/oracle [oracle@EDE5R2P0 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 29 12:52:19 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> create pluggable database pdb1 using '/home/oracle/pdb1.xml' nocopy; Pluggable database created. SQL> alter pluggable database pdb1 open; Pluggable database altered. SQL> connect app/app@pdb1 Connected. SQL> select * from scott.dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> select * from session_privs; PRIVILEGE ---------------------------------------- CREATE SESSION SELECT ANY TABLE SQL> connect / as sysdba Connected. SQL> select role,common from cdb_roles where role='C##ROLE'; ROLE -------------------------------------------------------------------------------- COM --- C##ROLE YES
As seen above, the common role has been copied upon the plug-in like the picture illustrates:
Not surprisingly the local user app together with the local privilege CREATE SESSION was moved to the target container database. But it is not so obvious that the common role is copied then to the target CDB. This is something I found out during delivery of a recent Oracle University LVC about 12c New Features, thanks to a question of one attendee. My guess was it will lead to an error upon unplug, but this test-case proves it doesn’t. I thought that behavior may be of interest to the Oracle Community. As always: Don’t believe it, test it! 🙂
#1 von Jim Roll am Februar 3, 2015 - 12:35
There is another interesting case. When common user have some objects in PDB and this PDB moved to another CDB. User became locked in mover PDB. And it’s not visible at new CDB level. However, you are not able to create new one because of username conflict.