Oracle であるユーザの権限を別のユーザーにコピーする SQL スクリプト

例えば、SCOTT ユーザーがいたとして、SCOTT ユーザーに SYS ユーザーの権限をコピーしたいような時に使う SQL スクリプト。
SYS ユーザーは例としてよくないかもしれないけど、以下の通り実験に使ってしまったので。。。

• yoheia/create_grant_user_script.sql at master · yoheia/yoheia · GitHub

prompt specify user name which you want to create;
define grantee = &grantee
prompt specify user name whom you want to copy privallege from;
define from_user_name = &from_user_name

set echo off
set pagesize 0
set head off
set feed off
set verify off

col spool_file_name new_value spool_file_name format a100
select 'grant_privilege_to_'||lower('&&grantee')||'.sql' spool_file_name from dual;
spool &spool_file_name

select '-- grant system privilege to user.' from dual;
select 'GRANT '||PRIVILEGE||' TO '|| upper('&&grantee') || DECODE(ADMIN_OPTION,'YES',' WITH ADMIN OPTION','')||';'
        from dba_sys_privs where grantee = upper('&&from_user_name');
select '' from dual;

select '--grant object privilege to user.' from dual;
select 'GRANT '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' TO '|| upper('&&grantee') || DECODE(GRANTABLE,'YES',' WITH GRANT OPTION','')||';'
        from dba_tab_privs where grantee = upper('&&from_user_name');
select '' from dual;

select '--grant role to user.' from dual;
select 'GRANT '||GRANTED_ROLE||' TO '|| upper('&&grantee') || DECODE(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION', '')||';'
        from DBA_ROLE_PRIVS where GRANTEE = upper('&&from_user_name');
select '' from dual;

select 'exit' from dual;

spool off
exit

実行してみるとこんな感じ。

$ sqlplus -s / as sysdba @create_grant_user_script.sql
specify user name which you want to create
Enter value for grantee: scott
specify user name whom you want to copy privallege from
Enter value for from_user_name: sys

$ cat grant_privilege_to_scott.sql 
-- grant system privilege to user.                                              
GRANT CREATE EXTERNAL JOB TO SCOTT;                                             
GRANT MANAGE FILE GROUP TO SCOTT;                                               
GRANT ADMINISTER SQL TUNING SET TO SCOTT;                                       

...

GRANT GATHER_SYSTEM_STATISTICS TO SCOTT WITH ADMIN OPTION;                      
GRANT SELECT_CATALOG_ROLE TO SCOTT WITH ADMIN OPTION;                           
GRANT CONNECT TO SCOTT WITH ADMIN OPTION;                                       
                                                                                
exit

$ sqlplus / as sysdba @grant_privilege_to_scott.sql


Grant succeeded.


Grant succeeded.


...

追記(2010/01/29):
単に SCOTT ユーザが SMITH ユーザのテーブルを参照できるようにしたいとか、ディクショナリ表を参照できるようにしたい場合は、以下のシステム権限を付与してやれば良い。

SQL> conn scott/tiger
Connected.
SQL> select count(1) from sys.dba_tables;
select count(1) from sys.dba_tables
                         *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn / as sysdba
Connected.
SQL> grant select any table to scott;

Grant succeeded.

SQL> grant select any dictionary to scott;

Grant succeeded.

SQL> conn scott/tiger
Connected.
SQL> select count(1) from sys.dba_tables;

  COUNT(1)
----------
      1293

追記(2010/04/05):

SELECT ANY TABLE 任意のスキーマ内の表、ビューまたはマテリアライズド・ビューの問合せ

SQL文: DROP SEQUENCE〜ROLLBACK