Defined Type: ora_secured::controls::implement_separation_of_duties_through_assigned_information_access_authorizations

Defined in:
manifests/controls/implement_separation_of_duties_through_assigned_information_access_authorizations.pp

Summary

Obtain a list of privileges assigned to the DBMS user accounts.

Overview

ora_secured::controls::implement_separation_of_duties_through_assigned_information_access_authorizations

If any direct privilege assignments exist that can be assigned to a role, this is a finding.

SELECT

'User '
|| grantee
|| ' is directly granted '
|| privilege
|| ' privilege on '
|| table_name value

FROM dba_tab_privs WHERE grantee NOT IN (SELECT role FROM dba_roles) AND grantee NOT IN

(
'XDB', 'SYSTEM', 'SYS', 'LBACSYS',
'DVSYS', 'DVF', 'SYSMAN_RO', 'SYSMAN_BIPLATFORM',
'SYSMAN_MDS', 'SYSMAN_OPSS', 'SYSMAN_STB', 'PUBLIC',
'DBSNMP', 'SYSMAN', 'APEX_040200', 'WMSYS',
'SYSDG', 'SYSBACKUP', 'SPATIAL_WFS_ADMIN_USR',
'SPATIAL_CSW_ADMIN_US',
'SI_INFORMTN_SCHEMA', 'OUTLN', 'ORDSYS', 'ORDDATA',
'OJVMSYS', 'ORACLE_OCM', 'MDSYS', 'ORDPLUGINS',
'GSMADMIN_INTERNAL', 'FLOWS_FILES', 'DIP', 'CTXSYS',
'AUDSYS', 'APPQOSSYS', 'APEX_PUBLIC_USER',  'ANONYMOUS',
'SPATIAL_CSW_ADMIN_USR', 'SYSKM', 'SYSMAN_TYPES',
'MGMT_VIEW', 'EUS_ENGINE_USER', 'GSMCATUSER', 'OLAPSYS',
'CLOUD_SWLIB_USER',  'GSMUSER', 'MDDATA', 'XS$NULL', 'CLOUD_ENGINE_USER'
)

UNION SELECT

'User '
|| grantee
|| ' is directly granted '
|| privilege
|| ' privilege ' value

FROM dba_sys_privs WHERE grantee NOT IN (SELECT role FROM dba_roles) AND privilege NOT IN

(
'CREATE SEQUENCE',
'CREATE TRIGGER',
'CREATE CLUSTER',
'CREATE INDEXTYPE',
'CREATE PROCEDURE',
'CREATE TYPE',
'CREATE SESSION',
'CREATE OPERATOR',
'CREATE TABLE',
'UNLIMITED TABLESPACE' )

AND grantee NOT IN

(
'XDB', 'SYSTEM', 'SYS', 'LBACSYS',
'DVSYS', 'DVF', 'SYSMAN_RO', 'SYSMAN_BIPLATFORM',
'SYSMAN_MDS', 'SYSMAN_OPSS', 'SYSMAN_STB', 'PUBLIC',
'DBSNMP', 'SYSMAN', 'APEX_040200', 'WMSYS',
'SYSDG', 'SYSBACKUP', 'SPATIAL_WFS_ADMIN_USR',
'SPATIAL_CSW_ADMIN_US',
'SI_INFORMTN_SCHEMA', 'OUTLN', 'ORDSYS', 'ORDDATA',
'OJVMSYS', 'ORACLE_OCM', 'MDSYS', 'ORDPLUGINS',
'GSMADMIN_INTERNAL', 'FLOWS_FILES', 'DIP', 'CTXSYS',
'AUDSYS', 'APPQOSSYS', 'APEX_PUBLIC_USER', 'ANONYMOUS',
'SPATIAL_CSW_ADMIN_USR', 'SYSKM', 'SYSMAN_TYPES',
'MGMT_VIEW', 'EUS_ENGINE_USER', 'GSMCATUSER', 'OLAPSYS',
'CLOUD_SWLIB_USER',
'GSMUSER', 'MDDATA', 'XS$NULL', 'CLOUD_ENGINE_USER'
)

UNION SELECT

'User '
|| username
|| ' is granted '
|| privilege
|| ' privilege via role '
|| rp.granted_role value

