Oracle12c-Multitenant-Checks.sql

/* 

Run this script   sqlplus system/password@ServerName:Port/ServiceName @Oracle12c-Multitenant-Checks.sql
Output  filename is : DBName_yymmdd_Security_Checks.html


 */

 
Prompt	
Prompt *****  THE START OF SECURITY CHECKS REPORT  ****** 	
Prompt *****  IT CAN TAKE TIME PLEASE WAIT.....    ****** 	
Prompt

set pagesize 10000
SET TERMOUT OFF
SET RECSEP WRAPPED

colum  expiry_date  format a15
column GRANTED_CRITIC_ROLE format A20
column ADMIN_OPTION format A15
column COMMON format A15
column SYSDBA format A15
column SYSOPER format A15
column SYSASM format A15
column SYSBACKUP format A15
column SYSDG format A15
column SYSKM format A15
column DB_UNIQUE_NAME format A15
column AUDIT_TRAIL format A15
column POLICY_COLUMN_OPTIONS format A25
column ENABLED format A15
column OBJECT_SCHEMA format A15
column OBJECT_NAME format A32
column POLICY_OWNER format A15
column POLICY_NAME format A25
column POLICY_TEXT format A32
column POLICY_COLUMN format A15
column PF_SCHEMA format A15
column PF_PACKAGE format A15
column PF_FUNCTION format A15
column PRIVILEGE format A15
column IS_GRANT format A15
column INVERT format A15
column CONTAINER_NAME format A32
column INVERTED_PRINCIPAL format A25
column PRINCIPAL format A15
column PRINCIPAL_TYPE format A25



SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON -
HEAD '<TITLE>  </TITLE> -
<STYLE type="text/css"> -
th { -
font:bold 10pt Ariel,Helvetica,sans-serif; -
color:#336699; -
background:#cccc99; -
padding:3px 3px 3px 3px;} -
td { -
font: 9pt Arial,Helvetica,sans-serif; -
color:black; -
background:white; -
padding:2px 2px 2px 2px;} -
</STYLE>' -
BODY 'TEXT="#00000"' -
TABLE 'WIDTH="60%" BORDER="1"'


column filename new_val filename
select name||'_'||SYS_CONTEXT('USERENV','CON_NAME')||'_'||to_char(sysdate, 'yyyymmdd' )||'_Security_Checks.html' filename from dual , v$database;
spool &filename

set define off

SET MARKUP HTML   OFF
Prompt  <h2> Oracle 12c Multitenant Database Security Check SQLs </h2>
Prompt  <p>Open Source code from  https://github.com/yusufanilakduygu/Oracle-DB-Security-Checks </p>
Prompt  <p>This Report was developed by Y. Anil Akduygu ver 1.2 2018 </p>

Prompt  <h3> Server and Database Information  </h3>
SET MARKUP HTML   ON

/* ALTER SESSION SET container = cdb$root; */

SELECT to_char(SYSDATE,'dd-mm-yyyy hh24:mi') REPORT_DATE,
       SUBSTR (host_name, 1, 20) HOST_NAME,
       name,
       database_role,
       SUBSTR (open_mode, 1, 10) OPEN_MODE,
       SUBSTR (db_unique_name, 1, 10) DB_UNIQUE_NAME,
	   CDB,
	   SYS_CONTEXT('USERENV','CON_NAME') CONTAINER_NAME   
  FROM v$database, v$instance;

SET MARKUP HTML   OFF


prompt </b>
prompt <h3>   Database version  </h3>
SET MARKUP HTML   ON

SELECT BANNER "DB VERSION INFORMATION" from v$version;

SET MARKUP HTML   OFF


prompt <h3>   Check - 020 Containers List  </h3>
SET MARKUP HTML   ON

SELECT CON_ID, NAME, OPEN_MODE FROM v$containers;

SET MARKUP HTML OFF

prompt </b>
prompt <h3>   Check - 010 User List  </h3>
SET MARKUP HTML   ON
 
colum  expiry_date  format a15

SELECT 
       A.USERNAME,
       A.ACCOUNT_STATUS,
       A.CREATED,
       A.ORACLE_MAINTAINED,
       A.COMMON,
       B.NAME CONTAINER_NAME
  FROM 	
	   CDB_USERS A,
	   V$CONTAINERS B
  ORDER BY A.ACCOUNT_STATUS,B.CON_ID DESC;

 
SET MARKUP HTML   OFF



prompt <h3>   Check - 020 List Permanent and Temporary Tablespaces  </h3>
SET MARKUP HTML   ON

SELECT
	PROPERTY_NAME,
	PROPERTY_VALUE
FROM
	DATABASE_PROPERTIES
WHERE
	PROPERTY_NAME IN(
		'DEFAULT_PERMANENT_TABLESPACE',
		'DEFAULT_TEMP_TABLESPACE'
	);

SET MARKUP HTML OFF



prompt <h3>   Check - 030 Users which use SYSTEM and SYSAUX as  Default Tablespaces  </h3>
SET MARKUP HTML   ON

SELECT
	A.USERNAME,
	A.DEFAULT_TABLESPACE,
	A.TEMPORARY_TABLESPACE,
	B.NAME CONTAINER_NAME
FROM
	CDB_USERS A,
	V$CONTAINERS B
WHERE
	(
		A.DEFAULT_TABLESPACE IN(
			'SYSTEM',
			'SYSAUX'
		)
		OR A.TEMPORARY_TABLESPACE IN(
			'SYSTEM',
			'SYSAUX'
		)
	)
	AND A.ORACLE_MAINTAINED = 'N'
	AND A.CON_ID = B.CON_ID;

SET MARKUP HTML OFF
prompt <h3>   Check - 035 List Users Last Login Date  </h3>
SET MARKUP HTML   ON
	
SELECT
	A.USERNAME,
	A.ACCOUNT_STATUS,
	to_char(A.LAST_LOGIN,'DD-MM-YYYY HH24:MI') LAST_LOGIN,
	B.NAME CONTAINER_NAME
FROM
	CDB_USERS A,
	V$CONTAINERS B
WHERE
	A.ACCOUNT_STATUS = 'OPEN'
	AND A.CON_ID = B.CON_ID
ORDER BY
	B.NAME,
	A.USERNAME;
	

SET MARKUP HTML OFF
prompt <h3>   Check - 040 List Sample Application Users  </h3>
SET MARKUP HTML   ON

SELECT
	DISTINCT USERNAME,CON_ID
FROM
	CDB_USERS
WHERE
	USERNAME IN(
		'BI',
		'HR',
		'OE',
		'PM',
		'IX',
		'SH',
		'SCOTT'
	);

SET MARKUP HTML OFF
prompt <h3>   Check - 050 List  Database Profile Names  </h3>
SET MARKUP HTML   ON
	
SELECT
	DISTINCT PROFILE
FROM
	CDB_PROFILES
ORDER BY
	PROFILE;
	
	
SET MARKUP HTML OFF
prompt <h3>   Check - 060 List  Database Profiles Details  </h3>
SET MARKUP HTML   ON

