两种自动化获得Oracle授权语句的脚本

对于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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25957310/viewspace-712831/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25957310/viewspace-712831/