用DBMS_SYS_SQL包进行批量授权
在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
....