SELECT
	A.PROFILE,A.RESOURCE_NAME,A.RESOURCE_TYPE,A.LIMIT ,A.COMMON,B.NAME CONTAINER_NAME
FROM
	CDB_PROFILES A ,
	V$CONTAINERS B
ORDER BY
	A.PROFILE   ;


	
SET MARKUP HTML OFF
prompt <h3>   Check - 080 List User and Profile Usage  </h3>
SET MARKUP HTML   ON	
	
SELECT
	A.USERNAME,
	A.PROFILE,
	B.NAME CONTAINER_NAME
FROM
	CDB_USERS A,
	V$CONTAINERS B
WHERE
	A.CON_ID = B.CON_ID
ORDER BY
	B.NAME,
	A.PROFILE;


SET MARKUP HTML OFF
prompt <h3>   Check - 080 List resource_limit parameter  </h3>
SET MARKUP HTML   ON

SELECT
	NAME,
	VALUE
FROM
	V$PARAMETER
WHERE
	NAME = 'resource_limit';

SET MARKUP HTML OFF
prompt <h3>   Check - 090 List Resource Usage Profile Parameters  </h3>
SET MARKUP HTML   ON

SELECT
	A.PROFILE,
	A.RESOURCE_NAME,
	A.LIMIT,
	B.NAME CONTAINER_NAME
FROM
	CDB_PROFILES A,
	V$CONTAINERS B
WHERE
	A.RESOURCE_NAME IN(
		'SESSIONS_PER_USER',
		'CPU_PER_SESSION',
		'CPU_PER_CALL',
		'CONNECT_TIME',
		'IDLE_TIME',
		'LOGICAL_READS_PER_SESSION',
		'PRIVATE_SGA',
		'COMPOSITE_LIMIT'
	)
	AND A.CON_ID = B.CON_ID
ORDER BY
	B.NAME,
	A.PROFILE,
	A.RESOURCE_NAME;


SET MARKUP HTML OFF
prompt <h3>   Check - 100 List resource_manager_plan parameters  </h3>
SET MARKUP HTML   ON

SELECT
	NAME,
	VALUE
FROM
	V$PARAMETER
WHERE
	NAME LIKE 'resource_manager_plan';

SET MARKUP HTML OFF
prompt <h3>   Check - 110 List Resource Plans  </h3>
SET MARKUP HTML   ON

SELECT
	NAME,
	IS_TOP_PLAN
FROM
	V$RSRC_PLAN;


SET MARKUP HTML OFF
prompt <h3>   Check - 120 List Password Parameters  </h3>
SET MARKUP HTML   ON

SELECT
	A.PROFILE,
	A.RESOURCE_NAME,
	A.LIMIT,
	B.NAME CONTAINER_NAME
FROM
	CDB_PROFILES A,
	V$CONTAINERS B
WHERE
	A.RESOURCE_NAME IN(
		'FAILED_LOGIN_ATTEMPTS',
		'PASSWORD_LIFE_TIME',
		'PASSWORD_LOCK_TIME',
		'PASSWORD_GRACE_TIME',
		'PASSWORD_VERIFY_FUNCTION',
		'PASSWORD_REUSE_TIME',
		'PASSWORD_REUSE_MAX'
	)
	AND A.CON_ID = B.CON_ID
ORDER BY
	B.NAME,
	A.PROFILE,
	A.RESOURCE_NAME;

	

SET MARKUP HTML OFF
prompt <h3>   Check - 130 List Profiles with NULL Password Verify Function  </h3>
SET MARKUP HTML   ON

SELECT
	A.PROFILE,
	A.RESOURCE_NAME,
	A.LIMIT,
	B.NAME CONTAINER_NAME
FROM
	CDB_PROFILES A,
	V$CONTAINERS B
WHERE
	A.RESOURCE_NAME IN('PASSWORD_VERIFY_FUNCTION')
	AND(
		A.LIMIT = 'NULL'
		OR A.LIMIT IS NULL
		OR A.LIMIT = 'DEFAULT'
	)
	AND A.CON_ID = B.CON_ID
ORDER BY
	B.NAME,
	A.PROFILE,
	A.RESOURCE_NAME;



SET MARKUP HTML OFF
prompt <h3>   Check - 140 List  Default User with Default Passwords </h3>
SET MARKUP HTML ON

SELECT
	A.USERNAME,
	B.NAME CONTAINER_NAME
FROM
	CDB_USERS_WITH_DEFPWD A,
	V$CONTAINERS B
WHERE
	A.CON_ID = B.CON_ID
ORDER BY
	B.NAME,
	A.USERNAME;



SET MARKUP HTML OFF
prompt <h3>   Check - 150 List Default Users with default passwords and not in EXPIRED and LOCKED status </h3>
SET MARKUP HTML ON

SELECT
	A.USERNAME,
	B.ACCOUNT_STATUS,
	C.NAME CONTAINER_NAME
FROM
	SYS.CDB_USERS_WITH_DEFPWD A,
	CDB_USERS B,
	V$CONTAINERS C
WHERE
	A.USERNAME = B.USERNAME
	AND B.ACCOUNT_STATUS <> 'EXPIRED & LOCKED'
	AND A.CON_ID = C.CON_ID
	AND B.CON_ID = C.CON_ID
ORDER BY
	C.NAME,
	A.USERNAME;


SET MARKUP HTML OFF
prompt <h3>   Check - 160 List Users with DBA role  </h3>
SET MARKUP HTML ON

SELECT
	DISTINCT A.GRANTEE,
	A.GRANTED_ROLE,
	B.COMMON,
	C.NAME CONTAINER_NAME,
	'DBA' GRANTED_CRITIC_ROLE
FROM
	(
		SELECT
			DISTINCT LEVEL LEVEL_DEEP,
			GRANTEE,
			GRANTED_ROLE,
			CON_ID
		FROM
			CDB_ROLE_PRIVS START WITH GRANTED_ROLE = 'DBA' 
                                 CONNECT BY PRIOR GRANTEE = GRANTED_ROLE
			AND PRIOR CON_ID = CON_ID
	) A,
	CDB_USERS B,
	V$CONTAINERS C
WHERE
	A.GRANTEE = B.USERNAME
	AND B.USERNAME NOT IN(
		'SYSTEM',
		'SYS'
	)
	AND B.ACCOUNT_STATUS = 'OPEN'
	AND A.CON_ID = C.CON_ID
	AND B.CON_ID = C.CON_ID
ORDER BY
	NAME;



SET MARKUP HTML OFF
prompt <h3>   Check - 180 List Users with IMP_FULL_DATABASE role </h3>
SET MARKUP HTML ON


SELECT
	DISTINCT A.GRANTEE,
	A.GRANTED_ROLE,
	B.COMMON,
	C.NAME CONTAINER_NAME,
	'IMP_FULL_DATABASE' GRANTED_CRITIC_ROLE
FROM
	(
		SELECT
			DISTINCT LEVEL LEVEL_DEEP,
			GRANTEE,
			GRANTED_ROLE,
			CON_ID
		FROM
			CDB_ROLE_PRIVS 
START WITH GRANTED_ROLE = 'IMP_FULL_DATABASE' 
CONNECT BY PRIOR GRANTEE = GRANTED_ROLE
			AND PRIOR CON_ID = CON_ID
	) A,
	CDB_USERS B,
	V$CONTAINERS C
