创建只读视图
以下脚本用于在同一数据库中创建任何现有模式的只读模式。 只读模式仅被授予对现有模式的表和视图的读取权限。 没有其他任何对象的特权。 脚本可以根据要求进行定制。
CREATE OR REPLACE PROCEDURE CREATE_READONLY_SCHEMA (
i_owner IN VARCHAR2,
i_readonly IN VARCHAR2
)
IS
CURSOR cur_c1
IS
SELECT OBJECT_NAME, OBJECT_TYPE
FROM DBA_OBJECTS
WHERE OWNER = i_owner
AND OBJECT_TYPE IN ('TABLE', 'VIEW')
AND OBJECT_NAME NOT LIKE 'BIN$%' AND STATUS = 'VALID';
BEGIN
BEGIN
IF i_owner=i_readonly then
dbms_output.put_line('Please use a different name for the Readonly Schema');
exit;
else
--drop the readonly user if it already exists. This grants previliges on newly added tables and views in the original schema.
EXECUTE IMMEDIATE
'DROP USER '||i_readonly ||' CASCADE';
end if;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
BEGIN
EXECUTE IMMEDIATE
'CREATE USER ' || i_readonly || ' IDENTIFIED BY ' || i_readonly || ' DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP2';
--The tablespace part can be customized.
--The password can also be customized or altered later on.
EXECUTE IMMEDIATE
'GRANT CONNECT TO ' || i_readonly;
EXCEPTION
WHEN OTHERS
THEN
--add any exception handler if you want.
NULL;
END;
FOR rec IN cur_c1
LOOP
BEGIN
EXECUTE IMMEDIATE
'GRANT SELECT ON ' || i_owner || '.' || rec.OBJECT_NAME || ' TO ' || i_readonly;
EXECUTE IMMEDIATE
'CREATE OR REPLACE VIEW ' || i_readonly || '.' || rec.OBJECT_NAME || ' AS SELECT * FROM ' || i_owner || '.' || rec.OBJECT_NAME;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('Refresh Attempt Failed for : ' || rec.Object_Name || ' (' || rec.Object_Type || ')');
END;
END LOOP;
END CREATE_READONLY_SCHEMA;
翻译自: https://bytes.com/topic/oracle/insights/856090-script-create-readonly-schema-existing-schema
创建只读视图