ITPub博客

首页 > 数据库 > Oracle > 【GRANTS】【SCRIPTS】两种自动化获得Oracle授权语句的脚本

【GRANTS】【SCRIPTS】两种自动化获得Oracle授权语句的脚本

Oracle 作者:Orace技术交流 时间:2019-04-18 11:40:34 0 删除 编辑

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


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

下一篇: Oracle基础面试题
请登录后发表评论 登录
全部评论

注册时间:2019-04-18

  • 博文量
    12
  • 访问量
    15975