Oracle Database Security and Audit (Includes startup work program)

Oracle database is the most widely used database by most if not all organizations

worldwide. From keeping records of customer data in banks, hospitals etc, subscriber

data and call logs by telecommunication companies to as big as keeping the entire

identity record of citizens of a nation. 

Because of its popularity of usage, it has become a target for malicious entities to

attempt a compromise on the database with the hope of:

    1.  Stealing sensitive information,

    2.  Maliciously altering sensitive data,

    3.  Performing unauthorized data modification,

    4.  Denying access to original owners (DOS)

And the list goes on.

This post will focus on how you can effectively audit an oracle database in order to

properly protect it from external and internal attacks and misuse.

Examples on this post will be based on Oracle Database versions 11g r2

So let’s begin

As usual to begin we must first identify the risk points.

Risk Points

1.    Poor Database Access Controls

2.    Insecure Database Configuration

3.    Poor Database Auditing

We will look at these risk points one by one in a detailed manner.

And as usual at the end, I will make available my startup work program.

What is required for this audit

·         Database access to Oracle. Ensure your DBA grants you the following permissions: ü  “SELECT_CATALOG_ROLE”:  This role grants you read-only access to every object in the database. ü  “SELECT ANY TABLE”: This is a system privilege that allows you to view any table in the database. ·         SQLNAVIGATOR or any other sql runtime environment

Risk 1: Poor Database Access Controls

Access controls are critical for a properly secured database through the use of

Authentication and Authorization.

Authentication requires verifying the identity of the user that request access to data,

resources, or applications thus validating identity and establishing a trust

relationship for further interactions. Authorization on the other part is required to

allow or limit the levels of access and action permitted to that entity.

We will look at all these in details.

1.  Ensure exited staff no longer have access to the database

    Exited staff should never be found active in and enterprise database.

    When users are created, they are stored in table called DBA_USERS

    Audit point

    ·   Request for the list of Exited staff from HR department

    ·   Run the following query. This should display the list of active users on the database

 select user_id,username,account_status from dba_users 

where lower(account_status)='open';

    ·   Compare the list with the exited staff provided by HR.

    ·   Any exited staff in the list should be immediately disabled.

2.  All default accounts with default passwords should be disabled

     Oracle comes shipped with several default users with known default passwords. These default users include the Administrative accounts (SYS and SYSTEM). It is important that the passwords be changed for those accounts required to be active while the rest should be immediately locked.

   

     Audit point

     ·   As from Oracle 11g and above, a view has been created which displays default accounts that are currently using their default passwords (DBA_USERS_WITH_DEFPWD)

     ·   Run the following query. This should display the list of active default users on the database that are still running with their default and known password.

select user_id,username,account_status from dba_users 

where lower(account_status)='open' 

and username in (SELECT USERNAME FROM DBA_USERS_WITH_DEFPWD);

    ·   Any users found in this list should be immediately disabled and a query sent to the database administrator to explain why the accounts are in use.

    ·   Click here to view all oracles default users and their known passwords

3.  Ensure that Database profiles are effectively distributed

     Database user accounts are mapped to profiles to ensure the following:

·   It should be noted that password ageing, expiration and history are managed through profiles. This profile should conform to the organisations password policy

     ·    The database resource usage are effectively controlled.

     Oracle database store these profiles in a table (DBA_PROFILES).

     It should be noted that any organisation can create customised profile to fit its organisational policy.

     Audit point

     ·   Obtain the organizations information security policy and IT Policy.

     ·   Ascertain that the organization has established a password standard in either of the policies obtained.

     ·   Run the following query  to ascertain which profiles were assigned to the active users on the database:

select user_id,username,account_status,profile from dba_users

where lower(account_status)='open';

     ·   Additionally run the following query on the database and note down the profiles available with their values:

