The following script is used to create a read only schema of any existing schema in the same database. The readonly schema is granted only read permisions on the tables and views of the existing schema. No previliges on any other objects. The script can be customized as per requirment.
- 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;
