eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 五一的生活 | Blog首页 | 2005.05.03司马台之行 »

Using DBMS_SYS_SQL Package to grant Privilege

此话题源于留言板上的一个提问,看了Itpub上也有很久以前的的提问:
关于用户授权的问题!
在Oracle9i之前,如果你想要把对象权限授予某些用户,那么你需要使用该对象属主或者使用具有该对象with grant option权限的用户。
很多时候你可能需要进行批量授权,那么DBMS_SYS_SQL包可以为你提供简便.
以下过程供参考:
declare 
sqltext 	varchar2(200); 
c 		integer; 
begin 
for userlist in (select user_id,username from all_users where username not in ('SYS','SYSTEM','EYGLE')) loop
for tablelist in (select owner,table_name from dba_tables where owner = userlist.username) loop 
sqltext := 'grant all on '||tablelist.owner||'.'||tablelist.table_name ||' to eygle with grant option'; 
c := sys.dbms_sys_sql.open_cursor(); 
sys.dbms_sys_sql.parse_as_user( c,sqltext,dbms_sql.native,userlist.user_id); 
sys.dbms_sys_sql.close_cursor(c); 
end loop; 
end loop;
end; 
/ 


以下是817中的执行过程,供参考:
SQL> declare
  2  sqltext         varchar2(200);
  3  c               integer;
  4  begin
  5  for userlist in (select user_id,username from all_users where username not in ('SYS','SYSTEM','EYGLE')) loop
  6  for tablelist in (select owner,table_name from dba_tables where owner = userlist.username) loop
  7  sqltext := 'grant all on '||tablelist.owner||'.'||tablelist.table_name ||' to eygle with grant option';
  8  c := sys.dbms_sys_sql.open_cursor();
  9  sys.dbms_sys_sql.parse_as_user( c,sqltext,dbms_sql.native,userlist.user_id);
 10  sys.dbms_sys_sql.close_cursor(c);
 11  end loop;
 12  end loop;
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL> 
SQL> set pause on
SQL> select owner,table_name,privilege,grantable from dba_tab_privs where grantee='EYGLE' and owner='SCOTT';


OWNER                          TABLE_NAME                     PRIVILEGE  GRA
------------------------------ ------------------------------ ---------- ---
SCOTT                          BONUS                          ALTER      YES
SCOTT                          BONUS                          DELETE     YES
SCOTT                          BONUS                          INDEX      YES
SCOTT                          BONUS                          INSERT     YES
SCOTT                          BONUS                          SELECT     YES
SCOTT                          BONUS                          UPDATE     YES
SCOTT                          BONUS                          REFERENCES YES
SCOTT                          DEPT                           ALTER      YES
SCOTT                          DEPT                           DELETE     YES
SCOTT                          DEPT                           INDEX      YES
SCOTT                          DEPT                           INSERT     YES


OWNER                          TABLE_NAME                     PRIVILEGE  GRA
------------------------------ ------------------------------ ---------- ---
SCOTT                          DEPT                           SELECT     YES
SCOTT                          DEPT                           UPDATE     YES
SCOTT                          DEPT                           REFERENCES YES
SCOTT                          EMP                            ALTER      YES
SCOTT                          EMP                            DELETE     YES
SCOTT                          EMP                            INDEX      YES
....


历史上的今天...
    >> 2015-05-07文章:
    >> 2012-05-07文章:
    >> 2009-05-07文章:
    >> 2008-05-07文章:
    >> 2006-05-07文章:
           繁华,不过是一掬细沙
           节日过后 几点关注

无觅

By eygle on 2005-05-07 09:35 | Comments (1) | SQL.PLSQL | 277 |

1 Comment

9i之前可以这样,那么9i呢?请解答,多谢


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com