Privileges missing after Oracle full import


Privileges missing after Oracle full import

by Robert Buda | Mar 12, 2014 | Backup and Recovery

Last modified on September 3rd, 2016 at 11:53 pm

Reading Time: 2 minutes

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. 

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? 

So here is how we rebuilt them this time (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. 

Posted on Categories Backup and RecoveryTags ,

4 thoughts on “Privileges missing after Oracle full import”

  1. JuanMa, thanks for the comments. I am not sure I have seen a case where role or system privileges were not brought over but if you have then you can use a similar technique to what I described above to resolve them.

  2. >I am not sure I have seen a case where role or system privileges were not brought over
    transportable tablespace import
    🙂

  3. i have created a script that create DDL of user , roles , cascaded roles , sys privileges and tab privileges

    this will also resolve like :
    eg : roles : A,B,C,D
    grant A to B;
    grant C to C;
    grant C to D;
    grant D to user_1;

    using hierarchical query we can figure out all the cascaded roles ;

    REM AUTHOR
    REM piyushjo@amazon.com
    REM VERSION 1.02
    REM SCRIPT
    REM pre_sql_user_role_ddl.sql
    REM
    REM DESCRIPTION
    REM This script generates another that contains the commands to
    REM create a user and role from source database
    REM
    REM PRE-REQUISITES
    REM 1. execute permission on dbms_metadata package
    REM
    REM PARAMETERS
    REM 1. SCHEMA to migrate
    REM EXECUTION
    REM 1. Connect into SQL*Plus as privileged user or user with access to
    REM data dictionary and dbms_metadata package.
    REM 2. Execute script pre_sql_user_role_ddl.sql passing SCHEMA name.
    REM
    SET FEED OFF
    SET ECHO OFF
    SET HEAD OFF
    SET ARRAYSIZE 1
    SET SPACE 1
    SET VERIFY OFF
    SET PAGES 250
    SET LINES 200
    SET TERMOUT ON
    CLEAR SCREEN
    SET LONG 9999
    SET SERVEROUTPUT ON SIZE 1000000

    accept user_to_find char prompt ‘NAME OF SCHEMA TO MIGRATE [ADMIN]: ‘ default ADMIN
    accept passwd char prompt ‘PASSWORD OF SCHEMA TO MIGRATE [asdfasdf]: ‘ default asdfasdf
    accept exec_role char prompt ‘exclude specific system roles [RESOURCE]: ‘ default RESOURCE

    PRO PRE REQ DDL FILE BEFORE IMPORT datapump_imp_exp_pre_&&user_to_find..sql

    SPO datapump_imp_exp_pre_&&user_to_find..sql
    select ddl3||’;’ from (select regexp_replace(ddl2,REGEXP_SUBSTR(ddl2,'(\S*)(\s)’, 1,10),’&&passwd’) as ddl3 from (select regexp_replace(ddl,REGEXP_SUBSTR(ddl,'(\S*)(\s)’, 1,10)) as ddl2 from (select dbms_metadata.get_ddl(‘USER’, ‘&&user_to_find’) as ddl from dual)));
    –select ddl||’;’ from (select dbms_metadata.get_ddl(‘USER’, ‘&&user_to_find’) as ddl from dual);
    — CREATE ROLES INCLUDING DEPENDENT ROLE

    declare
    CURSOR C1 IS select distinct ROLES from (SELECT CONNECT_BY_ROOT GRANTED_ROLE as ROLES
    FROM dba_role_privs
    WHERE grantee = ‘&&user_to_find’
    CONNECT BY PRIOR grantee = GRANTED_ROLE) ;
    sql_stmt VARCHAR2(2000);
    role_name dba_roles.role%type;
    begin
    OPEN C1;
    LOOP
    FETCH C1 INTO role_name;
    EXIT WHEN C1%NOTFOUND;
    if role_name not in (‘&&exec_role’) then
    for rec in (select dbms_metadata.get_ddl(‘ROLE’, r.role) as ddl from dba_roles r where r.role = role_name
    union all
    select dbms_metadata.get_granted_ddl(‘ROLE_GRANT’, rp.grantee) AS ddl
    from dba_role_privs rp
    where rp.grantee = role_name
    and rownum = 1
    union all
    select dbms_metadata.get_granted_ddl(‘SYSTEM_GRANT’, sp.grantee) AS ddl
    from dba_sys_privs sp
    where sp.grantee = role_name
    and rownum = 1
    ) LOOP
    dbms_output.put_line(rec.ddl||’;’);
    END LOOP;
    end if;
    END LOOP;
    CLOSE C1;
    END;
    /
    — GRTANTING ROLES TO USER
    select case ADMIN_OPTION
    when ‘YES’ THEN ‘GRANT ‘||GRANTED_ROLE||’ TO ‘||GRANTEE||’ WITH ADMIN OPTION; ‘
    ELSE ‘GRANT ‘||GRANTED_ROLE||’ TO ‘||GRANTEE||’;’
    END
    from dba_role_privs where GRANTEE=’&&user_to_find’;
    — SYSTEM LEVEL PRIVILEDGE TO USER
    select case ADMIN_OPTION
    when ‘YES’ THEN ‘GRANT ‘||PRIVILEGE||’ TO ‘||GRANTEE||’ WITH ADMIN OPTION; ‘
    ELSE ‘GRANT ‘||PRIVILEGE||’ TO ‘||GRANTEE||’;’
    END
    from dba_sys_privs where GRANTEE=’&&user_to_find’;

    SPOOL OFF
    — OBJECT LEVEL PRIVILEDGE GRANTED TO ROLE
    SPO datapump_imp_exp_post_&&user_to_find..sql
    PROMPT –OBJECT LEVEL PRIVILEDGE GRANTED TO ROLE

    declare
    CURSOR C1 IS select distinct ROLES from (SELECT CONNECT_BY_ROOT GRANTED_ROLE as ROLES
    FROM dba_role_privs
    WHERE grantee = ‘&&user_to_find’
    CONNECT BY PRIOR grantee = GRANTED_ROLE) ;
    sql_stmt VARCHAR2(2000);
    role_name dba_roles.role%type;
    begin
    OPEN C1;
    LOOP
    FETCH C1 INTO role_name;
    EXIT WHEN C1%NOTFOUND;
    if role_name not in (‘&&exec_role’) then
    for rec in (select dbms_metadata.get_granted_ddl(‘OBJECT_GRANT’, tp.grantee) AS ddl
    from dba_tab_privs tp
    where tp.grantee = role_name
    and rownum = 1) LOOP
    dbms_output.put_line(rec.ddl||’;’);
    END LOOP;
    end if;
    END LOOP;

    CLOSE C1;
    END;
    /
    –OBJECT LEVEL PRIVILEDGE GRANTED TO USER
    PROMPT –OBJECT LEVEL PRIVILEDGE GRANTED TO USER
    select case GRANTABLE
    when ‘YES’ THEN ‘GRANT ‘||PRIVILEGE||’ ON “‘||OWNER||'”.”‘||TABLE_NAME||'” TO ‘||GRANTEE||’ WITH GRANT OPTION;’
    ELSE ‘GRANT ‘||PRIVILEGE||’ ON “‘||OWNER||'”.”‘||TABLE_NAME||'” TO ‘||GRANTEE||’;’
    END
    from DBA_TAB_PRIVS where GRANTEE=’&&user_to_find’;
    SPO OFF
    PRO —————————————————————————-\n
    PRO
    PRO PRE DDL SCRIPT TO BE EXECUTED BEFORE IMPORT : => datapump_imp_exp_pre_&&user_to_find..sql
    PRO PRE DDL SCRIPT TO BE EXECUTED AFTER IMPORT : => datapump_imp_exp_post_&&user_to_find..sql

    undef user_to_find
    undef passwd

Leave a Reply

Your email address will not be published. Required fields are marked *