Getting ready

To complete this recipe, you will need the following:

How to do it...

How it works...

By completing the 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 the pdb1 database.

The following is how you try to connect to pdb1 as a local user:

SQL> connect mike@pdb1

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, as follows:

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 a 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 the user c##john, we will get the following 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 the 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 the create session privilege (either common or local) to the user c##john, as follows:

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

In the earlier recipe (Granting privileges and roles locally), we granted a create synonym local privilege to a user, c##john. Let's try this privilege on the migrated pdb2:

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

This proves that local privileges are always migrated.