What Is Required For An Oracle Database User To Access Secure View ...

Create AccountLog in

Oracle Database

--

Questions

--

Followers

Top Experts

JAKEX🇺🇸What is required for an Oracle database user to access secure view HR_ORGANIZATION_UNITS? I've created an Oracle R12 database user xxbx. This user has the following privs and roles. This user must be able to access Oracle secure views such as HR_ORGANIZATION_UNITS, PER_ASSIGNMENTS_F AND PAY_PAYROLLS_F. Using synonyms and grants I have access to the underlying tables. For example this database user can successfully query HR_ALL_ORGANIZATION_UNITS. Note that I use fnd_globals apps_initialize to set the environment. This works for both the APPS and new XXBX user. However when the new user XXKX queries the secure HR_ORGANIZATION_UNIT after the apps initialize there are no records returned. What is needed to access these Oracle secure views. Example of query to verify apps_initialize select fnd_profile.value('RESP_NAME') from dual select fnd_profile.value('RESP_ID') from dual Example of apps_initialize begin fnd_global.apps_initialize (1013415,21540,800,0); end; DBA_SYS_PRIVS for XXKX XXKX      UNLIMITED TABLESPACE      NO XXKX      CREATE SESSION            NO XXKX      CREATE SYNONYM            NO DBA_ROLE_PRIVS for XXKX XXKX      CONNECT      NO      YES Example of the underlying 'HR_ORGANIZATION_UNITS' query... SELECT hao.organization_id, hr_security.view_all, hr_security.show_record ('HR_ALL_ORGANIZATION_UNITS',haotl.organization_id) zero, DECODE (hr_security.view_all,'Y','TRUE',hr_security.show_record ('HR_ALL_ORGANIZATION_UNITS',haotl.organization_id)) one, DECODE (hr_general.get_xbg_profile,'Y', hao.business_group_id,hr_general.get_business_group_id) two   FROM hr_all_organization_units hao, hr_all_organization_units_tl haotl  WHERE hao.organization_id = 202 AND hao.business_group_id = 202 AND hao.organization_id = haotl.organization_id   AND haotl.language = 'US'  order by 1; Above query results as the APPS user where hr_security.view_all = TRUE 202      Y      TRUE      TRUE      202 Above query results as the XXKX user where hr_security.view_all = FALSE 202      N      FALSE      FALSE      202

Zero AI Policy

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

David Johnson, CD🇨🇦 Turn on auditing of these objects to help find the cause.  Auditing is a good practice for Items that you want to maintain security on. http://docs.oracle.com/cd/B28359_01/network.111/b28531/auditing.htm#DBSEG98324 any stored procedures in use and how is the security on the sp set via app or by invoker? btan Taking a step when you create a view, you must meet the following requirements: a.  You must have been granted one of the following system privileges, either explicitly or through a role: >The CREATE VIEW system privilege (to create a view in your schema) >The CREATE ANY VIEW system privilege (to create a view in another user's schema) b. You must have been explicitly granted one of the following privileges: >The SELECT, INSERT, UPDATE, or DELETE object privileges on all base objects underlying the view >The SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, or DELETE ANY TABLE system privileges In addition, in order to grant other users access to your view, you must have received object privileges to the base objects with the GRANT OPTION clause or appropriate system privileges with the ADMIN OPTION clause. If you have not, then grantees cannot access your view. You may want to catch an example in the forum discussed - use of secured views per_people_f https://community.oracle.com/thread/2190591?tstart=0

If you use per_people_f in your query rather than per_all_people_f that will secure data according to the responsibility's security profile. ... if you're joining to assignments use per_assignments_f2 (f2 supports individual assignment security) as well as per_people_f.

I am not a query expert but though below for consideration i was thinking of the "How to Add a Policy to a Table, View, or Synonym", specifically on the section on "Enforcing VPD Policies on Specific SQL Statement Types" and " Users Exempt from VPD Policies" http://docs.oracle.com/cd/B19306_01/network.102/b14266/apdvcntx.htm#i1007410 and other from forum

fnd_client_info.setup_client_info is required when you want to use the views which are based on organization. you can use this function if you want to run the organization base query in TOAD or sqlplus.This function would setup the organization at the backend level also. fnd_global.apps_initialize is used in a program to setup the Apps use related parameters. Generally used in SQL scripts used in Conc programs. Also this function is required to call the submit request API. This function based on resp id, user_id and resp application id.

Mark Geerlings🇺🇸 This is specifically an Oracle e-Business Suite question, not just a general Oracle database question.  And yes, in Oracle e-Business Suite databases, you do often have to call the procedure: "fnd_global.apps_initialize" (and pass it the correct parameters for your database instance) to be able to query some of the restricted views in an EBS database.  We also have EBS R12 (which actually uses an Oracle database verison 11 database) but we do not have the HR module here.  Maybe there is another procedure specific to the HR module that you would need to call to get your queries to work as expected. In our system, I can query the view HR_ORGANIZATION_UNITS directly and see the results even without calling: "fnd_global.apps_initialize" first.  Your problem may actually be in the hr_security package that your query refers to, and not in the view HR_ORGANIZATION_UNITS.

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

JAKEX🇺🇸

ASKER

