469,273 Members | 1,765 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

Script to create readonly schema of existing schema.

debasisdas
8,127 Expert 4TB
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.


Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE PROCEDURE CREATE_READONLY_SCHEMA (
  2.   i_owner IN VARCHAR2,
  3.   i_readonly IN VARCHAR2
  4.   )
  5. IS
  6.  
  7.   CURSOR cur_c1
  8.   IS
  9.   SELECT OBJECT_NAME, OBJECT_TYPE
  10.   FROM DBA_OBJECTS
  11.   WHERE OWNER = i_owner
  12.   AND OBJECT_TYPE IN ('TABLE', 'VIEW')
  13.   AND OBJECT_NAME NOT LIKE 'BIN$%' AND STATUS = 'VALID';
  14.  
  15. BEGIN
  16.   BEGIN
  17.   IF i_owner=i_readonly then
  18.   dbms_output.put_line('Please use a different name for the Readonly Schema');
  19.   exit;
  20.   else
  21.  
  22. --drop the readonly user if it already exists. This grants previliges on newly added tables and views in the original schema.
  23.     EXECUTE IMMEDIATE
  24.     'DROP USER '||i_readonly ||' CASCADE';
  25.   end if;
  26.   EXCEPTION
  27.     WHEN OTHERS
  28.     THEN
  29.       NULL;
  30.   END;
  31.   BEGIN
  32.     EXECUTE IMMEDIATE
  33.     'CREATE USER ' || i_readonly || ' IDENTIFIED BY ' || i_readonly || ' DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP2';
  34. --The tablespace part can be customized.
  35. --The password can also be customized or altered later on.
  36.     EXECUTE IMMEDIATE
  37.     'GRANT CONNECT TO ' || i_readonly;
  38.   EXCEPTION
  39.     WHEN OTHERS
  40.     THEN
  41. --add any exception handler if you want.
  42.       NULL;
  43.   END;
  44.  
  45.   FOR rec IN cur_c1
  46.   LOOP
  47.     BEGIN
  48.       EXECUTE IMMEDIATE
  49.       'GRANT SELECT ON ' || i_owner || '.' || rec.OBJECT_NAME || ' TO ' || i_readonly;
  50.  
  51.       EXECUTE IMMEDIATE
  52.       'CREATE OR REPLACE VIEW ' || i_readonly || '.' || rec.OBJECT_NAME || ' AS SELECT * FROM ' || i_owner || '.' || rec.OBJECT_NAME;
  53.     EXCEPTION
  54.       WHEN OTHERS
  55.       THEN
  56.         DBMS_OUTPUT.PUT_LINE('Refresh Attempt Failed for : ' || rec.Object_Name || ' (' || rec.Object_Type || ')');
  57.     END;
  58.   END LOOP;
  59. END CREATE_READONLY_SCHEMA;
Nov 21 '08 #1
0 8496

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by cmc | last post: by
2 posts views Thread by Anantha | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.