Description: Audit trail data must be retained for at least one year.

Automation Logic:

SELECT * FROM ( SELECT CASE WHEN ((SELECT count(*) from V$OPTION WHERE PARAMETER='Unified Auditing' AND VALUE='FALSE')=1) THEN (SELECT name||' parameter is set to '||value||'.' value from v$parameter where name='audit_trail' and value='NONE')
    END AS VALUE FROM DUAL) WHERE VALUE IS NOT NULL

Change to STIG Rule: Combined the rule queries to check if audit is enabled by means of either Traditional or Unified system. Need to manually check if audit data is retained for at least one year.

Description: Oracle instance names must not contain Oracle version numbers.

Automation Logic:

select 'Instance name contain version number' from v$instance where instance_name LIKE '%12%';

Change to STIG Rule: Provided an even more specific query to check if instance name contains version number.

Description: Fixed user and public database links must be authorized for use.

Automation Logic:

select 'Fixed user database link '||db_link||' found for '||owner value from dba_db_links 
where db_link not in (select master from sys.dba_repcatlog) 

Change to STIG Rule: Combined the rule queries to return db_link as violations only if dba_repcatalog has records.

Description: A minimum of two Oracle control files must be defined and configured to be stored on separate, archived physical disks or archived directories on a RAID device.

Automation Logic:

select 'A minimum of two oracle control files must be defined' value from v$controlfile having count(*) < 2

Change to STIG Rule: Need to manually check if each file is located on a separate RAID device.

Description: A minimum of two Oracle redo log groups or files must be defined and configured to be stored on separate, archived physical disks or archived directories on a RAID device.

Automation Logic:

select 'A minimum of two Oracle redo log groups/files must be defined ' value from v$LOG where members > 1 having count(*) < 2

Change to STIG Rule: Used the more stricter query to get the violation. Need to manually check if a RAID device is used.

Description: System privileges granted using the WITH ADMIN OPTION must not be granted to unauthorized user accounts.

Automation Logic:

select 'User '|| grantee||' granted system privilege ' ||privilege ||' WITH ADMIN option' value from dba_sys_privs
where grantee not in
('SYS', 'SYSTEM', 'AQ_ADMINISTRATOR_ROLE', 'DBA',
'MDSYS', 'LBACSYS', 'SCHEDULER_ADMIN',
'WMSYS', 'APEX_030200', 'APEX_040200','DVSYS','SYSKM','DV_ACCTMGR')
and admin_option = 'YES'
and grantee not in
(select grantee from dba_role_privs where granted_role = 'DBA')

Change to STIG Rule: Added default users/roles to the query - 'APEX_030200', 'APEX_040200', 'DVSYS', 'SYSKM', and 'DV_ACCTMGR'.

Description: Oracle roles granted using the WITH ADMIN OPTION must not be granted to unauthorized accounts.

Automation Logic:

select 'Role ' ||grantee||' granted '||granted_role||' WITH ADMIN OPTION' value from dba_role_privs
where grantee not in
('ANONYMOUS','CTXSTS','EXFSYS','LBACSYS','MDSYS','OLAPSYS','OEDDATA','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN','SI_INFORMTN_SCHEMA','WK_TEST','WK_SYS','WKPROXY','WMSYS','XDB','DBSNMP','MGMT_VIEW','SYS','SYSMAN','SYSTEM','DBA','DV_ACCTMGR','DV_OWNER','RECOVERY_CATALOG_OWNER','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR')
and admin_option = 'YES' 
and grantee not in
(select distinct owner from dba_objects)
and grantee not in
(select grantee from dba_role_privs
where granted_role = 'DBA')
order by grantee

Change to STIG Rule: Added default users/roles to the query: 'DBA', 'DV_ACCTMGR', 'DV_OWNER', 'RECOVERY_CATALOG_OWNER', 'SPATIAL_CSW_ADMIN_USR', and 'SPATIAL_WFS_ADMIN_USR'.

Description: Listener must be configured for administration authentication.

Automation Logic:

perl %scriptsDir%/lsnrSecStatus.pl {OracleHome} {MachineName} {Port} {Protocol}

Change to STIG Rule: Script provided by Oracle.

Description: Connections by mid-tier web and application systems to the Oracle DBMS from a DMZ or external network must be encrypted.

Automation Logic:

perl %scriptsDir%/encryptedCommCheck.pl {OracleHome}

Change to STIG Rule: Script provided by Oracle.

Description: Application user privilege assignment must be reviewed monthly, or more frequently to ensure compliance with least privilege, and documented policy.

Automation Logic:

select 'No privilege analysis policy is defined/run to analyze unrequired application user privilege assignment' value from SYS.DBA_UNUSED_SYSPRIVS having count(*)=0

Change to STIG Rule: Added a query to check whether privilege analysis policy is defined/run to analyze non-required application user privilege assignment.

Description: Audit trail data must be reviewed daily or more frequently.

Automation Logic:

SELECT * FROM ( SELECT CASE WHEN ((SELECT count(*) from SYS.V$OPTION WHERE PARAMETER='Unified Auditing' AND VALUE='FALSE')=1) THEN 
   (SELECT name||' parameter is set to '||value||'.' value from sys.v$parameter where name='audit_trail' and value='NONE')
    END AS VALUE FROM DUAL) WHERE VALUE IS NOT NULL

Change to STIG Rule: Combined the rule queries to check if audit is enabled by means of either Traditional or Unified system.

Description: The directories assigned to the LOG_ARCHIVE_DEST* parameters must be protected from unauthorized access.

Automation Logic:

perl %scriptsDir%/logArchiveDestPerm.pl {OracleHome} {MachineName} {Port} {Protocol} {SID} {UserName} {password} {Role}

Change to STIG Rule: Script provided by Oracle.

Description: Application object owner accounts must be disabled when installation or maintenance actions are not performed.

Automation Logic:

select distinct 'Application object owner account '||owner||' found' value from dba_objects, dba_users 
where owner not in
('ANONYMOUS','AURORA$JIS$UTILITY$',
'AURORA$ORB$UNAUTHENTICATED','CTXSYS','DBSNMP','DIP','DVF',
'DVSYS','EXFSYS','LBACSYS','MDDATA','MDSYS','MGMT_VIEW','ODM', 'ODM_MTR','OLAPSYS','ORDPLUGINS','ORDSYS','OSE$HTTP$ADMIN', 'OUTLN','PERFSTAT','PUBLIC','REPADMIN','RMAN', 'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TRACESVR', 'TSMSYS','WK_TEST','WKPROXY','WKSYS','WKUSER','WMSYS','XDB', 'HR', 'OE', 'PM', 'IX', 'SH','OJVMSYS','ORDDATA','APPQOSSYS','ORACLE_OCM','SCOTT','APEX_040200','AUDSYS','GSMADMIN_INTERNAL','FLOWS_FILES') 
and owner in (select distinct owner from dba_objects
where object_type <> 'SYNONYM')
and owner = username
and upper(account_status) not like '%LOCKED%'

Change to STIG Rule: Changed the query to include more default users/roles which are not in the list.

Description: Access to DBMS software files and directories must not be granted to unauthorized users.

Automation Logic:

perl %scriptsDir%/umaskCheck.pl {OracleHome} 022

Change to STIG Rule: Changed the query to include more default users/roles which are not in the list.

Description: Changes to DBMS security labels must be audited.

Automation Logic:

 SELECT * FROM (
SELECT CASE UPPER(value) WHEN 'FALSE'
     THEN
      (SELECT CASE UPPER(value) WHEN 'NONE' 
            THEN
                name||' parameter is set to '||value||'.'
            ELSE
                (SELECT 'Changes to DBMS security labels must be audited.' value from dba_sa_audit_options having count(*)=0)
            END AS VALUE FROM  v$parameter where name='audit_trail' )        
     END AS value FROM v$option 
           WHERE parameter  ='Unified Auditing') where VALUE IS NOT NULL;

Change to STIG Rule: Combined rule queries.

Description: The /diag subdirectory under the directory assigned to the DIAGNOSTIC_DEST parameter must be protected from unauthorized access.

Automation Logic:

perl %scriptsDir%/diagDestPerm.pl {OracleHome} {MachineName} {Port} {Protocol} {SID} {UserName} {password} {Role}

Change to STIG Rule: Script provided by Oracle.

Description: Remote administration must be disabled for the Oracle connection manager.

Automation Logic:

perl %scriptsDir%/remoteAdminCheck.pl {OracleHome}

Change to STIG Rule: Script provided by Oracle.

Description: Network client connections must be restricted to supported versions.

Automation Logic:

perl %scriptsDir%/allowedLogonVersion.pl {OracleHome} 11

Change to STIG Rule: Script provided by Oracle.

Description: The DBMS must employ cryptographic mechanisms preventing the unauthorized disclosure of information during transmission unless the transmitted data is otherwise protected by alternative physical measures.

Automation Logic:

perl %scriptsDir%/encryptionCheck.pl {OracleHome}

Change to STIG Rule: Script provided by Oracle.

Description: The DBMS must utilize approved cryptography when passing authentication data for remote access sessions.

Automation Logic:

perl %scriptsDir%/encryptionCheck.pl {OracleHome}

Change to STIG Rule: Script provided by Oracle.

Description: A DBMS providing remote access capabilities must utilize organization-defined cryptography to protect the confidentiality of data passing over remote access sessions.

Automation Logic:

perl %scriptsDir%/encryptionCheck.pl {OracleHome}

Change to STIG Rule: Script provided by Oracle.

Description: A DBMS providing remote access capabilities must utilize approved cryptography to protect the integrity of remote access sessions.

Automation Logic:

perl %scriptsDir%/encryptionCheck.pl {OracleHome}

Change to STIG Rule: Script provided by Oracle.

Description: The DBMS must ensure remote sessions that access an organization-defined list of security functions and security-relevant information are audited.

Automation Logic:

SELECT * FROM ( SELECT CASE WHEN ((SELECT count(*) from SYS.V$OPTION WHERE PARAMETER='Unified Auditing' AND VALUE='FALSE')=1) THEN 
   (SELECT name||' parameter is set to '||value||'.' value from sys.v$parameter where name='audit_trail' and value='NONE')
    END AS VALUE FROM DUAL) WHERE VALUE IS NOT NULL

Change to STIG Rule: Combined rule queries to check if audit is enabled by means of either Traditional or Unified system. Need to manually check if remote sessions that are accessing security information are being audited.

Description: The DBMS must support the disabling of network protocols deemed as non-secure by the organization.

Automation Logic:

perl %scriptsDir%/secureProtocolCheck.pl {Protocol}

Change to STIG Rule: Script provided by Oracle.

Description: The DBMS must provide a mechanism to automatically terminate accounts designated as temporary or emergency accounts after an organization-defined time period.

Automation Logic:

select 'User '||u.username||' is assigned profile '||p.profile||' with PASSWORD_LIFE_TIME='||p.limit||'.'
value from dba_profiles p, dba_users u,
(select limit as def_pwd_life_tm
from dba_profiles
where profile = 'DEFAULT'
and resource_name = 'PASSWORD_LIFE_TIME')
where p.resource_name = 'PASSWORD_LIFE_TIME'
and ((replace(p.limit, 'DEFAULT', def_pwd_life_tm) in
('UNLIMITED', 'NULL'))
or (lpad(replace(p.limit, 'DEFAULT', def_pwd_life_tm),40,'0') >
lpad('35',40,'0')))
  AND u.profile = p.profile

