The article outlines the Security practices to be followed to protect the database from insider’s threat.It can be used as a Guide to plan and implement the practices in your environment. Please share your comments and experiences in the comment section below.
Database security have been categorized into the below components.
- Securing the Database logons and Oracle Binaries at the OS level
- Configuring Network Authentication (JDBC and OCI clients)
- Securing application Data
- Fine Grained Access to the Tab
- Dictionary table access, DBA roles
- Backup security
Securing the database logons and Oracle binaries
- Ensure that no other users are connected while installing Oracle 11g.In order for other users to avoid overwriting installation files when installation is in progress, if possible the installation should be done offline. If installation is performed with box connected to network, ensure that no other users are logged in. It is further recommended that $TMP and $TMPDIR environment variables to a protected directory with access given only to the Oracle software owner and the ORA_INSTALL group.
- Minimal component installation – Ensure that only components necessary for the target environment are installed. Installation of all components increases the attack surface of the database server. Remove unused components which may have been installed in previous installation.
- Ensure that latest version and patches are in use. Using outdated or unpatched software will put the Oracle database and host system at unnecessary risk and violates security best practices. In order to get details on latest patches and versions, visit Oracle’s website.
- Set “umask” to 022 – “umask” must be set even before installing Oracle Database, since improper “umask” settings can lead to unrestricted permissions. Ensure that “umask” value is set to 022 for the owner before installing Oracle.
- It is recommended to verify permissions for important files and directories to avoid unauthorized access to important files and folders. There are many files and directories created during the installation process. Following is one example of permission hardening:
Ex: All files in $ORACLE_HOME directories (except for $ORACLE_HOME/bin) must have permission set to 0750 or less. For files in $ORACLE_HOME/bin directory, permissions should be set to 0755 or less
- su to Oracle Software owner should be disabled in the OS level
- No OS users other than Oracle software owner should belong to dba and oinstall This prevents sysdba logins to the database.
- Disable/Remove “Tkprof” utility from Production. “TKProf” is an Oracle database utility used to format SQL Trace output into human readable format. It is a powerful tool for an attacker to find issues in the running database and hence should be removed from Production environment. If it is not possible to remove “Tkprof”, ensure that file permissions are restricted. On *Nix based systems, set file permissions to 0750 or less.
Configuring Network Authentication
- Listener settings – Listener Configuration of the database should be in non-default port other than 1521. Also set the DB parameter Local Listener to the listener which is configured with non-default port. This disables PMON registration of the DB with the default listener. Also, it is recommended that IP Addresses be used instead of using host names. This will provide a defense against DNS spoofing attacks. This can be done by renaming DNS names with IP Addresses in listener.ora file.
Protect the Listener with password by using Change password command which prevents un-authorized administration of Listeners. Once the password is set, save the configuration using save config command. Now all Listener activities should have a valid password entered.
- Securing Client Connections – The default setting, REMOTE_OS_AUTHENT = FALSE, creates a more secure configuration that enforces proper, server-based authentication of users connecting to an Oracle database. Setting the initialization parameter REMOTE_OS_AUTHENT to TRUE forces the database to accept the client, operating-system user name received over a nonsecure connection and use it for account access. Because clients, such as PCs, are not trusted to perform operating system authentication properly, it is poor security practice to use this feature.
- Network encryption – Network encryption refers to encrypting data as it travels across the network between the client and server. The reason for data encryption at the network level is because data can be exposed on the network level.
Connection Encryption types for client and server can be set as follows: The sql.net ora file of server and client should have below entries respectively to enable network encryption.
On the server:
SQLNET.ENCRYPTION_SERVER = [accepted |rejected |requested |required]SQLNET.ENCRYPTION_TYPES_SERVER = (valid_encryption_algorithm[,valid_encryption_algorithm])
On the client:
SQLNET.ENCRYPTION_CLIENT = [accepted |rejected |requested |required]SQLNET.ENCRYPTION_TYPES_CLIENT = (valid_encryption_algorithm [,valid_encryption_algorithm])
Communication between the client and the server is only possible on the basis of an agreement between both the components regarding the connection encryption. To ensure encrypted communication, it is recommended to set the value to “REQUIRED”
Valid algorithms are listed below
-
AES256: Advanced Encryption Standard (AES). AES was approved by the National Institute of Standards and Technology (NIST) to replace Data Encryption Standard (DES). AES256 enables you to encrypt a block size of 256 bits.
-
RC4_256: Rivest Cipher 4 (RC4), which is the most commonly used stream cipher that protects protocols such as Secure Sockets Layer (SSL). RC4_256 enables you to encrypt up to 256 bits of data.
-
AES192: Enables you to use AES to encrypt a block size of 192 bits.
-
3DES168: Triple Data Encryption Standard (TDES) with a three-key option. 3DES168 enables you to encrypt up to 168 bits of data.
-
AES128: Enables you to use AES to encrypt a block size of 128 bits.
-
RC4_128: Enables you to use RC4 to encrypt up to 128 bits of data.
-
3DES112: Enables you to use Triple DES with a two-key (112 bit) option.
Securing application data
TDE (Transparent Data Encryption)
- Transparent Data provides an additional layer of security by transparently encrypting column data stored on disk.
- TDE protects the data from unauthorized access such as direct disk access to the database host server and backup media that contains copies of our data files
- Transparent data encryption is a key-based access control system. Even if the encrypted data is retrieved, it cannot be understood until authorized decryption occurs, which is automatic for users authorized to access the table
- Even Log miner cannot be used to decrypt the data that has been encrypted
- Expdp can export the tables that are encrypted but required a valid authentication while importing
- TDE comes with belongs to the Advanced Security Option that is available as an Option for the Oracle Database Enterprise Edition only
Key Management
The keys for all tables containing encrypted columns are encrypted with the database server master key and stored in a dictionary table in the database. The master key is stored in the wallet file. Both master key and table keys are encrypted with AES256. When we create a wallet, the master key is generated automatically using a secure random number generator which is different from the wallet password.
A wallet file which stores the master key should be accessible only to the security administrator.
Setup
Specify the Wallet File location in sqlnet.ora file as the following parameter ENCRYPTION_WALLET_LOCATION
- Create a wallet by using Alter system set Encryption key identified by “wallet password “command.
- Once the wallet is created open the same after each database restart by using
- ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY “ wallet password “ command
- Once the wallet is opened any columns that needs encryption can be created as below
Create table CUST_PAYMENT_INFO (first_name varchar2(11), last_name varchar2(10), order_number number(13), CREDIT_CARD_NUMBER varchar2(20) ENCRYPT NO SALT);
Encryption settings
- Revoke the “PUBLIC” execute privileges from the “DBMS_OBFUSCATION_TOOLKIT”
By removing “PUBLIC” privileges from “DBMS_OBFUSCATION_TOOLKIT”, attackers will not be able to decrypt data, thus the limiting malicious use.
2. Tablespace encryption
If a tablespace contains large amount of confidential data (e.g. PII data), it is recommended that entire tablespace should be encrypted rather than encrypting a particular column.
3. Usage of Hardware Security Modules (HSM)
Where possible, it is recommended that HSM be used to store master encryption keys. HSM is a more secure alternative to Oracle Wallet. It also provides secure computational space (memory) to perform encryption and decryption operations.
Logging and Disaster Recovery
1. Regular backup of redo logs
The most crucial structure for recovery operations is the redo log. It is very important to ensure that redundancy of Redo logs is looked into since Redo logs can prevent catastrophic losses in an event of disk failure or a system crash. Ensure that Redo logs are backed up regularly on redundant disks/systems. This way in event of failure of primary disk, secondary disks are available from where Redo logs can be recovered.
2. Regular backup of control files
Every Oracle Database has a control file, which is a small binary file that records the physical structure of the database. Without the control file, the database cannot be mounted and recovery is difficult. Mirror control files to multiple separate physical partitions to ensure that they are available in event of system crash or failure or if control files are corrupted.
3. Ensure sufficient space is allocated for the archive logging process
If enough space is not allocated for archive logging process, it may cause the system to hang resulting into a Denial of Service. Allocating more disk space to redo logs partition is recommended.
4. Backup Verification
Automated Backups should be verified by performing recoveries to ensure that such automated backups function properly. Failure to do so may cause inability to recover the data leading to a data loss situation.
Securing Backup
1. RMAN Encryption
RMAN backups created as backup sets can be encrypted. Encrypted backups cannot be read if they are obtained by unauthorized people. Encrypted backups are decrypted automatically during restore and recover operations, as long as the required decryption keys are available, by means of either a user-supplied password or the Oracle Encryption Wallet. RMAN offers three encryption modes: transparent mode, password mode, and dual mode.
The BACKUP command arguments do not change for creating encrypted backups. Encryption is performed based on the encryption settings specified. Oracle uses a new encryption key for every encrypted backup.
Encryption can have a negative effect upon backup performance since encrypted backups consume more CPU resource than non-encrypted backups the same can be improved using more RMAN channels.
Transparent Encryption of Backups
Transparent encryption can create and restore encrypted backups using TDE key management.
This is the default mode for RMAN encryption. A Database Wallet must be opened before encrypting the backup. CONFIGURE ENCRYPTION command can be used to take the backup. This does not require DBA intervention.
Password Encryption of Backups
Password encryption requires a password to be provided by DBA when creating and restoring encrypted backups. Restoring a password-encrypted backup requires the same password that was used to create the backup. Password encryption is useful for backups that will be restored at remote locations, but which must remain secure in transit. Password encryption cannot be persistently configured. The Oracle Encryption Wallet need not be configured if password encryption is to be used exclusively.
SET ENCRYPTION ON IDENTIFIED BY password is used to specify the backup.
Dual Mode Encryption of Backups
Dual-mode encrypted backups can be restored either transparently or by specifying a password. Dual-mode encrypted backups are useful when backups that are normally restored on-site using the Oracle Encryption Wallet, but which occasionally need to be restored off-site, where the Oracle Encryption Wallet is not available.
When restoring a dual-mode encrypted backup, Oracle Encryption Wallet or a password for decryption can be us
2. EXPDP Encryption
Data pump encryption is available only for Enterprise Editions and this is available only from 11.1 versions.
Encryption Parameters used while taking export are
ENCRYPTION or ENCRYPTION_PASSWORD
The use of encryption is controlled by a combination of the ENCRYPTION or ENCRYPTION_PASSWORD parameters. The allowable values and their default settings are explained below:
ALL: Both metadata and data are encrypted.
DATA_ONLY: Only data is encrypted.
ENCRYPTED_COLUMNS_ONLY: Only encrypted columns are written to the dump file in an encrypted format.
METADATA_ONLY: Only metadata is encrypted.
NONE: No Encryption
If neither the ENCRYPTION nor ENCRYPTION_PASSWORD parameters are set, it is assumed the required level of encryption is NONE
ENCRYPTION_ALGORITHM
The ENCRYPTION_ALGORITHM parameter specifies the encryption algorithm to be used during the export, with the default being “AES128”.
ENCRYPTION_ALGORITHM = {AES128 | AES192 | AES256 }
The ENCRYPTION_ALGORITHM parameter must be used in conjunction with the ENCRYPTION or ENCRYPTION_PASSWORD
ENCRYPTION_MODE
The ENCRYPTION_MODE parameter specifies the type of security used during export and import operations.
ENCRYPTION_MODE = {DUAL | PASSWORD | TRANSPARENT}
The allowable values and their default settings are explained below:
DUAL: This mode creates a dump file that can be imported using an Oracle Encryption Wallet, or the the ENCRYPTION_PASSWORD specified during the export operation. This is the default setting if the ENCRYPTION_PASSWORD parameter is set and there is an open wallet.
PASSWORD: This mode creates a dump file that can only be imported using the ENCRYPTION_PASSWORD specified during the export operation. This is the default setting if the ENCRYPTION_PASSWORD parameter is set and there isn’t an open wallet.
TRANSPARENT: This mode creates an encrypted dump file using Oracle Encryption Wallet.This is the default setting of only the ENCRYPTION parameter is set.
Database profiles
1. Limiting failed login attempts
Restricting the number of failed login attempts helps in thwarting against brute force attacks, dictionary attacks and similar other automated attacks on password management.
Application accounts must be set for:
failed_login_attempts=3
Depending on the organization’s password policy this value can be modified. Normally this value is set to a value “3” or “5”.
2. Fine tuning Profile Password Settings
By default the Password age is 180 days. It is recommended that this age be set to “90” days (unless the password policy states otherwise). Following query should help in setting the life time of password to 90 days:
ALTER PROFILE profile_name LIMIT password_life_time 90;
“password_reuse_max” sets the number of different passwords that must be rotated by the user before the current password can be reused. This prevents users from cycling through a few common passwords and helps ensure the integrity and strength of user credentials. Recommended setting is “20”. Following query can be used for the same.
ALTER PROFILE profile_name LIMIT password_reuse_max 20;
“password_reuse_time” sets the amount of time that must pass before a password can be reused. Creating a long window before password reuse helps protect from password brute force attacks strengthens the integrity of the user credential. Default setting is “unlimited” and recommended setting is 365 (unless local policy overrides it). Following query can be used to achieve the same.
ALTER PROFILE profile_name LIMIT password_reuse_time 365;
“password_lock_time” specifies the time duration (in days) for which the account will be locked out, should the maximum number of authentication attempts be reached. Recommended setting is “1”. Following query can be used to set this limit
ALTER PROFILE profile_name LIMIT password_lock_time 1;
“password_grace_time” specifies the amount of time (in days) that the user is warned to change their password before their password expires. Recommended setting is “3”, which again could be overridden by organization’s password policy. Following query can be used to update the grace time.
ALTER PROFILE profile_name LIMIT password_grace_time 3;
Allow “password_verification_function” to be called when passwords are changed. This always works for password changes via the “password” command at an SQL prompt. Oracle provides “utlpwdmg.sql” which can be used to create a password verification function. If this script is used to create a password verification function, make the following changes at the bottom of the “utlpwdmg.sql” file (or modify the values mentioned below as per organization’s password policy):
PASSWORD_GRACE_TIME 3
PASSWORD_REUSE_TIME 365
PASSWORD_REUSE_MAX 20
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1
3. Disallow Remote Operating System (OS) Authentication
If Remote OS authentication is set for a user and if the host operating system has a userid same as the OS username/userid, then Oracle will not check its credentials anymore. It simply assumes the host must have done its authentication and lets the user into the database without any further checking.
Check and review any user who has “ password=’EXTERNAL’ ”.
Set Idle Time
Idle sessions held open for excessive periods of time can consume system resources and cause a denial of service for other users of the Oracle database. Limit the maximum number of minutes a session can be idle. To set the Idle time, following query can be used
ALTER PROFILE profile_name LIMIT IDLE_TIME <value>;
Profiles/User – Access settings
1. Fine tuning access controls on Tablespace
Do not have “default_tablespace” set to SYSTEM for user accounts. Only SYS should have a default tablespace of SYSTEM. This prevents administrative users from altering system objects.
Note: It may be difficult to move some objects
Following query can be used for the same:
ALTER USER DEFAULT_TABLESPACE table
2. Removal of Default Accounts
When Oracle Database is installed, the installation process creates a set of predefined accounts – also known as default accounts. These accounts should be either locked or removed from the Database. If the accounts have been locked, a regular check should be performed to ensure that these accounts have not been unlocked. Following SQL query should be helpful in finding default accounts:
SELECT * FROM DBA_USERS_WITH_DEFPWD;
3. Fine tuning access controls on Tables
- Prevent access to ”SYS.AUD$”
Allowing users to alter the “AUD$” table can compromise the audit trail or integrity of the Oracle database.
- Prevent access to “SYS.USER_HISTORY$”
Revoke access to this table from all users and roles except for SYS and DBA accounts. Allowing users to alter the “USER_HISTORY$” table can compromise the audit trail or integrity of the Oracle database.
- Prevent access to “SYS.LINK$”
Non administrative or system users should be prevented from accessing “LINK$” table as it stores sensitive user and password data.
- Prevent access to “SYS.USER$”
Non administrative or system users should be prevented from accessing “USER$” table as it stores sensitive user and password data.
- Prevent access to “SYS.SOURCE$”
Allowing users to alter codes in the “SOURCE$” table can compromise the security and integrity of the Oracle database.
- Prevent access to any ”x$” table
“x$” tables are kernel tables used by Oracle internals and should not be accessible to users.
4. Fine tuning access controls on Views
- Prevent access to ” DBA_” views
“DBA” views return information about all objects and should only be accessible by administrators. If any user is granted access, the same should be reviewed and revoked.
- Prevent access to any “v$” views
“v$” tables contain sensitive information about Oracle database and should only be accessible by system administrators.
- Prevent access to “ALL_SOURCE”
Users should not have access on this view since “ALL_SOURCE” contains data about all of the user’s objects in text form.
- Prevent access to “DBA_ROLES”
Allowing users to access “DBA_ROLES” view may result in privilege escalation and hence access of this view should be only limited to SYS and DBA’s.
- Prevent access to “DBA_SYS_PRIVS”
Allowing a user to access the “dba_sys_privs” will show the users’ privileges for all users in the Oracle database and hence the access to view should be restricted only to SYS and DBA’s.
- Prevent access to “DBA_ROLE_PRIVS”
Allowing a user to access the “dba_role_privs” view will show the roles privileges for all roles in the Oracle database and hence the access to view should be restricted only to SYS and DBA’s.
- Prevent access to “DBA_TAB_PRIVS”
Allowing a user to access the “dba_tab_privs” view will show the table privileges for all the users in Oracle database and hence the access to view should be restricted only to SYS and DBA’s.
- Prevent access to “DBA_USERS”
Allowing a user to access the “dba_users” view will show the role privileges for all the users in Oracle database and hence the access to view should be restricted only to SYS and DBA’s.
- Prevent access to “ROLE_ROLE_PRIVS”
Allowing a user to access the “role_role_privs” view will show the role grants for all the roles in Oracle database and hence the access to view should be restricted only to SYS and DBA’s.
- Prevent access to “USER_TAB_PRIVS”
Allowing a user to access the “user_tab_privs” view will show the granted table privileges for all the users in Oracle database and hence the access to view should be restricted only to SYS and DBA’s.
- Prevent access to “USER_ROLE_PRIVS”
Allowing a user to access the “user_role_privs” view will show the granted role privileges for all the users in Oracle database and hence the access to view should be restricted only to SYS and DBA’s.
- Prevent access to “ALL_”
Revoke public access to all views that start with “ALL_” when possible to avoid unauthorized disclosure of data.
5. Fine tuning access of dbms_packages
These packages are powerful and allow network access (e.g. utl_tcp, utl_http,…), file access (dbms_advisor, utl_file, …), unsecure (dbms_random) or other powerful operations (e.g. dbms_obfuscation_toolkit). Execution privileges on these package should not be granted to public.
– dbms_sql(No. 1, allows privilege escalation)
– utl_fileutl_mail
– utl_inaddr
– utl_tcp(No. 3, overtake the DB via TNS Listener)
– dbms_lob
– dbms_xmlgen(No. 2, steal the entire DB with a single SQL Injection)
– dbms_aw_xml
– ctxsys.drithsx
– ordsys.ord_dicom
– kupp$proc
6. Audit Trial
Auditing is monitoring and recording of selected user database actions. Auditing is normally used to investigate suspicious activity or to Monitor and gather data about specific database activities. However there will be performance impact on the Database by enabling audit
DB initialization parameters that influence Auditing are
audit_file_dest – specifies the OS directory used for the audit trail when the os, xml and xml, extended options are used
audit_sys_operations – static parameter enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges, including the SYS user
AUDIT_TRAIL = {none | os | db | db, extended | xml | xml, extended}
The following list provides a description of each setting:
none or false – Auditing is disabled.
db or true – Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
db,extended – As db, but the SQL_BIND and SQL_TEXT columns are also populated.
xml- Auditing is enabled, with all audit records stored as XML format OS files.
xml,extended – As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
os- Auditing is enabled, with all audit records directed to the operating system’s audit trail.
Audit Records and the Audit Trail
Audit records include information such as the operation that was audited, the user performing the operation, and the date and time of the operation. Audit records can be stored in either a data dictionary table, called the database audit trail, or an operating system audit trail.
The database audit trail is a single table named SYS.AUD$ in the SYS schema of each Oracle database’s data dictionary.
Types of Auditing
- Statement Auditing
Statement auditing is the selective auditing of related groups of DDL or DML Statements
- Privilege Auditing
Privilege auditing is the selective auditing of the statements allowed using a system privilege. It is more focused than statement auditing because it audits only the use of the target privilege. Privilege auditing can be set to audit a selected user or every user in the database
- Schema object Auditing
It is selective auditing of specific statements on a particular schema object, such as AUDIT SELECT ON employees. Schema object auditing is much focused, auditing only a specific statement on a specific schema object. Schema object auditing always applies to all users of the database.
DataDock 