« 五一的生活 | Blog首页 | 2005.05.03司马台之行 »
Using DBMS_SYS_SQL Package to grant Privilege
作者：eygle | 【转载请注出处】|【云和恩墨 领先的zData数据库一体机 | zCloud PaaS云管平台 | SQM SQL审核平台 | ZDBM 数据库备份一体机】
链接：https://www.eygle.com/archives/2005/05/using_dbms_sys.html
此话题源于留言板上的一个提问，看了Itpub上也有很久以前的的提问:链接：https://www.eygle.com/archives/2005/05/using_dbms_sys.html
关于用户授权的问题！
在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中的执行过程，供参考:
历史上的今天...
>> 2015-05-07文章:
>> 2012-05-07文章:
>> 2009-05-07文章:
>> 2008-05-07文章:
>> 2006-05-07文章:
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 |
9i之前可以这样，那么9i呢？请解答，多谢