Change to STIG Rule: A query added by Oracle.

Description: The DBMS must automatically disable accounts after a 35 day period of account inactivity.

Automation Logic:

select 'User '||u.username||' is assigned profile '||p.profile||' with PASSWORD_LIFE_TIME='||p.limit||'.'
value from dba_profiles p, dba_users u,
(select limit as def_pwd_life_tm
from dba_profiles
where profile = 'DEFAULT'
and resource_name = 'PASSWORD_LIFE_TIME')
where p.resource_name = 'PASSWORD_LIFE_TIME'
and ((replace(p.limit, 'DEFAULT', def_pwd_life_tm) in
('UNLIMITED', NULL))
or (lpad(replace(p.limit, 'DEFAULT', def_pwd_life_tm),40,'0') >
lpad('35',40,'0')))
  AND u.profile = p.profile
UNION ALL
select 'Table SYS.LOGIN_AUDIT_INFO_ALL is not used.' value FROM DUAL WHERE NOT EXISTS (select table_name from dba_tables where table_name='LOGIN_AUDIT_INFO_ALL')

Change to STIG Rule: A query added by Oracle.

Description: The DBMS must automatically audit account creation.

Automation Logic:

  SELECT * FROM (
     SELECT CASE UPPER(value) WHEN 'FALSE'
     THEN
      (SELECT CASE UPPER(value) WHEN 'NONE' 
            THEN
                name||' parameter is set to '||value||'.'
            ELSE
                (SELECT 'Account creation is not being audited' value from sys.dba_stmt_audit_opts where AUDIT_OPTION='CREATE USER' having count(*)=0)
            END AS VALUE FROM  v$parameter where name='audit_trail' )
     ELSE
        (SELECT CASE UPPER(value) WHEN 'NONE' 
           THEN
        (SELECT 'Account creation is not being audited' from audit_unified_policies where AUDIT_OPTION='CREATE USER' AND AUDIT_OPTION_TYPE='STANDARD ACTION' AND AUDIT_CONDITION!='NONE' having count(*)=0)
     ELSE 
              (SELECT DISTINCT value FROM (SELECT 'Account creation is not being audited' value from audit_unified_policies where AUDIT_OPTION='CREATE USER' AND AUDIT_OPTION_TYPE='STANDARD ACTION' AND AUDIT_CONDITION!='NONE' having count(*)=0
          UNION 
      SELECT 'Account creation is not being audited' value from sys.dba_stmt_audit_opts where AUDIT_OPTION='CREATE USER' having count(*)=0  )) 
            END AS VALUE FROM  v$parameter where name='audit_trail' )      
     END AS value FROM v$option WHERE parameter  ='Unified Auditing') where VALUE IS NOT NULL;

Change to STIG Rule: Combined rule queries to check if audit is enabled by means of either Traditional or Unified system and to check if account creation is being audited.

Description: The DBMS must automatically audit account modification.

Automation Logic:

  SELECT * FROM (
     SELECT CASE UPPER(value) WHEN 'FALSE'
     THEN
      (SELECT CASE UPPER(value) WHEN 'NONE' 
            THEN
                name||' parameter is set to '||value||'.'
            ELSE
                (SELECT 'Account modification is not being audited' value from sys.dba_stmt_audit_opts where AUDIT_OPTION='ALTER USER' having count(*)=0)
            END AS VALUE FROM  v$parameter where name='audit_trail' )
     ELSE
        (SELECT CASE UPPER(value) WHEN 'NONE' 
           THEN
        (SELECT 'Account modification is not being audited' from audit_unified_policies where AUDIT_OPTION='ALTER USER' AND AUDIT_OPTION_TYPE='STANDARD ACTION' AND AUDIT_CONDITION!='NONE' having count(*)=0)
     ELSE 
              (SELECT DISTINCT value FROM (SELECT 'Account modification is not being audited' value from audit_unified_policies where AUDIT_OPTION='ALTER USER' AND AUDIT_OPTION_TYPE='STANDARD ACTION' AND AUDIT_CONDITION!='NONE' having count(*)=0
          UNION 
      SELECT 'Account modification is not being audited' value from sys.dba_stmt_audit_opts where AUDIT_OPTION='ALTER USER' having count(*)=0  )) 
            END AS VALUE FROM  v$parameter where name='audit_trail' )      
     END AS value FROM v$option WHERE parameter  ='Unified Auditing') where VALUE IS NOT NULL;

Change to STIG Rule: Combined rule queries to check if audit is enabled by means of either Traditional or Unified system and to check if account modification is being audited.

Description: The DBMS must automatically audit account disabling actions.

Automation Logic:

SELECT * FROM (
     SELECT CASE UPPER(value) WHEN 'FALSE'
     THEN
      (SELECT CASE UPPER(value) WHEN 'NONE' 
            THEN
                name||' parameter is set to '||value||'.'
            ELSE
                (SELECT 'Account disabling is not being audited' value from sys.dba_stmt_audit_opts where AUDIT_OPTION='ALTER USER' having count(*)=0)
            END AS VALUE FROM  v$parameter where name='audit_trail' )
     ELSE
        (SELECT CASE UPPER(value) WHEN 'NONE' 
           THEN
        (SELECT 'Account disabling is not being audited' from audit_unified_policies where AUDIT_OPTION='ALTER USER' AND AUDIT_OPTION_TYPE='STANDARD ACTION' AND AUDIT_CONDITION!='NONE' having count(*)=0)
     ELSE 
              (SELECT DISTINCT value FROM (SELECT 'Account disabling is not being audited' value from audit_unified_policies where AUDIT_OPTION='ALTER USER' AND AUDIT_OPTION_TYPE='STANDARD ACTION' AND AUDIT_CONDITION!='NONE' having count(*)=0
          UNION 
      SELECT 'Account disabling is not being audited' value from sys.dba_stmt_audit_opts where AUDIT_OPTION='ALTER USER' having count(*)=0  )) 
            END AS VALUE FROM  v$parameter where name='audit_trail' )      
     END AS value FROM v$option WHERE parameter  ='Unified Auditing') where VALUE IS NOT NULL;

Change to STIG Rule: Combined rule queries to check if audit is enabled by means of either Traditional or Unified system. Need to manually check if account disabling is being audited.

Description: The DBMS must automatically audit account termination.

Automation Logic:

SELECT * FROM (
     SELECT CASE UPPER(value) WHEN 'FALSE'
     THEN
      (SELECT CASE UPPER(value) WHEN 'NONE' 
            THEN
                name||' parameter is set to '||value||'.'
            ELSE
                (SELECT 'Account termination is not being audited' value from sys.dba_stmt_audit_opts where AUDIT_OPTION='DROP USER' having count(*)=0)
            END AS VALUE FROM  v$parameter where name='audit_trail' )
     ELSE
        (SELECT CASE UPPER(value) WHEN 'NONE' 
           THEN
        (SELECT 'Account termination is not being audited' from audit_unified_policies where AUDIT_OPTION='DROP USER' AND AUDIT_OPTION_TYPE='STANDARD ACTION' AND AUDIT_CONDITION!='NONE' having count(*)=0)
     ELSE 
              (SELECT DISTINCT value FROM (SELECT 'Account termination is not being audited' value from audit_unified_policies where AUDIT_OPTION='DROP USER' AND AUDIT_OPTION_TYPE='STANDARD ACTION' AND AUDIT_CONDITION!='NONE' having count(*)=0
          UNION 
      SELECT 'Account termination is not being audited' value from sys.dba_stmt_audit_opts where AUDIT_OPTION='DROP USER' having count(*)=0  )) 
            END AS VALUE FROM  v$parameter where name='audit_trail' )      
     END AS value FROM v$option WHERE parameter  ='Unified Auditing') where VALUE IS NOT NULL;

Change to STIG Rule: Combined rule queries to check if audit is enabled by means of either Traditional or Unified system and to check if account termination is being audited.

Description: Administrative privileges must be assigned to database accounts through database roles.

Automation Logic:

select 'User '|| dsp.grantee ||' is granted '|| dsp.privilege ||' privilege' value
     from dba_sys_privs dsp, dba_users du
     where dsp.grantee in (SELECT username
     FROM dba_users
     WHERE username NOT IN
     (
     'XDB', 'SYSTEM', 'SYS', 'LBACSYS',
     'DVSYS', 'DVF', 'SYSMAN_RO',
     'SYSMAN_BIPLATFORM', 'SYSMAN_MDS',
     'SYSMAN_OPSS', 'SYSMAN_STB', 'DBSNMP',
     'SYSMAN', 'APEX_040200', 'WMSYS',
     'SYSDG', 'SYSBACKUP', 'SPATIAL_WFS_ADMIN_USR',
     'SPATIAL_CSW_ADMIN_US', 'GSMCATUSER',
     'OLAPSYS', 'SI_INFORMTN_SCHEMA',
     'OUTLN', 'ORDSYS', 'ORDDATA', 'OJVMSYS',
     'ORACLE_OCM', 'MDSYS', 'ORDPLUGINS',
     'GSMADMIN_INTERNAL', 'MDDATA', 'FLOWS_FILES',
     'DIP', 'CTXSYS', 'AUDSYS',
     'APPQOSSYS', 'APEX_PUBLIC_USER', 'ANONYMOUS',
     'SPATIAL_CSW_ADMIN_USR', 'SYSKM',
     'SYSMAN_TYPES', 'MGMT_VIEW',
     'EUS_ENGINE_USER', 'EXFSYS', 'SYSMAN_APM'
     )
     ) AND dsp.privilege NOT IN ('UNLIMITED TABLESPACE', 'REFERENCES', 'INDEX', 'SYSDBA','SYSOPER') and dsp.grantee=du.username and du.account_status not like '%EXPIRED%LOCKED%' order by dsp.grantee

Change to STIG Rule: A query added by Oracle.

Description: All usage of privileged accounts must be audited.

Automation Logic:

SELECT * FROM ( SELECT CASE WHEN ((SELECT count(*) from SYS.V$OPTION WHERE PARAMETER='Unified Auditing' AND VALUE='FALSE')=1) THEN    (select 'audit_trail parameter is set to '||value value from v$parameter where name='audit_trail' and value = 'NONE')
 END AS VALUE FROM DUAL) WHERE VALUE IS NOT NULL

Change to STIG Rule: Combined rule queries to check if audit is enabled by means of either Traditional or Unified system. Need to manually check if all use of privileged accounts are audited.

Description: The DBMS must verify if account lock-outs persist until reset by an administrator.

Automation Logic:

select p.resource_name||' is not set to UNLIMITED for user '||u.username||' through profile '||p.profile AS value from dba_users u, dba_profiles p
where u.profile = p.profile
  and p.resource_name = 'PASSWORD_LOCK_TIME'
  and p.limit != 'UNLIMITED'
  and u.account_status not like '%EXPIRED%LOCKED%'

Change to STIG Rule: A query added by Oracle.

Description: The DBMS must limit the number of consecutive failed logon attempts to 3.

Automation Logic:

select p.resource_name||' limit is set to '||p.limit||' for user '||u.username||' through profile '||p.profile AS value from dba_profiles p, dba_users u,
(select limit as def_fld_lgn_atmt from dba_profiles
where profile = 'DEFAULT'
and resource_name = 'FAILED_LOGIN_ATTEMPTS')
where p.resource_name = 'FAILED_LOGIN_ATTEMPTS'
and ((replace(p.limit, 'DEFAULT', def_fld_lgn_atmt) in
('UNLIMITED', NULL))
or (lpad(replace(p.limit, 'DEFAULT', def_fld_lgn_atmt),40,'0') > lpad('3',40,'0')))
  AND u.profile = p.profile
