000030679 - ORA-00942: Table of View does not exist encountered with Reports on custom table/view in Identity Management and Governance

Document created by RSA Customer Support Employee on Jun 14, 2016•Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 3Show Document
  • Comment • 0
  • View in full screen mode

Article Content

Article Number000030679
Applies ToRSA Product Set: Identity Management and Governance
 
IssueWhen a custom database object, for example a table or view, has been created in the default Aveksa schema AVDB if a report is created via Reports / Tabular and this custom object is named in the SQL statement the following error may be encountered
com.aveksa.server.runtime.ServerException: com.aveksa.server.runtime.ServerException: java.sql.SQLException: ORA-00942: table or view does not exist
and has depicted below
User-added image
CauseThe cause of the failure is that the report user, AVDWUSER does not have SELECT privileges against the custom table/view referenced in the Report definitions select clause.   At time of database creation a script is run which will effectively run the following SQL as the AVUSER database user

grant select on <table | view> to AVDWUSER;
where <table|view> is the name of a table or view which is in the AVUSER schema.
In the above scenario, as the custom table/view was created post database creation the above SQL will need to be resubmitted referencing the new custom table/view.
 
ResolutionConnect to the AVDB scheme as database user AVUSER and run the followng SQL
grant select on <table | view> to AVDWUSER;

replacing <table | view> with the actual table or view name, for example
grant select on mycustomtable to AVDWUSER;

Attachments

    Outcomes