在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
2sqltext varchar2(200);
3c integer;
4begin
5for userlist in (select user_id,username from all_users where username not in ('SYS','SYSTEM','EYGLE')) loop
6for tablelist in (select owner,table_name from dba_tables where owner = userlist.username) loop
7sqltext := 'grant all on 'tablelist.owner'.'tablelist.table_name ' to eygle with grant option';
8c := sys.dbms_sys_sql.open_cursor();
9sys.dbms_sys_sql.parse_as_user( c,sqltext,dbms_sql.native,userlist.user_id);
10sys.dbms_sys_sql.close_cursor(c);
11end loop;
12end loop;
13end;
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';
OWNERTABLE_NAME PRIVILEGEGRA
------------------------------ ------------------------------ ---------- ---
SCOTTBONUSALTERYES
SCOTTBONUSDELETE YES
SCOTTBONUSINDEXYES
SCOTTBONUSINSERT YES
SCOTTBONUSSELECT YES
SCOTTBONUSUPDATE YES
SCOTTBONUSREFERENCES YES
SCOTTDEPT ALTERYES
SCOTTDEPT DELETE YES
SCOTTDEPT INDEXYES
SCOTTDEPT INSERT YES
OWNERTABLE_NAME PRIVILEGEGRA
------------------------------ ------------------------------ ---------- ---
SCOTTDEPT SELECT YES
SCOTTDEPT UPDATE YES
SCOTTDEPT REFERENCES YES
SCOTTEMPALTERYES
SCOTTEMPDELETE YES
SCOTTEMPINDEXYES
....
right"（出处：清风软件下载学院）