WHERE
	A.GRANTEE = B.USERNAME
	AND B.USERNAME NOT IN(
		'SYSTEM',
		'SYS'
	)
	AND B.ACCOUNT_STATUS = 'OPEN'
	AND A.CON_ID = C.CON_ID
	AND B.CON_ID = C.CON_ID;


SET MARKUP HTML OFF
prompt <h3>   Check - 190 List Users with DATAPUMP_IMP_FULL_DATABASE role </h3>
SET MARKUP HTML ON

	
SELECT
	DISTINCT A.GRANTEE,
	A.GRANTED_ROLE,
	B.COMMON,
	C.NAME CONTAINER_NAME,
	'DATAPUMP_IMP_FULL_DATABASE' GRANTED_CRITIC_ROLE
FROM
	(
		SELECT
			DISTINCT LEVEL LEVEL_DEEP,
			GRANTEE,
			GRANTED_ROLE,
			CON_ID
		FROM
			CDB_ROLE_PRIVS 
START WITH GRANTED_ROLE = 'DATAPUMP_IMP_FULL_DATABASE' 
CONNECT BY PRIOR GRANTEE = GRANTED_ROLE
			AND PRIOR CON_ID = CON_ID
	) A,
	CDB_USERS B,
	V$CONTAINERS C
WHERE
	A.GRANTEE = B.USERNAME
	AND B.USERNAME NOT IN(
		'SYSTEM',
		'SYS'
	)
	AND B.ACCOUNT_STATUS = 'OPEN'
	AND A.CON_ID = C.CON_ID
	AND B.CON_ID = C.CON_ID ;



SET MARKUP HTML OFF
prompt <h3>   Check - 200 List Users with EXP_FULL_DATABASE role </h3>
SET MARKUP HTML ON


SELECT
	DISTINCT A.GRANTEE,
	A.GRANTED_ROLE,
	B.COMMON,
	C.NAME CONTAINER_NAME,
	'EXP_FULL_DATABASE' GRANTED_CRITIC_ROLE
FROM
	(
		SELECT
			DISTINCT LEVEL LEVEL_DEEP,
			GRANTEE,
			GRANTED_ROLE,
			CON_ID
		FROM
			CDB_ROLE_PRIVS 
START WITH GRANTED_ROLE = 'EXP_FULL_DATABASE' 
CONNECT BY PRIOR GRANTEE = GRANTED_ROLE
			AND PRIOR CON_ID = CON_ID
	) A,
	CDB_USERS B,
	V$CONTAINERS C
WHERE
	A.GRANTEE = B.USERNAME
	AND B.USERNAME NOT IN(
		'SYSTEM',
		'SYS'
	)
	AND B.ACCOUNT_STATUS = 'OPEN'
	AND A.CON_ID = C.CON_ID
	AND B.CON_ID = C.CON_ID ;


SET MARKUP HTML OFF
prompt <h3>   Check - 210 List Users with DATAPUMP_EXP_FULL_DATABASE role </h3>
SET MARKUP HTML ON
	

SELECT
	DISTINCT A.GRANTEE,
	A.GRANTED_ROLE,
	B.COMMON,
	C.NAME CONTAINER_NAME,
	'DATAPUMP_EXP_FULL_DATABASE' GRANTED_CRITIC_ROLE
FROM
	(
		SELECT
			DISTINCT LEVEL LEVEL_DEEP,
			GRANTEE,
			GRANTED_ROLE,
			CON_ID
		FROM
			CDB_ROLE_PRIVS 
START WITH GRANTED_ROLE = 'DATAPUMP_EXP_FULL_DATABASE' 
CONNECT BY PRIOR GRANTEE = GRANTED_ROLE
			AND PRIOR CON_ID = CON_ID
	) A,
	CDB_USERS B,
	V$CONTAINERS C
WHERE
	A.GRANTEE = B.USERNAME
	AND B.USERNAME NOT IN(
		'SYSTEM',
		'SYS'
	)
	AND B.ACCOUNT_STATUS = 'OPEN'
	AND A.CON_ID = C.CON_ID
	AND B.CON_ID = C.CON_ID;





SET MARKUP HTML OFF
prompt <h3>   Check - 220 List Users with SELECT_CATALOG_ROLE role </h3>
SET MARKUP HTML ON

SELECT
	DISTINCT A.GRANTEE,
	A.GRANTED_ROLE,
	B.COMMON,
	C.NAME  CONTAINER_NAME,
	'SELECT_CATALOG_ROLE' GRANTED_CRITIC_ROLE
FROM
	(
		SELECT
			DISTINCT LEVEL LEVEL_DEEP,
			GRANTEE,
			GRANTED_ROLE,
			CON_ID
		FROM
			CDB_ROLE_PRIVS 
			START WITH GRANTED_ROLE = 'SELECT_CATALOG_ROLE' 
			CONNECT BY PRIOR GRANTEE = GRANTED_ROLE
			AND PRIOR CON_ID = CON_ID
	) A,
	CDB_USERS B,
	V$CONTAINERS C
WHERE
	A.GRANTEE = B.USERNAME
	AND B.USERNAME NOT IN(
		'SYSTEM',
		'SYS'
	)
	AND B.ACCOUNT_STATUS = 'OPEN'
	AND A.CON_ID = C.CON_ID
	AND B.CON_ID = C.CON_ID
	ORDER BY NAME;



SET MARKUP HTML OFF
prompt <h3>   Check - 230 List Users with DELETE_CATALOG_ROLE role </h3>
SET MARKUP HTML ON

SELECT
	DISTINCT A.GRANTEE,
	A.GRANTED_ROLE,
	B.COMMON,
	C.NAME CONTAINER_NAME,
	'DELETE_CATALOG_ROLE' GRANTED_CRITIC_ROLE
FROM
	(
		SELECT
			DISTINCT LEVEL LEVEL_DEEP,
			GRANTEE,
			GRANTED_ROLE,
			CON_ID
		FROM
			CDB_ROLE_PRIVS 
			START WITH GRANTED_ROLE = 'DELETE_CATALOG_ROLE' 
			CONNECT BY PRIOR GRANTEE = GRANTED_ROLE
			AND PRIOR CON_ID = CON_ID
	) A,
	CDB_USERS B,
	V$CONTAINERS C
WHERE
	A.GRANTEE = B.USERNAME
	AND B.USERNAME NOT IN(
		'SYSTEM',
		'SYS'
	)
	AND B.ACCOUNT_STATUS = 'OPEN'
	AND A.CON_ID = C.CON_ID
	AND B.CON_ID = C.CON_ID
	ORDER BY NAME;




SET MARKUP HTML OFF
prompt <h3>   Check - 240 List Users with EXECUTE_CATALOG_ROLE role </h3>
SET MARKUP HTML ON

SELECT
	DISTINCT A.GRANTEE,
	A.GRANTED_ROLE,
	B.COMMON,
	C.NAME CONTAINER_NAME,
	'EXECUTE_CATALOG_ROLE' GRANTED_CRITIC_ROLE