select * from dba_profiles order by profile;

     The following profiles: (DEFAULT and MONITORING_PROFILE) are profiles with

     unlimited resources and unstructured password settings. These should not be used for named/human user accounts. 

     They can however be mapped to application accounts that don’t require password

     expiration.

     A sample default profile looks like the table below:

     Notice the Resource_Type called PASSWORD. This DEFAULT profile should never be assigned to Named/Human user accounts

       ·   Ascertain if there exist profiles asides those stated above that conforms to the organization password policy. Note the LIMIT of the RESOURCE_TYPE with “PASSWORD”.

        ·   Ascertain that these profiles are the only profiles mapped to the named/human user accounts. Any profiles other than these is an exception which should be immediately remediated. In addition, the DBA should be queried on why such mapping was done.
      ·   A sample of how it should be can be seen below. I have created a profile called SECURE_PROFILE

 4.  Ensure external authentication is prevented

      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 screen shot of such access request which was granted. 

     Here I requested for database access without a password.

     All I used was the “/” symbol. 

     Audit point

     ·   There are configuration parameters that come into play when using operating system authenticated accounts.

     ·   Run the following query

select name,value from v$parameter 

where lower(name)in ('os_authent_prefix','remote_os_authent');

     ·   If the value is set as seen below, then this means the database accepts Operating system authentication. 

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

     Ensure that this setting is resolved immediately by the DBA by employing the

     following remediation:

     ·  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 

     Rationale

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

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

       ·   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;

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

       ·    Thus, 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.

5.    Ensure no user is configured for external authentication

      This is in continuation to the previous audit check. Should in case OS authentication was set, then there should be users configured to connected externally.

      Audit point

     ·   To have a list of all accounts that can authenticate externally,
          Run these queries
select username,account_status from dba_users 
where lower(username) like '%ops$%';

     ·   This query should also be run to identify users that are configured with external OS authentication 

SELECT username,account_status FROM dba_users 

WHERE password='EXTERNAL';

    ·   Any user found in this list should be sent to the DBA for justification.

6.   Ensure Password Complexity check is enabled

      Oracle provides a default password complexity check with the database. The script is called utlpwdmg.sql and can be found in the following location: ORACLE_HOME/rdbms/admin.

     The default password complexity check verifies the following password attributes:

         ·   The password contains no fewer than eight characters and does not exceed 30 characters.

         ·   The password is not the same as the user name, nor is it the user name spelled backward or with numeric characters appended.

         ·   The password is not the same as the server name or the server name with the numbers one through 100 appended.

         ·   The password is not too simple, for example, welcome1, database1, account1, user1234, password1, oracle, oracle123, computer1, abcdefg1, or change_on_install.

         ·   The password includes at least one numeric and one alphabetic character.

         ·   The password differs from the previous password by at least three letters. 

A sample of the password complexity scrip can be seen here

Password Complexity script

Audit point

     ·   To ascertain that password complexity has been enabled run the following queries
select * from dba_profiles 
where upper(resource_name)='PASSWORD_VERIFY_FUNCTION' 
and lower(limit) not in ('null','default');

     ·   If the output returns a Profile, check that profile has been mapped to named/human user accounts. This is important as this profile will ensure password complexity is enforced.

     ·   Please note that Password complexity check is not enabled by default.

7.    Ensure System Privilege grants are justified and are not granted directly to named/human user accounts

      A system privilege is the right to perform a particular action, or to perform an action on any schema objects of a particular type. In other words, system privileges allow the user to perform system level activities some of which includes the following:

      o   CREATE or ALTER session

      o   DROP TABLESPACE

      o   GRANT ANY ROLE

      o   CREATE TRIGGER

      o   DROP ANY TABLE

And the dangerous list goes on. There are over 60 distinct system privileges and they

should all be monitored.

It is important that these system privilege are granted only based on need-to-use.

Direct privilege granted to named/human user account should be flagged and the

DBA be queried to provide justification for such grants.

It should be noted that system privileges should be granted to ROLEs while the

roles should then granted to users.

This makes it easier to manage and maintain.

Audit point

     ·   To ascertain if named/human user accounts are granted direct system privileges, run the following queries
select * from dba_sys_privs where grantee in (select username from dba_users
where lower(account_status)='open'
and upper(grantee) not in ('SYS','DBSNMP','SYSMAN','SYSTEM'));

     ·   Review to ascertain that named/human users are not directly granted system privilege. If found raise an exception. Query the DBA to provide justification for such grants and have the DBA sanitize immediately by granting the system privilege via roles.

