-- 例子:授予select on table权限 to UserB -- 要对其他的用户对象授权,请查看all_objects视图,然后写类似的代码create or replace package types as type rc is ref cursor; end; /Declare cur types.rc; sUser varchar2(20); sUser2 varchar2(20); sTable varchar2(30); sSQL varchar2(1024);BEGIN sUser :='UserA'; sUser2 :='UserB';
open cur for ' select table_name from all_tables ' || ' where OWNER=''' || UPPER(sUser) || '''' ; fetch cur into sTable; while cur%found loop sSQL :=' Grant select on ' || sTable ||' to ' || sUser2; execute immediate sSQL; commit; fetch cur into sTable; end loop; close cur; END; /
-- 例子:授予select on table权限 to UserB
-- 要对其他的用户对象授权,请查看all_objects视图,然后写类似的代码create or replace package types
as type rc is ref cursor;
end;
/Declare
cur types.rc;
sUser varchar2(20);
sUser2 varchar2(20);
sTable varchar2(30);
sSQL varchar2(1024);BEGIN
sUser :='UserA';
sUser2 :='UserB';
open cur for
' select table_name from all_tables ' ||
' where OWNER=''' || UPPER(sUser) || '''' ; fetch cur into sTable;
while cur%found
loop
sSQL :=' Grant select on ' || sTable ||' to ' || sUser2;
execute immediate sSQL;
commit; fetch cur into sTable;
end loop; close cur;
END;
/