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.
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 pvfrom dba_sys_privs where grantee = 'DUMMY' unionselect username grantee, '---' type, 'empty user ---' pv from dba_userswhere not username in (select distinct grantee from dba_role_privs) andnot username in (select distinct grantee from dba_sys_privs) andnot username in (select distinct grantee from dba_tab_privs) and username like 'DUMMY'group by usernameorder by grantee, type, pv;
References:
Primary Note For Privileges And Roles (Doc ID 1347470.1)