FROM dba_users u,

dba_role_privs rp,
dba_sys_privs sp

WHERE username = rp.grantee AND rp.granted_role = sp.grantee AND privilege NOT IN

(
'CREATE SEQUENCE',
'CREATE TRIGGER',
'SET CONTAINER',
'CREATE CLUSTER',
'CREATE PROCEDURE',
'CREATE TYPE',
'CREATE SESSION',
'CREATE OPERATOR',
'CREATE TABLE',
'CREATE INDEXTYPE'
)

AND 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 rp.granted_role NOT IN

(
'EXP_FULL_DATABASE','AQ_ADMINISTRATOR_ROLE','DV_REALM_RESOURCE',
'DBA','CDB_DBA','OEM_ADVISOR','RECOVERY_CATALOG_OWNER',
'EM_EXPRESS_ALL','SCHEDULER_ADMIN','OLAP_USER',
'RESOURCE','EM_EXPRESS_BASIC','IMP_FULL_DATABASE','CONNECT',
'AUDIT_ADMIN','DATAPUMP_EXP_FULL_DATABASE','GSMADMIN_ROLE',
'DV_REALM_OWNER','OLAP_DBA','JAVADEBUGPRIV',
'DATAPUMP_IMP_FULL_DATABASE','OEM_MONITOR',
'APEX_GRANTS_FOR_NEW_USERS_ROLE'
)

UNION SELECT

'User '
||grantee
||' is granted '
||privilege
||' on '
||owner
||'.'
||table_name
||'.'
||column_name
||' by '
||grantor

FROM dba_col_privs WHERE grantee 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', 'IMP_FULL_DATABASE',
'DIP', 'CTXSYS', 'AUDSYS', 'APPQOSSYS',
'APEX_PUBLIC_USER', 'ANONYMOUS',
'SPATIAL_CSW_ADMIN_USR','SYSKM',
'SYSMAN_TYPES', 'MGMT_VIEW', 'EUS_ENGINE_USER',
'EXFSYS','SYSMAN_APM'
)

;

(The lists of special accounts that are excluded from this requirement may not be complete. It is expected that the DBA will edit the lists to suit local circumstances, adding other special accounts as necessary, and removing any that are not supposed to be in use in the Oracle deployment that is under review. Similarly, the lists of privileges and roles excluded from the subqueries may be modified according to circumstances.)

Define DBMS user roles based on privilege and job function requirements.

Assign the required privileges to the role, and assign the role to authorized DBMS user accounts.

Revoke any privileges directly assigned to DBMS user accounts, and assign them to a role the DBMS user already has assigned.

## Skipping

To deliberately skip this control (e.g. meaning don’t use Puppet to enforce this setting), we provide you with three ways:

1) Add ‘ora_secured::controls::implement_separation_of_duties_through_assigned_information_access_authorizations: skip` to your hiera data. This will skip this control for ALL databases. 2) Add `ora_secured::controls::implement_separation_of_duties_through_assigned_information_access_authorizations::dbname: skip` to your hiera data. This will skip this control for specified database only. 3) Add an entry with the content `implement_separation_of_duties_through_assigned_information_access_authorizations` to the array value `ora_secured::skip_list` in your hiera data.

## Benchmarks

This control is used in the following benchmarks:

  • [Oracle Database 12c CIS V1](/docs/ora_secured/stig/db12c_V1.html) - id V-61809

See the file “LICENSE” for the full license governing this code.

Parameters:

  • title —

    The SID to apply the control to. All controls need an SID to apply the control to. Here is a simple example: “‘ puppet ora_secured::controls::control_name { ’DBSID’:} “‘ In this example, the string DBSID is the sid to apply the control to.



194
195
196
197
198
199
200
201
 
# File 'manifests/controls/implement_separation_of_duties_through_assigned_information_access_authorizations.pp', line 194

define ora_secured::controls::implement_separation_of_duties_through_assigned_information_access_authorizations
{
  ora_secured_setup { "implement_separation_of_duties_through_assigned_information_access_authorizations on ${title}":
    ensure => 'present',
  }

  alert('Specified STIG control is not yet automatically enforced.')
}