8.    Ensure there are no named/human user accounts with INSERT, DELETE and UPDATE privileges on sensitive system tables.

     The Oracle user SYS owns all sensitive system tables and user-accessible views of the data dictionary. No Oracle user should ever alter (UPDATE, DELETE, or INSERT) any rows or schema objects contained in the SYS schema, because such activity can compromise data integrity. – (culled from Oracle)

     Name/human user’s accounts should also never be allowed to perform data manipulation statements (insert, delete and update) on sensitive system tables. This should be strictly enforced

      Additionally users can be granted roles which have been granted these sensitive privileges to the system tables. This should also be looked out for.

Audit point

     ·   Run the following queries to extract USERS that have UPDATE, DELETE, or INSERT privilege on SYS schema.
select * from dba_tab_privs where privilege in ('INSERT','DELETE','UPDATE')and
owner='SYS'and grantee in (select username from dba_users where
upper(account_status)='OPEN');

     ·  Discuss your outcomes with your DBA.

     ·   Also run the following queries to extract all ROLES that have UPDATE, DELETE, or INSERT privilege on SYS schema.

select * from dba_tab_privs where privilege in ('INSERT','DELETE','UPDATE')and

owner='SYS'

and grantee in (select role from dba_roles);

     ·   Ascertain the users assigned each role by running this script:

select grantee,granted_role from dba_role_privs

    ·   Discuss all your outcomes with your DBA.

9.    Ensure proper sanitation of System Privileges are enforced

     Like said earlier, privileges should only be granted through roles and not directly to users. Roles allow for easier and better management of privileges. Many DBAs still create this mess for themselves.

     What can go wrong?

     Let’s take a look at this scenario

o   USER_A an IT Staff was granted the following privileges directly:

update any table,

drop any table

o   ROLE_A was created and granted the following system privilege:

select  any table,

update any table,

drop any table

o   ROLE_B was created and granted the following system privilege:

Select any table

o   ROLE_A was granted to USER_A

o   USER_A was later redeployed to a less sensitive position

o   Because of the new position which was less sensitive, ROLE_A was revoked from USER_A and granted ROLE_B.

o   Thus the DBA now assumes USERS_A has only “select any table” privilege since he has been assigned to ROLE_B.

o   However what the DBA has forgotten is that he initially assigned direct privilege to USER_A. Removal of USER_A from ROLE_A does not deter him from having access to the sensitive privilege which he was initially granted directly.

o   This must be prevented

Audit point

     ·   Run the following query to extract number of direct system privilege granted to each active user.
select count(*) as "Nos of Direct SYS Priv",grantee from dba_sys_privs where
grantee in
(select username from dba_users where account_status='OPEN')    
Group by grantee;

     ·   On the other hand if you prefer to see the full list of users with each direct privilege granted to them, you can run this query below:
select PRIVILEGE,grantee from dba_sys_privs where grantee in
(select username from dba_users where account_status='OPEN');

     ·  Separate named/human user account from application users accounts as they have different attribute.
     ·   Raise an exception where found and get the DBA to justify it.
     ·   Immediately make a recommendation to have all the system privileges reassigned to the users through roles with major focus on named/human user account.
     ·   Note as said earlier that application user accounts might have a different attribute requiring it to have direct privileges. However this should still be explained out by the DBA.
     ·   Note also that it is normal to see the following users in the list:

SYS: Main account used to perform database administration task.

SYSTEM: Auxiliary account used to perform database administration tasks

DNSNMP: Account used by the Management Agent component of Oracle

Enterprise Manager to monitor and manage the database.

