Login
Register

Home

Trainings

Fusion Blog

EBS Blog

Authors

Contact Us

Oracle Scripts
  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new Click here to raise Support Ticket. Get reply within 48 hours.

Search Courses

User Rating: 4 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Inactive
 
In this article I have discussed how to create and maintain a read only schema for APPS in Oracle eBusiness Suite.

Whilst in the past I have known clients to implement this using synonyms. However the approach discussed below is designed without the need of having to create a single synonym in APPS_QUERYschema.

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

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 APPS_QUERY. This table will capture the exceptions during Grants
PROMPT create table XX_GRANTS_FAIL_APPS_QUERY
create table XX_GRANTS_FAIL_APPS_QUERY (
        object_name VARCHAR2(100)
       ,sqlerrm varchar2(2000)
       ,creation_date DATE
        );


grant all on XX_GRANTS_FAIL_APPS_QUERY to apps with grant option;

grant select on XX_GRANTS_FAIL_APPS_QUERY to apps_query ;

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

conn apps/&1 ;

PROMPT This can take upto 15-30 minutes
PROMPT Granting SELECT on All synonyms and views to apps_query
DECLARE
--One off script to execute grants to apps_query
  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 apps_query';
    EXCEPTION
      WHEN OTHERS THEN
        v_error := substr(SQLERRM, 1, 2000);
        INSERT INTO bes.XX_GRANTS_FAIL_apps_query
          (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 apps_query 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 apps_query restrictions).In doing so your logon will retain the permissions of apps_query 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_apps_query_logon_trg
CREATE OR REPLACE TRIGGER xx_apps_query_logon_trg
--16Jun2006 By Anil Passi
--Trigger to toggle schema to apps, but yet retaining apps_query resitrictions
--Also sets the org_id
  AFTER logon ON apps_query.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 apps_query 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 apps_query in Step 2.

