'ORA-00942: Table or View does not exist' error when referencing a custom table/view in a report query in RSA Identity Governance & Lifecycle

[#1]

Article Number

000030679

Applies To

RSA Product Set: RSA Identity Governance & Lifecycle
RSA Version/Condition: 7.0.x, 7.1.x, 7.2.x
 

Issue

When creating a new report (Reports > Tabular/Charts > Create Report) or when editing an existing report (Reports > Tabular/Charts > {report_name} > Edit) and referencing a custom table under the Query tab, the following error occurs:
 

  com.aveksa.server.runtime.ServerException:
  com.aveksa.server.runtime.ServerException:
java.sql.SQLSyntaxErrorException: ORA-00942:
          table or view does not exist

Image description

 

Cause

This error occurs because the report user, avdwuser, does not have select privileges on the custom table/view referenced in the query.
 

Resolution

To resolve this issue, grant select on the custom table to the avdwuser.

1. Login to SQL as the avuser
2. Execute the following SQL grant statement:

grant select on <table | view> to avdwuser

For example:

grant select on custom_table_1 to avdwuser

 

Notes

When the avdb schema is initially created, the avdwuser is given select privileges on all tables using the following script file:
 

$AVEKSA_HOME/database/Create Scripts/Grant_Select_To_Reports.sql 

Any tables created after the avdb schema has been created need to have the grant select statement executed manually.

Please see RSA Knowledge Base Article 000038569 -- 'Request could not be handled' error exporting metadata from RSA Identity Governance & Lifecycle for an additional problem that can occur if a custom table in the avuser schema is not granted the select privilege.