Musings on Database Security

Well, I threatened to post something more technical, and here we are. I’ve just come back from a business trip to the US, meeting some prospects and customers. As always, I was asked a lot of technical questions. One of the frequent questions I encounter is – how can we propagate the application user and context information to the database, for monitoring and prevention purposes?

I’m going to answer this question here for a specific technological setup of Java and Oracle, but the solution can be adapted to other technologies. Then I’ll give some examples using Spring framework and various Java database tools such as Hibernate and apache commons-dbcp.
In Oracle, there are a couple of methods to propagate application user info into the database. Two such methods are:

• Lightweight user sessions that can be created on top of a single database session in the middle tier or using a connection pool. This method is available to applications using OCI using OCI specific calls and settings like OCI_ATTR_PROXY_CREDENTIALS and using the new JDBC drivers from Oracle. Please note that this method only works when all your application users are users in the database.

• Tracking users, modules and actions using specific CONTEXT (USERENV) parameters. This method is available to OCI applications using OCI_ATTR_MODULE, OCI_ATTR_ACTION and OCI_ATTR_CLIENT_INFO. This method is also available to Java applications using Oracle JDBC driver (either type 2 or type 4).

We will focus on the second method using the Oracle thin JDBC driver as it does not mandate creating all application users in the database.
First, let’s stick to the bare minimum (no exception handling and no frameworks):
Here is what should have worked in Oracle 9i –

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import oracle.jdbc.driver.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;
public class Test
{
public static void main(String[] args)
throws SQLException, ClassNotFoundException
{
OracleDataSource dataSource = new OracleDataSource();
dataSource.setURL(“jdbc:oracle:thin:@localhost:1521:slavdev”);
dataSource.setUser(“scott”);
dataSource.setPassword(“tiger”);
OracleConnection conn = (OracleConnection) dataSource.getConnection();
conn.setClientIdentifier(“Slavik”);
Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery(“select sysdate, sys_context(‘USERENV’,'CLIENT_IDENTIFIER’) from dual”);
rs.next();
System.out.println(rs.getString(1) + ” – ” + rs.getString(2));
rs.close();
statement.close();
conn.clearClientIdentifier(“Slavik”);
conn.close();
dataSource.close();
}
}

Surprisingly, this code actually works in 10gR2 giving the result: 2007-05-20 16:25:15.0 – Slavik. But on the other hand, making it work under 9iR2 is not so simple. Also, unfortunately, the setClientIdentifier() as well as clearClientIdentifier()are deprecated. So the recommended way to code this is to use the relatively new end-to-end metrics API.

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import oracle.jdbc.driver.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;
public class Test1
{
public static void main(String[] args)
throws SQLException, ClassNotFoundException
{
OracleDataSource dataSource = new OracleDataSource();
dataSource.setURL(“jdbc:oracle:thin:@localhost:1521:slavdev”);
dataSource.setUser(“scott”);
dataSource.setPassword(“tiger”);
OracleConnection conn = (OracleConnection) dataSource.getConnection();
String metrics[] = new
String[OracleConnection.END_TO_END_STATE_INDEX_MAX];
metrics[OracleConnection.END_TO_END_ACTION_INDEX] = “Simple Test”;
metrics[OracleConnection.END_TO_END_MODULE_INDEX] = “Test Application”;
metrics[OracleConnection.END_TO_END_CLIENTID_INDEX] = “Slavik”;
// Set these metrics
conn.setEndToEndMetrics(metrics, (short) 0);
Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery(
“select sysdate, ” +
“sys_context(‘USERENV’,'ACTION’), ” +
“sys_context(‘USERENV’,'MODULE’), ” +
“sys_context(‘USERENV’,'CLIENT_IDENTIFIER’) ” +
“from dual”);
rs.next();
System.out.println(rs.getString(1) + ” – ” + rs.getString(2) + “, ” +
rs.getString(3) + “, ” + rs.getString(4));
rs.close();
statement.close();
conn.close();
dataSource.close();
}
}

Now the results are – 2007-05-20 16:31:55.0 – Simple Test, Test Application, Slavik, and we did not waste any roundtrips to the server while setting all those details.

Please notice that the metrics setting will be set on the connection for all statements until we reset it by setting nulls and Short.MIN_VALUE.

For more details on the API, you can check http://download-west.oracle.com/docs/cd/B19306_01/java.102/b14355/endtoend.htm documentation.

A short explanation of what we set here:

• Action – The current action that the application performs (can be a page name or an action within the page).
• Module – The module within the application. Can be the application name.
• Client Identifier – The user within the application performing these actions.

Now that we are over the basics, let’s move on to the fun stuff.

My next post will show how to transparently set those metrics using Spring framework, Hibernate and commond-dbcp.

Recent opinions about PCI-DSS and whether it should or should not be softened made me think of a wider issue I often come across: The illusory equivalence of regulatory compliance with “security”.

I would therefore like to try and argue that compliance cannot equate security, and it never will. The reasons for this are inherent to the motivation behind regulations and the process by which they are created and enforced.

First, regulations (be they law or industry standard) have limited scope. They are there to ensure that a certain set of rules is followed in order to achieve a specific goal. If they end up generating better security against threats outside their target scope, that’s a positive side-effect. Sarbanes-Oxley (SOX) is there to ensure truthful financial reporting to the SEC, so it requires financial data to be watched closely. If millions of customer records are stolen from a public company, under SOX this company may be 100% compliant as long as they can show how it affected its financial figures, but a company that allows massive data theft to happen is clearly not as secure as it ought to be.

Additionally, regulations are often created, even within their applied scope, as a minimum requirement. That is, a requirement that many organizations within the relevant space have some chance of fulfilling – perhaps not the lowest common denominator, but a low one to be sure. Some regulations emphasize auditing, focusing on what had already happened, and not necessarily preventing it from happening in the first place. In other words, regulatory compliance is not an Olympic medal – it just means you get to participate in the opening ceremony.

Third, enforcement of compliance is not perfect. In some cases (HIPAA comes to mind) it’s very weak. This leaves many companies not even knowing whether they’re compliant or not – it is up to their own interpretation, which usually means the path of least resistance. With full compliance setting the minimum standard, less than that is, well, not much…

And fourth, regulations are often too slow to keep up with emerging threats. A few years back nobody knew what phishing was, or how to gain DBA privileges using SQL injections. Regulatory requirements, especially legislation (like SOX, HIPAA and GLBA) are difficult to update, and so will always trail behind fast moving computer-related threats and techniques. PCI-DSS stands a better chance, since it is an industry standard and was originally intended to be updated as circumstances change.

While some enterprises struggle with achieving compliance, leading companies will have systems and procedures in place that exceed the compliance requirements. Their focus will be on securing their systems and data, while achieving compliance with minimal extra effort and at minimum cost.

While it’s not headline news yet (and may never achieve such lofty status), a recent database breach at UWF was exposed and later reported in local news. What exactly happened and how many records were compromised is, as usual in such cases, unknown.

This made me think: We hear of breaches at universities all too frequently. Privacy Rights Clearinghouse, a website that documents data breaches, lists over 140 breaches in universities since January 2005. That’s more than one per week on average. Ouch.

Why is that?

The crucial factor here is that universities have very large populations of “insiders”. Students are like employees: They are authorized users. They have logins and passwords. They are also young and rebellious, and many are tech savvy – e.g., computer science students, to state the painfully obvious. Some are “hackers”, looking to prove they can hack, or influenced by some anarchist/Marxist/New Age book they browsed in the library, and others may be more traditionally motivated by money, criminal intent or a deep desire to change their grades…

This is also a transient population, and very hard to control. Every 3-4 years the population changes almost completely. Unlike employees, they do not stay long enough to develop any kind of loyalty, plus of course the don’t get paid – quite to the contrary, they’re the ones paying.

What about the data itself? Naturally grades are very important to students, but they are of little value to anyone else. Other student data is a lot more interesting, including Social Security numbers, bank account details and other personally identifiable information – the bread and butter of identity thieves. At least gone are the days when SSNs were used as student numbers – although many of those still lurk in alumni databases around the US, which highlights another point: Although the population is transient, the data is not. It stays. A large-ish university will have hundreds of thousands of former student records. Quite the honeypot.

Universities mostly lack the IT resources that Fortune 500 companies have, but the challenge they face in securing their data is no less daunting. I think that one simple, non-technical solution would be not to collect unnecessary data in the first place, and if it must be collected for current students, dispose of it once the student graduates. As an alumnus, why would I possibly need my alma mater to retain my Social Security number?

Technically there are many things the universities can do, but I don’t want to already sound tedious on my second post (hint: If you don’t monitor database activity, you won’t know if the DB was breached, when, how, by whom and how badly – but enough of the hard sell)

What better way to start a blog about database security than to discuss what is possibly the biggest data breach ever?

It now seems that several banks are suing TJX over claimed losses of tens of millions of dollars – so negligence in data protection carries a cash penalty, not just nebulous damage to reputation. Gross negligence, in fact – this is not some one-off lapse in judgment such as a laptop with sensitive information forgotten on a bus, or a CD lost in the post.

The details recently published about the ongoing investigation provide insight into what possibly happened:

1. The breach lasted 17 months: For 17 months someone (or more than one person) was systematically stealing data. I can only infer from this that security measures and procedures at TJX were grossly inadequate. It also means the breach was not accidental – it may have been opportunistic at first, but certainly malicious after that. More likely it was malicious from the start.
2. Insider(s) were involved: It seems that some encrypted credit card data was decrypted using keys, which only an insider with privileged access would have. Whether such an insider was knowingly complicit or duped into divulging such information is unknown, but it shows us all what the sophisticated criminals already know – why bother sweating and hacking your way through firewalls and IDS when it’s so much simpler to use an insider?
3. Utter lack of visibility: Most astonishing of all, “more than 50 experts TJX put on the case have reached no conclusions. Besides not knowing how many thieves were involved, TJX isn’t sure whether there was one continuing intrusion or multiple separate break-ins, according to a March 28 regulatory filing.”
   In other words, the thieves either did a great job of covering their tracks (and they certainly had ample time to do that!), or worse, they didn’t have to do it because their actions were invisible to begin with…

It is clear that even a rudimentary audit could have prevented the breach from going undiscovered for so long. It is also evident that encryption alone wasn’t enough to protect the data, and that perimeter defenses such as firewalls are useless against inside jobs like this one.

But ultimately, the entire thing could have been prevented with real-time monitoring and intrusion prevention at the database level.