Script to create readonly schema of existing schema.

[debasisdas]

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;