FROM
	(
		SELECT
			DISTINCT LEVEL LEVEL_DEEP,
			GRANTEE,
			GRANTED_ROLE,
			CON_ID
		FROM
			CDB_ROLE_PRIVS 
			START WITH GRANTED_ROLE = 'EXECUTE_CATALOG_ROLE' 
			CONNECT BY PRIOR GRANTEE = GRANTED_ROLE
			AND PRIOR CON_ID = CON_ID
	) A,
	CDB_USERS B,
	V$CONTAINERS C
WHERE
	A.GRANTEE = B.USERNAME
	AND B.USERNAME NOT IN(
		'SYSTEM',
		'SYS'
	)
	AND B.ACCOUNT_STATUS = 'OPEN'
	AND A.CON_ID = C.CON_ID
	AND B.CON_ID = C.CON_ID
	ORDER BY NAME;


SET MARKUP HTML OFF
prompt <h3>   Check - 250 List remote_login_passwordfile parameter </h3>
SET MARKUP HTML ON
SELECT
	NAME,
	VALUE
FROM
	V$PARAMETER
WHERE
	NAME = 'remote_login_passwordfile';


SET MARKUP HTML OFF
prompt <h3>   Check - 260 List Users which use password file </h3>
SET MARKUP HTML ON

SELECT
	A.USERNAME,
	A.SYSDBA,
	A.SYSOPER,
	A.SYSASM,
	A.SYSBACKUP,
	A.SYSDG,
	A.SYSKM,
	B.NAME
FROM
	V$PWFILE_USERS A,
	V$CONTAINERS B
WHERE
	A.CON_ID = B.CON_ID;



SET MARKUP HTML OFF
prompt <h3>   Check - 270 List users and roles with critic privileges  </h3>
SET MARKUP HTML ON



SELECT
	A.GRANTEE,
	A.PRIVILEGE,
	B.COMMON,
	C.NAME CONTAINER_NAME,
	'GRANTED TO USER' TYPE
FROM
	CDB_SYS_PRIVS A,
	CDB_USERS B,
	V$CONTAINERS C
WHERE
	A.GRANTEE = B.USERNAME
	AND B.ORACLE_MAINTAINED <> 'Y'
	AND A.CON_ID = C.CON_ID
	AND B.CON_ID = C.CON_ID
	AND A.PRIVILEGE IN(
		'BECOME USER',
		'ALTER USER',
		'DROP USER',
		'CREATE ROLE',
		'ALTER ANY ROLE',
		'DROP ANY ROLE',
		'GRANT ANY ROLE',
		'CREATE PROFILE',
		'ALTER PROFILE',
		'DROP PROFILE',
		'CREATE ANY TABLE',
		'ALTER ANY TABLE',
		'DROP ANY TABLE',
		'INSERT ANY TABLE',
		'UPDATE ANY TABLE',
		'DELETE ANY TABLE',
		'SELECT ANY TABLE',
		'CREATE ANY PROCEDURE',
		'ALTER ANY PROCEDURE',
		'DROP ANY PROCEDURE',
		'EXECUTE ANY PROCEDURE',
		'CREATE ANY TRIGGER',
		'ALTER ANY TRIGGER',
		'DROP ANY TRIGGER',
		'CREATE TABLESPACE',
		'ALTER TABLESPACE',
		'DROP TABLESPACES',
		'ALTER DATABASE',
		'ALTER SYSTEM',
		'SELECT ANY DICTIONARY',
		'EXEMPT ACCESS POLICY',
		'CREATE ANY LIBRARY',
		'GRANT ANY OBJECT PRIVILEGE',
		'GRANT ANY PRIVILEGE',
		'AUDIT ANY'
	)
UNION ALL 
SELECT
	A.GRANTEE,
	A.PRIVILEGE,
	B.COMMON,
	C.NAME CONTAINER_NAME ,
	'GRANTED TO ROLE' TYPE
FROM
	CDB_SYS_PRIVS A,
	CDB_ROLES B,
	V$CONTAINERS C
WHERE
	A.GRANTEE = B.ROLE
	AND A.CON_ID = C.CON_ID
	AND B.CON_ID = C.CON_ID
	AND B.ORACLE_MAINTAINED <> 'Y'
	AND A.PRIVILEGE IN(
		'BECOME USER',
		'ALTER USER',
		'DROP USER',
		'CREATE ROLE',
		'ALTER ANY ROLE',
		'DROP ANY ROLE',
		'GRANT ANY ROLE',
		'CREATE PROFILE',
		'ALTER PROFILE',
		'DROP PROFILE',
		'CREATE ANY TABLE',
		'ALTER ANY TABLE',
		'DROP ANY TABLE',
		'INSERT ANY TABLE',
		'UPDATE ANY TABLE',
		'DELETE ANY TABLE',
		'SELECT ANY TABLE',
		'CREATE ANY PROCEDURE',
		'ALTER ANY PROCEDURE',
		'DROP ANY PROCEDURE',
		'EXECUTE ANY PROCEDURE',
		'CREATE ANY TRIGGER',
		'ALTER ANY TRIGGER',
		'DROP ANY TRIGGER',
		'CREATE TABLESPACE',
		'ALTER TABLESPACE',
		'DROP TABLESPACES',
		'ALTER DATABASE',
		'ALTER SYSTEM',
		'SELECT ANY DICTIONARY',
		'EXEMPT ACCESS POLICY',
		'CREATE ANY LIBRARY',
		'GRANT ANY OBJECT PRIVILEGE',
		'GRANT ANY PRIVILEGE',
		'AUDIT ANY'
	)


	
SET MARKUP HTML OFF
prompt <h3>   Check - 280 List DEFAULT Packages which are granted to PUBLIC with EXECUTE privileges   </h3>
SET MARKUP HTML ON
	
SELECT
	A.GRANTEE,
	A.TABLE_NAME,
	B.NAME CONTAINER_NAME
FROM
	CDB_TAB_PRIVS A,
	V$CONTAINERS B
WHERE
	A.GRANTEE = 'PUBLIC'
	AND A.PRIVILEGE = 'EXECUTE'
	AND A.TABLE_NAME IN(
		'DBMS_ADVISOR',
		'DBMS_CRYPTO',
		'DBMS_JAVA',
		'DBMS_JAVA_TEST',
		'DBMS_JOB',
		'DBMS_LDAP',
		'DBMS_LOB',
		'DBMS_OBFUSCATION_TOOLKIT',
		'DBMS_RANDOM',
		'DBMS_SCHEDULER',
		'DBMS_SQL',
		'DBMS_XMLGEN',
		'DBMS_XMLQUERY',
		'UTL_FILE',
		'UTL_INADDR',
		'UTL_TCP',
		'UTL_MAIL',
		'UTL_SMTP',
		'UTL_DBWS',
		'UTL_ORAMTS',
		'UTL_HTTP',
		'HTTPURITYPE',
		'DBMS_SYS_SQL',
		'DBMS_BACKUP_RESTORE',
		'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_FILE_TRANSFER'
	)
	AND A.CON_ID = B.CON_ID
