SQL query fails or action on the Information Analyzer Web UI throws an Internal Server Error due to insufficient privileges on an Oracle database
Technote (troubleshooting)
Problem(Abstract)
Unable to retrieve data from the metadata repository using a SQL view due to insufficient privileges on an Oracle database. This may manifest itself as an internal server error when clicking an action on the Information Analyzer Web UI.
Cause
On an Oracle environment the metadata repository user (default name: xmeta) doesn't have the permission to access the objects under the Information Governance Dashboard(CMVIEWS, IGVIEWS, IAVIEWS, etc.,) schemas by default which causes exceptions such as the following when trying to query a SQL view:
[Oracle]ORA-01031: insufficient privileges
[Oracle]ORA-00942: table or view does not exist
Missing privileges to access a SQL view can cause actions on the Information Analyzer Web UI such as 'Find data sets' to fail since these actions query the SQL views to retrieve values from the metadata repository based on the privileges of the metadata repository user (default name: xmeta).
Resolving the problem
Follow the steps mentioned below to grant the metadata repository user the necessary permission to access the tables/views under the Information Governance Dashboard schemas
1. Copy the attached script(cmd/sh) and the sql file into a directory on the machine where Oracle is installed
2. Make sure Oracle(sqlplus) is available on the path. If Oracle(sqlplus) isn't available on the path, do the following
On Windows
- Open command prompt and navigate to the folder where the script and sql file are copied into
- set ORACLE_HOME=<ORACLE_HOME_DIR>
- set PATH=%ORACLE_HOME%\bin;%PATH%;
- set ORACLE_SID=<ORACLE_SERVICE_NAME>
On Unix
- Open unix terminal with a root/root equivalent user and navigate to the folder where the script and sql file are copied into
- export ORACLE_HOME=<ORACLE_HOME>
- export PATH=$ORACLE_HOME/bin:$PATH:
- export ORACLE_SID=<ORACLE_SERVICE_NAME>
- chmod -R 755, to grant required permissions for the script to be executed
3. Run the grant_xmeta_permissions.cmd/sh script by passing the necessary parameters
Usage: grant_xmeta_permissions ^<OracleSystemUser^> ^<OracleSystemPassword^> ^<OracleServiceName^> ^<XmetaUserName^>
Note : The <OracleSystemUser> should be a user with sysdba privileges.
4. grant_xmeta_permissions.log file is created in the directory where the script and sql file are located. This log will have the information of all the grant commands that were executed. Verify that the log does not contain any errors.
--