Hardening the Oracle database through secured database Initialization parameters

Oracle Initialization Parameters are used to setup or configure the Oracle Instance. The initialization parameters can be used to optimize performance of the database and set database-wide defaults and limits,

I have complied a list of important parameters that can control many of the security risks within the database. Let us look at them in details:

AUDIT_TRAIL:

This parameter is used to enable or disable database auditing Avoid setting this to NONE, DB or DB, EXTENDED. The various options available are stated below:

a.     NONE: Disables database auditing.

b.    OS: Enables database auditing and directs all audit records to the operating system's audit trail.

c.     XML: Enables database auditing and writes all audit records to XML format OS files.

d.    XML, EXTENDED: Enables database auditing and prints all columns of the audit trail, including SqlText and SqlBind values.

e.     DB: Enables database auditing and directs all audit records to the database audit trail (the SYS.AUD$ table). This option is not recommended as the DBA has access to modify these data.

f.     DB, EXTENDED: Enables database auditing and directs all audit records to the database audit trail (the SYS.AUD$ table). In addition, populates the SQLBIND and SQLTEXT CLOB columns of the SYS.AUD$ table. This option is not recommended as the DBA has access to modify these data.

 Note that setting the audit trail significantly affects the databases performance. This should be discussed with your DBA.

Reference:

http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams017.htm#REFRN10006

AUDIT_FILE_DEST

This parameter specifies the operating system directory into which the audit trail is written. Ensure the location set is one that is not accessible or writable by the DBA.

Reference:

http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams014.htm#REFRN10004

AUDIT_SYS_OPERATIONS

This parameter should be set to TRUE.

It ensures that all activities performed by users that authenticated as the SYSDBA or the SYSOPER are monitored and captured.

Reference:

http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams015.htm#REFRN10005

O7_DICTIONARY_ACCESSIBILITY

This parameter should be set to FALSE.

If this setting is set to TRUE then any user with the "select any…" privilege will have unrestricted access to any object in the database. This includes access to sensitive objects owned by SYS. For example if a user’s is granted "select ay table" privilege, then sensitive base tables owned by SYS would also be accessible. Examples of these sensitive base tables are:

o    SYS.LINK$

o    SYS.USER$

o    SYS.AUD$  and the list goes on.
These are security table that should be heavily restricted.

Reference:

http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams149.htm#REFRN10133

CONTROL_FILES

Every database has a control file, which contains entries that describe the structure of the database (such as its name, the timestamp of its creation, and the names and locations of its data files and redo log  files).

Oracle recommends that you multiplex multiple control files on different devices or mirror the file at the operating system level. The rationale is when a control file gets corrupt or out rightly has its physical storage medium damaged, then the redundant copy can be used to restore it back.

Reference:

http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams035.htm#REFRN10021

OS_AUTHENT_PREFIX and REMOTE_OS_AUTHENT

I will treat this two parameters together at the same time because they work together. OS_AUTHENT_PREFIX parameter should be set to NULL. It should NEVER be set to ops$ while REMOTE_OS_AUTHENT should be set to FALSE.

Oracle database also allows operating system authenticated accounts.  What this means is that, provided you are logged into the operating system as a user Oracle trust, then the access would be granted to the database even if a password is not provided. In other word, Oracle trusts that the operating system has authenticated the user and thus relaxes its database authentication.

Below is a screenshot of such access request which was granted.

Here I requested for database access without a password. All I used was the “/” symbol. 

This is a huge risk for an organization with such setting. 

Ensure that this setting is configured as below:

• The remote_os_authent should be set to FALSE.
• The os_authent_prefix should be set to null ie it should be empty as seen below

NAME	VALUE
remote_os_authent	FALSE
os_authent_prefix

Quick scenario

This is to demonstrate the security exposures with remote OS authentication.

o    A database named DB01 is installed on a server named SERVER01 and the client machine connected to the Server01 is named CLIENT01. 

o    Users “TAYO” was granted dba permission with  external OS authentication on the database DB01 using the following commands

alter system set remote_os_authent=true, scope=spfile;

alter system set os_authent_prefix=ops$

create user ops$tayo identified externally;

grant dba to ops$tayo;

o    However, note that the user TAYO was created only as an OS user on CLIENT01 PC. User TAYO does not exist on SERVER01

o    However, when the os user TAYO connects to the database without a user ID and password, a connection is made as seen below

sqlplus /@DB01

In this case, the connection is accepted if the remote_os_authent parameter is set to TRUE

while os_authent_prefix is also set to ops$, otherwise it is rejected.

Reference:

http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams166.htm#REFRN10152http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams199.htm#REFRN10185

OS_ROLES

This parameter should be set to FALSE.

OS_ROLES determines whether Oracle or the operating system identifies and manages the roles of each username.

If set to TRUE, then the operating system completely manages the role grants for all database usernames. When a user attempts to create a session, the username's security domain is initialized based on the groups the user belongs in the operating system.

It is safer security wise to allow the Oracle Database to manage role assignment. This is because if the Operating system is compromised it will not cascade down to the database.

Reference:

http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams167.htm#REFRN10153