David Johnson - Thanks. I will give that a try and get back. Was hoping the issue was obvious to one of you experts. breadtan - Thanks. I am aware of those requirements. Markgeer... agree this is related EBS hr_security. Have a growing knowledge of same as I have an open Oracle SR over 2 years on a RAC failover bug. Troubleshot/provided solution... Oracle agrees however they have internal arguments on which Oracle department must fix, EBS / multitenancy or OLS / rdbms.  Todays question is not about RAC. However this is likely a context issue in that I created a new database user, gave the priv and synonyms. And as this new user, I query HR_ORGANIZATION_UNITS prior to apps_initialize to see all the records. Once I call apps_initialize, I get no records. I'm missing something... AS APPS this works and is the core of hr_organization_units... Results: 202      N      TRUE      Y      202      202 begin fnd_global.apps_initialize (1013415,21540,800,0); end; SELECT hao.organization_id, hr_security.view_all, hr_security.show_record ('HR_ALL_ORGANIZATION_UNITS',haotl.organization_id) zero, hr_general.get_xbg_profile, hao.business_group_id, hr_general.get_business_group_id   FROM hr_all_organization_units hao, hr_all_organization_units_tl haotl  WHERE hao.organization_id = 202    AND hao.business_group_id = 202    AND hao.organization_id = haotl.organization_id    AND haotl.language = 'US'  order by 1; AS the NEW Database User get this result for the same apps_initialize and query... Results: 202      N      FALSE      {null}      202      202   Notes: * HR_SECURITY.SHOW_RECORD is called if the current security profile is a restricted security profile. It validates whether the row in question is visible through the current security profile. * HR_GENERAL.GET_XBG_PROFILE returns the value of the HR:Cross Business Group profile option. btan thought this is useful link on the HR_ORGANIZATION_UNITS http://docs.oracle.com/cd/E18727_01/doc.121/e13488/T2650T402389.htm

Note: FND_GLOBAL is not accessible from HR reporting users. If you have created your own custom tables, perform the following steps to make them accessible to reporting users: Create table. Select a table name that does not conflict with any tables or views that might exist in Oracle Applications. Do not use two or three character prefixes such as HR, PER, PAY, FF, DT, SSP, GHR, BEN, OTA, HXT, EDW, HRI, HXC, PQH, PQP or IRC. Grant select access on the table to HR_REPORTING_USER role, from the user that owns the custom table. GRANT SELECT ON custom_table TO hr_reporting_user; You must repeat this step every time you perform an installation or upgrade. However, you do not need to rerun SECGEN as existing reporting users that have already been granted access to the HR_REPORTING_USER role will automatically receive any new permissions added to the role. Create a synonym to the table. If you use public synonyms, remember that the Oracle user from which you create the public synonym must have CREATE PUBLIC SYNONYM system privilege. CREATE PUBLIC SYNONYM custom_table         FOR base_table_account.custom_table;

ASKER CERTIFIED SOLUTIONbtanmembershipLog in or create a free account to see answer.Signing up is free and takes 30 seconds. No credit card required.Create AccountWasim Akram Shaik🇮🇳 User level views are controlled by security contexts and profiles associated by those contexts in oracle apps. As far as HR is concerned, the HR_SECURITY package has the control the values retrieved by your query There are few profiles which you should enable to user to have access to user level Go to System Administrator Responsibility and navigate to System->Profile->Options and do query for the below three profiles and set each of the profile at User level one by one and execute the query which you had posted here and see whether this helps to get your desired result User Profile Option Name              Profile Option Name  HR:Query Only Mode      -->            PER_QUERY_ONLY_MODE       HR: Security Profile    -->            PER_SECURITY_PROFILE_ID       HR:Organization ID      -->          PER_ORGANIZATION_ID

Get a FREE t-shirt when you ask your first question.

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

JAKEX🇺🇸

ASKER

breadtan - Thanks. The database user xxkx is not setup as an HR reporting user. And I have access to fnd_global. In fact I go a step further to limit access to same by creating a wrapper in APPS and a synonym fnd_global in xxkx to my wrapper [etc.]. This way I have more control of the fnd layer. However you struck gold with the fnd_oracle_userid and the read_only_flag. I did have my Oracle ID there but with the read_only_flag as 'A' [the default]. I changed to 'U' and indeed the Oracle secure view HR_ORGANIZATION_UNITS returns values after fnd_global_apps_intialize. Prior to apps_initialize... select count(assignment_id) from per_assignments_f where business_group_id = 202; --Count=5353 select count(*) from HR_ORGANIZATION_UNITS where business_group_id = 202; -- Count=480 begin fnd_global.apps_initialize (1013415,21540,800,0); end; After apps_initialize... select count(assignment_id) from per_assignments_f where business_group_id = 202; --Count=2458 select count(*) from HR_ORGANIZATION_UNITS where business_group_id = 202; -- Count=185 Earlier test posted above as the NEW Database User now gets this correct result for the same apps_initialize and query. Note that hr_security.show_record ('HR_ALL_ORGANIZATION_UNITS',haotl.organization_id) now equals TRUE 202      N      TRUE      Y      202      202 I'll find the meaning for the READ_ONLY_FLAG "U" and test further.  And thanks to everyone who contributed. I really appreciate your time. JAKEX🇺🇸

ASKER

I've requested that this question be closed as follows: Accepted answer: 0 points for JAKEX's comment #a40124450 for the following reason: The suggestion worked. I will do additional testing but the project deadline is now secure. Thanks JAKEX🇺🇸

ASKER

Let me try this again. I am assigning breadtan 500 points. The solution worked.

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

Oracle Database

--

Questions

--

Followers

Top Experts

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.