Error "ORA-00942: table or view does not exist" when running a CER job on Oracle 19c using the CIS Benchmark standard.

Products

• Control Compliance Suite
• Control Compliance Suite Standards Server

Issue/Introduction

Control Compliance Suite (CCS)

When running a CER (Collection-Evaluation-Reporting) job using the CIS Oracle Database 19c Benchmark v1.0.0 standard to perform data collection on Oracle 19c assets, the data collection may fail with the following error messages:

• For Agentless:
  Error in GetResultSet. Scope is database '<database name>' on server '<server IP address>'. Additional Error Information: Exception thrown while getting datareader.ORA-00942: table or view does not exist
• For Agent-based:
  Failure occurred in query execution. ERROR at line 1:=0AORA-00942: table or view does not exist=0A. Scope is database '<database name>' on server '<server name>'

Environment

CCS 12.x

SCU 2021-3 or newer

Cause

As per the CIS documentation, check 2.2.15 in the 'CIS Oracle Database 19c Benchmark v1.0.0' standard relies on the following custom views for data collection, which are not predefined:

• X_$KSPPI
• X_$KSPPCV

Therefore, if you did not create the custom views, and when you run a CER job for the CIS Oracle Database 19c Benchmark v1.0.0 standard, it fails to collect data from Oracle 19c assets. For more information, see https://workbench.cisecurity.org/benchmarks/5799/sections/597488.html

Resolution

To resolve this issue and successfully perform data collection on systems running Oracle 19c using the CIS Oracle Database 19c Benchmark v1.0.0 standard, the following prerequisites must be met. To fulfill the prerequisites, sysdba or equivalent privileges are required.

• Create the SYS.X_$KSPPI and SYS.X_$KSPPCV custom views
• Grant the SELECT privilege to the custom views

 

Create the SYS.X_$KSPPI and SYS.X_$KSPPCV custom views

Use the following commands to create the custom views:

• CREATE VIEW SYS.X_$KSPPI AS SELECT * FROM SYS.X$KSPPI;
• CREATE VIEW SYS.X_$KSPPCV AS SELECT * FROM SYS.X$KSPPCV;

Grant the SELECT privilege to the custom views

Use the following commands to grant the SELECT privilege to the custom views:

• GRANT SELECT ON SYS.X_$KSPPI to <Role assigned to the user configured in CCS for Data Collection>;
• GRANT SELECT ON SYS.X_$KSPPCV to <Role assigned to the user configured in CCS for Data Collection>;

NOTE: Alternately, if you do not want to evaluate check 2.2.15, you can create a copy of the CIS Oracle Database 19c Benchmark v1.0.0 standard and delete check 2.2.15 from the copied standard and use it for further assessment.

Additional Information

This information can also be found in the CCS SCU 2021-3 documentation:
https://techdocs.broadcom.com/us/en/symantec-security-software/information-security/control-compliance-suite/SCU-2021-3/Security-Content-Update/Troubleshooting/troubleshooting-data-collection-on-oracle-19c-scu-2021-3.html

 

Optional information on CIS Benchmarks:

Center for Internet Security (CIS) homepage: https://www.cisecurity.org/