ORDER BY
NAME;


SET MARKUP HTML OFF
prompt <h3>   Check - 290 List Grants on critic tables   </h3>
SET MARKUP HTML ON


SELECT
	A.GRANTEE,
	A.PRIVILEGE,
	B.NAME CONTAINER_NAME
FROM
	CDB_TAB_PRIVS A,
	V$CONTAINERS B
WHERE
	A.TABLE_NAME IN(
		'AUD$',
		'USER_HISTORY$',
		'LINK$',
		'USER$',
		'SCHEDULER$_CREDENTIAL'
	)
	AND A.GRANTEE NOT IN(
		'ANONYMOUS',
		'APEX_040200',
		'APEX_PUBLIC_USER',
		'APPQOSSYS',
		'AUDSYS',
		'CTXSYS',
		'DBSNMP',
		'DIP',
		'DVF',
		'DVSYS',
		'EXFSYS',
		'FLOWS_FILES',
		'GSMADMIN_INTERNAL',
		'GSMCATUSER',
		'GSMUSER',
		'LBACSYS',
		'MDDATA',
		'MDSYS',
		'ORACLE_OCM',
		'ORDDATA',
		'ORDPLUGINS',
		'ORDSYS',
		'OUTLN',
		'SI_INFORMTN_SCHEMA',
		'SPATIAL_CSW_ADMIN_USR',
		'SPATIAL_WFS_ADMIN_USR',
		'SYS',
		'SYSBACKUP',
		'SYSDG',
		'SYSKM',
		'SYSTEM',
		'WMSYS',
		'XDB',
		'XS$NULL',
		'OLAPSYS',
		'OJVMSYS',
		'DV_SECANALYST'
	)
	AND A.CON_ID = B.CON_ID
	ORDER BY B.NAME;

SET MARKUP HTML OFF
prompt <h3>   Check - 300 List Objects Priviliges Granted to Public   </h3>
SET MARKUP HTML ON
	
SELECT
	A.GRANTEE,
	A.PRIVILEGE,
	A.OWNER,
	A.TABLE_NAME,
	C.NAME CONTAINER_NAME
FROM
	CDB_TAB_PRIVS A,
	CDB_USERS B,
	V$CONTAINERS C
WHERE
	A.GRANTEE = 'PUBLIC'
	AND A.CON_ID = C.CON_ID
	AND B.CON_ID = C.CON_ID
	AND A.OWNER = B.USERNAME
	AND B.ORACLE_MAINTAINED <> 'Y'
ORDER BY C.NAME;

	
SET MARKUP HTML OFF
prompt <h3>   Check - 302 List  Roles Granted to Public   </h3>
SET MARKUP HTML ON

SELECT
	A.GRANTEE,
	A.GRANTED_ROLE,
	B.NAME CONTAINER_NAME
FROM
	CDB_ROLE_PRIVS A,
	V$CONTAINERS B
WHERE
	A.GRANTEE = 'PUBLIC'
	AND A.CON_ID = B.CON_ID
ORDER BY B.NAME;

SET MARKUP HTML OFF
prompt <h3>   Check - 304 List  System Privileges  Granted to Public   </h3>
SET MARKUP HTML ON	

SELECT
	A.GRANTEE,
	A.PRIVILEGE,
	B.NAME CONTAINER_NAME
FROM
	CDB_SYS_PRIVS A,
	V$CONTAINERS B
WHERE
	A.GRANTEE = 'PUBLIC'
	AND A.CON_ID = B.CON_ID
ORDER BY
	B.NAME;


SET MARKUP HTML OFF
prompt <h3>   Check - 310 List Granted System Priviliges to Users with Admin Option   </h3>
SET MARKUP HTML ON
SELECT
	A.GRANTEE,
	A.PRIVILEGE,
	A.ADMIN_OPTION,
	A.COMMON,
	C.NAME CONTAINER_NAME
FROM
	CDB_SYS_PRIVS A,
	CDB_USERS B,
	V$CONTAINERS C
WHERE
	A.GRANTEE = B.USERNAME
	AND B.ORACLE_MAINTAINED <> 'Y'
	AND A.ADMIN_OPTION = 'YES'
	AND A.CON_ID = C.CON_ID
	AND B.CON_ID = C.CON_ID;



SET MARKUP HTML OFF
prompt <h3>   Check - 320 List Granted  System Priviliges to Roles with Admin Option   </h3>
SET MARKUP HTML ON	

SELECT
	A.GRANTEE,
	A.PRIVILEGE,
	A.ADMIN_OPTION,
	B.COMMON,
	C.NAME CONTAINER_NAME
FROM
	CDB_SYS_PRIVS A,
	CDB_ROLES B,
	V$CONTAINERS C
WHERE
	A.ADMIN_OPTION = 'YES'
	AND A.GRANTEE = B.ROLE
	AND B.ORACLE_MAINTAINED <> 'Y'
	AND A.CON_ID = C.CON_ID
	AND B.CON_ID = C.CON_ID;

SET MARKUP HTML OFF
prompt <h3>   Check - 322 List Roles Granted to Users   with Admin Option   </h3>
SET MARKUP HTML ON	

SELECT
	A.GRANTEE,
	A.GRANTED_ROLE,
	A.COMMON,
	A.ADMIN_OPTION,
	C.NAME CONTAINER_NAME
FROM
	CDB_ROLE_PRIVS A,
	CDB_USERS B,
	V$CONTAINERS C
WHERE
	A.ADMIN_OPTION = 'YES'
	AND A.GRANTEE = B.USERNAME
	AND B.ORACLE_MAINTAINED <> 'Y'
	AND A.CON_ID = C.CON_ID
	AND B.CON_ID = C.CON_ID
ORDER BY
	C.NAME;

SET MARKUP HTML OFF
prompt <h3>   Check - 324 List Roles Granted to Roles   with Admin Option   </h3>
SET MARKUP HTML ON

SELECT
	A.GRANTEE,
	A.GRANTED_ROLE,
	A.COMMON,
	A.ADMIN_OPTION,
	C.NAME CONTAINER_NAME
FROM
	CDB_ROLE_PRIVS A,
	CDB_ROLES B,
	V$CONTAINERS C
WHERE
	A.ADMIN_OPTION = 'YES'
	AND A.GRANTEE = B.ROLE
	AND B.ORACLE_MAINTAINED <> 'Y'
	AND A.CON_ID = C.CON_ID
	AND B.CON_ID = C.CON_ID
ORDER BY
	C.NAME;

	
SET MARKUP HTML OFF
prompt <h3>   Check - 328 List Object Privileges with Grant Option   </h3>
SET MARKUP HTML ON	
	
SELECT
	A.GRANTEE,
	A.OWNER,
	A.TABLE_NAME,
	A.PRIVILEGE,
	A.GRANTABLE,
	D.NAME CONTAINER_NAME
FROM
	CDB_TAB_PRIVS A,
	CDB_USERS B,
	CDB_USERS C,
	V$CONTAINERS D