AND u.account_status not like '%EXPIRED%LOCKED%'

Change to STIG Rule: A query added by Oracle.

Description: The DBMS, when the maximum number of unsuccessful logon attempts is exceeded, must automatically lock the account/node until released by an administrator.

Automation Logic:

select p.resource_name||' is set to '||p.limit||' for user '||u.username||' through profile '||p.profile AS
value from dba_profiles p, dba_users u,
(select limit as def_fld_lgn_atmt
from dba_profiles
where profile = 'DEFAULT'
and resource_name = 'FAILED_LOGIN_ATTEMPTS')
where p.resource_name = 'FAILED_LOGIN_ATTEMPTS'
and ((replace(p.limit, 'DEFAULT', def_fld_lgn_atmt) in
('UNLIMITED', NULL))
or (lpad(replace(p.limit, 'DEFAULT', def_fld_lgn_atmt),40,'0') >
lpad('3',40,'0')))
  AND u.profile = p.profile
AND u.account_status not like '%EXPIRED%LOCKED%'

Change to STIG Rule: A query added by Oracle.

Description: The DBMS must retain the notification message or banner on the screen until users take explicit actions to log on to the database.

Automation Logic:

perl bannerText.pl {OracleHome}

Change to STIG Rule: Script provided by Oracle.

Description: The DBMS must display the system use information when appropriate, before granting further access.

Automation Logic:

perl bannerText.pl {OracleHome}

Change to STIG Rule: Script provided by Oracle.

Description: The DBMS must have its auditing configured to reduce the likelihood of storage capacity being exceeded.

Automation Logic:

select tablespace_name ||' tablespace used for logging '||table_name value from sys.dba_tables where table_name in ('AUD$', 'FGA_LOG$')
 AND tablespace_name = 'SYSTEM' UNION ALL select tablespace_name ||' tablespace used for unified adit '||table_name value from sys.dba_tables where owner='AUDSYS' and tablespace_name='USERS'

Change to STIG Rule: A query added by Oracle.

Description: The DBMS must have allocated audit record storage capacity.

Automation Logic:

select tablespace_name ||' tablespace used for logging '||table_name value from sys.dba_tables where table_name in ('AUD$', 'FGA_LOG$')
 AND tablespace_name = 'SYSTEM' UNION ALL select tablespace_name ||' tablespace used for unified adit '||table_name value from sys.dba_tables where owner='AUDSYS' and tablespace_name='USERS'

Change to STIG Rule: A query added by Oracle.

Description: The DBMS must provide audit record generation capability for organization-defined auditable events within the database.

Automation Logic:

SELECT * FROM ( SELECT CASE WHEN ((SELECT count(*) from SYS.V$OPTION WHERE PARAMETER='Unified Auditing' AND VALUE='FALSE')=1) THEN    (select 'audit_trail parameter is set to '||value value from v$parameter where name='audit_trail' and value = 'NONE')
 END AS VALUE FROM DUAL) WHERE VALUE IS NOT NULL

Change to STIG Rule: Combined rule queries to check if audit is enabled by means of either Traditional or Unified system.

Description: The DBMS must generate audit records for the DoD-selected list of auditable events.

Automation Logic:

SELECT * FROM ( SELECT CASE WHEN ((SELECT count(*) from SYS.V$OPTION WHERE PARAMETER='Unified Auditing' AND VALUE='FALSE')=1) THEN (SELECT name||' parameter is set to '||value||'.' value from sys.v$parameter where name='audit_trail' and value='NONE')
    END AS VALUE FROM DUAL) WHERE VALUE IS NOT NULL

Change to STIG Rule: Combined rule queries to check if audit is enabled by means of either Traditional or Unified system.

Description: The DBMS must produce audit records containing sufficient information to establish what type of events occurred.

Automation Logic:

SELECT * FROM ( SELECT CASE WHEN ((SELECT count(*) from SYS.V$OPTION WHERE PARAMETER='Unified Auditing' AND VALUE='FALSE')=1) THEN    (select 'audit_trail parameter is set to '||value value from v$parameter where name='audit_trail' and value = 'NONE')
 END AS VALUE FROM DUAL) WHERE VALUE IS NOT NULL

Change to STIG Rule: Combined rule queries to check if audit is enabled by means of either Traditional or Unified system.

Description: The DBMS must produce audit records containing sufficient information to establish when (date and time) the events occurred.

Automation Logic:

SELECT * FROM ( SELECT CASE WHEN ((SELECT count(*) from SYS.V$OPTION WHERE PARAMETER='Unified Auditing' AND VALUE='FALSE')=1) THEN    (select 'audit_trail parameter is set to '||value value from v$parameter where name='audit_trail' and value = 'NONE')
 END AS VALUE FROM DUAL) WHERE VALUE IS NOT NULL

Change to STIG Rule: Combined rule queries to check if audit is enabled by means of either Traditional or Unified system.

Description: The DBMS must produce audit records containing sufficient information to establish where the events occurred.

Automation Logic:

SELECT * FROM ( SELECT CASE WHEN ((SELECT count(*) from SYS.V$OPTION WHERE PARAMETER='Unified Auditing' AND VALUE='FALSE')=1) THEN    (select 'audit_trail parameter is set to '||value value from v$parameter where name='audit_trail' and value = 'NONE')
 END AS VALUE FROM DUAL) WHERE VALUE IS NOT NULL

Change to STIG Rule: Combined rule queries to check if audit is enabled by means of either Traditional or Unified system.

Description: The DBMS must produce audit records containing sufficient information to establish the sources (origins) of the events.

Automation Logic:

SELECT * FROM ( SELECT CASE WHEN ((SELECT count(*) from SYS.V$OPTION WHERE PARAMETER='Unified Auditing' AND VALUE='FALSE')=1) THEN    (select 'audit_trail parameter is set to '||value value from v$parameter where name='audit_trail' and value = 'NONE')
 END AS VALUE FROM DUAL) WHERE VALUE IS NOT NULL

Change to STIG Rule: Combined rule queries to check if audit is enabled by means of either Traditional or Unified system.

Description: The DBMS must produce audit records containing sufficient information to establish the outcome (success or failure) of the events.

Automation Logic:

SELECT * FROM ( SELECT CASE WHEN ((SELECT count(*) from SYS.V$OPTION WHERE PARAMETER='Unified Auditing' AND VALUE='FALSE')=1) THEN    (select 'audit_trail parameter is set to '||value value from v$parameter where name='audit_trail' and value = 'NONE')
 END AS VALUE FROM DUAL) WHERE VALUE IS NOT NULL

Change to STIG Rule: Combined rule queries to check if audit is enabled by means of either Traditional or Unified system.

Description: The DBMS must produce audit records containing sufficient information to establish the identity of any user/subject or process associated with the event.

Automation Logic:

SELECT * FROM ( SELECT CASE WHEN ((SELECT count(*) from SYS.V$OPTION WHERE PARAMETER='Unified Auditing' AND VALUE='FALSE')=1) THEN    (select 'audit_trail parameter is set to '||value value from v$parameter where name='audit_trail' and value = 'NONE')
 END AS VALUE FROM DUAL) WHERE VALUE IS NOT NULL

Change to STIG Rule: Combined rule queries to check if audit is enabled by means of either Traditional or Unified system.

Description: The DBMS must include organization-defined additional, more detailed information in the audit records for audit events identified by type, location, or subject.

Automation Logic:

SELECT * FROM ( SELECT CASE WHEN ((SELECT count(*) from SYS.V$OPTION WHERE PARAMETER='Unified Auditing' AND VALUE='FALSE')=1) THEN    (select 'audit_trail parameter is set to '||value value from v$parameter where name='audit_trail' and value = 'NONE')
 END AS VALUE FROM DUAL) WHERE VALUE IS NOT NULL

Change to STIG Rule: Combined rule queries to check if audit is enabled by means of either Traditional or Unified system.

Description: The system must protect audit information from any type of unauthorized access.

Automation Logic:

SELECT GRANTEE||' has '||PRIVILEGE||' on '|| TABLE_NAME AS VALUE FROM sys.DBA_TAB_PRIVS where (table_name = 'AUD$' or table_name='FGA_LOG$') AND grantee not in ('SYS','SYSTEM', 'DELETE_CATALOG_ROLE') UNION ALL  SELECT GRANTEE|| ' has '||PRIVILEGE|| ' on '||TABLE_NAME AS VALUE FROM sys.DBA_TAB_PRIVS where owner='AUDSYS' AND grantee not in ('SYS', 'SYSTEM', 'DELETE_CATALOG_ROLE')

Change to STIG Rule: A query added by Oracle.

Description: The system must protect audit information from unauthorized modification.

Automation Logic:

SELECT GRANTEE||' has '||PRIVILEGE||' on '|| TABLE_NAME AS VALUE FROM sys.DBA_TAB_PRIVS where (table_name = 'AUD$' or table_name='FGA_LOG$') AND PRIVILEGE IN ('DELETE','INSERT','UPDATE') AND grantee not in ('SYS','SYSTEM', 'DELETE_CATALOG_ROLE') UNION ALL  SELECT GRANTEE|| ' has '||PRIVILEGE|| ' on '||TABLE_NAME AS VALUE FROM sys.DBA_TAB_PRIVS where owner='AUDSYS' AND PRIVILEGE IN ('DELETE','INSERT','UPDATE') AND grantee not in ('SYS','SYSTEM', 'DELETE_CATALOG_ROLE') 

Change to STIG Rule: A query added by Oracle.

Description: The system must protect audit information from unauthorized deletion.

Automation Logic:

SELECT GRANTEE||' has '||PRIVILEGE||' on '|| TABLE_NAME AS VALUE FROM sys.DBA_TAB_PRIVS where (table_name = 'AUD$' or table_name='FGA_LOG$') AND PRIVILEGE='DELETE' AND grantee not in ('SYS','SYSTEM', 'DELETE_CATALOG_ROLE') UNION ALL  SELECT GRANTEE|| ' has '||PRIVILEGE|| ' on '||TABLE_NAME AS VALUE FROM sys.DBA_TAB_PRIVS where owner='AUDSYS' AND PRIVILEGE='DELETE' AND grantee not in ('SYS','SYSTEM', 'DELETE_CATALOG_ROLE')

Change to STIG Rule: A query added by Oracle.

Description: The DBMS must protect audit data records and integrity by using cryptographic mechanisms.

Automation Logic:

SELECT 'Tablespace '||t.tablespace_name ||' holding audit data in '||t.table_name||' is not encrypted.' value
      FROM dba_tables t, dba_tablespaces ts
   WHERE (t.table_name ='AUD$' OR t.table_name='FGA_LOG$' OR t.owner= 'AUDSYS')
         AND t.tablespace_name = ts.tablespace_name
         AND ts.encrypted = 'NO'
        AND EXISTS (SELECT PARAMETER as value1 from SYS.V$OPTION WHERE PARAMETER='Unified Auditing' AND VALUE='TRUE' UNION select name as value1 from v$parameter where name='audit_trail' and UPPER(value) != 'NONE')

Change to STIG Rule: A query added by Oracle.

Description: The DBMS must protect the audit records generated, as a result of remote access to privileged accounts, and the execution of privileged functions.

Automation Logic:

