Oracle import / export Grant source database grantee does not exist in target database

Hi there,

today I was confronted with the following problem:

I had to export a schema from one database and import to another database. The schema contained grants granted to some user/grantee, who only existed in the source database. These grants should be replaces by another grantee for the target database. A normal export and import was failing with the following errors:

GRANT ON ... ON "SCHEMA"."TABLE" TO "GRANTEE"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'GRANTEE' does not exist

A solution for this scenario is:

Do the normal import and ignore the errors. Then do the import again with the sqlfile parameter:

impdp system/peBa4ci9 schemas=... dumpfile=xxx.dmp sqlfile=getddl.sql

When you run this import command, it will not execute an import. Rather it will only create the sql file which will contain all the DDL statements that the import consists of.

Once you have the file, open it and search for the grants DDL's that affect the user/grantee you want to replace. All DDL's for this will be at the same place such as:

GRANT DEBUG ON "SCHEMA"."TABLE1" TO "GRANTEE";
GRANT QUERY REWRITE ON "SCHEMA"."TABLE2" TO "GRANTEE";
GRANT ON COMMIT REFRESH ON "SCHEMA"."TABLE3" TO "GRANTEE";
GRANT REFERENCES ON "SCHEMA"."TABLE4" TO "GRANTEE";
GRANT UPDATE ON "SCHEMA"."TABLE5" TO "GRANTEE";
GRANT SELECT ON "SCHEMA"."TABLE6" TO "GRANTEE";

Replace the GRANTEE with the one you want to have in the target database and run the GRANT commands again on the target database so that the GRANTS are set correctly. FINISH :)