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
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:
Post a Comment