SELECT GRANTEE||' has '||PRIVILEGE||' on '|| TABLE_NAME AS VALUE FROM sys.DBA_TAB_PRIVS where (table_name = 'AUD$' or table_name='FGA_LOG$') AND grantee not in ('SYS','SYSTEM', 'DELETE_CATALOG_ROLE') UNION ALL  SELECT GRANTEE|| ' has '||PRIVILEGE|| ' on '||TABLE_NAME AS VALUE FROM sys.DBA_TAB_PRIVS where owner='AUDSYS' AND grantee not in ('SYS','SYSTEM', 'DELETE_CATALOG_ROLE') UNION ALL  SELECT GRANTEE || ' has been granted with '||GRANTED_ROLE AS VALUE FROM sys.DBA_ROLE_PRIVS WHERE GRANTED_ROLE IN ('AUDIT_ADMIN','AUDIT_VIEWER','DELETE_CATALOG_ROLE') AND GRANTEE NOT IN ('SYS','SYSTEM','DBA')

Change to STIG Rule: Combined rule queries to check if audit records are being protected.

Description: The DBMS must support enforcement of logical access restrictions associated with changes to the DBMS configuration and to the database itself.

Automation Logic:

perl %scriptsDir%/umaskCheck.pl {OracleHome} 022

Change to STIG Rule: Script provided by Oracle.

Description: Database objects must be owned by accounts authorized for ownership.

Automation Logic:

SELECT 'Database objects are owned by unauthorized user '||OWNER value FROM ( SELECT OWNER, COUNT(*)  FROM DBA_OBJECTS
WHERE OWNER NOT IN ('PUBLIC', 'OUTLN', 'CTXSYS', 'SYSTEM', 'EXFSYS', 'DBSNMP', 'ORDSYS', 'ORDPLUGINS', 'APPQOSYS', 'XDB', 'IX', 'ORDDATA', 'SYS', 'WMSYS', 'MDSYS', 'OLAPSYS', 'SYSMAN', 'APEX_030200', 'FLOWS_FILES', 'SI_INFORMTN_SCHEMA', 'ORACLE_OCM', 'APPQOSSYS', 'PM', 'OE', 'SH', 'HR', 'ORACLE_OCM', 'SCOTT', 'OWBSYS_AUDIT', 'OWBSYS', 'BI','APEX_040200','DVF','DVSYS','LBACSYS','AUDSYS','GSMADMIN_INTERNAL','OJVMSYS') GROUP BY OWNER )

Change to STIG Rule: A query added by Oracle.

Description: Default demonstration and sample databases, database objects, and applications must be removed.

Automation Logic:

select distinct 'Demonstration account '||username||' found in database' value from dba_users where username in ('BI', 'HR', 'OE', 'PM', 'IX', 'SH', 'SCOTT')

Change to STIG Rule: A query added by Oracle.

Description: Use of external executables must be authorized.

Automation Logic:

SELECT owner||'.'||library_name||' is a library containing external procedure.' AS VALUE FROM ( select library_name,owner, '' grantee, '' privilege
from dba_libraries where file_spec is not null
minus
(
select library_name,o.name owner, '' grantee, '' privilege
 from dba_libraries l,
 sys.user$ o,
 sys.user$ ge,
 sys.obj$ obj,
 sys.objauth$ oa
 where l.owner=o.name
 and obj.owner#=o.user#
 and obj.name=l.library_name
 and oa.obj#=obj.obj#
 and ge.user#=oa.grantee#
 and l.file_spec is not null
))
union all

SELECT grantee||' has been granted with '||privilege||' on '||owner||'.'||library_name||' the library containing external procedures.' AS VALUE FROM (
select library_name,o.name owner, --obj.obj#,oa.privilege#,
 ge.name grantee,
 tpm.name privilege
 from dba_libraries l,
 sys.user$ o,
 sys.user$ ge,
 sys.obj$ obj,
 sys.objauth$ oa,
 sys.table_privilege_map tpm
 where l.owner=o.name
 and obj.owner#=o.user#
 and obj.name=l.library_name
 and oa.obj#=obj.obj#
 and ge.user#=oa.grantee#
 and tpm.privilege=oa.privilege#
 and l.file_spec is not null
 )

Change to STIG Rule: Made to be operated manually as query cannot be executed successfully because of special characters being added.

Description: Access to external executables must be disabled or restricted.

Automation Logic:

perl %scriptsDir%/externalExecs.pl {OracleHome}

Change to STIG Rule: Script provided by Oracle.

Description: The DBMS must have transaction journaling enabled.

Automation Logic:

select 'Database is in NOARCHIVELOG mode' value from v$database where log_mode != 'ARCHIVELOG' 

Change to STIG Rule: A query added by Oracle.

Description: The DBMS must use multifactor authentication for network access to privileged accounts.

Automation Logic:

perl %scriptsDir%/multiFactorAuth.pl {OracleHome}

Change to STIG Rule: Script provided by Oracle.

Description: The DBMS must use multifactor authentication for network access to non-privileged accounts.

Automation Logic:

perl %scriptsDir%/multiFactorAuth.pl {OracleHome}

Change to STIG Rule: Script provided by Oracle.

Description: The DBMS must use multifactor authentication for local access to privileged accounts.

Automation Logic:

perl %scriptsDir%/multiFactorAuth.pl {OracleHome}

Change to STIG Rule: Script provided by Oracle.

Description: The DBMS must use multifactor authentication for local access to non-privileged accounts.

Automation Logic:

perl %scriptsDir%/multiFactorAuth.pl {OracleHome}

Change to STIG Rule: Script provided by Oracle.

Description: The DBMS must use organization-defined replay-resistant authentication mechanisms for network access to privileged accounts.

Automation Logic:

perl %scriptsDir%/replayResistantAuthCheck.pl {OracleHome}

Change to STIG Rule: Script provided by Oracle.

Description: The DBMS must use organization-defined replay-resistant authentication mechanisms for network access to non-privileged accounts.

Automation Logic:

perl %scriptsDir%/replayResistantAuthCheck.pl {OracleHome}

Change to STIG Rule: Script provided by Oracle.

Description: The DBMS must support organizational requirements to disable user accounts after an organization-defined time period of inactivity.

Automation Logic:

select p.resource_name||' limit is set to '||p.limit||' for user '||u.username||' through profile '||p.profile AS
value from dba_profiles p, dba_users u,
(select limit as def_pwd_life_tm
from dba_profiles
where profile = 'DEFAULT'
and resource_name = 'PASSWORD_LIFE_TIME')
where p.resource_name = 'PASSWORD_LIFE_TIME'
and ((replace(p.limit, 'DEFAULT', def_pwd_life_tm) in
('UNLIMITED', 'NULL'))
or (lpad(replace(p.limit, 'DEFAULT', def_pwd_life_tm),40,'0') >
lpad('35',40,'0')))
  AND u.profile = p.profile
  AND u.account_status not like '%EXPIRED%LOCKED%' AND u.AUTHENTICATION_TYPE NOT IN ('GLOBAL','EXTERNAL')
UNION ALL
select 'Table SYS.LOGIN_AUDIT_INFO_ALL is not used' value FROM DUAL WHERE NOT EXISTS (select table_name from dba_tables where table_name='LOGIN_AUDIT_INFO_ALL')

Change to STIG Rule: A query added by Oracle.

Description: The DBMS must support organizational requirements to enforce minimum password length.

Automation Logic:

select p.resource_name||' is not set for user '||u.username||' through profile '||p.profile||' to check minimum password length' AS
value from sys.dba_profiles p, sys.dba_users u,
(select limit as def_pwd_verify_func
from sys.dba_profiles
where profile = 'DEFAULT'
and resource_name = 'PASSWORD_VERIFY_FUNCTION')
where p.resource_name = 'PASSWORD_VERIFY_FUNCTION'
and ((replace(p.limit, 'DEFAULT', def_pwd_verify_func) in ('', 'NULL'))) AND u.profile = p.profile
AND u.account_status not like '%EXPIRED%LOCKED%' and u.AUTHENTICATION_TYPE NOT IN ('EXTERNAL', 'GLOBAL')

Change to STIG Rule: A query added by Oracle.

Description: The DBMS must support organizational requirements to prohibit password reuse for the organization-defined number of generations.

Automation Logic:

elect profile|| ' profile has PASSWORD_REUSE_TIME set to '||limit
value from dba_profiles p,
(select limit as def_pwd_reuse_tm
from dba_profiles
where profile = 'DEFAULT'
and resource_name = 'PASSWORD_REUSE_TIME')
where p.resource_name = 'PASSWORD_REUSE_TIME'
and ((replace(p.limit, 'DEFAULT', def_pwd_reuse_tm) in
('UNLIMITED', NULL))
or (lpad(replace(p.limit, 'DEFAULT', def_pwd_reuse_tm),40,'0') <
lpad('6',40,'0')))
UNION
SELECT profile|| ' profile has PASSWORD_REUSE_MAX set to '||limit value FROM dba_profiles
 WHERE resource_name = 'PASSWORD_REUSE_MAX'
   AND (limit IS NULL 
        OR limit = 'UNLIMITED') 

Change to STIG Rule: A query added by Oracle.

Description: The DBMS must support organizational requirements to enforce password complexity by the number of upper-case characters used.

Automation Logic:

select p.resource_name||' is not set for user '||u.username||' through profile '||p.profile||' to check number of upper-case characters used' AS value from sys.dba_profiles p, sys.dba_users u, (select limit as def_pwd_verify_func from sys.dba_profiles where profile = 'DEFAULT' and resource_name = 'PASSWORD_VERIFY_FUNCTION') where p.resource_name = 'PASSWORD_VERIFY_FUNCTION' and ((replace(p.limit, 'DEFAULT', def_pwd_verify_func) in ('', 'NULL'))) AND u.profile = p.profile AND u.account_status not like '%EXPIRED%LOCKED%' and u.AUTHENTICATION_TYPE NOT IN ('EXTERNAL', 'GLOBAL')

Change to STIG Rule: A query added by Oracle.

Description: The DBMS must support organizational requirements to enforce password complexity by the number of lower-case characters used.

Automation Logic:

select p.resource_name||' is not set for user '||u.username||' through profile '||p.profile||' to check number of lower-case characters used' AS
value from sys.dba_profiles p, sys.dba_users u,
(select limit as def_pwd_verify_func
from sys.dba_profiles
where profile = 'DEFAULT'
and resource_name = 'PASSWORD_VERIFY_FUNCTION')
where p.resource_name = 'PASSWORD_VERIFY_FUNCTION'
and ((replace(p.limit, 'DEFAULT', def_pwd_verify_func) in ('', 'NULL'))) AND u.profile = p.profile
AND u.account_status not like '%EXPIRED%LOCKED%' and u.AUTHENTICATION_TYPE NOT IN ('EXTERNAL', 'GLOBAL')

Change to STIG Rule: A query added by Oracle.

Description: The DBMS must support organizational requirements to enforce password complexity by the number of numeric characters used.

Automation Logic:

select p.resource_name||' is not set for user '||u.username||' through profile '||p.profile||' to check number of numeric characters used' AS
value from sys.dba_profiles p, sys.dba_users u,
(select limit as def_pwd_verify_func
from sys.dba_profiles
where profile = 'DEFAULT'
and resource_name = 'PASSWORD_VERIFY_FUNCTION')
where p.resource_name = 'PASSWORD_VERIFY_FUNCTION'
and ((replace(p.limit, 'DEFAULT', def_pwd_verify_func) in ('', 'NULL'))) AND u.profile = p.profile
AND u.account_status not like '%EXPIRED%LOCKED%' and u.AUTHENTICATION_TYPE NOT IN ('EXTERNAL', 'GLOBAL')

