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.
Not so easy. You forget roles granted ( dba_role_privs) and system privileges (dba_sys_privs),
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.
>I am not sure I have seen a case where role or system privileges were not brought over
transportable tablespace import
🙂
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 [email protected]
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