As an editor of CIS Oracle Database benchmark, I have created a script, or rather compiled a script of ALL assessment/audit SQLs. I run this script extensively during my testing of the benchmarks as well as during compliance audits. I have found this script to be useful. I hope you will find this script useful, too. If you have any comments, please feel free to post.
By the way, this script is for standard database configuration, i.e. non multi-tenant database configuration. I do have a separate script for multi-tenant database that I will clean-up and post it here.
REM
==============================================================================
REM Name:
CIS_Assessment_12c_v30.sql
REM Version:
1.0
REM Author:
Jay Mehta
REM Date:
April 9, 2020
REM
Description: Run this script to assess compliance with CIS Oracle Database
Benchmark v3.0.
REM This script is for non
multi-tenant databases or PDB within CDB.
REM CIS documentation should be used
to interpret the output of this script.
REM This script is a compliation of
all audit SQLs from CIS Oracle Database Benchmark v3.0
REM Note:
This script does NOT assess controls 1.1 and 2.1.1 - 2.1.4
REM Usage:
CISSCAN/<Password>@<DB Connect String>
REM where CISSCAN is the db
account used to perform the assessment.
REM
===============================================================================
set echo off
set linesi
300
set pagesi
500
column
audit_condition format a20
column
audit_option format a30
column
audit_option_type format a20
column con
format a10
column
granted_role format a30
column
grantee format a30
column
grantor format a20
column
is_pdb_modifiable format a10
column
isdeprecated format a10
column limit
format a40
column name
format a40
column
object_name format a30
column
object_schema format a30
column owner
format a20
column
policy_name format a30
column
privilege format a30
column
profile format a30
column
proxy_name format a30
column
resource_name format a30
column stmt
format a120
column
table_name format a30
column
upper(value) format a60
column
user_name format a30
column
username format a30
column value
format a60
set echo on
REM
REM
REM
================================================
REM 2.2.1
SELECT
UPPER(VALUE)
FROM
V$SYSTEM_PARAMETER
WHERE
UPPER(NAME) = 'AUDIT_SYS_OPERATIONS'
AND
UPPER(VALUE) != 'TRUE' ;
REM
REM
REM
================================================
REM 2.2.2
SELECT
UPPER(VALUE)
FROM
V$SYSTEM_PARAMETER
WHERE
UPPER(NAME)='AUDIT_TRAIL'
AND
UPPER(VALUE) = 'NONE' ;
REM
REM
REM
================================================
REM 2.2.3
SELECT
UPPER(VALUE)
FROM
V$SYSTEM_PARAMETER
WHERE
UPPER(NAME)='GLOBAL_NAMES'
AND
UPPER(VALUE) != 'TRUE' ;
REM
REM
REM
================================================
REM 2.2.4
SELECT
UPPER(VALUE)
FROM
V$SYSTEM_PARAMETER
WHERE
UPPER(NAME)='O7_DICTIONARY_ACCESSIBILITY'
AND
UPPER(VALUE) != 'FALSE' ;
REM
REM
REM
================================================
REM 2.2.5
SELECT
UPPER(VALUE)
FROM
V$SYSTEM_PARAMETER
WHERE
UPPER(NAME)='OS_ROLES'
AND
UPPER(VALUE) != 'FALSE' ;
REM
REM
REM
================================================
REM 2.2.6
SELECT
UPPER(VALUE)
FROM
V$SYSTEM_PARAMETER
WHERE
UPPER(NAME)='REMOTE_LISTENER'
AND VALUE IS
NOT NULL ;
REM
REM
REM
================================================
REM 2.2.7
SELECT
UPPER(VALUE)
FROM
V$SYSTEM_PARAMETER
WHERE
UPPER(NAME)='REMOTE_LOGIN_PASSWORDFILE'
AND UPPER(VALUE)
!= 'NONE' ;
REM
REM
REM
================================================
REM 2.2.8
SELECT
UPPER(VALUE)
FROM
V$SYSTEM_PARAMETER
WHERE
UPPER(NAME)='REMOTE_OS_AUTHENT'
AND
UPPER(VALUE) != 'FALSE' ;
REM
REM
REM
================================================
REM 2.2.9
SELECT
UPPER(VALUE)
FROM
V$SYSTEM_PARAMETER
WHERE
UPPER(NAME)='REMOTE_OS_ROLES'
AND
UPPER(VALUE) != 'FALSE' ;
REM
REM
REM
================================================
REM 2.2.10
SELECT VALUE
FROM
V$SYSTEM_PARAMETER
WHERE
UPPER(NAME)='UTL_FILE_DIR'
AND
UPPER(VALUE) IS NOT NULL;
REM
REM
REM
================================================
REM 2.2.11
SELECT
UPPER(VALUE)
FROM
V$SYSTEM_PARAMETER
WHERE
UPPER(NAME)='SEC_CASE_SENSITIVE_LOGON'
AND
UPPER(VALUE) != 'TRUE' ;
REM
REM
REM
================================================
REM 2.2.12
SELECT
UPPER(VALUE)
FROM
V$SYSTEM_PARAMETER
WHERE
UPPER(NAME)='SEC_MAX_FAILED_LOGIN_ATTEMPTS'
AND
TO_NUMBER(VALUE) > 3 ;
REM
REM
================================================
REM 2.2.13
SELECT
UPPER(VALUE)
FROM
V$SYSTEM_PARAMETER
WHERE
UPPER(NAME)='SEC_PROTOCOL_ERROR_FURTHER_ACTION'
AND
UPPER(VALUE) NOT LIKE '%DROP,3%' ;
REM
REM
REM
================================================
REM 2.2.14
SELECT
UPPER(VALUE)
FROM
V$SYSTEM_PARAMETER
WHERE
UPPER(NAME)='SEC_PROTOCOL_ERROR_TRACE_ACTION'
AND
UPPER(VALUE) != 'LOG' ;
REM
REM
REM
================================================
REM 2.2.15
SELECT
UPPER(VALUE)
FROM
V$SYSTEM_PARAMETER
WHERE
UPPER(NAME)='SEC_RETURN_SERVER_RELEASE_BANNER'
AND
UPPER(VALUE) != 'FALSE' ;
REM
REM
REM
================================================
REM 2.2.16
SELECT
UPPER(VALUE)
FROM
V$SYSTEM_PARAMETER
WHERE
UPPER(NAME)='SQL92_SECURITY'
AND
UPPER(VALUE) != 'TRUE' ;
REM
REM
REM
================================================
REM 2.2.17
SELECT
A.KSPPINM, B.KSPPSTVL
FROM
SYS.X_$KSPPI a, SYS.X_$KSPPCV b
WHERE
A.INDX=B.INDX
AND
A.KSPPINM LIKE '\_%trace_files_public' escape '\'
AND
B.KSPPSTVL != 'FALSE' ;
REM
REM
REM
================================================
REM 2.2.18
SELECT
UPPER(VALUE)
FROM
V$SYSTEM_PARAMETER
WHERE
UPPER(NAME)='RESOURCE_LIMIT'
AND
UPPER(VALUE) != 'TRUE' ;
REM
REM
REM
================================================
REM 3.1
SELECT
P.PROFILE, P.RESOURCE_NAME, P.LIMIT
FROM
DBA_PROFILES P
WHERE
TO_NUMBER(DECODE(P.LIMIT,
'DEFAULT',(SELECT
DISTINCT DECODE(LIMIT,'UNLIMITED',9999,LIMIT)
FROM
DBA_PROFILES
WHERE
PROFILE='DEFAULT'
AND
RESOURCE_NAME='FAILED_LOGIN_ATTEMPTS'),
'UNLIMITED','9999',
P.LIMIT)) > 5
AND
P.RESOURCE_NAME = 'FAILED_LOGIN_ATTEMPTS'
AND EXISTS (
SELECT 'X' FROM DBA_USERS U WHERE U.PROFILE = P.PROFILE ) ;
REM
REM
REM
================================================
REM 3.2
SELECT
P.PROFILE, P.RESOURCE_NAME, P.LIMIT
FROM
DBA_PROFILES P
WHERE
TO_NUMBER(DECODE(P.LIMIT,
'DEFAULT',(SELECT
DISTINCT DECODE(LIMIT,'UNLIMITED',9999,LIMIT)
FROM DBA_PROFILES
WHERE
PROFILE='DEFAULT'
AND
RESOURCE_NAME='PASSWORD_LOCK_TIME'),
'UNLIMITED','9999',
P.LIMIT)) < 1
AND
P.RESOURCE_NAME = 'PASSWORD_LOCK_TIME'
AND EXISTS (
SELECT 'X' FROM DBA_USERS U WHERE U.PROFILE = P.PROFILE ) ;
REM
REM
REM
================================================
REM 3.3
SELECT
P.PROFILE, P.RESOURCE_NAME, P.LIMIT
FROM
DBA_PROFILES P
WHERE
TO_NUMBER(DECODE(P.LIMIT,
'DEFAULT',(SELECT
DISTINCT DECODE(LIMIT,'UNLIMITED',9999,LIMIT)
FROM
DBA_PROFILES
WHERE
PROFILE='DEFAULT'
AND RESOURCE_NAME='PASSWORD_LIFE_TIME'),
'UNLIMITED','9999',P.LIMIT)) > 90 AND
P.RESOURCE_NAME = 'PASSWORD_LIFE_TIME'
AND
EXISTS ( SELECT 'X' FROM DBA_USERS U WHERE
U.PROFILE = P.PROFILE ) ;
REM
REM
REM
================================================
REM 3.4
SELECT
P.PROFILE, P.RESOURCE_NAME, P.LIMIT
FROM
DBA_PROFILES P
WHERE
TO_NUMBER(DECODE(P.LIMIT,
'DEFAULT',(SELECT
DISTINCT DECODE(LIMIT,'UNLIMITED',9999,LIMIT)
FROM DBA_PROFILES
WHERE
PROFILE='DEFAULT'
AND
RESOURCE_NAME='PASSWORD_REUSE_MAX'),
'UNLIMITED','9999',P.LIMIT)) < 20 AND
P.RESOURCE_NAME = 'PASSWORD_REUSE_MAX'
AND
EXISTS ( SELECT 'X' FROM DBA_USERS U WHERE
U.PROFILE = P.PROFILE ) ;
REM
REM
REM
================================================
REM 3.5
SELECT
P.PROFILE, P.RESOURCE_NAME, P.LIMIT
FROM
DBA_PROFILES P
WHERE
TO_NUMBER(DECODE(P.LIMIT,
'DEFAULT',(SELECT
DISTINCT DECODE(LIMIT,'UNLIMITED',9999,LIMIT)
FROM
DBA_PROFILES
WHERE
PROFILE='DEFAULT'
AND RESOURCE_NAME='PASSWORD_REUSE_TIME'),
'UNLIMITED','9999',P.LIMIT)) < 365 AND
P.RESOURCE_NAME = 'PASSWORD_REUSE_TIME'
AND
EXISTS ( SELECT 'X' FROM DBA_USERS U WHERE
U.PROFILE = P.PROFILE ) ;
REM
REM
REM
================================================
REM 3.6
SELECT
P.PROFILE, P.RESOURCE_NAME, P.LIMIT
FROM
DBA_PROFILES P
WHERE
TO_NUMBER(DECODE(P.LIMIT,
'DEFAULT',(SELECT
DISTINCT DECODE(LIMIT,'UNLIMITED',9999,LIMIT)
FROM DBA_PROFILES
WHERE
PROFILE='DEFAULT'
AND
RESOURCE_NAME='PASSWORD_GRACE_TIME'),
'UNLIMITED','9999',P.LIMIT)) > 5 AND
P.RESOURCE_NAME = 'PASSWORD_GRACE_TIME'
AND
EXISTS ( SELECT 'X' FROM DBA_USERS U WHERE
U.PROFILE = P.PROFILE ) ;
REM
REM
REM
================================================
REM 3.7
SELECT
P.PROFILE, P.RESOURCE_NAME, P.LIMIT
FROM
DBA_PROFILES P
WHERE
DECODE(P.LIMIT,
'DEFAULT',(SELECT LIMIT
FROM DBA_PROFILES
WHERE PROFILE='DEFAULT'
AND RESOURCE_NAME = P.RESOURCE_NAME),
LIMIT) = 'NULL'
AND
P.RESOURCE_NAME = 'PASSWORD_VERIFY_FUNCTION'
AND EXISTS (
SELECT 'X'
FROM DBA_USERS U
WHERE U.PROFILE = P.PROFILE ) ;
REM
REM
REM
================================================
REM 3.8
SELECT
P.PROFILE, P.RESOURCE_NAME, P.LIMIT
FROM
DBA_PROFILES P
WHERE
TO_NUMBER(DECODE(P.LIMIT,
'DEFAULT',(SELECT
DISTINCT DECODE(LIMIT,'UNLIMITED',9999,LIMIT)
FROM
DBA_PROFILES
WHERE
PROFILE='DEFAULT'
AND
RESOURCE_NAME='SESSIONS_PER_USER'),
'UNLIMITED','9999',P.LIMIT)) > 10 AND
P.RESOURCE_NAME = 'SESSIONS_PER_USER' AND
EXISTS ( SELECT 'X' FROM DBA_USERS U WHERE
U.PROFILE = P.PROFILE ) ;
REM
REM
REM
================================================
REM 3.9
SELECT
P.PROFILE, P.RESOURCE_NAME, P.LIMIT
FROM
DBA_PROFILES P
WHERE
TO_NUMBER(DECODE(P.LIMIT,'DEFAULT',(SELECT DISTINCT
DECODE(LIMIT,'UNLIMITED',9999,LIMIT)
FROM
DBA_PROFILES
WHERE PROFILE='DEFAULT'
AND
RESOURCE_NAME='INACTIVE_ACCOUNT_TIME'),
'UNLIMITED','9999',
P.LIMIT)) >
120
AND
P.RESOURCE_NAME = 'INACTIVE_ACCOUNT_TIME'
AND EXISTS (
SELECT 'X' FROM DBA_USERS U WHERE U.PROFILE = P.PROFILE ) ;
REM
REM
REM
================================================
REM 4.1
SELECT
DISTINCT A.USERNAME
FROM
DBA_USERS_WITH_DEFPWD A, DBA_USERS B
WHERE
A.USERNAME = B.USERNAME
AND
B.ACCOUNT_STATUS = 'OPEN';
REM
REM
REM
================================================
REM 4.2
SELECT
USERNAME
FROM
DBA_USERS
WHERE
USERNAME IN ('BI','HR','IX','OE','PM','SCOTT','SH');
REM
REM
REM
================================================
REM 4.3
SELECT
USERNAME FROM DBA_USERS WHERE AUTHENTICATION_TYPE = 'EXTERNAL';
REM
REM
REM
================================================
REM 4.4
SELECT
USERNAME
FROM
DBA_USERS
WHERE
PROFILE='DEFAULT'
AND
ACCOUNT_STATUS='OPEN'
AND
ORACLE_MAINTAINED = 'N';
REM
REM
REM
================================================
REM 4.5
SELECT
OWNER, TABLE_NAME
FROM
DBA_TABLES
WHERE
TABLE_NAME='USER$MIG' AND OWNER='SYS';
REM
REM
REM
================================================
REM 4.6
SELECT
DB_LINK, HOST FROM DBA_DB_LINKS WHERE OWNER = 'PUBLIC';
REM
REM
REM
================================================
REM 5.1.1.1
SELECT
TABLE_NAME, PRIVILEGE, GRANTEE
FROM
DBA_TAB_PRIVS
WHERE
GRANTEE='PUBLIC'
AND
PRIVILEGE='EXECUTE'
AND
TABLE_NAME IN
('DBMS_LDAP','UTL_INADDR','UTL_TCP','UTL_MAIL','UTL_SMTP','UTL_DBWS','UTL_ORAMTS','UTL_HTTP','HTTPURITYPE');
REM
REM
REM
================================================
REM 5.1.1.2
SELECT
TABLE_NAME, PRIVILEGE, GRANTEE
FROM
DBA_TAB_PRIVS
WHERE
GRANTEE='PUBLIC'
AND
PRIVILEGE='EXECUTE'
AND
TABLE_NAME IN ('DBMS_ADVISOR','DBMS_LOB','UTL_FILE');
REM
REM
REM
================================================
REM 5.1.1.3
SELECT
TABLE_NAME, PRIVILEGE, GRANTEE
FROM
DBA_TAB_PRIVS
WHERE
GRANTEE='PUBLIC'
AND
PRIVILEGE='EXECUTE'
AND
TABLE_NAME IN ('DBMS_CRYPTO','DBMS_OBFUSCATION_TOOLKIT', 'DBMS_RANDOM');
REM
REM
REM
================================================
REM 5.1.1.4
SELECT
TABLE_NAME, PRIVILEGE, GRANTEE
FROM
DBA_TAB_PRIVS
WHERE
GRANTEE='PUBLIC'
AND
PRIVILEGE='EXECUTE'
AND
TABLE_NAME IN ('DBMS_JAVA','DBMS_JAVA_TEST');
REM
REM
REM
================================================
REM 5.1.1.5
SELECT
TABLE_NAME, PRIVILEGE, GRANTEE
FROM
DBA_TAB_PRIVS
WHERE
GRANTEE='PUBLIC'
AND
PRIVILEGE='EXECUTE'
AND
TABLE_NAME IN ('DBMS_SCHEDULER','DBMS_JOB');
REM
REM
REM
================================================
REM 5.1.1.6
SELECT
TABLE_NAME, PRIVILEGE, GRANTEE
FROM
DBA_TAB_PRIVS
WHERE
GRANTEE='PUBLIC'
AND
PRIVILEGE='EXECUTE'
AND
TABLE_NAME IN ('DBMS_SQL', 'DBMS_XMLGEN',
'DBMS_XMLQUERY','DBMS_XMLSTORE','DBMS_XMLSAVE','DBMS_AW','OWA_UTIL','DBMS_REDACT');
REM
REM
REM
================================================
REM 5.1.2.1
SELECT
TABLE_NAME, PRIVILEGE, GRANTEE
FROM
DBA_TAB_PRIVS
WHERE
GRANTEE='PUBLIC'
AND
PRIVILEGE='EXECUTE'
AND
TABLE_NAME IN
('DBMS_BACKUP_RESTORE','DBMS_FILE_TRANSFER','DBMS_SYS_SQL','DBMS_AQADM_SYSCALLS','DBMS_REPCAT_SQL_UTL','INITJVMAUX',
'DBMS_STREAMS_ADM_UTL','DBMS_AQADM_SYS','DBMS_STREAMS_RPC','DBMS_PRVTAQIM','LTADM','WWV_DBMS_SQL',
'WWV_EXECUTE_IMMEDIATE','DBMS_IJOB','DBMS_PDB_EXEC_SQL');
REM
REM
REM
================================================
REM 5.1.3.1
SELECT
GRANTEE, PRIVILEGE
FROM
DBA_TAB_PRIVS
WHERE
TABLE_NAME='AUD$'
AND OWNER =
'SYS';
REM
REM
REM
================================================
REM 5.1.3.2
SELECT
GRANTEE,TABLE_NAME
FROM
DBA_TAB_PRIVS
WHERE
TABLE_NAME LIKE 'DBA_%'
AND OWNER =
'SYS'
AND GRANTEE
NOT IN (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='Y')
AND GRANTEE
NOT IN (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED='Y');
REM
REM
REM
================================================
REM 5.1.3.3
SELECT
GRANTEE, PRIVILEGE, TABLE_NAME
FROM
DBA_TAB_PRIVS
WHERE
TABLE_NAME in
('CDB_LOCAL_ADMINAUTH$','DEFAULT_PWD$','ENC$','HISTGRM$','HIST_HEAD$','LINK$','PDB_SYNC$','SCHEDULER$_CREDENTIAL','USER$','USER_HISTORY$','XS$VERIFIERS')
AND OWNER =
'SYS'
AND GRANTEE
NOT IN (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='Y')
AND GRANTEE
NOT IN (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED='Y');
REM
REM
REM
================================================
REM 5.2.1
SELECT GRANTEE,
PRIVILEGE
FROM
DBA_SYS_PRIVS
WHERE
PRIVILEGE LIKE '%ANY%'
AND GRANTEE
NOT IN (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='Y')
AND GRANTEE
NOT IN (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED='Y');
REM
REM
REM
================================================
REM 5.2.2
SELECT
GRANTEE, PRIVILEGE
FROM
DBA_SYS_PRIVS
WHERE
ADMIN_OPTION='YES'
AND GRANTEE
NOT IN (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='Y')
AND GRANTEE
NOT IN (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED='Y');
REM
REM
REM
================================================
REM 5.2.3
SELECT
GRANTEE, PRIVILEGE
FROM
DBA_SYS_PRIVS
WHERE
PRIVILEGE='EXECUTE ANY PROCEDURE'
AND
GRANTEE='OUTLN';
REM
REM
REM
================================================
REM 5.2.4
SELECT
GRANTEE, PRIVILEGE
FROM
DBA_SYS_PRIVS
WHERE
PRIVILEGE='EXECUTE ANY PROCEDURE'
AND
GRANTEE='DBSNMP';
REM
REM
REM
================================================
REM 5.2.5
SELECT
GRANTEE, PRIVILEGE
FROM
DBA_SYS_PRIVS
WHERE
PRIVILEGE='SELECT ANY DICTIONARY'
AND GRANTEE
NOT IN (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='Y')
AND GRANTEE
NOT IN (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED='Y');
REM
REM
REM ================================================
REM 5.2.6
SELECT
GRANTEE, PRIVILEGE
FROM
DBA_SYS_PRIVS
WHERE
PRIVILEGE='SELECT ANY TABLE'
AND GRANTEE
NOT IN (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='Y')
AND GRANTEE
NOT IN (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED='Y');
REM
REM
REM
================================================
REM 5.2.7
SELECT
GRANTEE, PRIVILEGE
FROM
DBA_SYS_PRIVS
WHERE
PRIVILEGE='AUDIT SYSTEM'
AND GRANTEE
NOT IN (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='Y')
AND GRANTEE
NOT IN (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED='Y');
REM
REM
REM
================================================
REM 5.2.8
SELECT
GRANTEE, PRIVILEGE
FROM
DBA_SYS_PRIVS
WHERE
PRIVILEGE='EXEMPT ACCESS POLICY'
AND GRANTEE
NOT IN (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='Y')
AND GRANTEE
NOT IN (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED='Y');
REM
REM
REM
================================================
REM 5.2.9
SELECT
GRANTEE, PRIVILEGE
FROM
DBA_SYS_PRIVS
WHERE
PRIVILEGE='BECOME USER'
AND GRANTEE
NOT IN (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='Y')
AND GRANTEE
NOT IN (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED='Y');
REM
REM
REM
================================================
REM 5.2.10
SELECT
GRANTEE, PRIVILEGE
FROM
DBA_SYS_PRIVS
WHERE
PRIVILEGE='CREATE PROCEDURE'
AND GRANTEE
NOT IN (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='Y')
AND GRANTEE
NOT IN (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED='Y');
REM
REM
REM
================================================
REM 5.2.11
SELECT
GRANTEE, PRIVILEGE
FROM
DBA_SYS_PRIVS
WHERE
PRIVILEGE='ALTER SYSTEM'
AND GRANTEE
NOT IN (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='Y')
AND GRANTEE
NOT IN (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED='Y');
REM
REM
REM
================================================
REM 5.2.12
SELECT
GRANTEE, PRIVILEGE
FROM
DBA_SYS_PRIVS
WHERE
PRIVILEGE='CREATE ANY LIBRARY'
AND GRANTEE
NOT IN (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='Y')
AND GRANTEE
NOT IN (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED='Y');
REM
REM
REM
================================================
REM 5.2.13
SELECT
GRANTEE, PRIVILEGE
FROM
DBA_SYS_PRIVS
WHERE
PRIVILEGE='CREATE LIBRARY'
AND GRANTEE
NOT IN (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='Y')
AND GRANTEE
NOT IN (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED='Y');
REM
REM
REM
================================================
REM 5.2.14
SELECT
GRANTEE, PRIVILEGE
FROM
DBA_SYS_PRIVS
WHERE
PRIVILEGE='GRANT ANY OBJECT PRIVILEGE'
AND GRANTEE
NOT IN (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='Y')
AND GRANTEE
NOT IN (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED='Y');
REM
REM
REM
================================================
REM 5.2.15
SELECT
GRANTEE, PRIVILEGE
FROM
DBA_SYS_PRIVS
WHERE
PRIVILEGE='GRANT ANY ROLE'
AND GRANTEE
NOT IN (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='Y')
AND GRANTEE
NOT IN (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED='Y');
REM
REM
REM
================================================
REM 5.2.16
SELECT
GRANTEE, PRIVILEGE
FROM
DBA_SYS_PRIVS
WHERE
PRIVILEGE='GRANT ANY PRIVILEGE'
AND GRANTEE
NOT IN (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='Y')
AND GRANTEE
NOT IN (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED='Y');
REM
REM
REM
================================================
REM 5.3.1
SELECT
GRANTEE, GRANTED_ROLE
FROM
DBA_ROLE_PRIVS
WHERE
GRANTED_ROLE='DELETE_CATALOG_ROLE'
AND GRANTEE
NOT IN (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='Y')
AND GRANTEE
NOT IN (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED='Y');
REM
REM
REM
================================================
REM 5.3.2
SELECT
GRANTEE, GRANTED_ROLE
FROM
DBA_ROLE_PRIVS
WHERE
GRANTED_ROLE='SELECT_CATALOG_ROLE'
AND GRANTEE
NOT IN (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='Y')
AND GRANTEE
NOT IN (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED='Y');
REM
REM
REM
================================================
REM 5.3.3
SELECT
GRANTEE, GRANTED_ROLE
FROM DBA_ROLE_PRIVS
WHERE
GRANTED_ROLE='EXECUTE_CATALOG_ROLE'
AND GRANTEE
NOT IN (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='Y')
AND GRANTEE
NOT IN (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED='Y');
REM
REM
REM
================================================
REM 5.3.4
SELECT
'GRANT' AS PATH, GRANTEE, GRANTED_ROLE
FROM
DBA_ROLE_PRIVS
WHERE
GRANTED_ROLE = 'DBA' AND GRANTEE NOT IN ('SYS', 'SYSTEM')
UNION
SELECT
'PROXY', PROXY || '-' || CLIENT, 'DBA'
FROM
DBA_PROXIES
WHERE CLIENT
IN (SELECT GRANTEE
FROM DBA_ROLE_PRIVS
WHERE GRANTED_ROLE = 'DBA');
REM
REM
REM
================================================
REM 6.1.1
SELECT
AUDIT_OPTION,SUCCESS,FAILURE
FROM
DBA_STMT_AUDIT_OPTS
WHERE USER_NAME
IS NULL
AND
PROXY_NAME IS NULL
AND SUCCESS
= 'BY ACCESS'
AND FAILURE
= 'BY ACCESS'
AND
AUDIT_OPTION='USER';
REM
================================================
REM 6.1.2
SELECT
AUDIT_OPTION,SUCCESS,FAILURE
FROM
DBA_STMT_AUDIT_OPTS
WHERE
USER_NAME IS NULL
AND
PROXY_NAME IS NULL
AND SUCCESS
= 'BY ACCESS'
AND FAILURE
= 'BY ACCESS'
AND
AUDIT_OPTION='ROLE';
REM
================================================
REM 6.1.3
SELECT
AUDIT_OPTION,SUCCESS,FAILURE
FROM
DBA_STMT_AUDIT_OPTS
WHERE
USER_NAME IS NULL
AND
PROXY_NAME IS NULL
AND SUCCESS
= 'BY ACCESS'
AND FAILURE
= 'BY ACCESS'
AND
AUDIT_OPTION='SYSTEM GRANT';
REM
================================================
REM 6.1.4
SELECT
AUDIT_OPTION,SUCCESS,FAILURE
FROM
DBA_STMT_AUDIT_OPTS
WHERE
USER_NAME IS NULL
AND
PROXY_NAME IS NULL
AND SUCCESS
= 'BY ACCESS'
AND FAILURE
= 'BY ACCESS'
AND
AUDIT_OPTION='PROFILE';
REM
================================================
REM 6.1.5
SELECT
AUDIT_OPTION,SUCCESS,FAILURE
FROM
DBA_STMT_AUDIT_OPTS
WHERE
USER_NAME IS NULL
AND
PROXY_NAME IS NULL
AND SUCCESS
= 'BY ACCESS'
AND FAILURE
= 'BY ACCESS'
AND
AUDIT_OPTION='DATABASE LINK';
REM
================================================
REM 6.1.6
SELECT
AUDIT_OPTION,SUCCESS,FAILURE
FROM
DBA_STMT_AUDIT_OPTS
WHERE
USER_NAME IS NULL
AND
PROXY_NAME IS NULL
AND SUCCESS
= 'BY ACCESS'
AND FAILURE
= 'BY ACCESS'
AND
AUDIT_OPTION='PUBLIC DATABASE LINK';
REM
================================================
REM 6.1.7
SELECT
AUDIT_OPTION,SUCCESS,FAILURE
FROM
DBA_STMT_AUDIT_OPTS
WHERE
USER_NAME IS NULL
AND
PROXY_NAME IS NULL
AND SUCCESS
= 'BY ACCESS'
AND FAILURE
= 'BY ACCESS'
AND
AUDIT_OPTION='PUBLIC SYNONYM';
REM
================================================
REM 6.1.8
SELECT
AUDIT_OPTION,SUCCESS,FAILURE
FROM
DBA_STMT_AUDIT_OPTS
WHERE
USER_NAME IS NULL
AND
PROXY_NAME IS NULL
AND SUCCESS
= 'BY ACCESS'
AND FAILURE
= 'BY ACCESS'
AND
AUDIT_OPTION='SYNONYM';
REM
================================================
REM 6.1.9
SELECT
AUDIT_OPTION,SUCCESS,FAILURE
FROM
DBA_STMT_AUDIT_OPTS
WHERE
USER_NAME IS NULL
AND
PROXY_NAME IS NULL
AND SUCCESS
= 'BY ACCESS'
AND FAILURE
= 'BY ACCESS'
AND
AUDIT_OPTION='DIRECTORY';
REM
================================================
REM 6.1.10
SELECT
AUDIT_OPTION,SUCCESS,FAILURE
FROM
DBA_STMT_AUDIT_OPTS
WHERE
USER_NAME IS NULL
AND
PROXY_NAME IS NULL
AND SUCCESS
= 'BY ACCESS'
AND FAILURE
= 'BY ACCESS'
AND
AUDIT_OPTION='SELECT ANY DICTIONARY';
REM
================================================
REM 6.1.11
SELECT
AUDIT_OPTION,SUCCESS,FAILURE
FROM
DBA_STMT_AUDIT_OPTS
WHERE
USER_NAME IS NULL
AND PROXY_NAME
IS NULL
AND SUCCESS
= 'BY ACCESS'
AND FAILURE
= 'BY ACCESS'
REM
================================================
REM 6.1.12
SELECT
AUDIT_OPTION,SUCCESS,FAILURE
FROM
DBA_STMT_AUDIT_OPTS
WHERE
USER_NAME IS NULL
AND
PROXY_NAME IS NULL
AND SUCCESS
= 'BY ACCESS'
AND FAILURE
= 'BY ACCESS'
AND
AUDIT_OPTION='GRANT ANY PRIVILEGE';
REM
================================================
REM 6.1.13
SELECT
AUDIT_OPTION,SUCCESS,FAILURE
FROM
DBA_STMT_AUDIT_OPTS
WHERE
USER_NAME IS NULL
AND
PROXY_NAME IS NULL
AND SUCCESS
= 'BY ACCESS'
AND FAILURE
= 'BY ACCESS'
AND
AUDIT_OPTION='DROP ANY PROCEDURE';
REM
================================================
REM 6.1.14
SELECT *
FROM
DBA_OBJ_AUDIT_OPTS
WHERE
OBJECT_NAME='AUD$'
AND
ALT='A/A'
AND
AUD='A/A'
AND
COM='A/A'
AND
DEL='A/A'
AND
GRA='A/A'
AND
IND='A/A'
AND
INS='A/A'
AND
LOC='A/A'
AND
REN='A/A'
AND
SEL='A/A'
AND
UPD='A/A'
AND
FBK='A/A';
REM
================================================
REM 6.1.15
SELECT
AUDIT_OPTION,SUCCESS,FAILURE
FROM
DBA_STMT_AUDIT_OPTS
WHERE
USER_NAME IS NULL
AND
PROXY_NAME IS NULL
AND SUCCESS
= 'BY ACCESS'
AND FAILURE
= 'BY ACCESS'
AND
AUDIT_OPTION='PROCEDURE';
REM
================================================
REM 6.1.16
SELECT
AUDIT_OPTION,SUCCESS,FAILURE
FROM
DBA_STMT_AUDIT_OPTS
WHERE
USER_NAME IS NULL
AND
PROXY_NAME IS NULL
AND SUCCESS
= 'BY ACCESS'
AND FAILURE
= 'BY ACCESS'
AND
AUDIT_OPTION='ALTER SYSTEM';
REM
================================================
REM 6.1.17
SELECT
AUDIT_OPTION,SUCCESS,FAILURE
FROM
DBA_STMT_AUDIT_OPTS
WHERE
USER_NAME IS NULL
AND
PROXY_NAME IS NULL
AND SUCCESS
= 'BY ACCESS'
AND FAILURE
= 'BY ACCESS'
AND
AUDIT_OPTION='TRIGGER';
REM
================================================
REM 6.1.18
SELECT
AUDIT_OPTION,SUCCESS,FAILURE
FROM
DBA_STMT_AUDIT_OPTS
WHERE
USER_NAME IS NULL
AND
PROXY_NAME IS NULL
AND SUCCESS
= 'BY ACCESS'
AND FAILURE
= 'BY ACCESS'
AND
AUDIT_OPTION='CREATE SESSION';
REM
================================================
REM This is
not a CIS control
REM Purpose
is to capture additional audit if enabled
SELECT
AUDIT_OPTION,SUCCESS,FAILURE
FROM
DBA_STMT_AUDIT_OPTS ;
SELECT *
FROM DBA_OBJ_AUDIT_OPTS ;
SELECT *
FROM DBA_PRIV_AUDIT_OPTS ;
REM Unified
Audit Options
REM
================================================
REM This is
not a CIS control
REM Purpose
is to capture ALL unified auditing options through this SQL
REM
SELECT
POL_EN.POLICY_NAME, USER_NAME, SUCCESS, FAILURE, AUDIT_OPTION_TYPE,
AUDIT_OPTION, OBJECT_SCHEMA, OBJECT_NAME,OBJECT_TYPE
FROM
AUDIT_UNIFIED_ENABLED_POLICIES POL_EN, AUDIT_UNIFIED_POLICIES POL
WHERE
POL.POLICY_NAME = POL_EN.POLICY_NAME
ORDER BY
POL_EN.POLICY_NAME,USER_NAME,AUDIT_OPTION_TYPE,AUDIT_OPTION ;
REM
================================================
REM 6.2.1
SELECT
AUD.POLICY_NAME, AUD.AUDIT_OPTION, AUD.AUDIT_OPTION_TYPE
FROM
AUDIT_UNIFIED_POLICIES AUD, AUDIT_UNIFIED_ENABLED_POLICIES ENABLED
WHERE
AUD.POLICY_NAME = ENABLED.POLICY_NAME
AND
AUD.AUDIT_OPTION = 'CREATE USER'
AND
AUD.AUDIT_OPTION_TYPE = 'STANDARD ACTION'
AND
ENABLED.SUCCESS = 'YES'
AND
ENABLED.FAILURE = 'YES'
AND
ENABLED.ENABLED_OPT = 'BY'
AND
ENABLED.USER_NAME = 'ALL USERS';
REM
================================================
REM 6.2.2
SELECT
AUD.POLICY_NAME, AUD.AUDIT_OPTION, AUD.AUDIT_OPTION_TYPE
FROM
AUDIT_UNIFIED_POLICIES AUD, AUDIT_UNIFIED_ENABLED_POLICIES ENABLED
WHERE
AUD.POLICY_NAME = ENABLED.POLICY_NAME
AND
AUD.AUDIT_OPTION = 'ALTER USER'
AND
AUD.AUDIT_OPTION_TYPE = 'STANDARD ACTION'
AND
ENABLED.SUCCESS = 'YES'
AND
ENABLED.FAILURE = 'YES'
AND
ENABLED.ENABLED_OPT = 'BY'
AND
ENABLED.USER_NAME = 'ALL USERS';
REM
================================================
REM 6.2.3
SELECT
AUD.POLICY_NAME, AUD.AUDIT_OPTION, AUD.AUDIT_OPTION_TYPE
FROM
AUDIT_UNIFIED_POLICIES AUD, AUDIT_UNIFIED_ENABLED_POLICIES ENABLED
WHERE
AUD.POLICY_NAME = ENABLED.POLICY_NAME
AND
AUD.AUDIT_OPTION = 'DROP USER'
AND
AUD.AUDIT_OPTION_TYPE = 'STANDARD ACTION'
AND
ENABLED.SUCCESS = 'YES'
AND
ENABLED.FAILURE = 'YES'
AND
ENABLED.ENABLED_OPT = 'BY'
AND
ENABLED.USER_NAME = 'ALL USERS';
REM
================================================
REM 6.2.4
SELECT
AUD.POLICY_NAME, AUD.AUDIT_OPTION, AUD.AUDIT_OPTION_TYPE
FROM
AUDIT_UNIFIED_POLICIES AUD, AUDIT_UNIFIED_ENABLED_POLICIES ENABLED
WHERE
AUD.POLICY_NAME = ENABLED.POLICY_NAME
AND AUD.AUDIT_OPTION
= 'CREATE ROLE'
AND
AUD.AUDIT_OPTION_TYPE = 'STANDARD ACTION'
AND
ENABLED.SUCCESS = 'YES'
AND
ENABLED.FAILURE = 'YES'
AND
ENABLED.ENABLED_OPT = 'BY'
AND
ENABLED.USER_NAME = 'ALL USERS';
REM
================================================
REM 6.2.5
SELECT
AUD.POLICY_NAME, AUD.AUDIT_OPTION, AUD.AUDIT_OPTION_TYPE
FROM
AUDIT_UNIFIED_POLICIES AUD, AUDIT_UNIFIED_ENABLED_POLICIES ENABLED
WHERE
AUD.POLICY_NAME = ENABLED.POLICY_NAME
AND
AUD.AUDIT_OPTION = 'ALTER ROLE'
AND
AUD.AUDIT_OPTION_TYPE = 'STANDARD ACTION'
AND
ENABLED.SUCCESS = 'YES'
AND
ENABLED.FAILURE = 'YES'
AND
ENABLED.ENABLED_OPT = 'BY'
AND
ENABLED.USER_NAME = 'ALL USERS';
REM
================================================
REM 6.2.6
SELECT
AUD.POLICY_NAME, AUD.AUDIT_OPTION, AUD.AUDIT_OPTION_TYPE
FROM
AUDIT_UNIFIED_POLICIES AUD, AUDIT_UNIFIED_ENABLED_POLICIES ENABLED
WHERE
AUD.POLICY_NAME = ENABLED.POLICY_NAME
AND
AUD.AUDIT_OPTION = 'DROP ROLE'
AND
AUD.AUDIT_OPTION_TYPE = 'STANDARD ACTION'
AND
ENABLED.SUCCESS = 'YES'
AND
ENABLED.FAILURE = 'YES'
AND
ENABLED.ENABLED_OPT = 'BY'
AND
ENABLED.USER_NAME = 'ALL USERS';
REM
================================================
REM 6.2.7
SELECT
AUD.POLICY_NAME, AUD.AUDIT_OPTION, AUD.AUDIT_OPTION_TYPE
FROM
AUDIT_UNIFIED_POLICIES AUD, AUDIT_UNIFIED_ENABLED_POLICIES ENABLED
WHERE
AUD.POLICY_NAME = ENABLED.POLICY_NAME
AND
AUD.AUDIT_OPTION = 'GRANT'
AND
AUD.AUDIT_OPTION_TYPE = 'STANDARD ACTION'
AND
ENABLED.SUCCESS = 'YES'
AND
ENABLED.FAILURE = 'YES'
AND
ENABLED.ENABLED_OPT = 'BY'
AND
ENABLED.USER_NAME = 'ALL USERS';
REM
================================================
REM 6.2.8
SELECT
AUD.POLICY_NAME, AUD.AUDIT_OPTION, AUD.AUDIT_OPTION_TYPE
FROM
AUDIT_UNIFIED_POLICIES AUD, AUDIT_UNIFIED_ENABLED_POLICIES ENABLED
WHERE
AUD.POLICY_NAME = ENABLED.POLICY_NAME
AND
AUD.AUDIT_OPTION = 'REVOKE'
AND
AUD.AUDIT_OPTION_TYPE = 'STANDARD ACTION'
AND
ENABLED.SUCCESS = 'YES'
AND
ENABLED.FAILURE = 'YES'
AND ENABLED.ENABLED_OPT
= 'BY'
AND
ENABLED.USER_NAME = 'ALL USERS';
REM
================================================
REM 6.2.9
SELECT
AUD.POLICY_NAME, AUD.AUDIT_OPTION, AUD.AUDIT_OPTION_TYPE
FROM
AUDIT_UNIFIED_POLICIES AUD, AUDIT_UNIFIED_ENABLED_POLICIES ENABLED
WHERE
AUD.POLICY_NAME = ENABLED.POLICY_NAME
AND
AUD.AUDIT_OPTION = 'CREATE PROFILE'
AND
AUD.AUDIT_OPTION_TYPE = 'STANDARD ACTION'
AND
ENABLED.SUCCESS = 'YES'
AND
ENABLED.FAILURE = 'YES'
AND
ENABLED.ENABLED_OPT = 'BY'
AND
ENABLED.USER_NAME = 'ALL USERS';
REM
================================================
REM 6.2.10
SELECT
AUD.POLICY_NAME, AUD.AUDIT_OPTION, AUD.AUDIT_OPTION_TYPE
FROM
AUDIT_UNIFIED_POLICIES AUD, AUDIT_UNIFIED_ENABLED_POLICIES ENABLED
WHERE
AUD.POLICY_NAME = ENABLED.POLICY_NAME
AND
AUD.AUDIT_OPTION = 'ALTER PROFILE'
AND
AUD.AUDIT_OPTION_TYPE = 'STANDARD ACTION'
AND
ENABLED.SUCCESS = 'YES'
AND
ENABLED.FAILURE = 'YES'
AND
ENABLED.ENABLED_OPT = 'BY'
AND
ENABLED.USER_NAME = 'ALL USERS';
REM
================================================
REM 6.2.11
SELECT
AUD.POLICY_NAME, AUD.AUDIT_OPTION, AUD.AUDIT_OPTION_TYPE
FROM
AUDIT_UNIFIED_POLICIES AUD, AUDIT_UNIFIED_ENABLED_POLICIES ENABLED
WHERE
AUD.POLICY_NAME = ENABLED.POLICY_NAME
AND
AUD.AUDIT_OPTION = 'DROP PROFILE'
AND
AUD.AUDIT_OPTION_TYPE = 'STANDARD ACTION'
AND
ENABLED.SUCCESS = 'YES'
AND
ENABLED.FAILURE = 'YES'
AND
ENABLED.ENABLED_OPT = 'BY'
AND
ENABLED.USER_NAME = 'ALL USERS';
REM
================================================
REM 6.2.12
SELECT
AUD.POLICY_NAME, AUD.AUDIT_OPTION, AUD.AUDIT_OPTION_TYPE
FROM
AUDIT_UNIFIED_POLICIES AUD, AUDIT_UNIFIED_ENABLED_POLICIES ENABLED
WHERE
AUD.POLICY_NAME = ENABLED.POLICY_NAME
AND AUD.AUDIT_OPTION
= 'CREATE DATABASE LINK'
AND
AUD.AUDIT_OPTION_TYPE = 'STANDARD ACTION'
AND
ENABLED.SUCCESS = 'YES'
AND
ENABLED.FAILURE = 'YES'
AND
ENABLED.ENABLED_OPT = 'BY'
AND
ENABLED.USER_NAME = 'ALL USERS';
REM
================================================
REM 6.2.13
SELECT
AUD.POLICY_NAME, AUD.AUDIT_OPTION, AUD.AUDIT_OPTION_TYPE
FROM
AUDIT_UNIFIED_POLICIES AUD, AUDIT_UNIFIED_ENABLED_POLICIES ENABLED
WHERE
AUD.POLICY_NAME = ENABLED.POLICY_NAME
AND
AUD.AUDIT_OPTION = 'ALTER DATABASE LINK'
AND
AUD.AUDIT_OPTION_TYPE = 'STANDARD ACTION'
AND
ENABLED.SUCCESS = 'YES'
AND
ENABLED.FAILURE = 'YES'
AND
ENABLED.ENABLED_OPT = 'BY'
AND
ENABLED.USER_NAME = 'ALL USERS';
REM
================================================
REM 6.2.14
SELECT
AUD.POLICY_NAME, AUD.AUDIT_OPTION, AUD.AUDIT_OPTION_TYPE
FROM
AUDIT_UNIFIED_POLICIES AUD, AUDIT_UNIFIED_ENABLED_POLICIES ENABLED
WHERE
AUD.POLICY_NAME = ENABLED.POLICY_NAME
AND AUD.AUDIT_OPTION
= 'DROP DATABASE LINK'
AND
AUD.AUDIT_OPTION_TYPE = 'STANDARD ACTION'
AND
ENABLED.SUCCESS = 'YES'
AND
ENABLED.FAILURE = 'YES'
AND
ENABLED.ENABLED_OPT = 'BY'
AND
ENABLED.USER_NAME = 'ALL USERS';
REM
================================================
REM 6.2.15
SELECT
AUD.POLICY_NAME, AUD.AUDIT_OPTION, AUD.AUDIT_OPTION_TYPE
FROM
AUDIT_UNIFIED_POLICIES AUD, AUDIT_UNIFIED_ENABLED_POLICIES ENABLED
WHERE
AUD.POLICY_NAME = ENABLED.POLICY_NAME
AND
AUD.AUDIT_OPTION = 'CREATE SYNONYM'
AND
AUD.AUDIT_OPTION_TYPE = 'STANDARD ACTION'
AND
ENABLED.SUCCESS = 'YES'
AND
ENABLED.FAILURE = 'YES'
AND
ENABLED.ENABLED_OPT = 'BY'
AND
ENABLED.USER_NAME = 'ALL USERS';
REM
================================================
REM 6.2.16
SELECT
AUD.POLICY_NAME, AUD.AUDIT_OPTION, AUD.AUDIT_OPTION_TYPE
FROM
AUDIT_UNIFIED_POLICIES AUD, AUDIT_UNIFIED_ENABLED_POLICIES ENABLED
WHERE
AUD.POLICY_NAME = ENABLED.POLICY_NAME
AND
AUD.AUDIT_OPTION = 'ALTER SYNONYM'
AND
AUD.AUDIT_OPTION_TYPE = 'STANDARD ACTION'
AND
ENABLED.SUCCESS = 'YES'
AND
ENABLED.FAILURE = 'YES'
AND
ENABLED.ENABLED_OPT = 'BY'
AND
ENABLED.USER_NAME = 'ALL USERS';
REM
================================================
REM 6.2.17
SELECT
AUD.POLICY_NAME, AUD.AUDIT_OPTION, AUD.AUDIT_OPTION_TYPE
FROM
AUDIT_UNIFIED_POLICIES AUD, AUDIT_UNIFIED_ENABLED_POLICIES ENABLED
WHERE
AUD.POLICY_NAME = ENABLED.POLICY_NAME
AND
AUD.AUDIT_OPTION = 'DROP SYNONYM'
AND
AUD.AUDIT_OPTION_TYPE = 'STANDARD ACTION'
AND
ENABLED.SUCCESS = 'YES'
AND
ENABLED.FAILURE = 'YES'
AND
ENABLED.ENABLED_OPT = 'BY'
AND
ENABLED.USER_NAME = 'ALL USERS';
REM
================================================
REM 6.2.18
SELECT
AUD.POLICY_NAME, AUD.AUDIT_OPTION, AUD.AUDIT_OPTION_TYPE
FROM
AUDIT_UNIFIED_POLICIES AUD, AUDIT_UNIFIED_ENABLED_POLICIES ENABLED
WHERE
AUD.POLICY_NAME = ENABLED.POLICY_NAME
AND
AUD.AUDIT_OPTION = 'SELECT ANY DICTIONARY'
AND
AUD.AUDIT_OPTION_TYPE = 'SYSTEM PRIVILEGE'
AND
ENABLED.SUCCESS = 'YES'
AND
ENABLED.FAILURE = 'YES'
AND
ENABLED.ENABLED_OPT = 'BY'
AND
ENABLED.USER_NAME = 'ALL USERS';
REM
================================================
REM 6.2.19
SELECT
AUD.POLICY_NAME, AUD.AUDIT_OPTION, AUD.AUDIT_OPTION_TYPE
FROM
AUDIT_UNIFIED_POLICIES AUD, AUDIT_UNIFIED_ENABLED_POLICIES ENABLED
WHERE
AUD.POLICY_NAME = ENABLED.POLICY_NAME
AND
AUD.AUDIT_OPTION = 'ALL'
AND
AUD.AUDIT_OPTION_TYPE = 'OBJECT ACTION'
AND
AUD.OBJECT_SCHEMA = 'SYS'
AND
AUD.OBJECT_NAME = 'UNIFIED_AUDIT_TRAIL'
AND
ENABLED.SUCCESS = 'YES'
AND
ENABLED.FAILURE = 'YES'
AND
ENABLED.ENABLED_OPT = 'BY'
AND
ENABLED.USER_NAME = 'ALL USERS';
REM
================================================
REM 6.2.20
SELECT *
FROM
AUDIT_UNIFIED_ENABLED_POLICIES ENABLED
WHERE ENABLED.SUCCESS = 'YES'
AND ENABLED.FAILURE = 'YES'
AND ENABLED.ENABLED_OPT = 'BY'
AND ENABLED.USER_NAME = 'ALL USERS'
AND EXISTS
(SELECT 'x'
FROM AUDIT_UNIFIED_POLICIES
AUD
WHERE AUD.POLICY_NAME = ENABLED.POLICY_NAME
AND AUD.AUDIT_OPTION =
'CREATE PROCEDURE'
AND
AUD.AUDIT_OPTION_TYPE = 'STANDARD ACTION')
AND EXISTS
(SELECT 'x'
FROM AUDIT_UNIFIED_POLICIES AUD
WHERE AUD.POLICY_NAME = ENABLED.POLICY_NAME
AND AUD.AUDIT_OPTION =
'CREATE FUNCTION'
AND
AUD.AUDIT_OPTION_TYPE = 'STANDARD ACTION')
AND EXISTS
(SELECT 'x'
FROM AUDIT_UNIFIED_POLICIES
AUD
WHERE AUD.POLICY_NAME = ENABLED.POLICY_NAME
AND AUD.AUDIT_OPTION =
'CREATE PACKAGE'
AND
AUD.AUDIT_OPTION_TYPE = 'STANDARD ACTION')
AND EXISTS
(SELECT 'x'
FROM AUDIT_UNIFIED_POLICIES
AUD
WHERE AUD.POLICY_NAME = ENABLED.POLICY_NAME
AND AUD.AUDIT_OPTION =
'CREATE PACKAGE BODY'
AND AUD.AUDIT_OPTION_TYPE =
'STANDARD ACTION');
REM
================================================
REM 6.2.21
SELECT *
FROM
AUDIT_UNIFIED_ENABLED_POLICIES ENABLED
WHERE ENABLED.SUCCESS = 'YES'
AND ENABLED.FAILURE = 'YES'
AND ENABLED.ENABLED_OPT = 'BY'
AND ENABLED.USER_NAME = 'ALL USERS'
AND EXISTS
(SELECT 'x'
FROM AUDIT_UNIFIED_POLICIES
AUD
WHERE AUD.POLICY_NAME = ENABLED.POLICY_NAME
AND AUD.AUDIT_OPTION =
'ALTER PROCEDURE'
AND
AUD.AUDIT_OPTION_TYPE = 'STANDARD ACTION')
AND EXISTS
(SELECT 'x'
FROM AUDIT_UNIFIED_POLICIES
AUD
WHERE AUD.POLICY_NAME = ENABLED.POLICY_NAME
AND AUD.AUDIT_OPTION =
'ALTER FUNCTION'
AND
AUD.AUDIT_OPTION_TYPE = 'STANDARD ACTION')
AND EXISTS
(SELECT 'x'
FROM AUDIT_UNIFIED_POLICIES
AUD
WHERE AUD.POLICY_NAME = ENABLED.POLICY_NAME
AND AUD.AUDIT_OPTION =
'ALTER PACKAGE'
AND
AUD.AUDIT_OPTION_TYPE = 'STANDARD ACTION')
AND EXISTS
(SELECT 'x'
FROM AUDIT_UNIFIED_POLICIES
AUD
WHERE AUD.POLICY_NAME = ENABLED.POLICY_NAME
AND AUD.AUDIT_OPTION =
'ALTER PACKAGE BODY'
AND
AUD.AUDIT_OPTION_TYPE = 'STANDARD ACTION');
REM
================================================
REM 6.2.22
SELECT *
FROM
AUDIT_UNIFIED_ENABLED_POLICIES ENABLED
WHERE
ENABLED.SUCCESS = 'YES'
AND ENABLED.FAILURE = 'YES'
AND ENABLED.ENABLED_OPT = 'BY'
AND ENABLED.USER_NAME = 'ALL USERS'
AND EXISTS
(SELECT 'x'
FROM AUDIT_UNIFIED_POLICIES
AUD
WHERE AUD.POLICY_NAME =
ENABLED.POLICY_NAME
AND AUD.AUDIT_OPTION = 'DROP PROCEDURE'
AND
AUD.AUDIT_OPTION_TYPE = 'STANDARD ACTION')
AND EXISTS
(SELECT 'x'
FROM AUDIT_UNIFIED_POLICIES
AUD
WHERE AUD.POLICY_NAME = ENABLED.POLICY_NAME
AND AUD.AUDIT_OPTION =
'DROP FUNCTION'
AND
AUD.AUDIT_OPTION_TYPE = 'STANDARD ACTION')
AND EXISTS
(SELECT 'x'
FROM AUDIT_UNIFIED_POLICIES
AUD
WHERE
AUD.POLICY_NAME = ENABLED.POLICY_NAME
AND AUD.AUDIT_OPTION =
'DROP PACKAGE'
AND
AUD.AUDIT_OPTION_TYPE = 'STANDARD ACTION')
AND EXISTS
(SELECT 'x'
FROM AUDIT_UNIFIED_POLICIES
AUD
WHERE AUD.POLICY_NAME = ENABLED.POLICY_NAME
AND AUD.AUDIT_OPTION =
'DROP PACKAGE BODY'
AND
AUD.AUDIT_OPTION_TYPE = 'STANDARD ACTION');