Pre-creating Oracle database users in PDBs and CDBs

In a previous blog post, I gave an example on how to copy a schema using a database link within the same (pluggable) database.

Few people asked me about the errors during the Data Pump import, mostly because of a pre-created user: ORA-31684: Object type USER:”KERRY” already exists.

The user was created with just the DBA role (for simplicity) but often you need to know in advance, that is prior to creation, the roles and privileges needed for that user. That is, what to grant the user before the import!

Here is how to extract all with one command which creates a script for running the necessary privileges and roles, let us assume that the use is called JDONTCHEFF:

select 'grant '||GRANTED_ROLE||' to JDONTCHEFF;' from DBA_ROLE_PRIVS where grantee = 'JDONTCHEFF'

union all

select 'grant '||PRIVILEGE||' to JDONTCHEFF;' from DBA_SYS_PRIVS where grantee = 'JDONTCHEFF'

union all

select 'grant '||PRIVILEGE||' on '||GRANTOR||'.'||TABLE_NAME||' to JDONTCHEFF;' from DBA_TAB_PRIVS where grantee = 'JDONTCHEFF';

As you can see, all the information can be found/extracted from DBA_ROLE_PRIVS, DBA_SYS_PRIVS and DBA_TAB_PRIVS.

Note that you need to run this in the PDB where the local user is, if you run it in the CDB you will most likely get “no rows selected”.

If you do not have DBA access for some reason, then just modify the above queries to view the privileges just for the current user. This is done by alternatively querying the USER_ versions of the above DBA_ views. And you run them as the current user.

Here is another example which shows that things are not after all that simple:

Note that I have a role called HACKER which has been already granted to the DBA role:

SQL> grant HACKER to DBA;

Grant succeeded.

So, why is the HACKER role missing from the script above? It is because the DBA_ privilege views only display GRANTEES with directly assigned access – note here that the role HACKER is inherited from another role. Well, all fine – it will be eventually granted indirectly. Such roles are not just shown.

select * from dba_role_roles; will show which roles have been granted to roles.

Note also that you need to extract the user profile and the tablespace quotas before pre-creating the user. Use DBA_TS_QUOTAS and DBA_PROFILES. Plus the password!

Source: juliandontcheff.wordpress.com