{"id":1264,"date":"2022-09-14T11:10:00","date_gmt":"2022-09-14T09:10:00","guid":{"rendered":"https:\/\/promatis.com\/ch\/initializing-e-business-suite-context-for-a-read-only-user\/"},"modified":"2023-03-30T13:24:03","modified_gmt":"2023-03-30T11:24:03","slug":"initializing-e-business-suite-context-for-a-read-only-user","status":"publish","type":"post","link":"https:\/\/promatis-test.de\/ch\/initializing-e-business-suite-context-for-a-read-only-user\/","title":{"rendered":"Initializing E-Business Suite Context for a Read-Only-User"},"content":{"rendered":"<p>[et_pb_section fb_built=\"1\" custom_padding_last_edited=\"on|tablet\" disabled_on=\"off|off|off\" admin_label=\"Sektion\" _builder_version=\"4.17.6\" _module_preset=\"default\" custom_padding=\"5vh||5vh||true|false\" custom_padding_tablet=\"5vh||5vh||true|false\" custom_padding_phone=\"5vh||5vh||true|false\" global_module=\"23\" locked=\"off\" global_colors_info=\"%91%93\" theme_builder_area=\"post_content\"][et_pb_row column_structure=\"1_4,3_4\" _builder_version=\"4.17.6\" _module_preset=\"default\" custom_margin=\"||0px||false|false\" global_colors_info=\"{}\" theme_builder_area=\"post_content\"][et_pb_column type=\"1_4\" _builder_version=\"4.17.6\" _module_preset=\"default\" global_colors_info=\"{}\" theme_builder_area=\"post_content\"][et_pb_image src=\"https:\/\/promatis.com\/wp-content\/uploads\/2022\/07\/johannes-michler.png\" alt=\"Johannes Michler PROMATIS Horus Oracle\" title_text=\"johannes-michler\" _builder_version=\"4.20.2\" _module_preset=\"default\" width=\"90%\" custom_margin=\"0vh||0vh||true|false\" border_radii=\"on|516px|516px|516px|516px\" global_colors_info=\"{}\" theme_builder_area=\"post_content\"][\/et_pb_image][\/et_pb_column][et_pb_column type=\"3_4\" _builder_version=\"4.17.6\" _module_preset=\"default\" global_colors_info=\"{}\" theme_builder_area=\"post_content\"][et_pb_text ul_type=\"square\" _builder_version=\"4.20.2\" _module_preset=\"default\" link_font=\"Open Sans||||on|||gcid-0becd5ff-19fc-4653-a221-c8c75771a987|\" link_text_color=\"gcid-0becd5ff-19fc-4653-a221-c8c75771a987\" link_font_size=\"22px\" ul_font=\"Open Sans||||||||\" ul_font_size=\"17px\" ul_line_height=\"1.6em\" header_font=\"|600||on|||||\" header_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_2_font=\"|600||on|||||\" header_2_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_2_line_height=\"1.6em\" header_3_font=\"|600|||||||\" header_3_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_4_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_5_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_6_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_6_font_size=\"16px\" custom_margin=\"2vh||0px||false|false\" custom_padding=\"||||true|false\" text_font_size_tablet=\"20px\" text_font_size_phone=\"17px\" text_font_size_last_edited=\"on|tablet\" header_font_size_tablet=\"\" header_font_size_phone=\"28px\" header_font_size_last_edited=\"on|phone\" 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,%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%22link_text_color%22%93}\" theme_builder_area=\"post_content\"]<\/p>\n<div><a href=\"\/.\/ch\/author\/jmichler\/\" rel=\"author\">Johannes Michler<\/a><\/div>\n<p>[\/et_pb_text][et_pb_text ul_type=\"square\" _builder_version=\"4.20.0\" _module_preset=\"default\" link_font=\"Open Sans||||on||||\" link_text_color=\"#00A9A0\" ul_font=\"Open Sans||||||||\" ul_font_size=\"17px\" ul_line_height=\"1.6em\" header_font=\"|600||on|||||\" header_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_2_font=\"|600||on|||||\" header_2_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_2_line_height=\"1.6em\" header_3_font=\"|600|||||||\" header_3_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_4_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_5_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_6_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_6_font_size=\"16px\" custom_margin=\"1vh||0px||false|false\" custom_padding=\"||||true|false\" text_font_size_tablet=\"20px\" text_font_size_phone=\"17px\" text_font_size_last_edited=\"on|tablet\" header_font_size_tablet=\"\" header_font_size_phone=\"28px\" header_font_size_last_edited=\"on|phone\" 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%93}\" theme_builder_area=\"post_content\"]<\/p>\n<p><img decoding=\"async\" src=\"\/.\/wp-content\/uploads\/2023\/02\/ACE-Director-Logo_175x51.jpg\" \/><br \/><strong>Executive Vice President<\/strong> \u2013\u00a0Head of Platforms\u00a0&\u00a0Development<\/p>\n<p>[\/et_pb_text][et_pb_text ul_type=\"square\" _builder_version=\"4.20.0\" _module_preset=\"default\" text_text_color=\"gcid-0becd5ff-19fc-4653-a221-c8c75771a987\" text_font_size=\"22px\" link_font=\"Open Sans||||on||||\" link_text_color=\"#00A9A0\" ul_font=\"Open Sans||||||||\" ul_font_size=\"17px\" ul_line_height=\"1.6em\" header_font=\"|600||on|||||\" header_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_2_font=\"|600||on|||||\" header_2_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_2_line_height=\"1.6em\" header_3_font=\"|600|||||||\" header_3_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_4_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_5_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_6_text_color=\"gcid-32812186-bc94-4de4-814c-2bf202477fd5\" header_6_font_size=\"16px\" custom_margin=\"5px||0px||false|false\" custom_padding=\"||||true|false\" text_font_size_tablet=\"20px\" text_font_size_phone=\"17px\" text_font_size_last_edited=\"on|tablet\" header_font_size_tablet=\"\" header_font_size_phone=\"28px\" header_font_size_last_edited=\"on|phone\" 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,%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\"]<a target=\"_blank\" href=\"https:\/\/www.linkedin.com\/in\/johannes-michler-099892ab?originalSubdomain=de\" rel=\"noopener\"><i class=\"fab fa-linkedin\"><\/i><\/a><a target=\"_blank\" href=\"https:\/\/www.xing.com\/profile\/Johannes_Michler3\" rel=\"noopener\"><i class=\"fab fa-xing-square\"><\/i><\/a><a target=\"_blank\" href=\"mailto:johannes.michler@promatis.de\" rel=\"noopener\"><i class=\"fas fa-envelope\"><\/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.20.2\" _module_preset=\"default\" background_enable_color=\"off\" hover_enabled=\"0\" 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\"]Recently I described <a href=\"\/ch\/creating-a-read-only-apps-user\/\">how a read-only user can be created for Oracle E-Business Suite<\/a>.<\/p>\n<p>This works great, however, when trying to access the pre-built views of E-Business Suite, many of them do not show any data:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/promatis.com\/wp-content\/uploads\/2023\/02\/EBusinessSuiteContext-ReadOnlyUser1-1-300x99.png\" width=\"300\" height=\"99\" alt=\"\" class=\"wp-image-256665 alignnone size-medium\" \/><\/p>\n<p>Let\u2019s see how we can remedy this.<\/p>\n<h3>Initializing Context<\/h3>\n<p>When connected with the APPS user, this can be solved by running:<br \/>\nexec mo_global.init(\u2018M\u2019);<br \/>\nThis internally populates session-specific information on which operating units are accessible in a \u201cM\u201dulti-Org.<\/p>\n<p>Then, the above Statement returns all invoices as expected:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/promatis.com\/wp-content\/uploads\/2023\/02\/EBusinessSuiteContext-ReadOnlyUser2-1.png\" width=\"659\" height=\"246\" alt=\"\" class=\"wp-image-256667 alignnone size-full\" \/><\/p>\n<p>However, this query obviously can not run with the XXREAD user as created in the previous blog post, since we did not grant execute privileges to mo_global.<\/p>\n<h3>More grants<\/h3>\n<p>Let\u2019s fix this:<br \/>\nexec AD_ZD.GRANT_PRIVS(<span style=\"color: #008000;\">\u2018EXECUTE\u2019,\u2019MO_GLOBAL\u2019, \u2018XXREAD\u2019<\/span>);<\/p>\n<p>Unfortunately, this is not enough since the MO_GLOBAL package calls more procedures and is defined with AUTHID CURRENT_USER (see <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/dbseg\/managing-security-for-definers-rights-and-invokers-rights.html\" target=\"_blank\" rel=\"noopener\">https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/dbseg\/managing-security-for-definers-rights-and-invokers-rights.html<\/a>).<\/p>\n<p>There are basically two options to solve this problem:<\/p>\n<ul>\n<li>You can create a \u201cproxy package\u201d with \u201cAUTHID DEFINER\u201d in the APPS schema, grant execute privs on that package to XXREAD and then call mo_global.ini from there. This approach can be used for other functions as well.<\/li>\n<li>You need to grant the following privileges to XXREAD:<br \/>\nexec AD_ZD.GRANT_PRIVS(<span style=\"color: #008000;\">\u2018EXECUTE\u2019,\u2019FND_GLOBAL\u2019, \u2018XXREAD\u2019<\/span>);<br \/>\nexec AD_ZD.GRANT_PRIVS(<span style=\"color: #008000;\">\u2018ALL\u2019,\u2019MO_GLOB_ORG_ACCESS_TMP\u2019, \u2018XXREAD\u2019<\/span>);<\/li>\n<\/ul>\n<p>Afterwards, the mo_global.init procedure can also be run as XXREAD.<\/p>\n<h3>Extending the Logon-Trigger<\/h3>\n<p>If you prefer to not run the mo_global.init function after each session-creation, you can extend the logon-trigger as follows:<br \/>\ncreate or replace TRIGGER xxread.xxread_query_logon_trg<br \/>\nAFTER logon ON XXREAD.SCHEMA<br \/>\nDECLARE<br \/>\nBEGIN<br \/>\nEXECUTE IMMEDIATE <span style=\"color: #008000;\">\u2018ALTER SESSION SET CURRENT_SCHEMA =APPS\u2019<\/span>;<br \/>\napps.mo_global.init(<span style=\"color: #008000;\">\u2018M\u2019<\/span>);<br \/>\nEND;<\/p>\n<h3>Summary<\/h3>\n<p>By extending the XXREAD user created according to my previous blog post as described above, it is easily possible to access VPD-protected views and tables. It is thus even easier to replace (more dangerous) access as the APPS user with a less privileged user; in many scenarios where 3rd party systems are only reading the APPS user can be replaced with this XXREAD user without further modifications in the 3rd party systems.[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Recently I described how a read-only user can be created for Oracle E-Business Suite. This works great, however, when trying to access the pre-built views of E-Business Suite, many of them do not show any data. Let\u2019s see how we can remedy this.<\/p>\n","protected":false},"author":2,"featured_media":1243,"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":[23],"tags":[88,98,85,97,99],"dipi_cpt_category":[],"class_list":["post-1264","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-techblog","tag-e-business-suite","tag-e-business-suite-context","tag-oracle","tag-oracle-e-business-suite","tag-read-only-user"],"_links":{"self":[{"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/posts\/1264"}],"collection":[{"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/comments?post=1264"}],"version-history":[{"count":0,"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/posts\/1264\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/media\/1243"}],"wp:attachment":[{"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/media?parent=1264"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/categories?post=1264"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/tags?post=1264"},{"taxonomy":"dipi_cpt_category","embeddable":true,"href":"https:\/\/promatis-test.de\/ch\/wp-json\/wp\/v2\/dipi_cpt_category?post=1264"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}