Thursday, April 16, 2020

SQL Script to Assess Compliance with CIS Oracle Database Benchmark 12c v3.0

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');
                                    
                                    
                                    


                                    

Posted by at No comments:
Subscribe to: Posts (Atom)