Change to STIG Rule: A query added by Oracle.

Description: The DBMS must support organizational requirements to enforce password complexity by the number of special characters used.

Automation Logic:

select p.resource_name||' is not set for user '||u.username||' through profile '||p.profile||' to check number of special characters used' AS
value from sys.dba_profiles p, sys.dba_users u,
(select limit as def_pwd_verify_func
from sys.dba_profiles
where profile = 'DEFAULT'
and resource_name = 'PASSWORD_VERIFY_FUNCTION')
where p.resource_name = 'PASSWORD_VERIFY_FUNCTION'
and ((replace(p.limit, 'DEFAULT', def_pwd_verify_func) in ('', 'NULL'))) AND u.profile = p.profile
AND u.account_status not like '%EXPIRED%LOCKED%' and u.AUTHENTICATION_TYPE NOT IN ('EXTERNAL', 'GLOBAL')

Change to STIG Rule: A query added by Oracle.

Description: The DBMS must support organizational requirements to enforce the number of characters that get changed when passwords are changed.

Automation Logic:

select p.resource_name||' is not set for user '||u.username||' through profile '||p.profile||' to check number of characters changed on password reset' AS
value from sys.dba_profiles p, sys.dba_users u,
(select limit as def_pwd_verify_func
from sys.dba_profiles
where profile = 'DEFAULT'
and resource_name = 'PASSWORD_VERIFY_FUNCTION')
where p.resource_name = 'PASSWORD_VERIFY_FUNCTION'
and ((replace(p.limit, 'DEFAULT', def_pwd_verify_func) in ('', 'NULL'))) AND u.profile = p.profile
AND u.account_status not like '%EXPIRED%LOCKED%' and u.AUTHENTICATION_TYPE NOT IN ('EXTERNAL', 'GLOBAL')

Change to STIG Rule: A query added by Oracle.

Description: The DBMS must enforce maximum lifetime restrictions on password.

Automation Logic:

select p.profile||' has PASSWORD_LIFE_TIME set to '||p.limit||'.'
value from dba_profiles p,
(select limit as def_pwd_life_tm
from dba_profiles
where profile = 'DEFAULT'
and resource_name = 'PASSWORD_LIFE_TIME')
where p.resource_name = 'PASSWORD_LIFE_TIME'
and ((replace(p.limit, 'DEFAULT', def_pwd_life_tm) in
('UNLIMITED', NULL))
or (lpad(replace(p.limit, 'DEFAULT', def_pwd_life_tm),40,'0') >
lpad('35',40,'0')))

Change to STIG Rule: A query added by Oracle.

Description: The DBMS must use NIST-validated FIPS 140-2-compliant cryptography for authentication mechanisms.

Automation Logic:

perl %scriptsDir%/fipsCompliantCheck.pl {OracleHome}

Change to STIG Rule: Script provided by Oracle.

Description: The DBMS must support organizational requirements to encrypt information stored in the database and information extracted or derived from the database and stored on digital media.

Automation Logic:

select 'Parameter '||name||' is set to '||value  AS VALUE from  SYS.V$PARAMETER where name='DBFIPS_140' and value='FALSE'
UNION SELECT 'DBMS must support organizational requirements to encrypt information stored in the database and information extracted or derived from the database' as value FROM DUAL WHERE NOT EXISTS(SELECT NAME FROM SYS.V$PARAMETER where name='DBFIPS_140')

Change to STIG Rule: A query added by Oracle.

Description: The DBMS must terminate the network connection associated with a communications session at the end of the session or 15 minutes of inactivity.

Automation Logic:

select p.resource_name||' is set to '||p.limit||' for user '||u.username||' through profile '||p.profile AS value from sys.DBA_PROFILES p, sys.dba_users u,(SELECT limit as def_idle_time FROM sys.DBA_PROFILES where profile='DEFAULT' AND RESOURCE_NAME='IDLE_TIME') d where p.resource_name ='IDLE_TIME' and (DECODE (p.limit, 'DEFAULT', d.def_idle_time, limit) = 'UNLIMITED' OR (lpad(replace(p.limit, 'DEFAULT', d.def_idle_time),40,'0') > lpad('15',40,'0'))) and u.profile = p.profile and u.account_status not like '%EXPIRED%LOCKED%'

Change to STIG Rule: A query added by Oracle.

Description: The DBMS must implement required cryptographic protections using cryptographic modules complying with applicable federal laws, executive orders, directives, policies, regulations, standards, and guidance.

Automation Logic:

perl %scriptsDir%/cryptoProtectionCheck.pl {OracleHome} {MachineName} {Port} {Protocol} {SID} {UserName} {password} {Role}

Change to STIG Rule: Script provided by Oracle.

Description: Database data files containing sensitive information must be encrypted.

Automation Logic:

select 'Parameter '||name||' is set to '||value  AS VALUE from  SYS.V$PARAMETER where name='DBFIPS_140' and value='FALSE'
UNION SELECT 'Database data files containing sensitive information must be encrypted.' as value FROM DUAL WHERE NOT EXISTS(SELECT NAME FROM SYS.V$PARAMETER where name='DBFIPS_140')

Change to STIG Rule: A query added by Oracle.

Description: The DBMS must protect the integrity of publicly available information and applications.

Automation Logic:

SELECT TABLESPACE_NAME||' tablespace is not READ ONLY. ' AS VALUE FROM sys.DBA_TABLESPACES WHERE STATUS != 'READ ONLY' AND TABLESPACE_NAME NOT IN ('SYSTEM','SYSAUX','UD1','TEMP','SYSEXT','UNDOTBS')

Change to STIG Rule: A query added by Oracle.

Description: The DBMS must terminate user sessions upon user logoff or any other organization or policy-defined session termination events, such as exceeding idle time limit.

Automation Logic:

SELECT resource_name||' is set to '||limit||' for user '||username||' through profile '||profile AS value FROM (select u.username,p.profile,p.resource_name,p.limit,u.account_status from sys.DBA_PROFILES p, sys.dba_users u,(SELECT limit as def_idle_time FROM sys.DBA_PROFILES where profile='DEFAULT' AND RESOURCE_NAME='IDLE_TIME') d where p.resource_name ='IDLE_TIME' and (DECODE (p.limit, 'DEFAULT', d.def_idle_time, limit) = 'UNLIMITED' OR (lpad(replace(p.limit, 'DEFAULT', d.def_idle_time),40,'0') > lpad('15',40,'0'))) and u.profile = p.profile
UNION ALL
select u.username,p.profile, p.resource_name, p.limit,u.account_status from sys.DBA_PROFILES p, sys.dba_users u where p.resource_name='CONNECT_TIME' and DECODE (limit, 'DEFAULT', (SELECT limit from DBA_PROFILES d where d.resource_name=p.resource_name and profile='DEFAULT'), limit) = 'UNLIMITED' and u.profile = p.profile) where account_status not like '%EXPIRED%LOCKED%' 

Change to STIG Rule: A query added by Oracle.

Description: The DBMS must fail to a known safe state for defined types of failures.

Automation Logic:

select 'Database is in NOARCHIVELOG mode' value from v$database where log_mode != 'ARCHIVELOG'

Change to STIG Rule: A query added by Oracle.

Description: The DBMS must take needed steps to protect data at rest and ensure confidentiality and integrity of application data.

Automation Logic:

