How to Create a Read-Only APPS Schema?

How to Create a Read-Only APPS Schema?

We are going to create a schema similar to the APPS schema having only read-only privileges.

Step 1. Create the user for the required schema:

• SQL> connect system/manager
• SQL> create user <your_user> identified by <your_user_password> default tablespace;
• SQL><tablespace_to_assign_to_user> temporary tablespace temp;

Step 2. Grant connect and resource privileges to your user:

• SQL> connect system/manager
• SQL> grant connect, resource to <your_user>;

Step 3. Use the following select statement to generate a script that will grant privileges on APPS objects to your User.

This select statement would generate a script that will grant almost all required permissions to a user called READONLY.

SELECT 'GRANT ' 
       || Decode(O.object_type, 'TABLE', 'SELECT', 
                                'VIEW', 'SELECT', 
                                'EXECUTE') 
       || ' ON ' 
       || Decode(O.owner, 'PUBLIC', '', 
                          O.owner 
                          || '.') 
       || '"' 
       || O.object_name 
       || '"' 
       || ' TO MYUSER;' COMMAND 
FROM   all_objects O 
WHERE  O.object_type IN ( 'TABLE', 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 
                          'VIEW', 'FUNCTION' ) 
UNION 
SELECT 'GRANT ' 
       || Decode(O2.object_type, 'TABLE', 'SELECT', 
                                 'VIEW', 'SELECT', 
                                 'EXECUTE') 
       || ' ON ' 
       || Decode(O.owner, 'PUBLIC', '', 
                          O.owner 
                          || '.') 
       || '"' 
       || O.object_name 
       || '"' 
       || ' TO READONLY;' COMMAND 
FROM   all_objects O, 
       all_objects O2, 
       dba_synonyms S 
WHERE  O.object_type = 'SYNONYM' 
       AND O.object_name = S.synonym_name 
       AND O2.object_name = S.table_name 
       AND O2.object_type IN ( 'TABLE', 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 
                               'VIEW', 'FUNCTION' )

Now, Use the following select statement to generate a script that will create synonyms in <your_user> schema for all objects owned by APPS.

SELECT ‘CREATE SYNONYM MYUSER.’ || O.OBJECT_NAME || ‘ FOR APPS.’ || O.OBJECT_NAME || ‘;’ COMMAND FROM DBA_OBJECTS O WHERE O.Owner = ‘APPS’

Run the above two scripts as SYS user.