Read Only APPS User Creation (APPSRO)

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

bash $ sqlplus "/ as sysdba"

SQL> create user appsro identified by appsro default tablespace APPS_TS_TX_DATA;

SQL> grant connect to appsro;

SQL> grant ALTER SESSION to appsro;

SQL> exit;

Step 2: Connect with APPS User and Create XX_GRANTS_FAIL_APPSRO To Save the Details of Failed Grants

conn apps/<passwd> ;


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

grant all on XX_GRANTS_FAIL_APPSRO to apps with grant option;

grant select on XX_GRANTS_FAIL_APPSRO to appsro ;

Step 3: Granting SELECT on All synonyms and views to APPSRO

conn apps/<passwd> ;

PROMPT This can take upto 15-30 minutes
PROMPT Granting SELECT on All synonyms and views to appsro
DECLARE
--One off script to execute grants to appsro
  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' escape '\')
  LOOP
    BEGIN
      EXECUTE IMMEDIATE 'grant select on ' || p_rec.object_name ||
                        ' to appsro';
    EXCEPTION
      WHEN OTHERS THEN
        v_error := substr(SQLERRM, 1, 2000);
        INSERT INTO apps.XX_GRANTS_FAIL_APPSRO
          (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 appsro schema. The main purpose of this trigger is to alter the session to apps schema, such that the Current Schema will be set to apps for the session (whilst retaining appsro restrictions).In doing so your logon will retain the permissions of appsro schema(read_only). However it will be able to reference the apps objects with exactly the same name as does a direct connection to apps schema.

This will eliminate creation of synonyms in APPSRO Schema.

conn apps/<passwd> ;

PROMPT CREATE OR REPLACE TRIGGER xx_appsro_logon_trg
CREATE OR REPLACE TRIGGER xx_appsro_logon_trg
--Trigger to toggle schema to apps, but yet retaining appsro resitrictions
  AFTER logon ON appsro.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 - excluding grants for sequences. SELECT grants for views and synonyms will be provided to appsro as and when such objects are created in APPS schema.

conn apps/<passwd> ;

PROMPT CREATE OR REPLACE TRIGGER xx_grant_appsro
CREATE OR REPLACE TRIGGER xx_grant_appsro
  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' escape '\')
  THEN
    l_str := 'execute immediate "grant select on ' || ora_dict_obj_name ||
             ' to appsro";';
    dbms_job.submit(l_job, REPLACE(l_str, '"', ''''));
  END IF;
END;
/

APPSRO Read Only User is Ready to Use, You can validate by Selecting couple of tables by connecting to APPSRO schema.