The PTV Tables Are Not Accessible From GVA (Doc ID 2191360.1)

Last updated on OCTOBER 09, 2016

Applies to:

Oracle Life Sciences Data Hub - Version 2.2.2 and later
Information in this document applies to any platform.

Symptoms

On : 2.2.2 version, Definition Component

When attempting to access the PTV after few minutes of being able to access (2 -3 minutes) them, when the user try to access the same table at the same session in SQL Dev
the following error occurs.

ERROR
-----------------------
 ORA-01031: insufficient privileges


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. From SQL Dev , run the GVA initalization script
2. Run the following SQL:
  
SQL> select * from session_roles;
  CONNECT
  RESOURCE
  OLAP_USER
  OEM_MONITOR
  SELECT_CATALOG_ROLE
  HS_ADMIN_SELECT_ROLE
  HS_ADMIN_EXECUTE_ROLE
  CDR_CONNECT_ROLE
  CWM_USER
  OWB_DESIGNCENTER_VIEW
  OWB_USER

  12 rows selected.

SQL> select * from user_tab_privs;
  records shows users got CDR tab privileges granted by APPS

  386 rows selected.
  
SQL> select count(clinical_study_id) from gva_oc_stable_views.clinical_studies_v46;
  COUNT(CLINICAL_STUDY_ID)
  --------------------------------------------
  475


3) Wait for few minutes and run the following again:
  select count(clinical_study_id) from gva_oc_stable_views.clinical_studies_v46;

you got the error:
SQL> set time on
04:39:15 SQL> select count(clinical_study_id) from gva_oc_stable_views.clinical_studies_v46;
select count(clinical_study_id) from gva_oc_stable_views.clinical_studies_v46
  *
ERROR at line 1:
ORA-01031: insufficient privileges

Check the session roles and it's exactly the same as the first time it was run, however the user_tab_privs shows that the grants has been revoke to all CDR% PTV which was granted by APPS


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms