oracle把所有表查询权限赋与另一用户
oracle把所有表查询权限赋与另一用户。2008-02-01 9:07
昨天有一同事问如何把oracle里一个用户所有表查询权限赋与另一用户，在网上查了好像没有现成的一劳永逸的语句，只能手了一个批量处理语句，如下：
create or replace procedure grant_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 brokeruser3';
???????? Execute Immediate v_sql;
????????
????? else
??????? exit;
????? End if;
???? end loop;
????? Dbms_sql.close_cursor(The_C1);
出处：http://hi.baidu.com/lichangzai/blog/item/ccf30bec6bb451d52e2e2131.html
