Missing Grants After Data Pump Import

1.Object privileges for which the import schema is the grantee will not be imported in a schema level import.
2.Only object privileges granted by the import schema will be imported.
3.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.
4.run a full import of all grants.impdp full=y dumpfile=expdp.dmp
This would create all system and object grants on the target database.

 

http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#i1007466
------------------------------
Data Pump Export
Data Pump Import
Specifying EXCLUDE=GRANT excludes object grants on all object types and system privilege grants.
------------------------------

 

Missing Object Level Grants After Data Pump Schema Level Import [ID 795784.1]

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 before and discussed 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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24756186/viewspace-757220/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24756186/viewspace-757220/