用DBMS_SYS_SQL包進行批量授權
2024-07-21 02:33:50
供稿：網友
在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
.... right">（出處：清風軟件下載學院）
