Collecting from Oracle SQL databases
The Nanitor Oracle SQL benchmarks support Oracle versions 11g,12c, 19, and, 21 through the Nanitor Collector. Before you can add the Oracle database to your Nanitor instance you need to make some adjustments to your Oracle database instance. Otherwise, you can end with some permission issues that prevent Nanitor from collecting the necessary information and as a result, the Oracle benchmark will not get applied.
Prerequisites
- You need to have a Nanitor Collector up and running. A single collector can collect from multiple network devices, servers, and databases.
- You need to have the IP address of the Oracle SQL database and the collector needs to have network access to connect to the database.
- You need to have an Oracle user. The privileges at a minimum needed for this Oracle user depend on the version of the Oracle database.
Creating and granting privileges to the database user
Info
The recommendations expressed in this article assume the presence of a role named CISSCANROLE and a user named CISSCAN. This role and user should be created by executing the following SQL statements, being careful to substitute an appropriate password for password
-- Create the role
CREATE ROLE CISSCANROLE;
-- Grant necessary privileges to the role
GRANT CREATE SESSION TO CISSCANROLE ;
GRANT SELECT ON V_$PARAMETER TO CISSCANROLE ;
GRANT SELECT ON DBA_TAB_PRIVS TO CISSCANROLE ;
GRANT SELECT ON DBA_TABLES TO CISSCANROLE;
GRANT SELECT ON DBA_PROFILES TO CISSCANROLE ;
GRANT SELECT ON DBA_SYS_PRIVS TO CISSCANROLE ;
GRANT SELECT ON DBA_STMT_AUDIT_OPTS TO CISSCANROLE ;
GRANT SELECT ON DBA_ROLE_PRIVS TO CISSCANROLE ;
GRANT SELECT ON DBA_OBJ_AUDIT_OPTS TO CISSCANROLE ;
GRANT SELECT ON DBA_PRIV_AUDIT_OPTS TO CISSCANROLE ;
GRANT SELECT ON DBA_PROXIES TO CISSCANROLE ;
GRANT SELECT ON DBA_USERS TO CISSCANROLE ;
GRANT SELECT ON DBA_USERS_WITH_DEFPWD TO CISSCANROLE ;
GRANT SELECT ON DBA_DB_LINKS TO CISSCANROLE ;
GRANT SELECT ON DBA_ROLES TO CISSCANROLE;
GRANT SELECT ON V_$INSTANCE to CISSCANROLE ;
GRANT SELECT ON V_$DATABASE to CISSCANROLE ;
GRANT SELECT ON V_$PDBS to CISSCANROLE ;
GRANT SELECT ON V_$SYSTEM_PARAMETER TO CISSCANROLE ;
GRANT AUDIT_VIEWER TO CISSCANROLE ;
-- Create the user and assign the user to the role
CREATE USER CISSCAN IDENTIFIED BY <password>;
GRANT CISSCANROLE TO CISSCAN;
CREATE VIEW SYS.X_$KSPPI AS SELECT * FROM SYS.X$KSPPI;
CREATE VIEW SYS.X_$KSPPCV AS SELECT * FROM SYS.X$KSPPCV;
GRANT SELECT ON SYS.X_$KSPPI to CISSCANROLE;
GRANT SELECT ON SYS.X_$KSPPCV to CISSCANROLE;
-- Create the role
CREATE ROLE CISSCANROLE;
-- Grant necessary privileges to the role
GRANT CREATE SESSION TO CISSCANROLE ;
GRANT SELECT ON V_$PARAMETER TO CISSCANROLE ;
GRANT SELECT ON DBA_TAB_PRIVS TO CISSCANROLE ;
GRANT SELECT ON DBA_TABLES TO CISSCANROLE;
GRANT SELECT ON DBA_PROFILES TO CISSCANROLE ;
GRANT SELECT ON DBA_SYS_PRIVS TO CISSCANROLE ;
GRANT SELECT ON DBA_STMT_AUDIT_OPTS TO CISSCANROLE ;
GRANT SELECT ON DBA_ROLE_PRIVS TO CISSCANROLE ;
GRANT SELECT ON DBA_OBJ_AUDIT_OPTS TO CISSCANROLE ;
GRANT SELECT ON DBA_PRIV_AUDIT_OPTS TO CISSCANROLE ;
GRANT SELECT ON DBA_PROXIES TO CISSCANROLE ;
GRANT SELECT ON DBA_USERS TO CISSCANROLE ;
GRANT SELECT ON DBA_USERS_WITH_DEFPWD TO CISSCANROLE ;
GRANT SELECT ON DBA_DB_LINKS TO CISSCANROLE ;
GRANT SELECT ON DBA_ROLES TO CISSCANROLE;
GRANT SELECT ON V_$INSTANCE to CISSCANROLE ;
GRANT SELECT ON V_$DATABASE to CISSCANROLE ;
GRANT SELECT ON V_$PDBS to CISSCANROLE ;
GRANT SELECT ON V_$SYSTEM_PARAMETER TO CISSCANROLE ;
GRANT AUDIT_VIEWER TO CISSCANROLE ;
-- Create the user and assign the user to the role
CREATE USER CISSCAN IDENTIFIED BY <password>;
GRANT CISSCANROLE TO CISSCAN;
CREATE VIEW SYS.X_$KSPPI AS SELECT * FROM SYS.X$KSPPI;
CREATE VIEW SYS.X_$KSPPCV AS SELECT * FROM SYS.X$KSPPCV;
GRANT SELECT ON SYS.X_$KSPPI to CISSCANROLE;
GRANT SELECT ON SYS.X_$KSPPCV to CISSCANROLE;
-- Create the role
CREATE ROLE CISSCANROLE;
-- Grant necessary privileges to the role
GRANT CREATE SESSION TO CISSCANROLE ;
GRANT SELECT ON V_$PARAMETER TO CISSCANROLE ;
GRANT SELECT ON DBA_TAB_PRIVS TO CISSCANROLE ;
GRANT SELECT ON DBA_TABLES TO CISSCANROLE;
GRANT SELECT ON DBA_PROFILES TO CISSCANROLE ;
GRANT SELECT ON DBA_SYS_PRIVS TO CISSCANROLE ;
GRANT SELECT ON DBA_STMT_AUDIT_OPTS TO CISSCANROLE ;
GRANT SELECT ON DBA_ROLE_PRIVS TO CISSCANROLE ;
GRANT SELECT ON DBA_OBJ_AUDIT_OPTS TO CISSCANROLE ;
GRANT SELECT ON DBA_PRIV_AUDIT_OPTS TO CISSCANROLE ;
GRANT SELECT ON DBA_PROXIES TO CISSCANROLE ;
GRANT SELECT ON DBA_USERS TO CISSCANROLE ;
GRANT SELECT ON DBA_USERS_WITH_DEFPWD TO CISSCANROLE ;
GRANT SELECT ON DBA_DB_LINKS TO CISSCANROLE ;
GRANT SELECT ON DBA_ROLES TO CISSCANROLE;
GRANT SELECT ON V_$INSTANCE to CISSCANROLE ;
GRANT SELECT ON V_$DATABASE to CISSCANROLE ;
GRANT SELECT ON V_$PDBS to CISSCANROLE ;
GRANT SELECT ON V_$SYSTEM_PARAMETER TO CISSCANROLE ;
GRANT AUDIT_VIEWER TO CISSCANROLE ;
-- Create the user and assign the user to the role
CREATE USER CISSCAN IDENTIFIED BY <password>;
GRANT CISSCANROLE TO CISSCAN;
CREATE VIEW SYS.X_$KSPPI AS SELECT * FROM SYS.X$KSPPI;
CREATE VIEW SYS.X_$KSPPCV AS SELECT * FROM SYS.X$KSPPCV;
GRANT SELECT ON SYS.X_$KSPPI to CISSCANROLE;
GRANT SELECT ON SYS.X_$KSPPCV to CISSCANROLE;
Note
If you are creating the user in a multi tenant environmanet you need to adjust the permissions as follows:
-- Create the role
CREATE ROLE CISSCANROLE CONTAINER=ALL;
-- Grant necessary privileges to the role
GRANT CREATE SESSION TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON V_$PARAMETER TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_TAB_PRIVS TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_TABLES TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_PROFILES TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_SYS_PRIVS TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_STMT_AUDIT_OPTS TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_ROLE_PRIVS TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_OBJ_AUDIT_OPTS TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_PRIV_AUDIT_OPTS TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_PROXIES TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_USERS TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_ROLES TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_USERS_WITH_DEFPWD TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_DB_LINKS TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON V_$INSTANCE to CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON V_$DATABASE to CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON V_$PDBS to CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON V_$SYSTEM_PARAMETER TO CISSCANROLE CONTAINER=ALL;
GRANT AUDIT_VIEWER TO CISSCANROLE CONTAINER=ALL;
-- Create the user and assign the user to the role
CREATE USER C##CISSCAN IDENTIFIED BY <password> CONTAINER=ALL;
GRANT CISSCANROLE TO C##CISSCAN CONTAINER=ALL;
ALTER USER C##CISSCAN SET CONTAINER_DATA=ALL CONTAINER=CURRENT;
-- THE NEXT FOUR GRANTS MUST BE PERFORMED BY SYS:
CREATE VIEW SYS.X_$KSPPI AS SELECT * FROM SYS.X$KSPPI;
CREATE VIEW SYS.X_$KSPPCV AS SELECT * FROM SYS.X$KSPPCV;
GRANT SELECT ON SYS.X_$KSPPI to CISSCANROLE;
GRANT SELECT ON SYS.X_$KSPPCV to CISSCANROLE;
-- Create the role
CREATE ROLE CISSCANROLE CONTAINER=ALL;
-- Grant necessary privileges to the role
GRANT CREATE SESSION TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON V_$PARAMETER TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_TAB_PRIVS TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_TABLES TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_PROFILES TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_SYS_PRIVS TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_STMT_AUDIT_OPTS TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_ROLE_PRIVS TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_OBJ_AUDIT_OPTS TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_PRIV_AUDIT_OPTS TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_PROXIES TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_USERS TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_ROLES TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_USERS_WITH_DEFPWD TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_DB_LINKS TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON V_$INSTANCE to CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON V_$DATABASE to CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON V_$PDBS to CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON V_$SYSTEM_PARAMETER TO CISSCANROLE CONTAINER=ALL;
GRANT AUDIT_VIEWER TO CISSCANROLE CONTAINER=ALL;
-- Create the user and assign the user to the role
CREATE USER C##CISSCAN IDENTIFIED BY <password> CONTAINER=ALL;
GRANT CISSCANROLE TO C##CISSCAN CONTAINER=ALL;
ALTER USER C##CISSCAN SET CONTAINER_DATA=ALL CONTAINER=CURRENT;
-- THE NEXT FOUR GRANTS MUST BE PERFORMED BY SYS:
CREATE VIEW SYS.X_$KSPPI AS SELECT * FROM SYS.X$KSPPI;
CREATE VIEW SYS.X_$KSPPCV AS SELECT * FROM SYS.X$KSPPCV;
GRANT SELECT ON SYS.X_$KSPPI to CISSCANROLE;
GRANT SELECT ON SYS.X_$KSPPCV to CISSCANROLE;
-- Create the role
CREATE ROLE CISSCANROLE CONTAINER=ALL;
-- Grant necessary privileges to the role
GRANT CREATE SESSION TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON V_$PARAMETER TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_TAB_PRIVS TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_TABLES TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_PROFILES TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_SYS_PRIVS TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_STMT_AUDIT_OPTS TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_ROLE_PRIVS TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_OBJ_AUDIT_OPTS TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_PRIV_AUDIT_OPTS TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_PROXIES TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_USERS TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_ROLES TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_USERS_WITH_DEFPWD TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON CDB_DB_LINKS TO CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON V_$INSTANCE to CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON V_$DATABASE to CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON V_$PDBS to CISSCANROLE CONTAINER=ALL;
GRANT SELECT ON V_$SYSTEM_PARAMETER TO CISSCANROLE CONTAINER=ALL;
GRANT AUDIT_VIEWER TO CISSCANROLE CONTAINER=ALL;
-- Create the user and assign the user to the role
CREATE USER C##CISSCAN IDENTIFIED BY <password> CONTAINER=ALL;
GRANT CISSCANROLE TO C##CISSCAN CONTAINER=ALL;
ALTER USER C##CISSCAN SET CONTAINER_DATA=ALL CONTAINER=CURRENT;
-- THE NEXT FOUR GRANTS MUST BE PERFORMED BY SYS:
CREATE VIEW SYS.X_$KSPPI AS SELECT * FROM SYS.X$KSPPI;
CREATE VIEW SYS.X_$KSPPCV AS SELECT * FROM SYS.X$KSPPCV;
GRANT SELECT ON SYS.X_$KSPPI to CISSCANROLE;
GRANT SELECT ON SYS.X_$KSPPCV to CISSCANROLE;
Troubleshooting
As with all other assets that leverage the collector, a user account with the right permission is required for successful collection. If the user account does not have the right permission the collection will fail and benchmark calculations can not be completed.
If you get the following error in the collector debug screen you know you have a permission issue:
Failed to run scap checkin benchmark: ORA-00942: table or view does not exist
Ensure you have followed the steps above and granted the permission accordingly