【GRANTS】【SCRIPTS】两种自动化获得Oracle授权语句的脚本(转载+实验)

原文地址：http://www.cnblogs.com/wuyisky/archive/2009/09/08/1562339.html

对于DBA来说，一切可以简化操作的尝试都要被鼓励。这里提供两种快速得到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.

第二种方法：通过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.

鼓励任何形式的自动化尝试，这才是DBA的真正价值的体现。
-- The End --



下面是我使用这个脚本自己做的一个实验，经验证，该脚本十分准确，崇敬一下^^
※不过还要说明一点，一般情况下，使用普通用户执行下面脚本的时候，DBMS_METADATA的GET_GRANTED_DDL不会显示SYSDBA和SYSOPER权限。
   但是如果使用sys等高级用户的话可以显示全部的权限。
   下面脚本我是使用SYS用户连接后，获得的，所以可以看到授予SCOTT的“DBA” 权限。

脚本：
----------------------------------------------
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;
/
----------------------------------------------
执行结果：
----------------------------------------------
SQL>
SQL> set serveroutput on size 1000000
SQL> set verify off
SQL> undefine user_name
SQL> 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  /

   CREATE USER "SCOTT" IDENTIFIED BY VALUES 'F894844C34402B67'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP";
   GRANT UNLIMITED TABLESPACE TO "SCOTT";
   GRANT "CONNECT" TO "SCOTT";
   GRANT "RESOURCE" TO "SCOTT";
   GRANT "DBA" TO "SCOTT";
   GRANT ALTER ON "HR"."JOBS" TO "SCOTT";
   GRANT DELETE ON "HR"."JOBS" TO "SCOTT";
   GRANT INDEX ON "HR"."JOBS" TO "SCOTT";
   GRANT INSERT ON "HR"."JOBS" TO "SCOTT";
   GRANT SELECT ON "HR"."JOBS" TO "SCOTT";
   GRANT UPDATE ON "HR"."JOBS" TO "SCOTT";
   GRANT REFERENCES ON "HR"."JOBS" TO "SCOTT";
   GRANT READ ON DIRECTORY "TMP_DIR" TO "SCOTT" WITH GRANT OPTION;
   GRANT WRITE ON DIRECTORY "TMP_DIR" TO "SCOTT" WITH GRANT OPTION;
-- No tablespace quota specified
   ALTER USER "SCOTT" DEFAULT ROLE ALL;

PL/SQL procedure successfully completed

SQL>
----------------------------------------------

以上。

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

转载于:http://blog.itpub.net/20335819/viewspace-710156/