conn apps/&1 ;
PROMPT CREATE OR REPLACE TRIGGER xx_grant_apps_query
CREATE OR REPLACE TRIGGER xx_grant_apps_query
--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 apps_query";';
    dbms_job.submit(l_job, REPLACE(l_str, '"', ''''));
  END IF;
END;
/



Some notes for this design

Note1 
You need to ensure that the schema created in Step 1 has very limited permissions. Most importantly it must not be given grant for “EXECUTE/CREATE ANY PROCEDURE”. You will need to agree with your DBAs upfront for the permissions,

Note 2
Only views and synonyms will be granted access. Objects in your xx_g4g(bespoke) schema should have their synonyms in apps already in place.

Note 3
If your site has multi org enabled, you will then have to set the org I'd after loggiong on to apps query schema. In case you have only one single ORG_ID, then would have been set as in Step 4 above.

Note 4
ALTER SESSION SET CURRENT_SCHEMA =APPS
This facilitates users to run their queries as if they were connected to apps schema. However, their previliges will be restricted to those of apps_query

Note 5
It is assumed that ALTER SESSION privilege will exist for APPS_QUERY schema.

Thanks,
Anil Passi


Anil Passi

Comments   

0 #1 Dnyanesh 2007-02-07 00:00
Hi Anil,
This is a excellent note on creating read only schema for Apps.
Well, I am looking for creating read only responsibility. Will you pls prepare note for this.
Thanks,
Dnyanesh
Quote
0 #2 Hi Anil, 2007-07-16 03:31
If we give select any table to new user(who needs read only access),can't it be fine ?
Quote
0 #3 Anil Passi 2007-07-16 06:24
Hi there,

In this case, we are giving select privelege to SYNONYMS and Views in APPS.
The apps schema does not contain tables.
Also, we do not wish to create thousands of synonyms, hence ALTER SESSION SET CURRENT_SCHEMA is needed too.

Thanks
An il Passi
Quote
0 #4 Pranay 2007-08-19 08:58
'%_S' in the select statement above will not fetch any synonyms/views ending with 'S'.

The statement has to be like,

SELECT *
FROM all_objects
WHERE owner = 'APPS'
AND object_type IN ('SYNONYM', 'VIEW')
AND object_name NOT LIKE '%\_S' ESCAPE '';

This would aviod getting objects ending with '_S' (which I guess would be sequences, and they are not selected in the object_type above).

- Pranay
Quote
0 #5 Anil Passi 2007-08-19 09:50
Cheers Pranay for enhancing this further.

Thank s,
Anil Passi
Quote
0 #6 SRINIVAS. M 2007-08-23 09:44
Hi Anil/Dnyanesh,
I am looking for creating read only responsibility. Will you pls prepare note for this.
Thanks,
S rinivas
Quote
0 #7 Akil 2007-08-24 16:13
Hi Anil , Oracle says that Data Group attached to responsibility helps to connect to username which is defined in Data Group as Application Oracle ID pair. So e.g Application Name => Oracle GL Oracle ID => Apps. This means if user select GL responsibility it will connect with Apps User. So can we achieve Query Only Schema with this functionality like Creating New Data Group which will be paired to Apps_Query schema . Then creating a responsibility and attached this new data group. Just a thought.
Quote
0 #8 Anil Passi 2007-08-24 19:56
Hi Akil

If you want a read only responsibility, simplest way is to implement one of the two options:-
1. For hrms responsibilitie s, set profile option HR:Query Only to Yes
2. For non-hrms responsibility, you can set the default value for its form functions to include parameter
QUERY_ONLY=Yes

Thanks,
Anil Passi
Quote
0 #9 ashish dubeby 2007-10-20 10:32
hi
im new to orr-apps .can u elaborate me on.....Create custom Application.... .it would be gr8 help for me.....thanx in advance...
rega rds
Ashish
Quote
0 #10 Neeladri 2007-10-22 11:44
Hi Anil,

Just adding one thought to the point no. 2, in case of non-hrms responsibility, there would be many form functions, is there a single way to make all the forms query-only access to all users in apps? please give a suggestion on this.

Thanks,
Neeladri
Quote
0 #11 Carlos 2007-11-20 15:51
Hi Anil:

your doc is very useful!!! thanks in advance.

But I would like to comment another issue regarding this matter, read_only apps schema...
I working to implementing policies and procedures for SOX audit, and I need to define a specific procedure to established a emergency changes to live environment.. that is, create a mirror apps schema (apps-emer) for implement a emergency changes for a developer user if the person in charge to apply this cannot to do...

do you have any experiences about this issue, mirror apps schema?

thanks for your comments


Rega rds,

Carlos
Quote
0 #12 Carlos 2007-11-21 13:11
Hi Anil:

I'm trying to apply your process, but when I would like to create trigger xx_apps_query_l ogon_trg, failed with oracle error ora-600 [17057]. This lookup error is not identified!! do you have any experience about this trouble?

Thank s!
Carlos
Quote
0 #13 George 2007-11-26 22:49
Appreciate for the details about the APPS_QUERY read-only setup options for creating schema. Is there an answer or somebody ever tried this -> Create an Oracle Apps read-only responsibility with new data groups (this is linked to APPS_QUERY schema); Or what all components - objects and privilegs required to login and access standard oracle forms thru the read-only responsility attached to read-only schema data group. I know about making a readonly resp connecting to APPS schema. Appreciate your feedback.
Quote
0 #14 George 2007-12-01 02:45
Has anybody tried creating Read only responsibility connecting to Read only APPS_QUERY schema? Please share your ideas.
Quote
0 #15 NeoHyd 2008-01-18 16:58
Please be aware....
Apps Read only schema is a serious security violation.
When implementing this make sure you are not violating any Audit procedures. Access from back-end should always be discouraged unless there is a dire need and it is always better to have individual users created in DB and have access to data which they are authorized to see.
Quote
0 #16 Anil Passi 2008-01-18 18:50
Just wondering what brought you to this webpage !
Quote
0 #17 NeoHyd 2008-01-18 20:55
Actually I started at oracle forums....went to some blog...then went to another...and then to Atul's blog and from there I came here...was a long journey.... ;)
Quote
0 #18 Anil Passi 2008-01-18 21:55
Cheers NeoHyd.
I do not disagree with your statement though.
Perhaps you can modify procedure xx_grant_apps_q uery(), so as to exclude the tables that you fear might cause security concern.
If you end up doing so, then please feel free to paste the modified code for xx_grant_apps_q uery() on here.

Thanks,
Anil Passi
Quote
0 #19 Ranjith 2008-01-31 13:54
Hi Anil,

Just adding one thought to the point no. 2, in case of non-hrms responsibility, there would be many form functions, is there a single way to make all the forms query-only access to all users in apps? please give a suggestion on this.

Thanks,
Khaleel S
Quote
0 #20 VenkatJ 2008-03-11 17:31
Hi Anil

Can you please explain in detail about READ-ONLY APPS Schema.

I am not able to find schema "XX_G4G"


Than ks
Venkat.
Quote
0 #21 Damir Vadas 2008-03-12 19:09
Granting SELECT on that table is more then security flaw. What about that?
THX
Quote
0 #22 senthil kumar manimaran 2008-05-04 08:43
Hi Anil,

We have one requirement to create Readonly Database user for GL, AP, AR and PO Modules, How can we do that, we dont want him to touch another Module Tables or views or synonyms.

Do you have any idea to do that???
Quote
0 #23 Anil Passi 2008-05-04 09:06
Hi Subbu

The implementation code that I developed will by default create read-only for everything.
But you can tweak that code by putting where-clauses to implement just for your desired modules.

Thank s,
Anil
Quote
0 #24 senthil kumar manimaran 2008-05-04 10:25
Thanks Anil for the update,

SELECT *
FROM dba_objects
WHERE owner in ('GL','AP','AR' ,'PO')
AND object_type IN ('SYNONYM', 'VIEW')
AND object_name NOT LIKE '%/_S' ESCAPE '/';

will the above query serve our purpose? Here I am granting select privileges of the objects returned by the above query.

I want to do the following steps.

(a) create a readonly user in the database
(b) grant select privileges on the objects returned from the above query

Please correct me if i am wrong. did i miss anything?
Quote
0 #25 senthil kumar manimaran 2008-05-04 10:28
Here we dont want to create read only apps user. But we need to give readonly access to a dummy user for the said modules(AP,AR,G L and PO). This user wants to develop a report based on the above modules.

the procedure, which i have given in the above comment will it serve our purpose?

Pleas e correct me If I am wrong.
Quote
0 #26 Anil Passi 2008-05-04 10:33
Sure, that should be fine.

You also need to modify Step 5 to filter on your objects of interest

PROMP T CREATE OR REPLACE TRIGGER xx_grant_apps_q uery
CREATE OR REPLACE TRIGGER xx_grant_apps_q uery
--16Jun200 6 By Anil Passi
--
AFTER CREATE ON APPS.SCHEMA
DEC LARE
l_str VARCHAR2(255);
l_job NUMBER;
BEGIN
IF (ora_dict_obj_t ype IN ('SYNONYM', 'VIEW'))
AND (ora_dict_obj_n ame NOT LIKE '%_S')
AND (ora_dict_obj_n ame LIKE 'PO_%' OR ora_dict_obj_na me LIKE 'GL_%' OR ora_dict_obj_na me LIKE 'AP_%' OR ora_dict_obj_na me LIKE 'RA_%' or
ora_dict_obj_na me LIKE 'HZ_%' )
THEN
l_str := 'execute immediate "grant select on ' || ora_dict_obj_na me ||
' to apps_query";';
dbms_job.submit (l_job, REPLACE(l_str, '"', ''''));
END IF;
END;
/
Quote
0 #27 Alex2008 2008-05-20 08:22
Dear Anil,

Can you please guide me exactly what to do if multi org is enabled. You are explaining that "ORGID" to be set, which I don't know how to do while creating the trigger.

Thank s in advance.

Regar ds,
Alex
Quote
0 #28 Jerry 2009-08-25 16:19
Hi Anil:

Great article. Unfortunately there is an issue with the after logon trigger when using 11gR1 on a non-Windows server. You run in to Bug Bug 6747927 ALTER SESSION set CURRENT_SCHEMA does not work in AFTER LOGON TRIGGER From the descriptions:

Description

This problem is introduced in 11g.

Issuing an "alter session set current_schema= xxxxx" within an
AFTER LOGON trigger only has an effect during the duration of that
trigger. At the end of the trigger the schema is reset back
to the original schema.

eg:
connect / as sysdba
CREATE OR REPLACE TRIGGER TEST.change_sch ema
AFTER logon ON SCHEMA
begin
execute immediate 'alter session set current_schema= XXX';
end;
/

Connecting as users does not set current_schema to XXX.
eg: Check with
SELECT SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA ') as current_schema FROM DUAL;

Bug is fixed on Windows Servers only and in future 11gR2 for other systems:

* 11.1.0.6 Patch 5 on Windows Platforms
* 11.1.0.7 Patch 6 on Windows Platforms
* 11.2 (Future Release)

Take care,
Jerry
Quote
0 #29 Koyel Das 2009-12-18 07:38
I've created a read-only user appsview. This user after logon to the database, is unable to see the views that's starts with dba such as dba_data_files, dba_roles etc

Can you please tell me why this is so and how to resolve this issue? :(
Quote
0 #30 sri 2011-05-16 15:29
Hi Anil....really u r doing an excellent job.....u r like a GURU for many beginers like me.
I am constantly visiting your site for almost 5 months but never got a chance to add my comment.
But today, I really feel good to add a comment.
Here is my scenario -
->I want to create views on my apps schema under Custom Schema
->Myne is an Multi-Org company
->Now after creating the Views in Custom schema I need the users (from different legislations) to use those Views based on their User profile at the backend thru TOAD / SQL developer.
what is the best solution you can suggest?
Do you think - creating an apps-query schema is good or creating a Role and assinging users to that role and granting the select to that Role is good?
Please advise(explain in detail) with your valuable comments...!
Ch eers,
Sri.M
Rea lly feeling proud, that am getting touch with You dear :-)
Quote
0 #31 Mitul 2014-04-08 06:44
Infromative Article
Quote

Add comment


Security code
Refresh

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Aug 2021  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
        1
  2  3  4  5  6  7  8
  9101112131415
16171819202122
23242526272829
3031     

Enquire For Training

Related Items

Fusion Training Packages

Get Email Updates


Powered by Google FeedBurner