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

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