WHERE
	GRANTABLE = 'YES'
	AND A.OWNER = B.USERNAME
	AND B.ORACLE_MAINTAINED <> 'Y'
	AND A.GRANTEE = C.USERNAME
	AND C.ORACLE_MAINTAINED <> 'Y'
	AND A.CON_ID = D.CON_ID
	AND B.CON_ID = D.CON_ID
	AND C.CON_ID = D.CON_ID
ORDER BY
	D.NAME;



SET MARKUP HTML OFF
prompt <h3>   Check - 330 List Users with UNLIMITED TABLESPACE Privilege   </h3>
SET MARKUP HTML ON

SELECT
	A.GRANTEE,
	A.PRIVILEGE,
	A.ADMIN_OPTION,
	A.COMMON,
	C.NAME CONTAINER_NAME
FROM
	CDB_SYS_PRIVS A,
	CDB_USERS B,
	V$CONTAINERS C
WHERE
	A.PRIVILEGE = 'UNLIMITED TABLESPACE'
	AND A.GRANTEE = B.USERNAME
	AND B.ORACLE_MAINTAINED <> 'Y'
	AND A.CON_ID = C.CON_ID
	AND B.CON_ID = C.CON_ID
ORDER BY
	C.NAME;



	
SET MARKUP HTML OFF
prompt <h3>   Check - 340 List Users with Unlimited Tablespaces Quotas   </h3>
SET MARKUP HTML ON	

SELECT
	A.TABLESPACE_NAME,
	A.USERNAME,
	C.NAME CONTAINER_NAME
FROM
	CDB_TS_QUOTAS A,
	CDB_USERS B,
	V$CONTAINERS C
WHERE
	A.MAX_BYTES = - 1
	AND A.USERNAME = B.USERNAME
	AND B.ORACLE_MAINTAINED <> 'Y'
	AND A.CON_ID = C.CON_ID
	AND B.CON_ID = C.CON_ID
ORDER BY
	C.NAME;


SET MARKUP HTML OFF
prompt <h3>   Check - 350 List Database Components  </h3>
SET MARKUP HTML ON
	
SELECT
	A.COMP_ID,
	A.COMP_NAME,
	A.VERSION,
	A.STATUS,
	A.MODIFIED,
	B.NAME CONTAINER_NAME
FROM
	CDB_REGISTRY A,
	V$CONTAINERS B
WHERE
	A.CON_ID = B.CON_ID (+)
ORDER BY
	B.NAME,
	A.COMP_ID;

	
SET MARKUP HTML OFF
prompt <h3>   Check - 360 List O7_DICTIONARY_ACCESSIBILITY parameter  </h3>
SET MARKUP HTML ON
	
SELECT
	A.NAME,
	A.VALUE,
	A.ISPDB_MODIFIABLE,
	B.NAME CONTAINER_NAME,
	A.CON_ID
FROM
	V$SYSTEM_PARAMETER A,
	V$CONTAINERS B
WHERE
	UPPER( A.NAME ) = 'O7_DICTIONARY_ACCESSIBILITY'
	AND A.CON_ID = B.CON_ID(+);


SET MARKUP HTML OFF
prompt <h3>   Check - 370 List OS_ROLES parameter  </h3>
SET MARKUP HTML ON
	
SELECT
	A.NAME,
	A.VALUE,
	A.ISPDB_MODIFIABLE,
	B.NAME CONTAINER_NAME,
	A.CON_ID
FROM
	V$SYSTEM_PARAMETER A,
	V$CONTAINERS B
WHERE
	UPPER( A.NAME ) = 'OS_ROLES'
	AND A.CON_ID = B.CON_ID(+);


SET MARKUP HTML OFF
prompt <h3>   Check - 380 List REMOTE_LISTENER parameter  </h3>
SET MARKUP HTML ON
	
SELECT
	A.NAME,
	A.VALUE,
	A.ISPDB_MODIFIABLE,
	B.NAME CONTAINER_NAME,
	A.CON_ID
FROM
	V$SYSTEM_PARAMETER A,
	V$CONTAINERS B
WHERE
	UPPER( A.NAME ) = 'REMOTE_LISTENER'
	AND A.CON_ID = B.CON_ID(+);


SET MARKUP HTML OFF
prompt <h3>   Check - 390 List REMOTE_OS_AUTHENT parameter  </h3>
SET MARKUP HTML ON
	
SELECT
	A.NAME,
	A.VALUE,
	A.ISPDB_MODIFIABLE,
	B.NAME CONTAINER_NAME,
	A.CON_ID
FROM
	V$SYSTEM_PARAMETER A,
	V$CONTAINERS B
WHERE
	UPPER( A.NAME ) = 'REMOTE_OS_AUTHENT'
	AND A.CON_ID = B.CON_ID(+);

SET MARKUP HTML OFF
prompt <h3>   Check - 400 List REMOTE_OS_ROLES parameter  </h3>
SET MARKUP HTML ON
	
SELECT
	A.NAME,
	A.VALUE,
	A.ISPDB_MODIFIABLE,
	B.NAME CONTAINER_NAME,
	A.CON_ID
FROM
	V$SYSTEM_PARAMETER A,
	V$CONTAINERS B
WHERE
	UPPER( A.NAME ) = 'REMOTE_OS_ROLES'
	AND A.CON_ID = B.CON_ID(+);

	
SET MARKUP HTML OFF
prompt <h3>   Check - 410 List UTL_FILE_DIR parameter  </h3>
SET MARKUP HTML ON
	
SELECT
	A.NAME,
	A.VALUE,
	A.ISPDB_MODIFIABLE,
	B.NAME CONTAINER_NAME,
	A.CON_ID
FROM
	V$SYSTEM_PARAMETER A,
	V$CONTAINERS B
WHERE
	UPPER( A.NAME ) = 'UTL_FILE_DIR'
	AND A.CON_ID = B.CON_ID(+);


SET MARKUP HTML OFF
prompt <h3>   Check - 420 List SEC_CASE_SENSITIVE_LOGON parameter  </h3>
SET MARKUP HTML ON
	

SELECT
	A.NAME,
	A.VALUE,
	A.ISPDB_MODIFIABLE,
	B.NAME CONTAINER_NAME,
	A.CON_ID
FROM
	V$SYSTEM_PARAMETER A,
	V$CONTAINERS B
WHERE
	UPPER( A.NAME ) = 'SEC_CASE_SENSITIVE_LOGON'
	AND A.CON_ID = B.CON_ID(+);

SET MARKUP HTML OFF
prompt <h3>   Check - 430 List SEC_MAX_FAILED_LOGIN_ATTEMPTS parameter  </h3>
SET MARKUP HTML ON
SELECT
	A.NAME,
	A.VALUE,
	A.ISPDB_MODIFIABLE,
	B.NAME CONTAINER_NAME,
	A.CON_ID
FROM
	V$SYSTEM_PARAMETER A,
	V$CONTAINERS B
WHERE
	UPPER( A.NAME ) = 'SEC_MAX_FAILED_LOGIN_ATTEMPTS'
	AND A.CON_ID = B.CON_ID(+);

	
SET MARKUP HTML OFF
prompt <h3>   Check - 440 List SEC_PROTOCOL_ERROR_FURTHER_ACTION parameter  </h3>
SET MARKUP HTML ON
	
