Page 1
ORACLE DATABASE
Control
Objective
Control Activity
Test of Control
Conclusion
Signoff
A1.
Logical security tools and techniques are implemented and configured to enable restriction of access to programs, data, and other information resources.
Password and system
resources for the Oracle
database are configured
properly and configured to
agree with the corporate
password
guidelines/policies.
Obtain and evaluate password parameter configuration in the system by performing the
following steps:
1. Obtain all active db accounts and associated profile names by executing the following
SQL command:
SELECT username, profile FROM sys.dba_users;
2. Obtain system and password resource parameters configured for each profile obtained
from previous testing procedure. To do so, execute the following SQL command:
SELECT substr(profile,1,length(profile))||':'||
substr(resource_name,1,length(resource_name))||':'||
substr(resource_type,1,length(resource_type)) ||':'||
substr(limit,1,length(limit))||':'
"profile:name:type:limit"
FROM sys.dba_profiles
WHERE profile = upper('&input_profile');
The command will prompt with “Enter value for input_profile:”. Input the profile name
(e.g., DEFAULT) obtained from step 1 to get the parameter setting for this profile.
3. Based on the corporate policies and guidelines, evaluate if proper system and password
resource parameters, obtained from previous testing procedure, are properly configured
for each account.
Users are restricted from
accessing database using
vendor provided default
usernames and associated
passwords.
For the default usernames and related passwords, verify that the passwords are changed.
For this, perform the following:
1. First log in to database using the following username/password combinations:
sys/change_on_install (all versions), system/manager (all versions), scott/tiger (all
versions) , dbsnmp/dbsnmp (version 7.1.6 and later), demo/demo (all versions).
2. Login attempts using the default passwords should all fail; otherwise, it indicates that
users are allowed to access database using vendor provided default usernames and
associated passwords.
Privileged accounts are
restricted from end users.
1. Obtain and evaluate all database privileged user accounts that are authenticated at OS
level. The testing procedures are different depending on the OS platform. Here, only
UNIX and NT are covered. For other operating systems, refer to Oracle manuals for
the specific Operating System, which provide OS authentication specifications.
1
Page 2
ORACLE DATABASE
Control
Objective
Control Activity
Test of Control
Conclusion
Signoff
1a. UNIX environment:
1a.1 Obtain the ‘/etc/group’ file at UNIX. In the ‘/etc/group’ file, obtain all
accounts that are under the ‘dba’ group. In addition, obtain the ‘gid’ (group id) number
for ‘dba’ group. The ‘gid’ number is the number right after ‘dba’. For example, if
‘/etc/group’ shows that “dba::300:oracle”, the number 300 is the ‘gid’ for dba group,
and user ‘oracle’ is a user who belongs to the ‘dba’ group.
1a.2 Obtain and review the ‘/etc/passwd’ file at UNIX. All users with the same
‘gid’ obtained from step 2a.1 also belong to the ‘dba’ group. For example, if the
‘/etc/passwd’ file contains a line as follows:
test1:x:126:300:testingid:/export/home/test1
Then, OS user ‘test1’ belongs to the group ‘dba’, since the ‘gid’ assigned to this
username is ‘300’. The ‘gid number is the fourth entry in each line of the /etc/passwd
file.
1a.3. All UNIX user accounts obtained from 2a.1 and 2a.2 can access Oracle
database directly without database authentication since they are the privileged user
accounts that are OS authenticated. Evaluate all these user accounts that belong to the
‘DBA’ group. Under normal circumstances, this group should only consist of DBA
user account(s). If an exception is noted, communication with DBA and/or system
administrator should take place to either justify the reason why a non-DBA belongs to
the ‘DBA’ group at O/S level, or recommend that the client restrict non-DBA O/S users
from the ‘dba’ group.
1b. NT environment:
1b.1 Obtain list of accounts that belong to either ‘ORA_<SID>_DBA’ and
‘ORA_<SID>_OPER’, or ‘ORA_DBA’ and ‘ORA_OPER’, depending which of the
group naming methods the client uses.
1b.2 Evaluate all O/S user accounts listed. In normal circumstances, these groups
should only consist of DBA user account(s). If an exception is noted, communication
with the DBA and/or system administrator should take place to either justify the reason
why a non-DBA belongs to the ‘DBA’ group at O/S level, or recommend that the client
restrict non-DBA O/S users from the ‘dba’ group.
2. Obtain all database user accounts that have the ability to login to the Oracle database as
a privileged user account because of the usage of password file.
2a. Determine if the password file authentication is used in the production database by
executing the following server manager (SVRMGR) command:
SHOW PARAMETER remote_login_passwordfile
There are three possible returns for this command:
1) NONE - It indicates that password file authentication is not used for this Oracle
2
Page 3
ORACLE DATABASE
Control
Objective
Control Activity
Test of Control
Conclusion
Signoff
database. No further testing deemed necessary.
2) EXCLUSIVE - It indicates that password file authentication is used, and password
file contains names other than SYS and INTERNAL. Continue testing with step 3b.
3) SHARE – It indicates that password file authentication is used and password file
only contains user SYS and INTERNAL. No further testing deemed necessary.
2b. Obtain database usernamethat are defined in the password file by executing the
following SQL command: SELECT * FROM v$pwfile_users;
‘USERNAME’ is the column which shows all database usernames that can log into
Oracle Database as a privileged user.
2c. Evaluate all user accounts except SYS and INTERNAL. In normal circumstances,
only the DBA user account should be included in the list. If an exception is noted,
communication with DBA needs to take place to either justify why the password file
consist non-DBA account(s), or recommend that the client restrict non-DBA account(s)
from the password file.
Security related system
parameters/privileges are
HIGHLY restricted.
1. Obtain a complete list of security administrators as identified by management.
2. To test privileges associated with user id administration (create/alter/drop), the
following three steps should be completed:
2a. Identify all ROLES that exist in the database by executing the following SQL
statement:
SELECT *
FROM sys.dba_roles;
2b. Identify all ROLES that have access to the create/alter/drop user/role privileges.
This can be accomplished through use of the following SQL statement:
SELECT substr(grantee,1,length(grantee))||':'||
substr(privilege,1,length(privilege))||':'||
substr(admin_option,1,length(admin_option)) ||':'
"grantee:privilege:admin_option"
FROM sys.dba_sys_privs
WHERE (privilege = 'create user' or
privilege = 'CREATE ROLE' or
privilege = 'ALTER USER' or
privilege = 'ALTER ANY ROLE' or
privilege = 'DROP USER' or
privilege = 'DROP ANY ROLE') and
grantee in (SELECT role FROM sys.dba_roles)
3
Page 4
ORACLE DATABASE
Control
Objective
Control Activity
Test of Control
Conclusion
Signoff
ORDER BY grantee;
The Oracle default roles which come pre-configured with these privileges are the DBA
and IMPORT_FULL_DATABASE roles. The DBA role always needs all system
privileges to perform necessary database user maintenance. The
IMPORT_FULL_DATABASE role needs to be able to create/drop roles and users so
that it can be used to create (and drop) necessary database accounts during a database
import. All other roles which are granted these privileges do not exist by default. These
roles will generally be created specifically for security administration needs without
authorizing DBA level access.
2c. Using the results from query 2b above, identify all usernames that have been
granted roles with the create/alter/drop user/role privilege(s). This can be accomplished
through the use of the following SQL statement:
SELECT substr(granted_role,1,length(granted_role)) ||':'||
substr(grantee,1,length(grantee)) ||':'||
substr(admin_option,1,length(admin_option)) ||':'||
substr(DEFAULT_role,1,length(default_role)) ||':'
FROM sys.dba_role_privs
WHERE granted_role = upper('&INPUT_ROLE’)
ORDER BY grantee;
Note that grantees returned from this query may themselves be roles since roles can be
assigned to other roles. When a return value is a role, use the same SQL statement (2c)
to find a list of actual usernames that belong to THAT role (Refer to testing step 2a
above for a complete list of roles). Multiple recursions of the script may be necessary to
get to the final list of users associated with a particular role depending on the role
hierarchy created in the Oracle database. The default hierarchy for Oracle involves 2
levels of roles to get to the final user account. For instance, the DBA role is granted to
the IMPORT_FULL_DATABASE role which is itself granted to the user account
“SYS”. Also note that the default roles found in query 2b are both assigned
automatically to the database username SYS in the basic Oracle installation. The DBA
role is automatically assigned to the database username SYSTEM in the basic Oracle
installation.
In SQL*PLUS, once the SQL statement is entered, it is stored in the buffer and can be
re-executed by simply entering the forward slash (‘/’) and pressing <ENTER>.
From the list of usernames with create/alter/drop user/role privileges identified in this
query, evaluate whether the list is appropriate by comparing it to the list provided by
management. In general, only the security administrator and/or database administrator
should have the ability to create, alter, or drop users and roles.
4
Page 5
ORACLE DATABASE
Control
Objective
Control Activity
Test of Control
Conclusion
Signoff
2d. Identify all usernames that have been DIRECTLY granted the create/alter/drop
user/role privileges in the database. This can be accomplished by running the following
SQL statement:
SELECT substr(a.grantee,1,length(a.grantee)) ||':'||
substr(a.privilege,1,length(a.privilege)) ||':'||
substr(a.admin_option,1,length(a.admin_option)) ||':'
"grantee:privilege:admin_option"
FROM sys.dba_sys_privs a, sys.dba_users b
WHERE a.grantee = b.username and
(privilege = ‘CREATE USER' or
privilege = 'CREATE ROLE' or
privilege = 'ALTER USER' or
privilege = 'ALTER ANY ROLE' or
privilege = 'DROP USER' or
privilege = 'DROP ANY ROLE')
ORDER BY grantee;
From the list of usernames with create/alter/drop user/role privileges identified in this
query, evaluate whether the list is appropriate by comparing it to the list provided by
management. In general, only the security administrator and/or database administrator
should have the ability to create, alter, or drop users and roles.
3. In addition to create/alter/drop, there are two powerful user administration privileges
involving the privilege to “GRANT.” To test the “GRANT ANY PRIVILEGE” and
“GRANT ANY ROLE” privileges, which should only be assigned to the security
and/or database administrators, complete the following 3 steps:
3a. Identify all roles that have the “GRANT ANY PRIVILEGE” and/or “GRANT ANY
ROLE” system privileges. This can be accomplished through use of the following SQL
statement:
SELECT substr(grantee ,1,length(grantee)) ||':'||
substr(privilege ,1,length(privilege)) ||':'||
substr(admin_option,1,length(admin_option)) ||':'
"grantee:privilege:admin_option"
FROM sys.dba_sys_privs
WHERE (privilege = 'GRANT ANY PRIVILEGE' or
privilege = 'GRANT ANY ROLE') and
grantee in (SELECT role FROM sys.dba_roles)
ORDER BY grantee;
5
Page 6
ORACLE DATABASE
Control
Objective
Control Activity
Test of Control
Conclusion
Signoff
The only default role with both these privileges is the DBA role. The DBA role requires
these privileges to initially grant privileges to database usernames after installation and
username creation. All other roles having these privileges do not own these privileges
by default.
3b. Using the results from above, identify all usernames that have been granted roles
with the ‘GRANT ANY PRIVILEGE’ or ‘GRANT ANY ROLE’ privileges. This can
be accomplished through the use of the following SQL statement:
SELECT substr(granted_role,1,length(granted_role)) ||':'||
substr(grantee,1,length(grantee)) ||':'||
substr(admin_option,1,length(admin_option)) ||':'||
substr(default_role,1,length(default_role)) ||':'
FROM sys.dba_role_privs
WHERE granted_role = upper('&INPUT_ROLE’)
ORDER BY grantee;
Note that grantees returned from this query may themselves be roles since roles can be
assigned to other roles. When a return value is a role, use the same SQL statement (4b)
to find a list of actual usernames that belong to THAT role (Refer to testing step 3a
above for a complete list of roles). There may be a need for multiple recursions of the
script to get to the final list of users associated with a particular role depending on the
role hierarchy created in the Oracle database. Also note that the basic package
installation role "DBA" found in query 4a is automatically assigned to the database user
accounts SYS and SYSTEM.
In SQL*PLUS, once the SQL statement is entered, it is stored in the buffer and can be
re-executed by simply entering the forward slash (‘/’) and pressing <ENTER>.
From the list of usernames with the "GRANT" privileges identified in this query,
evaluate whether the list is appropriate by comparing it to the list provided by
management. In general, only the security administrator and/or database administrator
should have the ability "GRAN" privileges to users and roles.
3c. Identify all usernames that have been directly granted the ‘GRANT ANY
PRIVILEGE’ and/or ‘GRANT ANY ROLE’ privileges in the database. This can be
accomplished by running the following SQL statement:
SELECT substr(a.grantee,1,length(a.grantee)) ||':'||
substr(a.privilege,1,length(a.privilege)) ||':'||
substr(a.admin_option,1,length(a.admin_option)) ||':'
"grantee:privilege:admin_option"
FROM sys.dba_sys_privs a,
sys.dba_users b
6
Page 7
ORACLE DATABASE
Control
Objective
Control Activity
Test of Control
Conclusion
Signoff
WHERE a.grantee = b.username and
(privilege = 'GRANT ANY PRIVILEGE' or
privilege = 'GRANT ANY ROLE')
ORDER BY grantee;
From the list of usernames with the "GRANT" privileges identified in this query,
evaluate whether the list is appropriate by comparing it to the list provided by
management. In general, only the security administrator and/or database administrator
should have the ability "GRAN" privileges to users and roles.
Update access (INSERT,
UPDATE, DELETE, ALL)
to critical tables is
restricted to authorized
user accounts and is
regularly reviewed.
1. Through inquiry of management personnel and data owners, compile a list of the data
which they consider critical.
2. With the cooperation of the database administrator, identify the tables which store the
critical data identified in testing step 1 above.
2a. Identify all ROLES that exist in the database by executing the following SQL
statement:
SELECT *
FROM sys.dba_roles;
2b. Obtain a list of user ids that have DML access (Data Manipulation Language -
insert/update/delete) access to the tables identified in testing step 1 above by executing
the following SQL command:
SELECT substr(grantee,1,length(owner)) ||':'||
substr(table_name,1,length(owner)) ||':'||
substr(privilege ,1,length(owner)) ||':'||
substr(grantable ,1,length(table_name)) ||':'
FROM sys.dba_tab_privs
WHERE table_name = ('&INPUT_TABLE') and
(privilege = 'INSERT' or
privilege = 'UPDATE' or
privilege = 'DELETE' or
privilege = 'ALL')
ORDER by grantee;
This query will prompt for an input parameter "INPUT_TABLE" which should be
entered as the name of the critical table of interest. This script will need to be executed
for each critical table being tested. In SQL*PLUS, once the SQL statement is entered,
it is stored in the buffer and can be re-executed by simply entering the forward slash
(‘/’) and pressing <ENTER>.
7
Page 8
ORACLE DATABASE
Control
Objective
Control Activity
Test of Control
Conclusion
Signoff
Note that some of the return values for this query may be roles (Refer to testing step 4a
above for a complete list of roles). The database user accounts that have been assigned
that role will need to be determined by running the script identified in step 2c below.
Using the list of user accounts identified in this testing step (2b), evaluate the
appropriateness of the DML access to each critical table.
2c. To identify all user ids that have been assigned (granted) roles with the
insert/update/delete privileges (identified in testing step 2b), execute the following SQL
statement:
SELECT substr(granted_role,1,length(granted_role)) ||':'||
substr(grantee,1,length(grantee)) ||':'||
substr(admin_option,1,length(admin_option)) ||':'||
substr(default_role,1,length(default_role)) ||':'
FROM sys.dba_role_privs
WHERE granted_role = upper('&INPUT_role')
ORDER BY grantee;
Note that grantees returned from this query may themselves be roles since roles can be
assigned to other roles. When a return value is a role, use the same SQL statement (2c)
to find a list of actual user ids that belong to THAT role (Refer to testing step 2a above
for a complete list of roles). There may be a need for multiple recursions of the script to
get to the final list of users associated with a particular role depending on the role
hierarchy created in the Oracle database. The default hierarchy for Oracle involves 2
levels of roles to get to the final user account. For instance, the DBA role is granted to
the IMPORT_FULL_DATABASE role which is granted to the user id SYS.
This script will need to be re-executed for each role identified in step 4b. In
SQL*PLUS, once the SQL statement is entered, it is stored in the buffer and can be re-
executed by simply entering the forward slash (‘/’) and pressing <ENTER>.
Using the list of user accounts identified in this testing step (4c), evaluate the
appropriateness of the DML access to each critical table.
Limited system or object
privileges should be
granted to ‘PUBLIC’ role.
1. Understand and document company policies, procedures, standards, and guidance
regarding user privilege and access setup. Corroborate the functioning and
effectiveness of such policies, procedures, standards, and guidance through (a) inquiry
of individuals responsible for granting access, and (b) reference to client documentation
of the procedures performed. Ensure that adequate documentation exists.
2. Evaluate if the existing configurations in database are properly implemented for
PUBLIC user group. Perform the following to complete this testing procedure:
2a. Obtain all system privileges, besides ‘CREATE SESSION’, that are granted to
8
Page 9
ORACLE DATABASE
Control
Objective
Control Activity
Test of Control
Conclusion
Signoff
PUBLIC by executing the following SQL commands:
SELECT * FROM sys.dba_sys_privs WHERE grantee = ‘PUBLIC’ and privilege <>
‘CREATE SESSION’;
There are three columns in the returned list: grantee, privilege, and admin option.
2b. Evaluate if the returned system privileges should be granted to ‘PUBLIC’, and
ensure that ONLY NECESSARY system privileges are granted to ‘PUBLIC’ users. In
normal circumstances, system privileges, except CREATE SESSION privilege, should
not be granted to ‘PUBLIC’. If an exception is observed, communication with the
client needs to take place to either justify the reason why the system privilege is granted
to the ‘PUBLIC’, or recommend usage of roles, other than the ‘PUBLIC’ role, to
manage system privileges.
2c. Obtain all object privileges that are granted to PUBLIC by executing the following
2 SQL commands:
SQL>SELECT substr(grantee,1,length(grantee)) ||':'||
substr(owner,1,length(owner)) ||':'||
substr(table_name,1,length(table_name)) ||':'|| substr(privilege,1,length(privilege))||':'
"grantee:owner:table_name:priv"
FROM sys.dba_tab_privs
WHERE grantee = 'PUBLIC'
ORDER BY table_name,privilege,grantee;
SQL>SELECT substr(grantee,1,length(grantee)) ||':'||
substr(owner,1,length(owner)) ||':'||
substr(column_name,1,length(column_name)) ||':'||
substr(table_name,1,length(table_name)) ||':'|| substr(privilege,1,length(privilege))||':'
"grantee:owner:col_name:table_name:priv"
FROM sys.dba_col_privs
WHERE grantee = 'PUBLIC'
ORDER BY table_name,column_name, privilege,grantee;
2d. Evaluate if the returned object privileges should be granted to ‘PUBLIC’, and
ensure that ONLY NECESSARY object privileges are granted to ‘PUBLIC’. The
evaluation process should be accomplished with data owners and DBA of the objects
that privileges are granted to. If exception is observed, communication with the client
needs to take place to recommend usage of role to manage object privileges.
System level privileges in
1. Understand and document, through inquiry of management, policies and procedures as
they relate to system level privileges. Obtain through inquiry or documentation a list of
9
Page 10
ORACLE DATABASE
Control
Objective
Control Activity
Test of Control
Conclusion
Signoff
the production databases is
restricted to trained and
trusted security personnel.
the names of trusted security/database administrator(s) and those who may have a need
for system level access.
2. To test whether system privileges are restricted to a limited number of personnel, all
system privileges that are granted to user ids must be identified (with the exception of
the ‘CREATE SESSION’ system privilege). System privileges can be granted directly
or indirectly (though use of roles), and both types of grants must be tested.
2a. To obtain a listing of all accounts that have been granted system level access
DIRECTLY in the database, use the following SQL statements:
SET PAGESIZE 10000
COLUMN grantee FORMAT A35 WRAP
COLUMN privilege FORMAT A35 WRAP
BREAK ON grantee ON admin_option SKIP 1
SELECT grantee, admin_option, privilege
FROM sys.dba_sys_privs
WHERE privilege <> 'CREATE SESSION'
AND grantee NOT IN (SELECT role FROM sys.dba_roles)
ORDER BY grantee, admin_option DESC;
Evaluate the list of user accounts that have been granted system level privileges for
appropriateness. Particular attention must be paid to granted privileges that have the
admin option set to 'YES' since this represents the ability to grant the privilege to any
other user account in the database.
The Oracle user ids SYS, SYSTEM, and DBSNMP will all have a significant number
of system privileges. Some of these privileges will be with the admin option set to 'yes.'
This is not a logical security issue since such user ids are designed to perform database
maintenance functions by using system level privileges.
2b. Identify all ROLES that exist in the database by executing the following SQL
statement:
SELECT *
FROM sys.dba_roles;
2c. Identify INDIRECT grants of system level privileges by obtaining a complete
listing of all ROLES that have system level privileges. To do so, use the following SQL
statements:
SET pagesize 10000
COLUMN grantee format A35 wrap
COLUMN privilege format A35 wrap
BREAK ON grantee on admin_option skip 1
SELECT b.grantee, b.admin_option, b.privilege
10
Page 11
ORACLE DATABASE
Control
Objective
Control Activity
Test of Control
Conclusion
Signoff
FROM sys.dba_roles a, sys.dba_sys_privs b
WHERE b.privilege <> 'CREATE SESSION'
AND a.role = b.grantee
ORDER BY grantee, admin_option;
2d. Obtain the USER IDS associated with the roles identified in testing step 2c, use the
following SQL statements:
SET pagesize 10000
COLUMN granted_role heading 'ROLE' format A35 wrap
COLUMN grantee heading 'grantees' format A35 wrap
BREAK ON granted_role
SELECT granted_role, grantee, admin_option
FROM sys.dba_role_privs
WHERE granted_role = UPPER('&INPUT_ROLE’)
ORDER BY admin_option desc;
This statement should be repeated for ALL roles obtained in testing step 2c. Note that
the return values for testing step 2d may be roles themselves (Refer to testing step 2b
above for a complete list of roles). In this case, those roles should also be used in this
testing step (2d) to ultimately obtain the actual database user id. This query will prompt
for an input parameter "input_role" which should be entered as the name of the role of
interest. In SQL*PLUS, once the SQL statement is entered, it is stored in the buffer and
can be re-executed by simply entering the forward slash (‘/’) and pressing <ENTER>.
Evaluate the final list of user ids and their associated system privileges to see if the use
of such privileges by those accounts is appropriate.
Access to the database files
at Operating System level
has been appropriately
restricted through use of
OS level file/directory
protection.
1. Understand and document company policies, procedures, standards, and guidance
regarding OS file/directory access configuration. Note that file protection mechanisms
in differing operating systems will be different and will have different security
implications. Corroborate the functioning and effectiveness of such policies,
procedures, standards, and guidance through (a) inquiry of individuals responsible for
OS file/directory access setup, and (b) reference to client documentation of the
procedures performed. Ensure that adequate documentation exists.
2. Obtain locations for Oracle data files:
2a. Execute the following SQL commands to obtain the location for all files where data
are stored:
SET pagesize 1000
COLUMN file# format 9,999 word_wrapped
COLUMN status format A10 word_wrapped
11
Page 12
ORACLE DATABASE
Control
Objective
Control Activity
Test of Control
Conclusion
Signoff
COLUMN ts# heading 'Tablespace #' format 9,999 word_wrapped
COLUMN name heading 'File Name and Location' format A40 word_wrapped
SELECT file#, status, ts#, name FROM v$datafile;
2b. Execute the following SQL command to obtain the location for control file(s):
SELECT * FROM v$controlfile;
2c. Obtain the $ORACLE_HOME directory to obtain the location of the password file.
The testing steps here depend on the O/S system. In UNIX environment, execute the
following command at UNIX prompt:
echo $ORACLE_HOME
2d. Obtain the location of the init<SID>.ora file from the database administrator.
2e. Review the init<SID>.ora file and its included file (files after ‘ifile’ parameter) to
obtain the location of redo log files.
3. Obtain and document the location of all program files by identified the location of
$ORACLE_HOME. It can be accomplished either through inquiry of the database
administrator or by directly looking for the $ORACLE_HOME parameters in the
operating system. The detail testing steps depend on the O/S system. In UNIX, for
example, the following command can be issued at the UNIX prompt:
echo $ORACLE_HOME
4. Understand the OS level file/directory protection configuration. Obtain list of OS user
accounts that have read/write access to the files obtained from Step #2 and Step #3.
Evaluate the user account list for appropriateness. In normal circumstances, all general
users should be restricted from these files. Only the DBA and OS administrator should
have access to these files.
NOTE>Since Operating System file/directory protection configurations vary from
platform to platform, the detail testing procedures are not covered in this Testing of
Control section. Refer to Operating System documentation for file/directory
specifications.
Security parameters are
adequately defined and in
place around database links
and reviewed on a periodic
basis.
1. Understand and document the policies and procedures which the organization has
established around database links and database link security. Through inquiry of
management personnel, data owners, and database administrators, determine whether
database links have been used, are used, and/or will be used in the organization. If
database links are utilized, inquire the nature (roles, responsibilities, and scope) of the
administration functions related to the separate databases which make up the distributed
database. Inquire as to the extent and nature of the security issues identified in the past
as they relate to database links. If such issues existed, obtain an understanding and
document how they were resolved through inquiry. Also, obtain any documentation
12
Page 13
ORACLE DATABASE
Control
Objective
Control Activity
Test of Control
Conclusion
Signoff
associated with those issues and resolution methods. Inquire and obtain any
documentation of the monitoring activities associated with database links. If database
links are not used and are not expected to be used in the near future, no further testing
is required for this control activity.
2. Use the following SQL statement to determine what links, if any, are employed by the
local database to connect to other databases in the organization:
COLUMN owner FORMAT A15 wrap
COLUMN dblink FORMAT A15 wrap
COLUMN username FORMAT A15 wrap
COLUMN host FORMAT A15 wrap
SELECT *
FROM sys.dba_db_links;
The result from this query is a list of all outgoing links defined at the local database.
3. Determine existing incoming database links to the local database. First, obtain a
complete list of all database links through inquiry of the database administrator.
4. If outgoing database links are employed, verify that DBLINK_ENCRYPT_LOGIN is
set for each identified link at testing step 2 to TRUE in the INIT.ORA file. This ensures
that attempts to connect to another Oracle database are encrypted. As a secondary step
to this test, use the ‘SHOW PARAMETER dblink’ command in SERVER MANAGER
to verify that this parameter is set to TRUE. This step verifies that the parameter has
been activated for the Oracle database instance which is being checked.
5. For each database incoming link identified in testing step 3, inquire as to the specific
needs which caused the creation of such a link. Evaluate this with particular focus on
the need for such a link.
A2.
Logical security tools and techniques are administered to enable restriction of access to programs, data, and other information resources.
Oracle security
administration procedures
have been developed, and
implemented.
1. Understand and document policies, procedures, standards, and guidance regarding
database maintenance through use of application functionality. Understand and document
the extent and use of such functionality.
Note that application level controls should be referenced and tested to ensure that
application functionality allowing database maintenance are appropriately restricted to
authorized personnel. For specific testing of controls related to the application(s), refer to
specific application auditing documentation.
If application level maintenance control of the database is not utilized, no further testing is
required for this control activity.
13
Page 14
ORACLE DATABASE
Control
Objective
Control Activity
Test of Control
Conclusion
Signoff
2. Request the company’s policies and procedures in regards to the authorization of DBA
and security personnel. Note the requirements which are needed for authorization. Obtain a
listing of all personnel which are authorized with high access rights to maintain the database
through the application. Review this list for authorization and for reasonableness.
3. Work with application owners to obtain a listing of all user accounts that actually have
access to maintenance functionality to the database. Review listing against authorized listing
obtained in testing step 2.
4. Understand and document company policies, procedures, standards, and/or guidance
regarding periodic review of personnel with privileges associated with privileged access to
the database through the application. Corroborate the functioning and effectiveness of such
policies, procedures, standards, and guidance through (a) inquiry of individuals responsible
for database and security administration, and (b) reference to client documentation of the
procedures performed. Ensure that adequate documentation exists.
An audit trail has been
implemented and the audit
trail is regularly reviewed.
1. Understand and document policies, procedures, standards, and guidance regarding the
use of database auditing for purposes of system security and system performance
tuning. Corroborate with the database administrator that database auditing has been
activated. Note whether management uses and relies upon the Oracle database auditing
function. If management does not rely upon Oracle database auditing functionality, no
further testing for this control is necessary. Refer to Recommendation consideration for
this control activity in the Background section.
2. Verify that all active instances of the Oracle databases are in fact auditing. To do this,
run the "SHOW PARAMETER AUDIT" command at the Server Manager command
line for each active instance of Oracle. Check the “AUDIT_TRAIL” line item for the
correct parameter (i.e. DB, OS, or TRUE).
3. To verify that the database has been consistently logging audit trail information,
perform the following:
3a. If the “AUDIT_TRAIL” is set to DB or TRUE then use:
SELECT DISTINCT SUBSTR(timestamp,1,2), SUBSTR(timestamp,4,3),
SUBSTR(timestamp, 8,2)
FROM sys.dba_audit_trail
ORDER BY SUBSTR(timestamp,4,3), SUBSTR(timestamp,1,2);
From the list of date-stamps, check to see that the range of dates is appropriate. The list
should include all dates since the time the database administrator last performed
maintenance on the audit trail file to today. There should be no gaps in the date range.
Any gaps should be fully explained (e.g. no-one works and no database maintenance
takes place on weekends).
14
Page 15
ORACLE DATABASE
Control
Objective
Control Activity
Test of Control
Conclusion
Signoff
3b. If the “AUDIT_TRAIL” is set to OS: Obtain the last modified date for the audit
trail files. Note that the location of the audit trail files can be obtained by issuing the
Server Manager command "SHOW PARAMETER AUDIT_FILE_DEST". This
parameter defaults to $ORACLE_HOME/RDBMS/AUDIT. Here $ORACLE_HOME
represents the location path for the oracle home directory.
Since each operating system is unique, refer to operating system documentation for
specific information on testing the date and time stamps for files.
Access and modification of
sensitive and critical data is
logged, and the logs are
regularly reviewed to
assess whether the access
and use of such data was
appropriate.
1. Understand and document the policies and procedures, which the organization has
established regarding access to critical or sensitive data. Specifically, identify and
document policies and procedures that outline the organization’s acceptable levels of
risk and audit trail requirements. Note any special considerations relating to the data,
organization, regulatory requirements, and/or business environment that might affect
the criteria used for determining the sensitivity of database objects. Understand and
document the extent and nature of the data in the Oracle database.
2. Obtain a complete list of all critical objects in the database with the cooperation of
management.
3. Obtain a complete list of all candidate critical objects in the database by running the
following SQL statements.
COLUMN owner FORMAT A15 WRAP
COLUMN object_name FORMAT A30 WRAP
COLUMN subobject_name FORMAT A20 WRAP
COLUMN object_type FORMAT A11 WRAP
BREAK ON object_type SKIP 1
SELECT object_type, owner, object_name, subobject_name
FROM sys.dba_objects
WHERE (owner <> ‘SYS’ AND object_type <> ‘VIEW’) and (owner <> ‘SYSTEM’
AND object_type <> ‘UNDEFINED’) AND (owner <> ‘PUBLIC’ AND object_type
<> ‘SYNONYM’)
ORDER BY object_type;
The result of this query will reflect all the objects in the database except those objects
which have been specifically excluded in the 'WHERE' statement above. The 'WHERE'
statement can be omitted if a complete listing of all objects is desired. Review this list
of objects to determine which are the “critical” and/or “sensitive” objects. This review
should be done with an understanding of the business risks to the organization. The
majority of the critical and/or sensitive objects will be a “table” object type since all
the data is generally stored in a ‘table.’ Reconcile this compiled list of objects against
15
Page 16
ORACLE DATABASE
Control
Objective
Control Activity
Test of Control
Conclusion
Signoff
the client’s list of objects , which they consider critical or sensitive. Cooperation with
management, data owners, and/or database administrators should be sought when
completing this reconciliation.
Compare the list obtained in step 2 with the list obtained in step 3 and check for
reasonableness.
4. A check must be done to identify the actual object auditing options, which have been
implemented in the database. All critical database objects should be audited. To verify
auditing options, run the following SQL commands:
SELECT alt, aud, com, del, gra, ind, ins, loc, ren, sel, upd, ref, exe, cre, rea, wri
FROM sys.dba_obj_audit_opts
WHERE object_name = UPPER('&object_name');
This SQL statement should be repeated for ALL objects which have been identified as
critical from step 2 above. This query will prompt for an input parameter "input_role"
which should be entered as the name of the object of interest. In SQL*PLUS, once the
SQL statement is entered, it is stored in the buffer and can be re-executed by simply
entering the forward slash (‘/’) and pressing <ENTER>.
Evaluate the list of auditing options for each critical object being tested to see if they
are appropriate. The return values for the above query will involve a list of auditing
options. Each of the list members represents the first three letters of the audited
command: ALT = alter, AUD = audit, COM = comment, DEL = delete, GRA = grant,
IND = index, INS = insert, LOC = lock, REN = rename, SEL = select, UPD = update,
REF = references, EXE = execute, CRE = create, REA = read, WRI = write. The
auditing options for each audited command will be represented by a 3 character value.
The first character represents the option for a command that is executed successfully.
The second character is always a slash ('/') and is a separator. The third character
represents the option for a command that is executed unsuccessfully. The first and third
digits can be one of 3 values: A dash ('-') for 'none', an 'S' for 'by session', and an 'A' for
'by Access'. 'By session' means that when a command is executed, it is audited only
once (the first time) during the lifetime of the open user session. 'By access' means that
the command will be audited each time it is executed. For example, the value '-/-' would
mean that no auditing would occur for the associated command. The value of '-/A'
would mean that an auditing record will not occur whenever the command is
successfully issued but will occur every time (by access) that the command is not
successfully executed. The value of 'A/S' would mean that an auditing record would be
created every time (by access) the command is successfully executed and only once per
session (by session) whenever the command is not successfully executed. For further
explanation, please refer to Oracle Reference documentation.
16
Page 17
ORACLE DATABASE
Control
Objective
Control Activity
Test of Control
Conclusion
Signoff
An alternative procedure for testing this step is available below. This procedure
involves the spooling of a complete listing of all objects and their associated audit
options to a text file for later use. Run the following commands:
SET LINESIZE 150
SPOOL full_file_path_and_name
SELECT owner, object_name, object_type, alt, aud, com, del, gra, ind, ins, loc, ren, sel,
upd, ref, exe, cre, rea, wri
FROM sys.dba_obj_audit_opts
ORDER BY object_name;
SPOOL OFF
SET LINESIZE 80
Note that the full_file_path_and_name should be replaced with the appropriate file
name and path. For example 'C:\temp\spoolfile.txt'.
This query will return a complete list of all objects alphabetically which can be used to
assess the auditing options on any object. The list should be used as a reference list to
look up information on any object deemed to be critical/sensitive.
5. Determine whether the default settings for new objects are appropriately set, run the
following SQL statement:
select * from ALL_DEF_AUDIT_OPTS;
Note that in this case the return values will only be -, A, or S where - = None A =
access and S = session. In general, finding the default options all set to ‘NONE’ would
not result in an exception. Since this parameter is the default, setting too many of the
options to A or S would adversely affect system performance if a new object were
created. However, if all the default options are set to ‘NONE’one, procedures should be
in place to ensure that whenever a new object is created, auditing options for that object
are appropriately determined and set.
Security violation
monitoring procedures are
designed and implemented.
1. Understand and document company policies, procedures, standards, and/or guidance
regarding periodic review of security violations. Corroborate the functioning and
effectiveness of such policies, procedures, standards, and guidance through (a) inquiry
of individuals responsible for database and security administration, and (b) reference to
client documentation of the procedures performed. Ensure that adequate
documentation exists.
Note that if the Oracle audit trail function is relied upon by the company for this
control, the integrity of audit trail should be tested. Refer to control activity OR454.
2. Identify any reports that exist in the company that are used to monitor security
17
Page 18
ORACLE DATABASE
Control
Objective
Control Activity
Test of Control
Conclusion
Signoff
violations. Verify through documentation that such reports are regularly reviewed by
appropriate security administrators. At a minimum, such reports should contain
information on failed log in attempts. Verify that the "CREATE SESSION" system
privilege is audited and reported.
Use of system privileges is
logged and reviewed on a
regular basis.
1. Understand and document policies, procedures, standards, and guidance regarding
auditing within the Oracle database. If management chooses not to use or rely on oracle
database auditing to log use of system privileges, no further testing is required for this
control activity.
2. Verify that the Audit trail is configured correctly in the database by obtaining a copy of
INIT.ORA and ensuring that the AUDIT_TRAIL parameter is set to DB, OS, or TRUE.
Further verify that the audit trail is active by issuing the command "SHOW
PARAMETER AUDIT" in SERVER MANAGER. Note whether the audit trail is set to
OS or DB. If the audit trail is set to FALSE, no further testing is required. This is an
exception.
3. Corroborate with the database administrator(s) and security administrator(s) the extent
and use of database auditing related to system privileges. Specifically identify all the
database auditing options set for system privilege auditing by executing the following
SQL commands:
SELECT *
FROM sys.dba_priv_audit_opts
WHERE user_name is NULL
ORDER BY privilege;
This query will return a list of all the system privileges that are audited at the system-
wide level. Auditing options that are set for specific users are not listed. If a complete
listing of system privilege auditing options are required (for more detailed testing of
this control activity), including, those set for a specific users, the 'WHERE' clause of
the above SQL command can be excluded.
In general, a total of 90 rows will be returned for this query. This represents the
auditing of ALL system privilege use. If the auditing options for any of the system
privileges are not set, an appropriate business need should be demonstrated. 'System
performance degradation' is generally not a valid explanation. It should be followed up
with evidence. It should be noted that system privileges are, in general, used
infrequently and should not place a significant impact on system performance. If an
application user account excessively utilizes system privileges to do work, it may be
prudent to investigate alternative means of granting access to that account which would
minimize use of system privileges.
18
Page 19
ORACLE DATABASE
Control
Objective
Control Activity
Test of Control
Conclusion
Signoff
A.3
IR5005 In the event of a disaster, essential business processes and information systems can be recovered timely.
Archiving procedures have
been defined and
implemented for data and
redo logs
1. Obtained current archive log setting at the PRD database by executing the following at
Server Manager (SVRMGR):
SVRMGR> ARCHIVE LOG LIST
Here is a sample of the return value:
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/PRD/saparch/PRDarch
Oldest online log sequence 30879
Next log sequence to archive 30882
Current log sequence 30882
Review the output, and make sure that ‘database log mode’ is ‘archive mode’, indicating
archive mode is turned on. In addition, ensure that ‘automatic archival’ is ‘enabled’,
indicating that redo logs are automatically copied to the ‘archive destination’ once it is
filled. If any of these two are not turned, communicate with DBA and obtain the rationale
why it is not turned on.
Review the ‘archive destination’ and ensure that the archive logs are written to a separate
disk from redo logs.
2. Review the ‘alert_<SID>.log’ file for the PRD database. This file includes all parameters
that the instance read when it was started. If the following shows up in the alert_<SID>.log
at the start of the db, it indicates that archiving mode was turned since the start of the
instance.
log_archive_start = TRUE
log_archive_dest = /oracle/PRD/saparch/PRDarch
Backup are performed
regularly for Adage
production database,
source codes, and
executables/image.
Perform the following steps to ensure that all datafiles, control file(s), init.ora file, password
file, and archive logs are backed properly.
a.Obtain the location for all datafiles by executing the following SQL command:
SQL>SELECT * FROM v$datafile;
b. Obtain the location for all control file(s) by executing the following SQL command:
SQL>SELECT * FROM v$controlfile;
c. Obtain the location for init.ora by obtaining the ‘$ORACLE_HOME’ directory. In UNIX
environment, execute ‘echo $ORACLE_HOME’ at O/S level to find out.
19
Page 20
ORACLE DATABASE
Control
Objective
Control Activity
Test of Control
Conclusion
Signoff
20
d. Determine if the password file is used in the tested database by executing the following at
server manager level: SVRMGR>SHOW PARAMETER
REMOTE_LOGIN_PASSWORDFILE
If the return value is NONE, no password is used.
If the return value is SHARED or EXCLUSIVE, password file is used. In UNIX
environment, password file is located in the $ORACLE_HOME/dbs directory, while in NT
environment, the password file is located at %ORACLE_HOME%\DATABASE.
e. Obtain the location for the archive logs by executing the following server manager
command: SVRMGR>SHOW PARAMETER archive The return value for the parameter
‘log_archive_dest shows the location for archive logs.
f. Understand and document company policies, procedures, standards, and guidance
regarding backup and recovery. Corroborate the functioning and effectiveness of such
policies, procedures, standards, and guidance through (1) inquiry of individuals responsible
for file/directory access setup, and (2) reference to client documentation of the procedures
performed. Ensure that all files obtained above (step a – e) are covered in the backup
procedures.
On-going readability of
backup and retained data is
tested periodically through
restoration or other
methods.
Inquire DBA and IT manager if the backup files are regularly tested. Obtain timing and
documentation related to the last time the backup files are tested.
1