« 五一的生活 | Blog首页 | 2005.05.03司马台之行 »
Using DBMS_SYS_SQL Package to grant Privilege
作者：eygle |【转载时请务必以超链接形式标明文章原始出处和作者信息及本声明】链接：http://www.eygle.com/archives/2005/05/using_dbms_sys.html
此话题源于留言板上的一个提问，看了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中的执行过程，供参考:
-----
这篇 【Using DBMS_SYS_SQL Package to grant Privilege】来自 www.eygle.com | CSDN技术网摘| del.icio.us|365Key
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
....
|
-----
这篇 【Using DBMS_SYS_SQL Package to grant Privilege】来自 www.eygle.com | CSDN技术网摘| del.icio.us|365Key
By eygle on 2005-05-07 09:35 | Comments (1) | Posted to SQL.PLSQL | Edit |Pageviews:
| 相关文章 | 随机文章 |
|
用Rman的Plus archvielog选项简化数据库备份 Oracle HowTo:How to get Oracle SCN? 今天参加的Oracle Technology Day Lilina与李敖 转眼又是一年 |
网上相关主题:
留言 (1)
9i之前可以这样，那么9i呢？请解答，多谢
Posted by: 八头九面 at August 3, 2006 4:00 PM
