Datapump & system privileges with property value = 1

Many automation tools like Autoupgrade (a sample demo is provided in this link), Zero Downtime Migration (ZDM), etc has been released by Oracle to make DBA life easier. Though we have these tools, we always run towards the most preferred and simple method of Datapump whether to jump the DBs from one server to another or one version to another if it can be done within the allowed downtime. It is such a powerful utility used by almost every DBA in their work life.

In one of my previous post, I have explained regarding few tips to make datapump job perform better. They still stand true for current version of Oracle as well with many advancements included.

Today we see regarding the privileges with property value =1 and its effect on datapump. 

In my recent migration of DB from version 11.2.0.4 to 19c, post migration using datapump, application complained stating few privileges are missing in the 19c DB. This can't be possible as I have taken a completed schema export and imported in the target database without any errors reported in the impdp logfile.

Upon investigating, ADMINISTER RESOURCE MANAGER privilege has not granted in the 19c DB though there are no errors in both expdp and impdp logfile. 

Taking a look at this Doc ID 1163383.1, a few privileges are not granted normally via grant statements but they have to be granted through specific plsql packages. 

How do we find those privileges? - The below query can provide and the result differs w.r.to DB version. Example from my source system 11g. 

SQL> select name from system_privilege_map where property=1 order by name;

NAME
----------------------------------------
ADMINISTER RESOURCE MANAGER
ALTER ANY EVALUATION CONTEXT
ALTER ANY RULE
ALTER ANY RULE SET
CREATE ANY EVALUATION CONTEXT
CREATE ANY RULE
CREATE ANY RULE SET
CREATE EVALUATION CONTEXT
CREATE RULE
CREATE RULE SET
DEQUEUE ANY QUEUE

NAME
----------------------------------------
DROP ANY EVALUATION CONTEXT
DROP ANY RULE
DROP ANY RULE SET
ENQUEUE ANY QUEUE
EXECUTE ANY EVALUATION CONTEXT
EXECUTE ANY RULE
EXECUTE ANY RULE SET
MANAGE ANY FILE GROUP
MANAGE ANY QUEUE
MANAGE FILE GROUP
READ ANY FILE GROUP

22 rows selected.

SQL>

All the above are granted via specific packages or via explicit grants on the system. 

How do we find which privileges does our source schema has from the above list which needs to be granted explicitly via packages? - The below query is an example

SQL> select p.grantee, m.name from system_privilege_map m, dba_sys_privs p
  2  where m.name=p.privilege and m.property=1 and p.grantee in ('DUMMY')
  3  order by p.grantee, m.name;

GRANTEE                        NAME
------------------------------ ----------------------------------------
DUMMY                          ADMINISTER RESOURCE MANAGER
DUMMY                          READ ANY FILE GROUP

SQL>

I have got 2 of such privileges that needs explicit grants on the target DB. 

Now, on the 19c DB we need to grant the privileges as below. A few would require explicit grant (like the READ ANY FILE GOUP) and  few would require grants via packages as below (like the ADMINISTER RESOURCE MANAGER). 

SQL> @check_user_privs.sql

GRANTEE                        TYP PRIVILEGE OR ROLE
------------------------------ --- ---------------------------------------------------------------------------
DUMMY                          PRV ALTER SESSION
DUMMY                              CREATE ANY DIRECTORY
DUMMY                              CREATE DATABASE LINK
DUMMY                              CREATE JOB
DUMMY                              CREATE PROCEDURE
DUMMY                              CREATE SEQUENCE
DUMMY                              CREATE SESSION
DUMMY                              CREATE SYNONYM
DUMMY                              CREATE TABLE
DUMMY                              CREATE TYPE
DUMMY                              CREATE VIEW
DUMMY                              SELECT ANY DICTIONARY
DUMMY                              UNLIMITED TABLESPACE


13 rows selected.

SQL> BEGIN
  DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE(
   GRANTEE_NAME   => 'DUMMY',
   PRIVILEGE_NAME => 'ADMINISTER_RESOURCE_MANAGER',
   ADMIN_OPTION   => FALSE);
END;
/  2    3    4    5    6    7

PL/SQL procedure successfully completed.

SQL> grant READ ANY FILE GROUP to DUMMY;

Grant succeeded.

SQL> @check_user_privs.sql

GRANTEE                        TYP PRIVILEGE OR ROLE
------------------------------ --- ---------------------------------------------------------------------------
DUMMY                          PRV ADMINISTER RESOURCE MANAGER
DUMMY                              ALTER SESSION
DUMMY                              CREATE ANY DIRECTORY
DUMMY                              CREATE DATABASE LINK
DUMMY                              CREATE JOB
DUMMY                              CREATE PROCEDURE
DUMMY                              CREATE SEQUENCE
DUMMY                              CREATE SESSION
DUMMY                              CREATE SYNONYM
DUMMY                              CREATE TABLE
DUMMY                              CREATE TYPE
DUMMY                              CREATE VIEW
DUMMY                              READ ANY FILE GROUP
DUMMY                              SELECT ANY DICTIONARY
DUMMY                              UNLIMITED TABLESPACE


15 rows selected.

SQL>

Now, we are all set. So while performing export and import we need to make sure to take care of these privilege grants without fail for the application to run without any issues. 

Note: Similarly, grants on sys owned objects will also be not transferred via datapump to target database. They have to be explicitly granted on the target database. 

Query used: 

check_user_privs.sql

select grantee, 'PRV' type, privilege pv 

from dba_sys_privs where grantee = 'DUMMY' union

select username grantee, '---' type, 'empty user ---' pv from dba_users 

where not username in (select distinct grantee from dba_role_privs) and

not username in (select distinct grantee from dba_sys_privs) and 

not username in (select distinct grantee from dba_tab_privs) and username like 'DUMMY'

group by username

order by grantee, type, pv;

References: 

Primary Note For Privileges And Roles (Doc ID 1347470.1)

The dbms_metadata.get_granted_ddl Does Not Extract all System Privileges (Doc ID 1163383.1)

Data Pump: GRANTs On SYS Owned Objects Are Not Transferred With Data Pump And Are Missing In The Target Database (Doc ID 1911151.1)

Happy Datapumping...!!!