[\/et_pb_text][et_pb_text _builder_version=\"4.17.6\" _module_preset=\"default\" custom_margin=\"1vh||0px||false|false\" global_colors_info=\"{}\" theme_builder_area=\"post_content\"]<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=\"4.17.6\" _module_preset=\"default\" text_text_color=\"gcid-0becd5ff-19fc-4653-a221-c8c75771a987\" text_font_size=\"22px\" custom_margin=\"5px||0px||false|false\" global_colors_info=\"{%22gcid-32812186-bc94-4de4-814c-2bf202477fd5%22:%91%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22,%22header_4_text_color%22,%22header_5_text_color%22,%22header_6_text_color%22,%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22,%22header_4_text_color%22,%22header_5_text_color%22,%22header_6_text_color%22%93,%22gcid-0becd5ff-19fc-4653-a221-c8c75771a987%22:%91%22text_text_color%22%93}\" theme_builder_area=\"post_content\"]<\/i><\/a><\/i><\/a><\/i><\/a>[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section][et_pb_section fb_built=\"1\" _builder_version=\"4.16\" _module_preset=\"default\" custom_padding=\"0vh||10vh||false|false\" global_colors_info=\"{}\" theme_builder_area=\"post_content\"][et_pb_row use_custom_gutter=\"on\" _builder_version=\"4.17.3\" _module_preset=\"default\" custom_padding=\"0px||0px||true|false\" global_colors_info=\"{}\" theme_builder_area=\"post_content\"][et_pb_column type=\"4_4\" _builder_version=\"4.16\" _module_preset=\"default\" global_colors_info=\"{}\" theme_builder_area=\"post_content\"][et_pb_text _builder_version=\"4.19.4\" _module_preset=\"default\" background_enable_color=\"off\" custom_padding=\"0px||0px||true|false\" hover_enabled=\"0\" inline_fonts=\"Times New Roman\" global_colors_info=\"{%22gcid-32812186-bc94-4de4-814c-2bf202477fd5%22:%91%22header_text_color%22,%22header_2_text_color%22,%22header_3_text_color%22%93,%22gcid-0becd5ff-19fc-4653-a221-c8c75771a987%22:%91%22background_color%22%93}\" theme_builder_area=\"post_content\" sticky_enabled=\"0\"]<\/p>\n In Oracle E-Business Suite environments it is often necessary to query data. Typically, all data can be accessed through the APPS user. However, that user is highly privileged and, when in use, can easily \u201cbreak things\u201d. This is obviously a big compliance issue. Having a dedicated read-only user is thus a much better approach. Let\u2019s see how such a \u201cXXREAD\u201d-user can be created.<\/p>\n In order for the APPS user to be able to grant access to objects the user doesn\u2019t actually own (e.g. tables in XX schemes), the APPS user needs to have privileges on those objects \u201cincluding grant option\u201d. This can be achieved with a script such as the following one, which should be run whenever new custom objects are created (as the XX scheme user):<\/p>\n <\/p>\n First, create a XXREAD user and produce a script to grant privs:<\/p>\n . setenv_run.sh spool grant_privs.sql Then, run the spooled script and register the XXREAD user:<\/p>\n <\/p>\n After having initially created the user, sign in as XXREAD and do some initial testing by accessing apps.* tables. Such a trigger can look as follows:<\/p>\n <\/p>\n With the above procedure, it is possible to create a XXREAD user that can access (in a reading fashion) everything from the APPS user. This is a lot more secure than really using the APPS user\/password since in that way, no one can accidentally run a \u201ctruncate\u201d on some table. Also keep in mind, though, that such a XXREAD user is not the perfect solution and allows a lot of (reading) access, which still might be a GDPR issue; obviously, you also want to limit access to this XXREAD user as restrictive as possible.<\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" In Oracle E-Business Suite environments it is often necessary to query data. Typically, all data can be accessed through the APPS user. However, that user is highly privileged and, when in use, can easily \u201cbreak things\u201d. This is obviously a big compliance issue. Having a dedicated read-only user is thus a much better approach.<\/p>\n","protected":false},"author":2,"featured_media":1244,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[440],"tags":[115,110,133,145],"dipi_cpt_category":[],"class_list":["post-1330","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-techblog-en","tag-e-business-suite-2","tag-oracle-2","tag-oracle-e-business-suite-2","tag-read-only-apps-user-en"],"_links":{"self":[{"href":"https:\/\/promatis-test.de\/ch\/en\/wp-json\/wp\/v2\/posts\/1330"}],"collection":[{"href":"https:\/\/promatis-test.de\/ch\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/promatis-test.de\/ch\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/promatis-test.de\/ch\/en\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/promatis-test.de\/ch\/en\/wp-json\/wp\/v2\/comments?post=1330"}],"version-history":[{"count":0,"href":"https:\/\/promatis-test.de\/ch\/en\/wp-json\/wp\/v2\/posts\/1330\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/promatis-test.de\/ch\/en\/wp-json\/wp\/v2\/media\/1244"}],"wp:attachment":[{"href":"https:\/\/promatis-test.de\/ch\/en\/wp-json\/wp\/v2\/media?parent=1330"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/promatis-test.de\/ch\/en\/wp-json\/wp\/v2\/categories?post=1330"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/promatis-test.de\/ch\/en\/wp-json\/wp\/v2\/tags?post=1330"},{"taxonomy":"dipi_cpt_category","embeddable":true,"href":"https:\/\/promatis-test.de\/ch\/en\/wp-json\/wp\/v2\/dipi_cpt_category?post=1330"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}![](\"\/.\/wp-content\/uploads\/2023\/02\/ACE-Director-Logo_175x51.jpg\"){kind=logo}
Senior Vice President<\/strong> \u2013\u00a0Head of Platforms\u00a0&\u00a0Development<\/p>\nPreparation<\/h3>\n
set verify off;
BEGIN
FOR r IN (
SELECT
\u2018grant all on \u2018
|| object_name
|| \u2018 to apps with grant option\u2019 statement_to_run
FROM
user_objects dbos
WHERE
(object_type IN ( \u2018SEQUENCE\u2019, \u2018TABLE\u2019 )
or object_type=\u2019VIEW\u2019 and object_name like \u2018%#\u2019)
AND NOT EXISTS (
SELECT
1
FROM
user_tab_privs privs
WHERE
privs.table_name = dbos.object_name
AND privs.type = dbos.object_type
AND privs.grantable = \u2018YES\u2019
AND privs.owner = user
)
order by object_type asc
) LOOP
EXECUTE IMMEDIATE r.statement_to_run;
END LOOP;
END;
\/<\/div>\nCreating a user and granting privileges<\/h3>\n
sqlplus -s system\/
create user XXREAD identified by MyAppsReadPwd;
grant create session to XXREAD;
grant alter session to XXREAD;
SET echo off
SET feedback off
SET term off
SET pagesize 0
SET linesize 200
SET newpage 0
SET space 0<\/system-pwd><\/p>\n
select \u2018exec AD_ZD.GRANT_PRIVS(\u201dREAD\u201d,\u201d\u2019||VIEW_NAME || \u201d\u2019, \u201dXXREAD\u201d);\u2019 from all_views where OWNER =\u2019APPS\u2019 and view_NAME not like \u2018%\/%\u2019;
select \u2018exec AD_ZD.GRANT_PRIVS(\u201dREAD\u201d,\u201d\u2019||table_NAME || \u201d\u2019, \u201dXXREAD\u201d);\u2019 from all_tables where OWNER =\u2019APPS\u2019 and table_NAME not like \u2018%\/%\u2019;
select \u2018exec AD_ZD.GRANT_PRIVS(\u201dREAD\u201d,\u201d\u2019||SYNONYM_NAME || \u201d\u2019, \u201dXXREAD\u201d);\u2019 from all_synonyms syns, all_objects objs where
syns.OWNER =\u2019APPS\u2019 and syns.SYNONYM_NAME not like \u2018%\/%\u2019
and syns.table_owner=objs.owner and syns.table_name=objs.object_name
and objs.object_type in (\u2018TABLE\u2019,\u2019VIEW\u2019);
spool off
exit<\/p>\n<\/div>\n
sqlplus apps\/First tests and logon trigger<\/h3>\n
Unfortunately, so far, it is still necessary to do all selects with an \u201capps.\u201d prefix. This can be solved with two approaches:<\/p>\n\n
AFTER logon ON XXREAD.SCHEMA
DECLARE
BEGIN
EXECUTE IMMEDIATE \u2018ALTER SESSION SET CURRENT_SCHEMA =APPS\u2019;
END;
\/<\/div>\nSummary<\/h3>\n