對於DBA來說，一切可以簡化操作的嘗試都值得鼓勵，因為在故障來臨的時候，擁有一套順手高效的指令碼可見大大的縮短故障對業務的衝擊。這裡提供兩種快速得到Oracle授權語句的指令碼，供參考。

1.第一種獲取Oracle授權語句方法
可以透過SQL從一些資料字典中查詢到授權資訊，生成授權語句。
undefine user_name
set pagesize 1000
select 'grant '||tt.granted_role||' to '||tt.grantee||';' as SQL_text
from dba_role_privs tt where tt.grantee=(upper('&&user_name'))
union all
select 'grant '||tt.privilege||' to '||tt.grantee||';'
from dba_sys_privs tt where tt.grantee=(upper('&&user_name'))
union all
select 'grant '||tt.privilege||' on '||owner||'.'||table_name||' to '||tt.grantee||';'
from dba_tab_privs tt where tt.grantee=(upper('&&user_name'))
union all
select 'alter user '||tt.user_name||' quota '||maxblocks*blocksize||' on '||ts_name||';'
from KU$_TSQUOTA_VIEW tt where tt.user_name=(upper('&&user_name'));

使用效果如下：
sys@ora10g> undefine user_name
sys@ora10g> set pagesize 1000
sys@ora10g> select 'grant '||tt.granted_role||' to '||tt.grantee||';' as SQL_text
2 from dba_role_privs tt where tt.grantee=(upper('&&user_name'))
3 union all
4 select 'grant '||tt.privilege||' to '||tt.grantee||';'
5 from dba_sys_privs tt where tt.grantee=(upper('&&user_name'))
6 union all
7 select 'grant '||tt.privilege||' on '||owner||'.'||table_name||' to '||tt.grantee||';'
8 from dba_tab_privs tt where tt.grantee=(upper('&&user_name'))
9 union all
10 select 'alter user '||tt.user_name||' quota '||maxblocks*blocksize||' on '||ts_name||';'
11 from KU$_TSQUOTA_VIEW tt where tt.user_name=(upper('&&user_name'));
Enter value for user_name: sec
old   2: from dba_role_privs tt where tt.grantee=(upper('&&user_name'))
new   2: from dba_role_privs tt where tt.grantee=(upper('sec'))
old   5: from dba_sys_privs tt where tt.grantee=(upper('&&user_name'))
new   5: from dba_sys_privs tt where tt.grantee=(upper('sec'))
old   8: from dba_tab_privs tt where tt.grantee=(upper('&&user_name'))
new   8: from dba_tab_privs tt where tt.grantee=(upper('sec'))
old 11: from KU$_TSQUOTA_VIEW tt where tt.user_name=(upper('&&user_name'))
new 11: from KU$_TSQUOTA_VIEW tt where tt.user_name=(upper('sec'))

SQL_TEXT
---------------------------------------
grant DBA to SEC;
grant UNLIMITED TABLESPACE to SEC;
grant WRITE on SYS.DIR1 to SEC;
grant READ on SYS.DIR1 to SEC;
grant READ on SYS.dir2 to SEC;
grant WRITE on SYS.dir2 to SEC;

6 rows selected.

2.第二種獲取Oracle授權語句方法
透過Oracle提供的dbms_metadata包，獲得更加詳細準確的建立使用者以及授權的DDL語句。
set serveroutput on size 1000000
set verify off
undefine user_name
declare
v_name varchar2(30) := upper('&user_name');
no_grant exception;
pragma exception_init( no_grant, -31608 );
begin
dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
dbms_output.enable(1000000);
dbms_output.put_line(dbms_metadata.get_ddl('USER',v_name));
begin
   dbms_output.put_line(dbms_metadata.get_granted_ddl('SYSTEM_GRANT',v_name));
exception
   when no_grant then dbms_output.put_line('-- No system privs granted');
end;
begin
   dbms_output.put_line(dbms_metadata.get_granted_ddl('ROLE_GRANT',v_name));
exception
   when no_grant then dbms_output.put_line('-- No role privs granted');
end;
begin
   dbms_output.put_line(dbms_metadata.get_granted_ddl('OBJECT_GRANT',v_name));
exception
   when no_grant then dbms_output.put_line('-- No object privs granted');
end;
begin
dbms_output.put_line(dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA',v_name));
exception
   when no_grant then dbms_output.put_line('-- No tablespace quota specified');
end;
dbms_output.put_line(dbms_metadata.get_granted_ddl('DEFAULT_ROLE', v_name ));
exception
when others then
if SQLCODE = -31603 then dbms_output.put_line('-- User does not exists');
else raise;
end if;
end;
/

使用過程如下：
sys@ora10g> set serveroutput on size 1000000
sys@ora10g> set verify off
sys@ora10g> undefine user_name
sys@ora10g> declare
2   v_name varchar2(30) := upper('&user_name');
3   no_grant exception;
4   pragma exception_init( no_grant, -31608 );
5 begin
6   dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
7   dbms_output.enable(1000000);
8   dbms_output.put_line(dbms_metadata.get_ddl('USER',v_name));
9   begin
10     dbms_output.put_line(dbms_metadata.get_granted_ddl('SYSTEM_GRANT',v_name));
11   exception
12     when no_grant then dbms_output.put_line('-- No system privs granted');
13   end;
14   begin
15     dbms_output.put_line(dbms_metadata.get_granted_ddl('ROLE_GRANT',v_name));
16   exception
17     when no_grant then dbms_output.put_line('-- No role privs granted');
18   end;
19   begin
20     dbms_output.put_line(dbms_metadata.get_granted_ddl('OBJECT_GRANT',v_name));
21   exception
22     when no_grant then dbms_output.put_line('-- No object privs granted');
23   end;
24   begin
25    dbms_output.put_line(dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA',v_name));
26   exception
27     when no_grant then dbms_output.put_line('-- No tablespace quota specified');
28   end;
29   dbms_output.put_line(dbms_metadata.get_granted_ddl('DEFAULT_ROLE', v_name ));
30 exception
31   when others then
32    if SQLCODE = -31603 then dbms_output.put_line('-- User does not exists');
33    else raise;
34    end if;
35 end;
36 /
Enter value for user_name: sec

   CREATE USER "SEC" IDENTIFIED BY VALUES '9EC74A4FC0A9E227'
      DEFAULT TABLESPACE "TBS_SEC_D"
      TEMPORARY TABLESPACE
"TEMP";

GRANT UNLIMITED TABLESPACE TO "SEC";

   GRANT "DBA" TO "SEC";

GRANT READ ON DIRECTORY "DIR1" TO "SEC" WITH GRANT OPTION;

GRANT WRITE ON DIRECTORY "DIR1" TO "SEC" WITH GRANT OPTION;

GRANT WRITE ON DIRECTORY "dir2" TO "SEC" WITH GRANT OPTION;

GRANT READ ON DIRECTORY "dir2" TO "SEC" WITH GRANT OPTION;

-- No tablespace quota specified

   ALTER USER "SEC" DEFAULT ROLE ALL;

PL/SQL procedure successfully completed.

3.小結
鼓勵任何形式的自動化嘗試，這是DBA真正價值的體現。歡迎大家提供更多更好的指令碼。

Good luck.

secooler
10.10.07

-- The End --

【SCRIPTS】兩種自動化獲得Oracle授權語句的指令碼

相關文章