Thursday, October 20, 2011

Tranfer all Roles and Grants to Another Database

Run in source database:

SET pages 0
SPOOL privs.sql
-- create roles
SELECT 'create role ' || ROLE || ';'
  FROM dba_roles;
-- grant roles
SELECT    'grant '
       || granted_role
       || ' to '
       || grantee
       || DECODE (admin_option, 'YES', ' with admin option;', ';')
  FROM dba_role_privs;
-- grant system privileges
SELECT    'grant '
       || PRIVILEGE
       || ' to '
       || grantee
       || DECODE (admin_option, 'YES', ' with admin option;', ';')
  FROM dba_sys_privs;
-- grant object privileges
BREAK on myGrantor
COL myCommand newline
SELECT   'prompt ' || grantor || '
connect ' || grantor mygrantor,
            'grant '
         || PRIVILEGE
         || ' on '
         || owner
         || '.'
         || table_name
         || ' to '
         || grantee
         || DECODE (grantable, 'YES', ' with grant option;', ';') mycommand
    FROM dba_tab_privs
ORDER BY grantor;
-- grant column privileges
SELECT   'connect ' || grantor mygrantor,
            'grant '
         || PRIVILEGE
         || ' on '
         || owner
         || '.'
         || table_name
         || '('
         || column_name
         || ')'
         || ' to '
         || grantee
         || DECODE (grantable, 'YES', ' with grant option;', ';') mycommand
    FROM dba_col_privs
ORDER BY grantor;
QUIT

This will generate spool file "privs.sql" in current working directory

Remove references to users from privs.sql that does not exist in target database

connect as user with dba role on target database and run script privs.sql Object privileges can be granted by "object owner" only. Script will ask for password of each user who granted object privilege.

No comments: