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.
--转自