Privileges missing after Oracle full import

While performing a migration from Oracle 10g to Oracle 11g (on new hardware) recently,  we encountered a familiar frustration that I am surprised is not resolved yet. Privileges missing after Oracle full import.

There are multiple ways to migrate a database from one version to another. One of the oldest and easiest ways is to create your database in the target location and then use data pump (formerly Export and Import) to bring the data out of the source database and place it in the target.

I am a fan of Oracle and I think its reliability and functionality make it the best database for large, mission-critical applications, but by now I think we should have a full export (expdp) and import (impdp) utility that brings grants over reliably. The problem is that in certain cases not all grants are rebuilt on the target database. This may have to do with the order in which the users are created during the import, resulting in a grant taking place before a user is created, but sometimes they don’t even come over if you do a second import after all the users have been created.

This does not seem like a difficult task for an import routine to handle; multiple intelligent passes through the import file should resolve this. But we are still left to manually rebuild grants at times. Really Oracle? 

We found Privileges missing after Oracle full import, So here is how we rebuilt them.

(there are a variety of ways to do this and this seemed like the best way this time).

In this case, there is only one user that owns tables (the application owner) so we needed only to gather the privileges for that user. Also, there were no other objects such as stored procedures that needed privileges set so we needed only to use dba_tab_privs to gather the privileges. Similar techniques can be used for other object types and for roles.

Here are the steps:

1. If using sqlplus, be sure to set the environment variables such as pagesize, heading, and linesize first, and then spool the output to a file. 

2. Gather grants from the source database by issuing a select statement against the data dictionary tables that will build grant statements. 

select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';' from dba_tab_privs
 where grantor = ‘APPOWNER'
/

This creates a set of grant statements like this:

grant SELECT on APPOWNER.TABLE1 to APPUSER;
grant UPDATE on APPOWNER.TABLE1 to APPUSER;
grant SELECT on APPOWNER.TABLE1 to APPUSER;

3. Execute the generated grants on the target system in sqlplus to restore your grants. These should be executed as the owner of the tables. 

I hope future versions of data pump or successor utilities resolve this issue but until then this is one way to get around this annoying issue. This is only one of a number of ways to work around this annoying deficiency of the data pump utility.  

If you have encountered similar issues and either know a way to avoid this entirely when using data pump or if you have a way of collecting the privileges that you like better than this approach please share your thoughts. 

Skip to content