Monday, February 2, 2009

Steps to Create a Read-Only APPS Schema

Print this post

 

Here we are going to create a schema similar to the APPS schema but has only read-only prviliges.


Steps:

1. Create the user for the required schema:

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

2. Grant connect and resource privileges to your user:

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

3. Use the following select statement to generate a script that will grant privileges on APPS
objects to your user.
Note the following:

A. This select statement should generate a script that will grant almost all required permissions
to a user called MYUSER.
B. This select statement is a guide only. You should work with your DBA to enhance it according to
your requirements.

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 MYUSER;' 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')

4. 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'

5. Run the above two scripts as SYS user.

1 comment:

Eugene said...

Famy,
Great site. I like it and learn a lot from it. One thing though: you post a code samples, but I can't copy it from your page. It makes difficult to open source and copy from there. Is there a way you can change it?
Thanks,
Eugene