SELECT
	A.NAME,
	A.VALUE,
	A.ISPDB_MODIFIABLE,
	B.NAME CONTAINER_NAME,
	A.CON_ID
FROM
	V$SYSTEM_PARAMETER A,
	V$CONTAINERS B
WHERE
	UPPER( A.NAME ) = 'SEC_PROTOCOL_ERROR_FURTHER_ACTION'
	AND A.CON_ID = B.CON_ID(+);


SET MARKUP HTML OFF
prompt <h3>   Check - 450 List SEC_PROTOCOL_ERROR_TRACE_ACTION parameter  </h3>
SET MARKUP HTML ON
	
SELECT
	A.NAME,
	A.VALUE,
	A.ISPDB_MODIFIABLE,
	B.NAME CONTAINER_NAME,
	A.CON_ID
FROM
	V$SYSTEM_PARAMETER A,
	V$CONTAINERS B
WHERE
	UPPER( A.NAME ) = 'SEC_PROTOCOL_ERROR_TRACE_ACTION'
	AND A.CON_ID = B.CON_ID(+);

SET MARKUP HTML OFF
prompt <h3>   Check - 460 List SEC_RETURN_SERVER_RELEASE_BANNER parameter  </h3>
SET MARKUP HTML ON
	
SELECT
	A.NAME,
	A.VALUE,
	A.ISPDB_MODIFIABLE,
	B.NAME CONTAINER_NAME,
	A.CON_ID
FROM
	V$SYSTEM_PARAMETER A,
	V$CONTAINERS B
WHERE
	UPPER( A.NAME ) = 'SEC_RETURN_SERVER_RELEASE_BANNER'
	AND A.CON_ID = B.CON_ID(+);

	
	
SET MARKUP HTML OFF
prompt <h3>   Check - 470 List SQL92_SECURITY parameter  </h3>
SET MARKUP HTML ON
	
SELECT
	A.NAME,
	A.VALUE,
	A.ISPDB_MODIFIABLE,
	B.NAME CONTAINER_NAME,
	A.CON_ID
FROM
	V$SYSTEM_PARAMETER A,
	V$CONTAINERS B
WHERE
	UPPER( A.NAME ) = 'SQL92_SECURITY'
	AND A.CON_ID = B.CON_ID(+);


SET MARKUP HTML OFF
prompt <h3>   Check - 480 List RESOURCE_LIMIT parameter  </h3>
SET MARKUP HTML ON
SELECT
	A.NAME,
	A.VALUE,
	A.ISPDB_MODIFIABLE,
	B.NAME CONTAINER_NAME,
	A.CON_ID
FROM
	V$SYSTEM_PARAMETER A,
	V$CONTAINERS B
WHERE
	UPPER( A.NAME ) = 'RESOURCE_LIMIT'
	AND A.CON_ID = B.CON_ID(+);

	
SET MARKUP HTML OFF
prompt <h3>   Check - 490 List AUDIT_SYS_OPERATIONS parameter  </h3>
SET MARKUP HTML ON
	
SELECT
	A.NAME,
	A.VALUE,
	A.ISPDB_MODIFIABLE,
	B.NAME CONTAINER_NAME,
	A.CON_ID
FROM
	V$SYSTEM_PARAMETER A,
	V$CONTAINERS B
WHERE
	UPPER( A.NAME ) = 'AUDIT_SYS_OPERATIONS'
	AND A.CON_ID = B.CON_ID(+);

	
SET MARKUP HTML OFF
prompt <h3>   Check - 500 List AUDIT_TRAIL parameter  </h3>
SET MARKUP HTML ON
	
SELECT
	A.NAME,
	A.VALUE,
	A.ISPDB_MODIFIABLE,
	B.NAME CONTAINER_NAME,
	A.CON_ID
FROM
	V$SYSTEM_PARAMETER A,
	V$CONTAINERS B
WHERE
	UPPER( A.NAME ) = 'AUDIT_TRAIL'
	AND A.CON_ID = B.CON_ID(+);


SET MARKUP HTML OFF
prompt <h3>   Check - 510 List GLOBAL_NAMES parameter  </h3>
SET MARKUP HTML ON
	
SELECT
	A.NAME,
	A.VALUE,
	A.ISPDB_MODIFIABLE,
	B.NAME CONTAINER_NAME,
	A.CON_ID
FROM
	V$SYSTEM_PARAMETER A,
	V$CONTAINERS B
WHERE
	UPPER( A.NAME ) = 'GLOBAL_NAMES'
	AND A.CON_ID = B.CON_ID(+);


SET MARKUP HTML OFF
prompt <h3>   Check - 520 List LOCAL_LISTENER parameter  </h3>
SET MARKUP HTML ON
	
SELECT
	A.NAME,
	A.VALUE,
	A.ISPDB_MODIFIABLE,
	B.NAME CONTAINER_NAME,
	A.CON_ID
FROM
	V$SYSTEM_PARAMETER A,
	V$CONTAINERS B
WHERE
	UPPER( A.NAME ) = 'LOCAL_LISTENER'
	AND A.CON_ID = B.CON_ID(+);



SET MARKUP HTML OFF
prompt <h3>   Check - 530 List Database Version  </h3>
SET MARKUP HTML ON
	
SELECT banner FROM v$version;

SET MARKUP HTML OFF
prompt <h3>   Check - 540 List Database Patch History  </h3>
SET MARKUP HTML ON

SELECT *
    FROM DBA_REGISTRY_HISTORY
ORDER BY action_time DESC;


SET MARKUP HTML OFF
prompt <h3>   Check - 550 List Database Data File Permissions For Unix\Linux Servers </h3>
prompt <p>  If the datafiles are on ASM skip this test , Run these commands on Unix\Linux System Command Shell </p>
SET MARKUP HTML ON

SELECT
	'ls -lrt '||FILE_NAME "Unix\Linux commands"
FROM
	CDB_DATA_FILES;

SET MARKUP HTML OFF

prompt <h3>   Check - 555 List Database Data File Permissions For Windows Servers </h3>
prompt <p>  If the datafiles are on ASM skip this test ,Run these commands on Windows PowerShell </p>
SET MARKUP HTML ON

SELECT
	'Icacls '||FILE_NAME "Windows commands"
FROM
	CDB_DATA_FILES;

	
SET MARKUP HTML OFF
prompt <h3>   Check - 560 List Control File Permissions For Unix\Linux Servers </h3>
prompt <p>  If the datafiles are on ASM skip this test , Run these commands on Unix\Linux System Command Shell </p>
SET MARKUP HTML ON

SELECT
	'ls -lrt '||NAME "Unix\Linux commands"
FROM
	V$CONTROLFILE;

SET MARKUP HTML OFF
prompt <h3>   Check - 565 List Control File Permissions For Windows Servers </h3>
prompt <p>  If the datafiles are on ASM skip this test , Run these commands on Windows PowerShell </p>
SET MARKUP HTML ON

SELECT
	'Icacls '||NAME "Windows commands"
FROM
	V$CONTROLFILE;	