SELECT 'Table '||a.owner||'.'||a.table_name||' in tablespace '||a.tablespace_name||' is not protected by means of encryption.' AS VALUE
 FROM dba_tables a WHERE a.tablespace_name NOT IN (select t.name from v$tablespace t, v$encrypted_tablespaces e where t.ts# = e.ts# ) AND a.tablespace_name NOT IN ('SYSTEM','SYSAUX','UD1','TEMP','SYSEXT','UNDOTBS') AND ROWNUM < 200

Change to STIG Rule: Modified the query to exclude -'SYSTEM', 'SYSAUX', 'UD1', 'TEMP', 'SYSEXT', and 'UNDOTBS'.

Description: The DBMS must employ cryptographic mechanisms preventing the unauthorized disclosure of information at rest unless the data is otherwise protected by alternative physical measures.

Automation Logic:

SELECT 'Table '||a.owner||'.'||a.table_name||' in tablespace '||a.tablespace_name||' is not protected by means of encryption.' AS VALUE
 FROM dba_tables a WHERE a.tablespace_name NOT IN (select t.name from v$tablespace t, v$encrypted_tablespaces e where t.ts# = e.ts# ) AND a.tablespace_name NOT IN ('SYSTEM','SYSAUX','UD1','TEMP','SYSEXT','UNDOTBS') AND ROWNUM < 200

Change to STIG Rule: Modified the query to exclude -'SYSTEM', 'SYSAUX', 'UD1', 'TEMP', 'SYSEXT', and 'UNDOTBS'.

Description: The DBMS must check the validity of data inputs.

Automation Logic:

select owner, 'Constraint '||owner ||'.'||constraint_name || ' is '|| status||' '|| validated value from dba_constraints where (status='DISABLED' or validated='NOT VALIDATED') and owner not in ('SYS', 'SYSMAN', 'SH', 'SYSTEM', 'PM', 'OE', 'SH', 'HR', 'IX', 'OLAPSYS', 'ORDDATA', 'CTXSYS', 'WMSYS')

Change to STIG Rule: A query added by Oracle.

Description: The DBMS must notify appropriate individuals when accounts are created.

Automation Logic:

SELECT * FROM (
     SELECT CASE UPPER(value) WHEN 'FALSE'
     THEN
      (SELECT CASE UPPER(value) WHEN 'NONE' 
            THEN
                name||' parameter is set to '||value||'.'
            ELSE
                (SELECT 'Account creation is not being audited' value from sys.dba_stmt_audit_opts where AUDIT_OPTION='CREATE USER' having count(*)=0)
            END AS VALUE FROM  v$parameter where name='audit_trail' )
     ELSE
        (SELECT CASE UPPER(value) WHEN 'NONE' 
           THEN
        (SELECT 'Account creation is not being audited' from audit_unified_policies where AUDIT_OPTION='CREATE USER' AND AUDIT_OPTION_TYPE='STANDARD ACTION' AND AUDIT_CONDITION!='NONE' having count(*)=0)
     ELSE 
              (SELECT DISTINCT value FROM (SELECT 'Account creation is not being audited' value from audit_unified_policies where AUDIT_OPTION='CREATE USER' AND AUDIT_OPTION_TYPE='STANDARD ACTION' AND AUDIT_CONDITION!='NONE' having count(*)=0
          UNION 
      SELECT 'Account creation is not being audited' value from sys.dba_stmt_audit_opts where AUDIT_OPTION='CREATE USER' having count(*)=0  )) 
            END AS VALUE FROM  v$parameter where name='audit_trail' )      
     END AS value FROM v$option WHERE parameter  ='Unified Auditing') where VALUE IS NOT NULL;

Change to STIG Rule: Combined to check if audit is enabled by means of either Traditional or Unified system and to check if account creation is being audited. Need to manually check if they are being notified.

Description: The DBMS must notify appropriate individuals when accounts are modified.

Automation Logic:

SELECT * FROM (
     SELECT CASE UPPER(value) WHEN 'FALSE'
     THEN
      (SELECT CASE UPPER(value) WHEN 'NONE' 
            THEN
                name||' parameter is set to '||value||'.'
            ELSE
                (SELECT 'Account modification is not being audited' value from sys.dba_stmt_audit_opts where AUDIT_OPTION='ALTER USER' having count(*)=0)
            END AS VALUE FROM  v$parameter where name='audit_trail' )
     ELSE
        (SELECT CASE UPPER(value) WHEN 'NONE' 
           THEN
        (SELECT 'Account modification is not being audited' from audit_unified_policies where AUDIT_OPTION='ALTER USER' AND AUDIT_OPTION_TYPE='STANDARD ACTION' AND AUDIT_CONDITION!='NONE' having count(*)=0)
     ELSE 
              (SELECT DISTINCT value FROM (SELECT 'Account modification is not being audited' value from audit_unified_policies where AUDIT_OPTION='ALTER USER' AND AUDIT_OPTION_TYPE='STANDARD ACTION' AND AUDIT_CONDITION!='NONE' having count(*)=0
          UNION 
      SELECT 'Account modification is not being audited' value from sys.dba_stmt_audit_opts where AUDIT_OPTION='ALTER USER' having count(*)=0  )) 
            END AS VALUE FROM  v$parameter where name='audit_trail' )      
     END AS value FROM v$option WHERE parameter  ='Unified Auditing') where VALUE IS NOT NULL;

Change to STIG Rule: Combined to check if audit is enabled by means of either Traditional or Unified system and to check if account modification is being audited. Need to manually check if it is notified.

Description: The DBMS must notify appropriate individuals when account disabling actions are taken.

Automation Logic:

SELECT * FROM (
     SELECT CASE UPPER(value) WHEN 'FALSE'
     THEN
      (SELECT CASE UPPER(value) WHEN 'NONE' 
            THEN
                name||' parameter is set to '||value||'.'
            ELSE
                (SELECT 'Account disabling is not being audited' value from sys.dba_stmt_audit_opts where AUDIT_OPTION='ALTER USER' having count(*)=0)
            END AS VALUE FROM  v$parameter where name='audit_trail' )
     ELSE
        (SELECT CASE UPPER(value) WHEN 'NONE' 
           THEN
        (SELECT 'Account disabling is not being audited' from audit_unified_policies where AUDIT_OPTION='ALTER USER' AND AUDIT_OPTION_TYPE='STANDARD ACTION' AND AUDIT_CONDITION!='NONE' having count(*)=0)
     ELSE 
              (SELECT DISTINCT value FROM (SELECT 'Account disabling is not being audited' value from audit_unified_policies where AUDIT_OPTION='ALTER USER' AND AUDIT_OPTION_TYPE='STANDARD ACTION' AND AUDIT_CONDITION!='NONE' having count(*)=0
          UNION 
      SELECT 'Account disabling is not being audited' value from sys.dba_stmt_audit_opts where AUDIT_OPTION='ALTER USER' having count(*)=0  )) 
            END AS VALUE FROM  v$parameter where name='audit_trail' )      
     END AS value FROM v$option WHERE parameter  ='Unified Auditing') where VALUE IS NOT NULL;

Change to STIG Rule: Combined to check if audit is enabled by means of either Traditional or Unified system and to check if account disabling is being audited. Need to manually check if it is notified.

Description: The DBMS must notify appropriate individuals when accounts are terminated.

Automation Logic:

SELECT * FROM (
     SELECT CASE UPPER(value) WHEN 'FALSE'
     THEN
      (SELECT CASE UPPER(value) WHEN 'NONE' 
            THEN
                name||' parameter is set to '||value||'.'
            ELSE
                (SELECT 'Account termination is not being audited' value from sys.dba_stmt_audit_opts where AUDIT_OPTION='DROP USER' having count(*)=0)
            END AS VALUE FROM  v$parameter where name='audit_trail' )
     ELSE
        (SELECT CASE UPPER(value) WHEN 'NONE' 
           THEN
        (SELECT 'Account termination is not being audited' from audit_unified_policies where AUDIT_OPTION='DROP USER' AND AUDIT_OPTION_TYPE='STANDARD ACTION' AND AUDIT_CONDITION!='NONE' having count(*)=0)
     ELSE 
              (SELECT DISTINCT value FROM (SELECT 'Account termination is not being audited' value from audit_unified_policies where AUDIT_OPTION='DROP USER' AND AUDIT_OPTION_TYPE='STANDARD ACTION' AND AUDIT_CONDITION!='NONE' having count(*)=0
          UNION 
      SELECT 'Account termination is not being audited' value from sys.dba_stmt_audit_opts where AUDIT_OPTION='DROP USER' having count(*)=0  )) 
            END AS VALUE FROM  v$parameter where name='audit_trail' )      
     END AS value FROM v$option WHERE parameter  ='Unified Auditing') where VALUE IS NOT NULL;

Change to STIG Rule: Combined to check if audit is enabled by means of either Traditional or Unified system and to check if account termination is being audited. Need to manually check if it is notified.

Description: The DBMS must support organizational requirements to implement separation of duties through assigned information access authorizations.

Automation Logic:

select grantee ||' has '||privilege||' privilege on '|| table_name value
FROM dba_tab_privs
WHERE grantee NOT IN (
SELECT role
FROM dba_roles)
  and grantee not in ('SYSKM', 'PUBLIC', 'SYSBACKUP', 'CTXSYS', 'EXFSYS', 'DVSYS', 'SYSTEM', 'AUDSYS', 'DBSNMP', 'ORDSYS',
                      'XDB', 'SYSDG', 'ORDDATA', 'APPQOSSYS', 'SYS', 'WMSYS', 'LBACSYS', 'MDSYS', 'ORACLE_OCM', 'OWBSYS_AUDIT' ,'DIP' ,'SPATIAL_WFS_ADMIN_USR' ,'FLOWS_FILES' ,'HR' ,'MGMT_VIEW' ,'OLAPSYS' ,'OUTLN' ,'OWBSYS' ,'SPATIAL_CSW_ADMIN_USR' ,'APEX_030200' ,'SCOTT' ,'APEX_PUBLIC_USER' ,'MDDATA' ,'OE' ,'ORDPLUGINS' ,'PM' ,'SH' ,'SYSMAN' ,'BI' ,'IX' ,'ANONYMOUS' ,'SI_INFORMTN_SCHEMA','DVF','GSMADMIN_INTERNAL','APEX_040200','OJVMSYS','GSMCATUSER')
UNION
select 'User '|| grantee ||' is granted '||privilege||' privilege ' value
 from dba_sys_privs
where grantee not in ( select role from dba_roles)
  and grantee not in ('SYSKM', 'PUBLIC', 'SYSBACKUP', 'CTXSYS', 'EXFSYS', 'DVSYS', 'SYSTEM', 'AUDSYS', 'DBSNMP', 'ORDSYS',
                      'XDB', 'SYSDG', 'ORDDATA', 'APPQOSSYS', 'SYS', 'WMSYS', 'LBACSYS', 'MDSYS', 'ORACLE_OCM', 'OWBSYS_AUDIT' ,'DIP' ,'SPATIAL_WFS_ADMIN_USR' ,'FLOWS_FILES' ,'HR' ,'MGMT_VIEW' ,'OLAPSYS' ,'OUTLN' ,'OWBSYS' ,'SPATIAL_CSW_ADMIN_USR' ,'APEX_030200' ,'SCOTT' ,'APEX_PUBLIC_USER' ,'MDDATA' ,'OE' ,'ORDPLUGINS' ,'PM' ,'SH' ,'SYSMAN' ,'BI' ,'IX' ,'ANONYMOUS' ,'SI_INFORMTN_SCHEMA','DVF','GSMADMIN_INTERNAL','APEX_040200','OJVMSYS','GSMCATUSER')

Change to STIG Rule: Changed query to exclude oracle default users/roles.

Description: The DBMS must display an approved system use notification message or banner before granting access to the database.

Automation Logic:

perl %scriptsDir%/bannerText.pl {OracleHome}

Change to STIG Rule: Script provided by Oracle.

Description: The DBMS must manage excess capacity, bandwidth, or other redundancy to limit the effects of information flooding types of Denial of Service (DoS) attacks.

Automation Logic:

select p.resource_name||' is set to '||p.limit||' for user '||u.username||' through profile '||p.profile AS value from sys.DBA_PROFILES p, sys.dba_users u,(SELECT limit as def_limit, resource_name FROM sys.DBA_PROFILES where profile='DEFAULT' ) d where p.resource_name IN ('CPU_PER_SESSION','LOGICAL_READS_PER_SESSION','CONNECT_TIME','PRIVATE_SGA') and (DECODE (p.limit, 'DEFAULT', d.def_limit, limit) = 'UNLIMITED' OR (p.resource_name='CPU_PER_SESSION' AND (lpad(replace(p.limit, 'DEFAULT', d.def_limit),40,'0') > lpad('6000',40,'0'))) OR (p.resource_name='LOGICAL_READS_PER_SESSION' AND (lpad(replace(p.limit, 'DEFAULT', d.def_limit),40,'0') > lpad('1000',40,'0'))) OR (p.resource_name='CONNECT_TIME' AND (lpad(replace(p.limit, 'DEFAULT', d.def_limit),40,'0') > lpad('30',40,'0'))) OR (p.resource_name='PRIVATE_SGA' AND (lpad(replace(p.limit, 'DEFAULT', d.def_limit),40,'0') > lpad('102400',40,'0'))) and u.profile = p.profile AND d.RESOURCE_NAME=p.resource_name AND u.account_status not like '%EXPIRED%LOCKED%'

Change to STIG Rule: A query added by Oracle.

Description: The DBMS must limit the use of resources by priority and not impede the host from servicing processes designated as a higher-priority.

Automation Logic:

select p.resource_name||' is set to '||p.limit||' for user '||u.username||' through profile '||p.profile AS value from DBA_PROFILES p, dba_users u
where p.resource_name IN ('SESSIONS_PER_USER', 'CPU_PER_SESSION', 'CPU_PER_CALL', 'CONNECT_TIME', 'IDLE_TIME', 'LOGICAL_READS_PER_SESSION', 'LOGICAL_READS_PER_CALL', 'PRIVATE_SGA', 'COMPOSITE_LIMIT')
  and DECODE (p.limit, 'DEFAULT', (SELECT d.limit from DBA_PROFILES d where d.resource_name=p.resource_name and d.profile='DEFAULT'), p.limit) = 'UNLIMITED' and u.profile = p.profile and u.account_status not like '%EXPIRED%LOCKED%'

Change to STIG Rule: A query added by Oracle.

Description: DBMS default accounts must be protected from misuse.

Automation Logic:

SELECT 'Account '||username||' is OPEN.' as value FROM sys.dba_users where ACCOUNT_STATUS NOT LIKE '%LOCKED%' AND USERNAME NOT IN ('SYS','SYSTEM','SYSMAN') AND ROWNUM < 200

Change to STIG Rule: A query added by Oracle.

Description: Database software directories, including DBMS configuration files, must be stored in dedicated directories, or DASD pools, separate from the host OS and other applications.

Automation Logic:

perl %scriptsDir%/oracleFiles.pl {OracleHome}

Change to STIG Rule: Script provided by Oracle.

Description: The DBMS must protect against an individual who uses a shared account falsely denying having performed a particular action.

Automation Logic:

SELECT * FROM ( SELECT CASE WHEN ((SELECT count(*) from SYS.V$OPTION WHERE PARAMETER='Unified Auditing' AND VALUE='FALSE')=1) THEN (SELECT name||' parameter is set to '||value||'.' value from sys.v$parameter where name='audit_trail' and value='NONE')
    END AS VALUE FROM DUAL) WHERE VALUE IS NOT NULL

Change to STIG Rule: A query added by Oracle.

Description: The directory assigned to the AUDIT_FILE_DEST parameter must be protected from unauthorized access and must be stored in a dedicated directory or disk partition separate from software or other application files.

Automation Logic:

perl %scriptsDir%/auditFileDestPerm.pl {OracleHome} {MachineName} {Port} {Protocol} {SID} {UserName} {password} {Role}

Change to STIG Rule: Script provided by Oracle.

Description: The DBMS must limit the number of concurrent sessions for each system account to an organization-defined number of sessions.

Automation Logic:

select p.resource_name||' is set to '||p.limit||' for user '||u.username||' through profile '||p.profile AS value from sys.DBA_PROFILES p, sys.dba_users u,(SELECT limit as def_limit FROM sys.DBA_PROFILES where profile='DEFAULT' AND RESOURCE_NAME='SESSIONS_PER_USER') d where p.resource_name ='SESSIONS_PER_USER' and DECODE (p.limit, 'DEFAULT', d.def_limit, limit) = 'UNLIMITED' and u.profile = p.profile and u.account_status not like '%EXPIRED%LOCKED%'

Change to STIG Rule: A query added by Oracle.

Name: Application objects should be owned by accounts authorized for ownership

Collection Query:

(select distinct 'Unauthorized user '||owner||' owns application objects in the database.'  from dba_objects
where owner not in
('ANONYMOUS','AURORA$JIS$UTILITY$',
'AURORA$ORB$UNAUTHENTICATED',
'CTXSYS','DBSNMP','DIP','DVF','DVSYS','EXFSYS','LBACSYS','MDDATA',
'MDSYS','MGMT_VIEW','ODM','ODM_MTR',
'OLAPSYS','ORDPLUGINS', 'ORDSYS',
'OSE$HTTP$ADMIN','OUTLN','PERFSTAT',
'PUBLIC','REPADMIN','RMAN','SI_INFORMTN_SCHEMA',
'SYS','SYSMAN','SYSTEM','TRACESVR',
'TSMSYSWK_TEST','WKPROXY','WKSYS',
'WKUSER','WMSYS','XDB', 'OWBSYS', 'SCOTT', 'ORACLE_OCM', 'ORDDATA', 'APEX_030200', 'OWBSYS_AUDIT', 'APPQOSSYS', 'FLOWS_FILES')
and owner not in
(select grantee from dba_role_privs where granted_role='DBA'))

Change to STIG Rule: Added Default Users/Roles

Name: Production databases should be protected from unauthorized access by developers on shared production/development host systems.

Collection Query:

select 'User/Role '||grantee||' granted '||privilege||' on production system' from dba_sys_privs
where (privilege like 'CREATE%' or privilege like 'ALTER%'
or privilege like 'DROP%')
and privilege <> 'CREATE SESSION'
and grantee not in
('ANONYMOUS','AURORA$JIS$UTILITY$',
'AURORA$ORB$UNAUTHENTICATED','CTXSYS','DBSNMP','DIP',
'DVF','DVSYS','EXFSYS','LBACSYS','MDDATA','MDSYS','MGMT_VIEW',
'ODM','ODM_MTR','OLAPSYS','ORDPLUGINS','ORDSYS',
'OSE$HTTP$ADMIN','OUTLN','PERFSTAT','PUBLIC','REPADMIN',
'RMAN','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM',
'TRACESVR','TSMSYSWK_TEST','WKPROXY','WKSYS','WKUSER',
'WMSYS','XDB', 'APEX_030200', 'APPQOSSYS', 'AQ_ADMINISTRATOR_ROLE','DATAPUMP_EXP_FULL_DATABASE', 
'DBA', 'EXP_FULL_DATABASE', 'FLOWS_FILES', 'IMP_FULL_DATABASE', 
'DATAPUMP_IMP_FULL_DATABASE', 'OEM_ADVISOR', 'OEM_MONITOR', 'OLAP_DBA', 
'OLAP_USER', 'OWB$CLIENT', 'OWBSYS', 'OWBSYS_AUDIT', 'RECOVERY_CATALOG_OWNER',
'RESOURCE', 'SCHEDULER_ADMIN', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR')
order by 1;

Change to STIG Rule: Added Default Users/Roles.

Name: DBMS login accounts require passwords to meet complexity requirements.

Collection Query:

select profile||': '||limit
from dba_profiles,
(select limit as def_pwd_verify_func
from dba_profiles
where resource_name='PASSWORD_VERIFY_FUNCTION'
and profile='DEFAULT')
where resource_name='PASSWORD_VERIFY_FUNCTION'
and replace(limit, 'DEFAULT', def_pwd_verify_func) in
('UNLIMITED', 'NULL')

Change to STIG Rule: Incorrect query. Replaced NULL with string 'NULL'.

Name: Custom and GOTS application source code stored in the database should be protected with encryption or encoding.

Collection Query:

(select 'Application source code of '||owner||'.'||name||' is not encrypted.' 
from dba_source
where line=1 and owner not in('SYS', 'CTXSYS', 'MDSYS', 'ODM', 'OE', 'OLAPSYS', 'ORDPLUGINS',
'ORDSYS', 'OUTLN', 'PM', 'QS_ADM', 'RMAN', 'SYSTEM', 'WKSYS',
'WMSYS', 'XDB', 'APEX_030200', 'SYSMAN', 'ORACLE_OCM', 'DBSNMP', 'EXFSYS' )
and owner not like 'OEM%'
and text not like '%wrapped%'
and type in ('PROCEDURE', 'FUNCTION', 'PACKAGE BODY'))

Change to STIG Rule: Added default users.

Name: Database privileged role assignments should be restricted to IAO-authorized DBMS accounts.

Collection Query:

select 'Privileged role '||granted_role||' is assigned to user '||grantee details
from dba_role_privs
where grantee not in
('ANONYMOUS','AURORA$JIS$UTILITY$',
'AURORA$ORB$UNAUTHENTICATED','CTXSYS','DBSNMP','DIP',
'DMSYS','DVF','DVSYS','EXFSYS','LBACSYS','MDDATA','MDSYS',
'MGMT_VIEW','ODM','ODM_MTR','OLAPSYS','ORDPLUGINS','ORDSYS',
'OSE$HTTP$ADMIN','OUTLN','PERFSTAT','REPADMIN','RMAN',
'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TRACESVR',
'TSMSYS','WK_TEST','WKPROXY','WKSYS','WKUSER','WMSYS','XDB', 'OEM_MONITOR')
and grantee not in
('DBA', 'OLAP_USER', 'IP', 'ORASSO_PUBLIC',
'PORTAL_PUBLIC', 'DATAPUMP_EXP_FULL_DATABASE',
'DATAPUMP_IMP_FULL_DATABASE', 'EXP_FULL_DATABASE',
'IMP_FULL_DATABASE', 'OLAP_DBA', 'EXECUTE_CATALOG_ROLE',
'SELECT_CATALOG_ROLE', 'JAVASYSPRIV')
and grantee not in
(select grantee from dba_role_privs where granted_role = 'DBA')
and grantee not in (select distinct owner from dba_objects)
and granted_role in
('AQ_ADMINISTRATOR_ROLE','AQ_USER_ROLE',
'CTXAPP',
'DELETE_CATALOG_ROLE','EJBCLIENT','EXECUTE_CATALOG_ROLE',
'EXP_FULL_DATABASE','GATHER_SYSTEM_STATISTICS',
'GLOBAL_AQ_USER_ROLE','HS_ADMIN_ROLE', 'IMP_FULL
DATABASE','JAVADEBUGPRIV','JAVAIDPRIV',
'JAVASYSPRIV','JAVAUSERPRIV','JAVA_ADMIN','JAVA_DEPLOY',
'LOGSTDBY_ADMINISTRATOR','OEM_MONITOR','OLAP_DBA',
'RECOVERY_CATALOG_OWNER',
'SALES_HISTORY_ROLE','SELECT_CATALOG_ROLE','WKUSER',
'WM_ADMIN_ROLE','XDBADMIN')
and granted_role not in ('CONNECT', 'RESOURCE', 'AUTHENTICATEDUSER')
order by 1;

Change to STIG Rule: Added default users.

Name: Administrative privileges should be assigned to database accounts via database roles.

Collection Query:

select 'Grantee '||grantee||' is directly granted '||privilege||' privilege. The privilege should be granted via a role.'
from dba_sys_privs
where grantee not in
('SYS', 'SYSTEM', 'SYSMAN', 'CTXSYS', 'MDSYS', 'WKSYS', 'ANONYMOUS', 'APEX_030200',
'APEX_PUBLIC_USER', 'FLOWS_FILES', 'OUTLN', 'DIP', 'APPQOSSYS', 'WMSYS',
'OLAPSYS', 'ORACLE_OCM', 'OWBSYS_AUDIT', 'DBSNMP', 'XDB', 'EXFSYS', 
'SPATIAL_WFS_ADMIN_USR', 'SPATIAL_CSW_ADMIN_USR', 'OWBSYS', 'OWBSYS_AUDIT')
and grantee not in
(select distinct granted_role from dba_role_privs)
and privilege <> 'UNLIMITED TABLESPACE'
order by 1

Change to STIG Rule: Added Default Users.

Name: DBMS application users should not be granted administrative privileges to the DBMS.

Collection Query:

select 'Application user '||grantee||' has administrative privilege  '||privilege||' on '||owner||'.'|| table_name from dba_tab_privs
where privilege in ('ALTER', 'REFERENCES', 'INDEX')
and grantee not in ('DBA', 'SYS', 'SYSTEM', 'LBACSYS', 'XDBADMIN', 'ANONYMOUS',
'APEX_PUBLIC_USER', 'CSW_USR_ROLE', 'WFS_USR_ROLE', 'SPATIAL_WFS_ADMIN', 
'SPATIAL_WFS_ADMIN_USR', 'SPATIAL_CSW_ADMIN', 'SPATIAL_CSW_ADMIN_USR')
and table_name not in
('SDO_IDX_TAB_SEQUENCE', 'XDB$ACL', 'XDB_ADMIN')
and grantee not in
(select grantee from dba_role_privs where granted_role = 'DBA')
and grantee not in (select distinct owner from dba_objects) order by 1

Change to STIG Rule: Added default users.

Name: Application users privileges should be restricted to assignment using application user roles.

Collection Query:

select 'User '||grantee||' has direct privilege '||privilege||' on the table '||owner||'.'||table_name||'. The privilege should be granted via a role.'
from dba_tab_privs where grantee not in
(select role from dba_roles)
and grantee not in
('APEX_PUBLIC_USER', 'AURORA$JIS$UTILITY$', 'CTXSYS',
'DBSNMP', 'EXFSYS', 'FLOWS_030000', 'FLOWS_FILES',
'LBACSYS', 'MDSYS', 'MGMT_VIEW', 'ODM', 'OLAPSYS',
'ORACLE_OCM', 'ORDPLUGINS', 'ORDSYS',
'OSE$HTTP$ADMIN', 'OUTLN', 'OWBSYS', 'PERFSTAT',
'PUBLIC', 'REPADMIN', 'SYS', 'SYSMAN', 'SYSTEM',
'WKSYS', 'WMSYS', 'XDB', 'ANONYMOUS', 'APEX_030200', 'APEX_PUBLIC_USER',
'APPQOSSYS', 'CSW_USR_ROLE', 'WFS_USR_ROLE', 'SPATIAL_WFS_ADMIN', 
'SPATIAL_WFS_ADMIN_USR', 'SPATIAL_CSW_ADMIN', 'SPATIAL_CSW_ADMIN_USR')
and table_name<>'DBMS_REPCAT_INTERNAL_PACKAGE'
and table_name not like '%RP'
and grantee not in
(select grantee from dba_tab_privs
where table_name in ('DBMS_DEFER', 'DEFLOB'))

Change to STIG Rule: Added default users.

Name: Access to DBMS system tables and other configuration or metadata should be restricted to DBAs.

Collection Query:

select 'Application user '|| grantee||' is granted '||privilege||' on system table '|| owner||'.'|| table_name from dba_tab_privs
where (owner='SYS' or table_name like 'DBA_%')
and privilege <> 'EXECUTE'
and grantee not in
('PUBLIC', 'AQ_ADMINISTRATOR_ROLE', 'AQ_USER_ROLE',
'AURORA$JIS$UTILITY$', 'OSE$HTTP$ADMIN', 'TRACESVR',
'CTXSYS', 'DBA', 'DELETE_CATALOG_ROLE',
'EXECUTE_CATALOG_ROLE', 'EXP_FULL_DATABASE',
'GATHER_SYSTEM_STATISTICS', 'HS_ADMIN_ROLE',
'IMP_FULL_DATABASE', 'LOGSTDBY_ADMINISTRATOR', 'MDSYS',
'ODM', 'OEM_MONITOR', 'OLAPSYS', 'ORDSYS', 'OUTLN',
'RECOVERY_CATALOG_OWNER', 'SELECT_CATALOG_ROLE',
'SNMPAGENT', 'SYSTEM', 'WKSYS', 'WKUSER', 'WMSYS', 'WM_ADMIN_ROLE', 'XDB',
'LBACSYS', 'PERFSTAT', 'XDBADMIN', 'ADM_PARALLEL_EXECUTE_TASK', 'APEX_030200',
'APPQOSSYS', 'DBFS_ROLE', 'EXFSYS', 'HS_ADMIN_SELECT_ROLE', 'OLAP_XS_ADMIN',
'ORACLE_OCM', 'OWB$CLIENT', 'OWBSYS', 'SYSMAN')
and grantee not in
(select grantee from dba_role_privs where granted_role='DBA')
order by 1

Change to STIG Rule: Added default users.

Name: Only authorized system accounts should have the SYSTEM tablespace specified as the default tablespace.

Collection Query:

(select 'User '||username||' is using SYSTEM as temporary or default tablespace.' from dba_users
where (default_tablespace = 'SYSTEM' or temporary_tablespace = 'SYSTEM')
and username not in
('AURORA$JIS$UTILITY$','AURORA$ORB$UNAUTHENTICATED',
'DBSNMP','MDSYS','ORDPLUGINS','ORDSYS','OSE$HTTP$ADMIN',
'OUTLN','REPADMIN','SYS','SYSTEM','TRACESVR','MTSSYS','DIP', 'MGMT_VIEW'))

Change to STIG Rule: Added default users.

Name: Application owner accounts should have a dedicated application tablespace.

Collection Query:

select distinct tablespace_name||' tablespace used by '||owner||' is not a dedicated tablespace.' from (
select distinct owner, tablespace_name
from dba_tables
where owner not in
('SYS','SYSTEM','OUTLN','OLAPSYS','CTXSYS','WKSYS','ODM','ODM_MTR'
'MDSYS','ORDSYS','WMSYS','RMAN','XDB', 'APEX_030200', 'APPQOSSYS', 'DBSNMP', 
'EXFSYS', 'FLOWS_FILES', 'ORDDATA', 'OWBSYS', 'SYSMAN', 'SCOTT')
and tablespace_name is not NULL
and (owner, table_name) not in
(select owner, table_name from dba_external_tables)
order by 1)

Change to STIG Rule: Added default users.

Name: Fixed user and public database links should be authorized for use.

Collection Query:

select 'Fixed user database link '||db_link||' found for '||owner value from dba_db_links 
where db_link not in (select master from sys.dba_repcatlog)

Comment: Combined the rule queries to return db_link as violations only if dba_repcatalog has records

Name: A minimum of two Oracle redo log groups/files should be defined and configured to be stored on separate, archived physical disks or archived directories on a RAID device.

Collection Query:

select 'redo_logs_count', log_count from
(select count(*) log_count from  V$LOG where members > 1)
where log_count < 2 

Comment: Used the more strict query to get the violation. Need to manually check if a RAID device is used.

Name: Oracle application administration roles should be disabled if not required and authorized.

Collection Query:

select 'Oracle Administration role '||granted_role||' granted to '||grantee||'.'
from dba_role_privs
where default_role='YES'
and granted_role in
(select grantee from dba_sys_privs where upper(privilege) like '%USER%')
and grantee not in
('DBA', 'SYS', 'SYSTEM', 'CTXSYS', 'DBA', 'IMP_FULL_DATABASE', 'DATAPUMP_IMP_FULL_DATABASE','MDSYS', 'SYS', 'WKSYS')
and grantee not in (select distinct owner from dba_tables)
and grantee not in
(select distinct username from dba_users where upper(account_status) like
'%LOCKED%')

Change to STIG Rule: Added default users.

Name: Oracle system privileges should not be directly assigned to unauthorized accounts.

Collection Query:

select 'User/Role '||grantee||' granted system privilege '||PRIVILEGE from dba_sys_privs
where privilege<>'CREATE SESSION' and grantee not in
('PUBLIC', 'AQ_ADMINISTRATOR_ROLE', 'AQ_USER_ROLE', 'CTXSYS',
'DBA', 'DELETE_CATALOG_ROLE', 'EXECUTE_CATALOG_ROLE',
'EXP_FULL_DATABASE', 'GATHER_SYSTEM_STATISTICS',
'HS_ADMIN_ROLE', 'IMP_FULL_DATABASE',
'LOGSTDBY_ADMINISTRATOR', 'MDSYS', 'ODM', 'OEM_MONITOR',
'OLAPSYS', 'ORDSYS', 'OUTLN', 'MTSSYS',
'RECOVERY_CATALOG_OWNER', 'SELECT_CATALOG_ROLE',
'SNMPAGENT', 'SYSTEM', 'WKSYS', 'WKUSER', 'WMSYS',
'WM_ADMIN_ROLE', 'XDB', 'ANONYMOUS', 'CONNECT', 'DBSNMP',
'JAVADEBUGPRIV', 'ODM_MTR', 'OLAP_DBA', 'ORDPLUGINS',
'RESOURCE', 'RMAN', 'SYS', 'WKPROXY', 'AURORA$JIS$UTILITY$',
'AURORA$ORB$UNAUTHENTICATED', 'OSE$HTTP$ADMIN',
'TIMESERIES_DBA', 'TIMESERIES_DEVELOPER', 'OLAP_USER', 'DATAPUMP_EXP_FULL_DATABASE',
'DATAPUMP_IMP_FULL_DATABASE', 'OEM_ADVISOR', 'OWB$CLIENT', 'SCHEDULER_ADMIN', 'SYSMAN')
and grantee not in
(select grantee from dba_role_privs where granted_role='DBA')
and grantee not in
(select username from dba_users where upper(account_status) like
'%LOCKED%') order by 1

Change to STIG Rule: Added default users and roles.

Name: The IDLE_TIME profile parameter should be set for Oracle profiles IAW DoD policy.

Collection Query:

select 'IDLE_TIME set to '||limit||' for profile '||profile||'.' from (
select profile, limit from DBA_PROFILES
where profile = 'DEFAULT'
and resource_name = 'IDLE_TIME')
where TO_NUMBER(DECODE (limit, 'UNLIMITED', 1000, limit)) > 15
UNION
select profile, limit from (
select profile, limit from DBA_PROFILES
where profile <> 'DEFAULT'
and resource_name = 'IDLE_TIME')
where TO_NUMBER(DECODE (limit, 'UNLIMITED', 1000, 'DEFAULT', (SELECT DECODE(limit, 'UNLIMITED', 1000, limit)
 from DBA_PROFILES where resource_name='IDLE_TIME' and profile='DEFAULT'), limit))
> 60

Comment: Combined the queries. De-referenced the DEFAULT value for the limit.

Name: System privileges granted using the WITH ADMIN OPTION should not be granted to unauthorized user accounts.

Collection Query:

select 'User '||grantee||' granted '||privilege||' privilege WITH ADMIN OPTION.'
from dba_sys_privs
where grantee not in
('SYS', 'SYSTEM', 'AQ_ADMINISTRATOR_ROLE', 'DBA',
'MDSYS', 'LBACSYS', 'SCHEDULER_ADMIN',
'WMSYS', 'APEX_030200', 'OWBSYS')
and admin_option = 'YES'
and grantee not in
(select grantee from dba_role_privs where granted_role = 'DBA') order by 1

Change to STIG Rule: Added default users and roles.

Name: Object permissions granted to PUBLIC should be restricted.

Collection Query:

select privilege||' on '||owner ||'.'|| table_name ||' is granted to PUBLIC.' from dba_tab_privs
where grantee = 'PUBLIC'
and owner not in
('SYS', 'CTXSYS', 'MDSYS', 'ODM', 'OLAPSYS', 'MTSSYS',
'ORDPLUGINS', 'ORDSYS', 'SYSTEM', 'WKSYS', 'WMSYS',
'XDB', 'LBACSYS', 'PERFSTAT', 'SYSMAN', 'DMSYS',
'EXFSYS','APEX_030200', 'DBSNMP', 'ORDDATA')

Change to STIG Rule: Added default users and roles.

Name: Access to DBMS software files and directories should not be granted to unauthorized users.

Comment: Script provided by Oracle

Name: Database software directories including DBMS configuration files are stored in dedicated directories separate from the host OS and other applications.

Comment: Script provided by Oracle

Name: Application software should be owned by a Software Application account.

Comment: Script provided by Oracle

Name: DBMS processes or services should run under custom, dedicated OS accounts.

Comment: Script provided by Oracle

Name: DBMS network communications should comply with PPS usage restrictions.

Comment: Script provided by Oracle

Name: The DBMS warning banner should meet Department of Defense (DoD) policy requirements.

Comment: Script provided by Oracle

Name: The Oracle software installation account should not be granted excessive host system privileges.

Comment: Script provided by Oracle

Name: OS DBA group membership should be restricted to authorized accounts.

Comment: Script provided by Oracle

Name: The Oracle INBOUND_CONNECT_TIMEOUT and SQLNET.INBOUND_CONNECT_TIMEOUT parameters should be set to a value greater than 0.

Comment: Script provided by Oracle

Name: The Oracle SQLNET.EXPIRE_TIME parameter should be set to a value greater than 0.

Comment: Script provided by Oracle

Name: The Oracle Listener ADMIN_RESTRICTIONS parameter if present should be set to ON.

Comment: Script provided by Oracle

Name: The Oracle listener.ora file should specify IP addresses rather than host names to identify hosts.

Comment: Script provided by Oracle

Name: The SQLNet SQLNET.ALLOWED_LOGON_VERSION parameter should be set to a value of 10 or higher.

Comment: Script provided by Oracle.