oracle批量用户授权

(1)用游标实现

BEGIN
  FOR lv_cur IN (
select 'grant select on ' ||owner|| '.' ||table_name|| 'to user2' AS str from dba_tables where owner='USER1'
  ) LOOP
     EXECUTE IMMEDIATE lv_cur.str;
  END LOOP;
END;
/

(2)用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 user2';
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;
/