SET MARKUP HTML OFF
prompt <h3>   Check - 570 List Redo Log File Permissions For Unix\Linux Servers </h3>
prompt <p>  If the datafiles are on ASM skip this test , Run these commands on Windows PowerShell </p>
SET MARKUP HTML ON

SELECT
	'ls -lrt '||MEMBER "Unix\Linux commands"
FROM
	V$LOGFILE;
	
	
SET MARKUP HTML OFF
prompt <h3>   Check - 575 List Redo Log File Permissions For Windows Servers </h3>
prompt <p>  If the datafiles are on ASM skip this test , Run these commands on Windows PowerShell </p>
SET MARKUP HTML ON

SELECT
	'Icacls '||MEMBER "Windows commands"
FROM
	V$LOGFILE;

SET MARKUP HTML OFF
prompt <h3>   Check - 580 List Database Audit Parameters </h3>
SET MARKUP HTML ON

SELECT
	NAME,
	VALUE,
	CON_ID
FROM
	V$PARAMETER
WHERE
	UPPER( NAME ) IN(
		'AUDIT_TRAIL',
		'AUDIT_SYS_OPERATIONS'
	);


SET MARKUP HTML OFF
prompt <h3>   Check - 590  List Audited Privileges </h3>
SET MARKUP HTML ON

SELECT
	*
FROM
	CDB_PRIV_AUDIT_OPTS
ORDER BY
	USER_NAME,
	PRIVILEGE;


SET MARKUP HTML OFF
prompt <h3>   Check - 600  List Audited Statements </h3>
SET MARKUP HTML ON

SELECT
	*
FROM
	CDB_STMT_AUDIT_OPTS
ORDER BY
	USER_NAME,
	AUDIT_OPTION;


SET MARKUP HTML OFF
prompt <h3>   Check - 610  List Audited Objects </h3>
SET MARKUP HTML ON

SELECT
	*
FROM
	CDB_OBJ_AUDIT_OPTS
ORDER BY
	OWNER,
	OBJECT_NAME;
	

SET MARKUP HTML OFF
prompt <h3>   Check - 620  List Audit Default Options </h3>
SET MARKUP HTML ON

SELECT
	*
FROM
	ALL_DEF_AUDIT_OPTS;
	
SET MARKUP HTML OFF
prompt <h3>   Check - 630  List Fine Grained Auditing Options </h3>
SET MARKUP HTML ON

SELECT
	*
FROM
	CDB_AUDIT_POLICIES;
	
SET MARKUP HTML OFF
prompt <h3>   Check - 632  List Unified Policies  for each pluggable database run this </h3>
SET MARKUP HTML ON

SELECT
	*
FROM
	AUDIT_UNIFIED_POLICIES;

SET MARKUP HTML OFF
prompt <h3>   Check - 634  List Unified Enabled Policies  for each pluggable database run this  </h3>
SET MARKUP HTML ON

SELECT
	*
FROM
	AUDIT_UNIFIED_ENABLED_POLICIES;

	
SET MARKUP HTML OFF
prompt <h3>   Check - 640  List Database Backups with RMAN </h3>
SET MARKUP HTML ON	

SELECT
	SESSION_KEY,
	INPUT_TYPE,
	STATUS,
	TO_CHAR( START_TIME, 'DD/MM/YY HH24:MI' ) START_TIME,
	TO_CHAR( END_TIME, 'DD/MM/YY HH24:MI' ) END_TIME,
	TRUNC(( ELAPSED_SECONDS / 3600 ) * 60 ) MINUTES,
	TIME_TAKEN_DISPLAY
FROM
	V$RMAN_BACKUP_JOB_DETAILS
ORDER BY
	SESSION_KEY DESC;

SET MARKUP HTML OFF
prompt <h3>   Check - 650  List Database Protection Mode  </h3>
SET MARKUP HTML ON	

	
SELECT
	PROTECTION_MODE
FROM
	V$DATABASE;

SET MARKUP HTML OFF
prompt <h3>   Check - 660  List Archived Logs at Primary Side   </h3>
Prompt <p>  This script should  run on Primary Side </p>
SET MARKUP HTML ON	
	
SELECT
	SEQUENCE#,
	TO_CHAR( FIRST_TIME, 'DD-MM-YYYY HH24:MI' ) FIRST_TIME,
	TO_CHAR( NEXT_TIME,  'DD-MM-YYYY HH24:MI' ) NEXT_TIME
FROM
	V$ARCHIVED_LOG 
	WHERE 
	NEXT_TIME > SYSDATE - 2
ORDER BY
	SEQUENCE# desc;

SET MARKUP HTML OFF
prompt <h3>   Check - 665 List Archived Logs at Standby  Side   </h3>
Prompt <p> Run this script on Standby Side and compare it with Primary Side
prompt <br>	
prompt <br>	
prompt SELECT
Prompt <br>
Prompt NAME
Prompt <br>
Prompt SEQUENCE#,
Prompt <br>
Prompt TO_CHAR( FIRST_TIME, 'DD-MM-YYYY HH24:MI' ) FIRST_TIME,
Prompt <br>
Prompt TO_CHAR( NEXT_TIME,  'DD-MM-YYYY HH24:MI' ) NEXT_TIME
Prompt <br>
Prompt FROM
Prompt <br>
Prompt V$ARCHIVED_LOG
Prompt <br>
Prompt 	WHERE 
Prompt <br>
Prompt 	NEXT_TIME > SYSDATE - 2
Prompt <br>
Prompt ORDER BY
Prompt <br>
Prompt SEQUENCE# desc;
Prompt </p>
SET MARKUP HTML ON


SET MARKUP HTML OFF
prompt <h3>   Check - 670  List Database Links BURAYA KADAR  </h3>
SET MARKUP HTML ON	

SELECT
	A.OWNER,
	A.DB_LINK,
	A.HOST,
	A.CREATED,
	A.CON_ID,
	B.NAME CONTAINER_NAME
FROM
	CDB_DB_LINKS A,
	V$CONTAINERS B
WHERE
	A.CON_ID = B.CON_ID(+)
ORDER BY
	A.HOST;

	
SET MARKUP HTML OFF
prompt <h3>   Check - 680  List PUBLIC Database Links   </h3>
SET MARKUP HTML ON


SELECT
	A.OWNER,
	A.DB_LINK,
	A.HOST,
	A.CREATED,
	A.CON_ID,
	B.NAME
FROM
	CDB_DB_LINKS A,
	V$CONTAINERS B
WHERE
	A.OWNER = 'PUBLIC'
	AND A.CON_ID = B.CON_ID(+)
ORDER BY
	A.HOST;


SET MARKUP HTML OFF
prompt <h3>   Check - 695  List ACL Definitions   </h3>
SET MARKUP HTML ON	
	
SELECT
	*
FROM
	CDB_HOST_ACLS;


SET MARKUP HTML OFF
prompt <h3>   Check - 705  List ACE Definitions   </h3>
SET MARKUP HTML ON	

SELECT
	*
FROM
	CDB_HOST_ACES;

	
Prompt	
Prompt *****  END OF SECURITY CHECKS REPORT  ****** 	
	
spool off
exit