下面代码是个存储过程,需要执行这个存储过程来批量赋权限.
执行存储过程之前,先看代码中的注释内容.
exec grant_selectAll_sql ;
create or replace procedure grant_selectAll_sql as
The_c1 Integer;
The_result number;
v_Table varchar2(80);
v_sql varchar2(400);
Begin
The_C1 :=Dbms_sql.open_cursor;
Dbms_sql.parse(the_C1,'Select table_name From User_Tables',dbms_sql.native);
Dbms_sql.define_column(the_c1,1,v_Table,80);
The_result:=Dbms_sql.execute(The_c1);
loop
If dbms_sql.fetch_rows(The_c1) > 0 then
Dbms_sql.column_value(The_c1,1,v_Table);
v_sql := 'Grant Select On '||v_Table||' to T'; -- T为目标用户,只需要修改这个即可
Execute Immediate v_sql;
else
exit;
End if;
end loop;
Dbms_sql.close_cursor(The_C1);
End;