This is expected behavior for a schema level import.
Object privileges for which the import schema is the grantee will not be imported in a schema level import.
Only object privileges granted by the import schema will be imported.
This example will demonstrate which object privileges will be imported.
It will also demonstrate that data pump will import system privileges for a schema level import.
As dba user create new user FOO that will be used for schema level import:
Missing Object Level Grants After Data Pump Schema Level Import (Doc ID 795784.1) To BottomTo Bottom
In this Document
Symptoms
Changes
Cause
Solution
References
APPLIES TO:
Oracle Database Cloud Schema Service – Version N/A and later
Oracle Database Exadata Cloud Machine – Version N/A and later
Oracle Cloud Infrastructure – Database Service – Version N/A and later
Oracle Database Cloud Exadata Service – Version N/A and later
Oracle Database Backup Service – Version N/A and later
Information in this document applies to any platform.
NOTE: In the images and/or the document content below, the user information and data used represents fictitious data. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.
SYMPTOMS
A data pump schema level import has just been performed.
After the import:
you do not see the expected object privileges as viewed from DBA_TAB_PRIVS
- OR –
the newly imported user may not be able to see expected objects and may get errors like following:
ORA-942 table or view does not exist
CHANGES
CAUSE
This is expected behavior for a schema level import.
Object privileges for which the import schema is the grantee will not be imported in a schema level import.
Only object privileges granted by the import schema will be imported.
This example will demonstrate which object privileges will be imported.
It will also demonstrate that data pump will import system privileges for a schema level import.
As dba user create new user FOO that will be used for schema level import:
drop user foo cascade;
CREATE USER foo
IDENTIFIED BY foo
DEFAULT TABLESPACE users
QUOTA 10M ON users
TEMPORARY TABLESPACE temp;
Also, grant these system privileges to user FOO:
grant create session to foo;
grant create table to foo;
grant alter session to foo;
Also, grant select on a sys owned object directly to user FOO:
grant select on v_$latch to FOO;
As user FOO, create a table fooemp:
create table fooemp
(col1 varchar2(20));
Grant select privilege on table fooemp to user SCOTT:
grant select on fooemp to scott;
This example will show that object privileges such as this for which the import schema is the granter will be imported.
Now as user scott or dba, grant select,update privilege on table scott.emp to user foo:
grant select on scott.emp to foo;
grant update on scott.emp to foo with GRANT OPTION;
This example will show that object privileges such as this for which the import schema is the grantee will not be imported.
Verify grantee object privileges for user FOO:
select GRANTEE, OWNER, TABLE_NAME, PRIVILEGE, GRANTABLE from DBA_TAB_PRIVS
where GRANTEE=’FOO’
and TABLE_NAME in (‘EMP’,’V_$LATCH’);
GRANTEE OWNER
TABLE_NAME PRIVILEGE GRA
FOO SYS
V_$LATCH SELECT NO
FOO SCOTT
EMP SELECT NO
FOO SCOTT
EMP UPDATE YES
Verify granter object privileges for user FOO:
select GRANTEE, OWNER, TABLE_NAME, PRIVILEGE from DBA_TAB_PRIVS
where GRANTEE=’SCOTT’
and TABLE_NAME=’FOOEMP’;
GRANTEE OWNER
TABLE_NAME PRIVILEGE
SCOTT FOO
FOOEMP SELECT
Look at system privileges granted to user FOO:
select GRANTEE,PRIVILEGE from DBA_SYS_PRIVS
where GRANTEE=’FOO’;
GRANTEE PRIVILEGE
FOO ALTER SESSION
FOO CREATE SESSION
FOO CREATE TABLE
Run a full data pump export to extract metadata only.
expdp full=y dumpfile=expdp.dmp logfile=expdp.log content=metadata_only exclude=STATISTICS
Now drop user FOO (or you could import to target database where user FOO does not exist);
drop user foo cascade;
Run data pump import to recreate the FOO user.
For a data pump schema level import you do not need to precreate the user.
Data pump will create the user for you. It will also import the system privileges.
impdp schemas=FOO dumpfile=expdp.dmp logfile=userimpdp.log
Now, look to see if the object privileges on scott.emp were given to FOO:
select GRANTEE, OWNER, TABLE_NAME, PRIVILEGE, GRANTABLE from DBA_TAB_PRIVS
where GRANTEE=’FOO’
and TABLE_NAME in (‘EMP’,’V_$LATCH’);
no rows selected
You can see that the object level privileges on scott.emp table were not granted to user FOO during import.
This is expected behavior for both data pump and original exp/imp utility.
The object level grantee privileges are not imported in a schema level or table level import.
This has been addressed and discussed before in unpublished Bug 7206859 (closed as not a bug).
Check the system grants:
select GRANTEE,PRIVILEGE from DBA_SYS_PRIVS
where GRANTEE=’FOO’;
GRANTEE PRIVILEGE
FOO ALTER SESSION
FOO CREATE SESSION
FOO CREATE TABLE
You can see that system privileges are imported for a data pump schema level import.
Run data pump import to a sqlfile to see the DDL for a schema level import:
impdp schemas=FOO dumpfile=expdp.dmp sqlfile=impdp.sql
If you look at the .sql file you will not see any object level grants on the scott.emp table.
You will see only the grant on FOO’s objects such as table FOOEMP and the system privileges granted.
grep -ir “FOO” impdp.sql
GRANT CREATE TABLE TO “FOO”;
GRANT ALTER SESSION TO “FOO”;
GRANT CREATE SESSION TO “FOO”;
— CONNECT FOO
GRANT SELECT ON “FOO”.”FOOEMP” TO “SCOTT”;
If you run a full import to a sqlfile you can see the DDL for all object grants.
Data pump provides finer grain control of objects. Use the include=GRANT to get DDL for grants only.
impdp full=y dumpfile=expdp.dmp sqlfile=fullimpdp.sql INCLUDE=GRANT
If you look at the .sql file you will see DDL for all object level grants on the scott.emp table:
grep -ir “FOO” fullimpdp.sql
GRANT SELECT ON “SCOTT”.”EMP” TO “FOO”;
GRANT UPDATE ON “SCOTT”.”EMP” TO “FOO” WITH GRANT OPTION;
You will also notice that you do not see a grant to FOO on V_$LATCH. This is because a full export does not export sys objects. The object level grants on sys objects cannot be obtained from a schema level or full export. You will need to use sql to extract object level grants on sys objects.
This has been addressed in Bug 5849587 (closed as not a bug).
SOLUTION
Running a full import to a sqlfile as described above is one method to obtain the sql to grant missing object level privileges after a schema level import. You could then run the sql statements as dba user to grant the missing privileges.
Another method would be to run a full import of all grants.
impdp full=y dumpfile=expdp.dmp INCLUDE=GRANT
This would create all system and object grants on the target database.
Another method to obtain the object level privileges would be to run sql against views on the source database.
Then, run the sql to create the missing grants after the schema level import on the target.
For example script on extracting object grants see
Note 1020176.6 – Script to Generate object privilege GRANTS
Ref: https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=340655822332200&parent=EXTERNAL_SEARCH&sourceId=PROBLEM&id=795784.1&_afrWindowMode=0&_adf.ctrl-state=602p2z7b9_4
