需求是这样:在A用户下,需要批量生成B用户的所有表对应的视图。在网上找了很多例子,最后用比较容易的方法解决了。
注意,不能简单的赋予A用户DBA权限,有些操作也是无效的;另外不能授权所有权限给A用户,否则A用户可以删除B用户中的表。
方法如下:
1.授权B用户connect,resource角色权限,再授予create any view、select any table系统权限。
2.由于要批量处理,所以用到了存储过程,在存储过程中需要查询dba_tables表,所以需要添加对应权限:在sys用户中,grant select any dictionary to B。
3.创建存储过程:
CREATE OR REPLACE PROCEDURE PROC_CREATE_VIEWS IS
CURSOR CUR IS
SELECT OWNER, TABLE_NAME FROM DBA_TABLES WHERE OWNER = 'B';
CREATE_SQL VARCHAR2(500);
BEGIN
FOR TEMP_CUR IN CUR LOOP
CREATE_SQL := 'create or replace view ' || TEMP_CUR.TABLE_NAME ||
' as select * from B.' || TEMP_CUR.TABLE_NAME;
EXECUTE IMMEDIATE CREATE_SQL;
END LOOP;
END PROC_CREATE_VIEWS;
可根据需要,添加参数,变成通用的存储过程。
查询角色对应的系统权限:select * from dba_sys_privs where grantee='CONNECT'
以上亲测可用。