REMOTE_LISTENER

This parameter should be set to NULL, unless a remote listener is needed. Discuss this with your DBA.

REMOTE_LISTENER specifies a network name that resolves to an address or address list of Oracle Net remote listeners. This parameter is used in an environment where the listener is not running on the same machine as this instance.

Reference:

http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams197.htm#REFRN10183

REMOTE_LOGIN_PASSWORDFILE

This parameter should be set to NONE.

REMOTE_LOGIN_PASSWORDFILE specifies whether Oracle checks for a password file.

• If set to SHARED, one or more databases can use the password file.
• If set to EXCLUSIVE, then the password file can be used by only one database.
• If set to NONE, Oracle ignores any password file. Therefore, privileged users must be authenticated by the operating system.

Please note that if a password file does not exist, the setting will default to NONE regardless of what was set.

If the parameter is set to NONE, discuss with your DBA for justification. 

Reference:

http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams198.htm#REFRN10184

REMOTE_OS_ROLES

This parameter should be set to FALSE.

REMOTE_OS_ROLES specifies whether operating system roles are allowed for remote clients. This should be set to FALSE such that Oracle database is allowed to manage the role assignment.

Reference:

http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams200.htm#REFRN10186

RESOURCE_LIMIT

This parameter should be set to TRUE.

The Oracle RESOURCE_LIMIT parameter determines whether resource limits are enforced in database profiles. If Oracle resource limits are disabled, any defined profile limits in the DBA_PROFILES will be ignored. Please note that this parameter does not apply to password resources.

Audit notes

• Discuss with the DBA if a customized profile has been created for named/human users. If customized profile exist , note it down
• Run the following query to ascertain the profile setting (note that we are excluding the PASSWORD resource type) 

select * from dba_profiles where
upper(resource_type) not in ('PASSWORD')
order by profile
 

• Ascertain if the profile settings are different from the default profile settings. 
• If different ensure that the RESOURCE_LIMIT parameter in the V$parameter is set to TRUE by running this query to confirm

select name,value from v$parameter where lower(name)='resource_limit'

•  If set to false recommend it be set to TRUE otherwise the customized setting will have no effect on the database profile.

Reference:

http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams202.htm#REFRN10188

SEC_CASE_SENSITIVE_LOGON

This parameter should be set to TRUE

SEC_CASE_SENSITIVE_LOGON enables or disables password case sensitivity in the database.

Reference:

http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams211.htm#REFRN10299

SEC_MAX_FAILED_LOGIN_ATTEMPTS

This parameter should be set to the value as stated in your organizations security standards.

SEC_MAX_FAILED_LOGIN_ATTEMPTS specifies the number of authentication attempts that can be made by a client on a connection to the server process. Connection is dropped after the specified number of failure attempts has been reached

Reference:

http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams212.htm#REFRN10

SEC_PROTOCOL_ERROR_FURTHER_ACTION

This parameter should be discussed with your DBA.

Note that if set to CONTINUE, The database server may be subject to a Denial of Service (DoS) if bad packets continue to be sent by a malicious client.

It is recommended to avoid the CONTINUE option.

SEC_PROTOCOL_ERROR_FURTHER_ACTION specifies the further execution of a server process when receiving bad packets from a possibly malicious client.

This can be used to prevent Denial of Service attacks.

Reference:

http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams213.htm#REFRN10282

SEC_PROTOCOL_ERROR_TRACE_ACTION

Specify the action a database should take when a bad packet is received.

ALERT or LOG option is recommended to be used to capture the event as a minimal amount of disk space is used.

TRACE option generates a detailed trace file and should only be used when debugging as it can generate significantly larger files over time.

This should however be discussed with your DBA and Storage team

Reference:

http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams214.htm#REFRN10283

SEC_RETURN_SERVER_RELEASE_BANNER

This parameter should be set to FALSE.

It specifies whether or not the server should return the complete database software information to clients. Setting it to false reduces the chances of a successful reconnaissance attack.

Reference:

http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams215.htm#REFRN10275

SMTP_OUT_SERVER

This parameter should list only authorized SMTP servers. SMTP_OUT_SERVER specifies the SMTP host and port to which UTL_MAIL package delivers out-bound E-mail.

If found to have values, discuss with your DBA on the justification.

Note that if set, it can be used to send data back to a malicious entity.

It is recommended to be left blank.

Reference:

http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams230.htm#REFRN10241

SQL92_SECURITY

This parameter should be set to TRUE.

SQL92_SECURITY enforces the requirement that a user must have SELECT privilege on a table in order to be able to execute UPDATE and DELETE statements while using the  WHERE clause.

Note that setting the parameter to TRUE should be thoroughly tested before implementing on production environment.

Reference:

http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams233.htm#REFRN10207

UTL_FILE_DIR

This should be set to a specific directory used only for necessary purposes.  Values such as /tmp or * should not be used. Discuss this outcome with your DBA

Reference:

http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams255.htm#REFRN10230

Click here to read more on UTL_FILE_DIR in details in this blog

Download the Checklist

You can download a checklist of these parameters from HERE