10.  Ensure proper sanitation of Table level Privileges are enforced

       User accounts can also be granted privilege to access or make modification to tables.Such permissions can be granted directly or through roles. Recall as said earlier, that it is best practice and for easier maintenance and security to grant such privileges through role rather than directly.

      Column level privilege

      Additionally privileges can also be granted to ONLY the column requested to be modified. This ensures that the entire role is not affected by the action.

      However this type of privilege can only work for modifications such as: INSERT and UPDATE.

     It should be noted that any user account that has INSERT and UPDATE on any table can perform such action on the entire table. Whereas if the user account was only given INSERT or UPDATE on the specific column requiring the action he/she would be restricted to that column in the row. 

     Let’s have a look at this scenario

o   A table called ACCTBAL keeps the account status and current balances of customers’ accounts in the bank as seen below:

o   A request was made by the Bank branch to change the Account status of Stanley with account no 0000000102 from ‘Inactive’ to ‘Active’ as customer has reactivated the account.

o   An IT staff (Caleb Rook) assigned to the tasked with user account crook made a request to the DBA to grant UPDATE privilege to the ACCTBAL table to make the necessary modification.

o   The DBA issues the following command:

grant update on acctbal to crook;

o   User account crook then issue the following query to perform the task:

update actbal set account_status=’Active’, Balance=’10,000,000’;

o   Notice that he has not only done the task assigned to him, he has also performed an unauthorized credit to the account (From 150,000 to 10,000,000).

How to avert this

Lets see how an experienced DBA could have handled it.

o   The DBA after getting the request issue the following command:

grant update(Account_status) on acctbal to crook;

o   What the DBA has now done is to restrict the update to the account_status column while all other columns are locked down. This is the best way to protect against such abuse. This should be carefully monitored on tables considered sensitive. 

To see users with column level privilege run the following query below:

 Select * from dba_col_privs;

Note

Delete privilege cannot be granted through column level privilege

     Audit point

     ·   Some user accounts can have column level privilege granted to them but might still have full privilege to the same table because of poor privilege sanitation. This must be guarded against.
     ·   Run the following query to extract users with full table access to perform (INSERT and UPDATE) and also with column level privilege.
select a.grantee, a.owner,a.table_name full_tab_PRIV, c.privilege|| ' on ' ||
c.column_name as "Col Level Priv"
from dba_tab_privs a, dba_col_privs c
where a.table_name=c.table_name
and a.grantee=c.grantee
  and a.privilege=c.privilege
     and lower(a.privilege) in ('insert','update')
order by a.grantee;

    ·   Raise an exception if any user is found and get the DBA to justify.
    ·   Ensure all such permission are immediately revoked.
    ·   Run the following query identify the users granted the delete privilege on tables. This is because Column level privilege cannot be effected on the DELETE command.
select * from dba_tab_privs where privilege in ('DELETE')
and grantee in
(select username from dba_users where account_status='OPEN')
union
select * from dba_tab_privs where privilege in ('DELETE')
and grantee in
select role from dba_roles;

    ·   Have the DBA justify any user account or role found in the list.

11.    Ensure no user account is granted access to the SYSTEM and the SYSAUX tablespaces.

     These tablespaces are sensitive. Oracle stores and manages all system critical file there. Should it be used for other activities it might get corrupted or worse still get filled up with unnecessary data from unauthorized sources thus shutting down the database.

     

       Audit point

       ·   Run the following query to confirm is any users was granted the SYSTEM or the SYSAUX tablespace as its default tablespace.

select * from dba_users where lower(account_status)='open'  

and  default_tablespace in ('SYSTEM','SYSAUX');

        ·   These are the only users Oracle has allowed to have access to the tablespace by default:

o   SYSTEM  

o   SYS     

o   SYSMAN  

o   DBSNMP  

o   MGMT_VIEW

        ·   Any users out of the ones state above should be raised as an exception.
        ·   The DBA should be asked to immediate reassign them to another less sensitive database

12.    Ensure no user is granted privilege to modify packages in the database

       A package is a group of related procedures and functions, stored together in  the database for continued use as a unit.

     If the database you are reviewing is that which host the organizations core banking, then all packages that perform various forms of transaction will also be stored in the database. ACCESS TO THESE PACKAGES SHOULD BE MONITORED AT ALL TIMES

     Only authorized persons are allowed to perform any sort of modification on packages.

     Audit point

         ·   Run the following query to ascertain if any users account has privilege to modify packages:

select * from dba_tab_privs

where table_name in (select object_name from dba_objects where object_type in

('PACKAGE')) and Privilege not in ('EXECUTE');

         ·   If the query produces any output, raise an exception and query the DBA for justification.

13.    Ensure usage and access to all database links are monitored

     A database link is used to access objects from other databases. Database links are of concern for database security.  This is because Oracle stores the USER ID and password inside the Oracle dictionary when the database link is created (the password storage has however been well secured as from Oracle versions 11g and above). Additionally, database links created as "public", will be accessible to use by anyone that has connection to the database. What this means is that anyone who knows the database link name may use it within their SQL.

     A database link creates a database connection through a user account profiled at the destination database. Such users should be reviewed to ascertain its permission on the destination database.

Audit point

         ·   Run the following query to identify all database links created:

select db_link,owner,username,host,created  from DBA_DB_LINKS

   ·   Note that If the link is PUBLIC, then the OWNER is listed as PUBLIC. 
         ·   The database links created as PUBLIC are accessible to every user. This particular db link is our primary focus.
        ·   Ascertain that the level of privilege the user configured to create the db link has.
       ·   An exception should be raised if a user is found with any sensitive privilege
       ·   Request for justification from the DBA for all PUBLIC database links created to other databases

Risk 2: Insecure Database Configuration

1.    Ensure the Database is adequately patched

     Without the appropriate patches, people with malicious intent may be able use available vulnerabilities to perform the following:

            ·  Escalate privileges

            ·  Gain unauthorized access.

            ·  Create a denial of service (DOS)

Audit point

         ·   Run the following query to check the version of your database
select version from v$instance;

  ·   Run the following query and note it down.
select comp_name,version,status,modified from dba_registry
where upper(status)='VALID';
This query displays the list of installed database options and features with the last time they were modified.

       ·   To See the status of Patche sets in the database you can also run this query:

select * from dba_registry_history

   ·  Oracle has a site where Critical Patch Updates and Security Alerts are cataloged.
         ·   As extracted from Oracle.com, Critical Patch Updates are collections of security fixes for Oracle products. They are available to customers with valid support contracts. They are released on the Tuesday closest to the 17th day of January, April, July and October. The next four dates are:

·  16 January 2018

·  17 April 2018

·  17 July 2018

·  16 October 2018

·   Go to the following link below to check the patch updates for your database

version.

https://www.oracle.com/technetwork/topics/security/alerts-086861.html

·   Select the critical patch update period of your choice.

·   Look for patch update advice link for Oracle Database Server, versions etc

·   Confirm if your database versions is included in the list of affected versions. If not then you are on safe grounds. If not continue.

·   There is an Oracle Database Server Risk Matrix as seen below 

It’s a table that gives details about the vulnerability. I will explain what the columns stand for:

o   CVE#: This column shows the Common Vulnerabilities and Exposure (CVE) number for reference purposes.

o   Component: This column states the database component that is vulnerable.  

o   Package and/or Privilege Required: This column states the privilege that ids required before the exploit can be successful       

o   Protocol: The column states the vulnerable protocol that is exploited   

o   Remote Exploit without Auth.?: This states if the exploit would require remote authentication       

o   Attack Vector: This column states if the exploit can be done over the NETOWORK of will required access to the database LOCALLY        

o   Attack Complex: This column states how difficult the exploit is. Your focus should be on "LOW"      

o   Privs Req'd: This column states the level of privilege required to execute to exploit    

o   User Interact: This column states if the exploit of this vulnerability requires access to an active user before execution       

o   Confidentiality: This column states if the exploit of this vulnerability will affect or compromise the confidentiality of sensitive data. This means the exploit can perform unauthorized extraction of sensitive data.

o   Integrity: This column states if the exploit of this vulnerability will affect or compromise the integrity of data. This means the exploit can create escalated privilege to make unauthorized changes to the database    

o   Availability: This column states if the exploit of this vulnerability will affect or compromise the availability of data. Denial of service (DOS) is one method that can cause this.

o   Supported Versions Affected: This column lists the affected Oracle database versions.     

o   Notes: This keeps additional notes required for the vulnerability

          ·    Discuss with the database administrator if you observe that the database requires patching.

2.    Ensure the Database Initialization parameter are securely configured

     The Oracle database is governed by a set of sensitive parameters that 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. It also contains security parameters that have to be carefully configured.

    Click here to see the post on securing parameters

3.  Ensure effective database security
         ·   Ascertain that the physical machine hosting the database is housed in a secured, locked and monitored environment to prevent unauthorized entry, access or theft.
         ·   Ascertain that application and web servers are not hosted on the same machine as the database server. This is very important.
          ·   Ascertain that the database server is located behind a firewall with default rules to allow only the required connection.
         ·    Regularly test machine hardening and firewall rules via network scans, or by allowing ISP scans through the firewall

4.    Ensure proper change management is carried out and documented when any change is done to the database
          ·   Requests for the list of changes that have been carried out in the past 6 months(Any date range of your choice is fine)
          ·   Ascertain that all changes passed through a change process that was documented and approved.
          ·   Take the inventory of all application that read or modify production data on the database.
          ·   Ascertain that these data are well documented


5.    Ensure there is an effective Database Backup & Recovery in place
     Picture an organisation that got hit by an incident which damaged its IT infrastructure including is core database. This incidence grinded their business to a halt. To now worsen the case, they discovered that the DBA had not been carrying out any backup of the database and as such there was a high chance of NO database recovery. This even should be considered a SAD event. 
     Below are some checks that can be done to forestall such failures

          ·    Ascertain that the backup and recovery procedures are documented and meet the organisations’s requirements.           
          ·    Ascertain that the Backup and recovery procedures are periodically tested.      ·    Ascertain that the Backup retention intervals are documented and sufficient to meet the business resumption requirements



Risk 3: Poor Database Auditing

Preventing attacks to the database is the only approach to securing the database.

Detecting potential attacks is also as important after all, the best security in the world

is not going to stop every attacker. This is where the advantage of auditing comes to

play.

Auditing allows us to monitor the environment and identify potential attacks.

This post will be based on Oracle 11g.

Basically an Oracle 11g database supports four levels of auditing which we will

discuss in a moment.

Audit records can be stored in the following locations:

·         Database

·         Operating System

The database initialization parameter AUDIT_TRAIL is used to enable or disable

auditing. The default setting for this parameter is DB. Let’s look at the various

settings available.

Please Click here to continue reading on Using Oracle Database Native Auditing.

Below are steps you can take to ensure Database Auditing is in place and enforced:

·   Ascertain that the following logs are captured:
      o  All logins to operating system and database servers, successful or unsuccessful, are logged. 
      o  Database objects with restricted data have auditing turned on where possible.  
       o  Accounts that are locked due to maximum database login failures should be logged. This will provide probable intelligence on persistent connections by a malicious entity.

·  Above all request for evidence of monitoring of these logs by an independent party. 
·  Ascertain that the outcome of the log review are documented.

Now it is important to note that most often than not, IT department may resist the
urge to turn on database auditing sighting performance as an excuse.In the event of
this, a third party application can be used to perform the capture. 
This is called Database Activity Monitoring applications.

According to Wikipedia:

"Database activity monitoring (DAM) is a database security technology for monitoring and analyzing database activity that operates independently of the database management system (DBMS) and does not rely on any form of native (DBMS-resident) auditing or native logs such as trace or transaction logs. DAM is typically performed continuously and in real-time." (TM Wikipedia)

Below are a list of Database Activity Monitoring applications:
1. Imperva SecureSphere    https://www.imperva.com/
2. Fortunet FortiDB    https://www.fortinet.com/
3. IBM Guardium   https://www.ibm.com/security/data-security/guardium
4. McAfee   https://www.mcafee.com
5. Trustwave DBProtect   
https://www.trustwave.com/Products/Database-Security/DbProtect/



Are you done with the post?

Ready for the work program?

Click here  to download............


======================================
Oracle Database Security and Audit Work Program
======================================