How to Create APPS Read-Only Account

Step 1:-
Create the read-only schema, in this case lets call it APPSREAD.

Step 2:-
Surely, the schema created in above Step 1 will be given read only grants to objects in apps. There will be cases where the grant command might fail. To monitor such failures  create a table as below
conn xx_g4g/&2 ;

–For APPSREAD. This table will capture the exceptions during Grants
PROMPT create table XX_GRANTS_FAIL_APPSREAD

create table XX_GRANTS_FAIL_APPSREAD (
object_name VARCHAR2(100)
,sqlerrm varchar2(2000)
,creation_date DATE
);

grant all on XX_GRANTS_FAIL_APPSREAD to apps with grant option;

grant select on XX_GRANTS_FAIL_APPSREAD to APPSREAD ;

Step 3
In this step we grant select on all the existing views and synonyms in apps schema to APPSREAD.

conn apps/&1 ;

PROMPT This can take upto 15-30 minutes
PROMPT Granting SELECT on All synonyms and views to APPSREAD
DECLARE
–One off script to execute grants to APPSREAD
v_error VARCHAR2(2000);
BEGIN

FOR p_rec IN (SELECT *
FROM   all_objects
WHERE  owner = ‘APPS’
AND    object_type IN (‘SYNONYM’, ‘VIEW’)
AND    object_name NOT LIKE ‘%_S’)
LOOP
BEGIN
EXECUTE IMMEDIATE ‘grant select on ‘ || p_rec.object_name ||
‘ to APPSREAD’;
EXCEPTION
WHEN OTHERS THEN
v_error := substr(SQLERRM, 1, 2000);
INSERT INTO bes.XX_GRANTS_FAIL_APPSREAD
(object_name
,SQLERRM
,creation_date
)
VALUES
(p_rec.object_name
,v_error
,sysdate
);
END;
END LOOP;
COMMIT;
END;
/

Step 4
Write a after logon trigger on APPSREAD schema. The main purpose of this trigger is to alter the session to apps schema, such that the CurrentSchema will be set to apps for the session(whilst retaining APPSREAD restrictions).In doing so your logon will retain the permissions of APPSREAD schema(read_only). Howerver it will be able to reference the apps objects with exactly the same name as does a direct connection to apps schema.
conn apps/&1 ;
PROMPT CREATE OR REPLACE TRIGGER xx_APPSREAD_logon_trg
CREATE OR REPLACE TRIGGER xx_APPSREAD_logon_trg
–16Jun2006 By Anil Passi
–Trigger to toggle schema to apps, but yet retaining APPSREAD resitrictions
–Also sets the org_id
AFTER logon ON APPSREAD.SCHEMA
DECLARE
BEGIN
EXECUTE IMMEDIATE
‘declare begin ‘ ||
‘dbms_application_info.set_client_info ( 101 ); end;’;
EXECUTE IMMEDIATE ‘ALTER SESSION SET CURRENT_SCHEMA =APPS’;
END;
/

Step 5
Create a Trigger on the apps schema to issue select only grants for all new views and synonyms. Please note that I am excluding grants for sequences. SELECT grants for views and synonyms will be provided to APPSREAD as and when such objects are created in APPS. Please note that, all the APPS objects (views and synonyms) that existed in APPS schema prior to the implementation of this design, would have been granted read-only access to APPSREAD in Step 2.

conn apps/&1 ;
PROMPT CREATE OR REPLACE TRIGGER xx_grant_APPSREAD
CREATE OR REPLACE TRIGGER xx_grant_APPSREAD
–16Jun2006 By Anil Passi

AFTER CREATE ON APPS.SCHEMA
DECLARE
l_str VARCHAR2(255);
l_job NUMBER;
BEGIN
IF (ora_dict_obj_type IN (‘SYNONYM’, ‘VIEW’))
AND (ora_dict_obj_name NOT LIKE ‘%_S’)
THEN
l_str := ‘execute immediate “grant select on ‘ || ora_dict_obj_name ||
‘ to APPSREAD”;’;
dbms_job.submit(l_job, REPLACE(l_str, ‘”‘, ””));
END IF;
END;
/

 

 

You can use this one also:

 

connect system/&systempwd
create user &&user identified by &&pass default tablespace iwatch temporary tablespace TEMP;
grant connect, resource, select any table to &&user;

connect apps/&appspwd
spool /tmp/syn.sql
set pages 500
set lines 200
select ‘create synonym ‘||synonym_name||’ for ‘||table_owner||’.’||table_name||’;’
from user_synonyms;
spool off
spool /tmp/syn2.sql
set pages 500
set lines 200
select ‘create synonym ‘||view_name||’ for APPS.’||view_name||’;’
from user_views;
spool off

connect &&user/&&pass
@/tmp/syn2.sql
@/tmp/syn.sql
select ‘NEW USER ‘||USER||’ CREATED’
from dual;
exit

One more method:
How to create a user similar to APPS but with READ-ONLY privileges.
——————————————————————-

1. Create user:

create user apps_ro identified by usi_ro
default tablespace XXXX
temporary tablespace temp
quota 0M on XXXX;

2. Create Role:

Create role r_role;
Grant create session to r_role;
Grant connect to r_role;
Grant select any table to r_role;
Grant execute any procedure to r_role;
Grant create trigger to r_role;

3. Grant role to user:
Grant r_role to apps_ro;

4. Create trigger:

Login as apps_ro and create the following trigger

Create trigger rlogin_trigger
After logon on apps_ro.schema
Begin
Execute immediate ‘Alter session set current_schema = apps’;
End;
/

5. Revoke privilege
Revoke create trigger from r_role;

Advertisements
  1. November 28, 2010 at 12:28 pm

    what is xx_g4g/&2 ?

    Like

  2. November 28, 2010 at 12:42 pm

    what is conn xx_g4g/&2 ???

    Like

    • prem kumar
      December 29, 2011 at 8:52 am

      conn xx_g4g/&2
      conn /

      Like

  3. lokanath
    April 19, 2012 at 8:30 am

    hi

    Please let me know about this note little bit more explain

    Like

  4. kishore
    April 26, 2012 at 5:51 pm

    hi im kishore if it was asked in interview how could v explain plz tell in simple steps

    Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: