I had an interesting conversation with Alexander Kornbrust yesterday about cloning databases. Most DBAs I know copy database files from production to create staging, integration and test environments. Those environments contain a lot of sensitive information (PII, CC, etc.) which is usually either deleted, scrambled or truncated. The problem with these solutions is that most DBAs forget that the database performs logical deletes and not physical deletes. This can be easily demonstrated on Oracle by the following simple steps that create a table, populate it using dummy data, truncating it and showing the data from the dump file:
create table test(t varchar2(30));
insert into test select object_name from user_objects where rownum < 1000;
commit;
select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from test where rownum < 2;
truncate table test;
For the following step, replace ‘x’ and ‘y’ with the results from the previous select
alter system dump datafile x block y;
show parameter user_dump_dest
Check out the new file in the user_dump_dest directory. The file will contain the truncated data in the block.
Of course, this is just an example but it is worth thinking about. It is also worth considering TDE to protect the data files from direct reading.
DBAs out there - what do you do to remove sensitive information from your non-production environments?
I’ll be presenting on Oracle database hacking and security at the UKOUG DBMS Special Interest Group meeting this week. The meeting will take place on Thursday, 20th March 2008 in Baylis House, Slough (UK, obviously). Here’s the link for the agenda and details http://www.ukoug.org/calendar/show_event.jsp?id=3358
Hope to see some of you there - come and say hello…
Just a short announcement this time - Sentrigo is hosting a live webinar/webcast with Pete Finnigan where he’ll share his wisdom on Oracle database security, show some attack vectors and how one can detect and prevent them, as well as other good stuff.
Those of you who’ve ever attended one of Pete’s masterclasses at an OUG or security conference know that they are well worth attending, and those of you who haven’t - you’re now given the chance to attend from the comfort of your own computer…
It takes place on Friday, March 28th. You need to register in advance - here.
In a recent survey we conducted, it turned out the DBAs are mostly ignoring security patches. Two thirds of the DBAs have never applied a CPU and only about 10% of them are applying CPUs in a timely fashion. After releasing the survey, we had some interesting responses in online publications and blogs which I would like to address in this post.
Response number 1 - Lies, damned lies, and statistics
The survey is made up, we asked the wrong questions, we did not understand the answers and my favorite - the survey contacted only lazy DBAs between at home between 1PM and 3PM.
Well, let me set the record straight here - the rolling survey was conducted in face to face OUG meetings - 14 different ones across the US, anywhere from 10 people in a room to 45 people. We did not select who was invited or who attended. These were OUG members who chose to attend our sessions. If anything, I would say these were people rather more interested in database security than the average.
We asked two questions -
1) Have you installed the latest Oracle CPU? (actually, since some meetings where right after the CPU we’ve asked about the latest 2 CPUs).
2) Have you ever installed an Oracle CPU?
You can find the actual answers in the survey itself. I should add that the results were quite similar across the different OUG meetings.
I can relate to this disbelief in the results. It sounds amazing that the most important assets of the organization are left un-patched especially after so many publicized incidents. But those in the know already knewthis.
Response number 2 - DBAs are just lazy
Well, as a former DBA for many years I definitely do not insinuate that DBAs are lazy. The simple fact is that they just have too much working against them when trying to apply the CPUs:
1. The need to test all applications using the database is a heavy burden
2. Oracle supports only the latest patchsets
3. The lack of application vendor certification of the CPUs
4. The simple fact that it takes a huge amount of work to manually shutdown the database and apply the patch in an organization running hundreds if not thousands of instances
5. For production critical databases you have to consider maintenance windows which might come once a year
6. The lack of understanding by some IT security personnel of the severity of the problem simply does not generate enough pressure in the organization - please see Rich Mogull’s excellent post on this topic.
All in all, I know of companies that analyze and deploy CPUs as soon as three months after release but those companies are very few and usually have budgets in the millions for such things…
Response number 3 - Yeah, we all knew that is the case
I know, I knew it as well being a DBA and all. But I always thought that DBAs at least deploy the patches after testing them. We are talking about severe vulnerabilities. Some of them are remotely exploitable without credentials.
A final remark - as always in security - use common sense:
1) Install only what you use, don’t install features you are not going to use and remove them if installed by default - many vulnerabilities are in rarely used components like Oracle Spatial, etc.
2) Use the least privilege principle - give the minimum permissions required for the task - every permission can be used to attack the database (create view, create procedure, etc.). Many packages can be used for an attack. Lock them down.
3) Check for default and weak passwords - there are many tools out there. Check after every patch as there were cases it restored default accounts.
4) Secure the network - use firewalls, valid node checking, etc.
5) Use secure coding - bind variables, bind variables, bind variables.
There are many white papers out there talking about Oracle security…
Oracle OpenWorld came and went. I had some interesting sessions which I’ll summarize shortly, some less interesting sessions, lots of beer and a great concert by Billy Joel and Lenny Kravitz. I arrived in SF on Friday night from Philadelphia (after being selected again at the airport for “random” inspection). I had several interesting meetings with customers in Philadelphia so I was very much exhausted and went straight to sleep. Saturday, I met with friends and registered to the conference.
Sunday started with a nice security session from Oracle’s Chad Hughes and two other guys. The interesting part in the session was a sneak peak into Oracle’s internal secure coding standards. It looks like Oracle is running Fortify on their code for code analysis. I believe they are also running it on their PL/SQL packages to catch un-validated user input passed to dynamic statements. In fact, I heard that one of the reasons for DBMS_ASSERT.NOOP was to remove false positive alerts coming from Fortify. Some other interesting stuff was related to Oracle’s “Secure by default” initiative. Things like auditing turned on by default, smarter password profiles that will lock users, etc. are interesting indeed but on the other hand, I got the distinct feeling that Oracle is talking the talk but not walking the walk, so to speak. If you check the attack surface in Oracle 11g, you can easily see that the number of public packages has increased tremendously and you have APEX installed by default?! The rest of the talk was dedicated to ISO-17799 (later renumbered as ISO-27002). All about control, asset management, etc.
Later that night we had Mr. Larry Ellison starring in the Sunday Night Live show. It was very interesting hearing about Oracle’s first days. I heard some other keynotes from Larry Ellison but none was so nostalgic and so informal. Ah, the early days of a young company - living in the office, surviving on pizza and coke. Reminds me of myself this past year.
Sunday we also had the nostalgic party which was fun…
Monday was filled with many announcements and coverage of new Oracle 11g features. Among them we heard about Oracle VM, which caught VMWare by surprise. Another highlighted though badly named feature was RAT (Real Application Testing) - a truly interesting feature. As I always tell my customers, you must test it before deploying in production This feature makes testing so much easier.
The best session of the day was from Tom Kyte of asktom in the no slide zone. It was pure entertainment. Tom hosted a contest between DBA 1.0 with scripts and command line against DBA 2.0 with the new Oracle tools like EM, ADDM, AWR and some more TLAs in “real live” scenarios. As you might expect, DBA 2.0 won the contest while showing the effectiveness and ease of use of the tools. Although, because of the slow WIFI, DBA 2.0 actually almost lost in the first scenario. It was hilarious. I must admit that although traditionally I’m more of a command line type of guy, the presentation was very convincing.
Monday night was OTN night and of course lots of beer and other liquids.
On Tuesday, I had more security sessions but I managed to squeeze in another Tom Kyte session where he counted his 11 best features of 11g. Again, very nicely delivered.
Another interesting session was the “Oracle CPU best practices” session. CPU stands for Critical Patch Update and Oracle has a predictable process to deliver them to customers. I really feel Oracle’s pain here. The process has to be predictable and ordered but this means that vulnerabilities like this one are published without a patch being available for 3 months. Also, from my experience, many customers find the CPUs too hard to follow and either skip them entirely (and rely on patch sets) or install every other one. Here are 10 interesting random facts about the CPU:
1. They are mostly tested on common platforms. Tests are hardly performed on non-common ones.
2. Pre-release information is available on last Thursday before CPU
3. The CPU is released on Tuesday
4. There are 3 types of patches - security, security dependent and customer conflicts in patches. From 10.2.0.3, the conflicts patches are removed.
5. Sometimes, patches are released without information because they are not available on all platforms
6. October 07 contained 82 combinations (5 supported versions ported to 12 platforms).
7. Testing is done in a 6 week cycle
8. 75% of bugs are found by Oracle internally (1% - open source, 10% - customers, 15% - researchers)
9. Oracle prioritizes by severity - source of discovery, availability of exploit code, CVSS score, etc.
10. Next dates - 01/15, 04/15, 07/15, 10/15
The funniest question from the audience was “the rate of vulnerabilities is not declining. When is Oracle going to fix all problems?”. And the truthful answer was “never”
On Tuesday night, Oracle Israel invited all the Israeli guys to Beni Hanna (a Japanese restaurant) where again, we ate and drank mojitos and sake.
The best part of Wednesday was the Billy Joel concert. Oracle organized the entire event superbly and besides entertainment we had plenty of food and drinks.
Of course, the day after, we saw a lot of tired Oracle attendees.
That’s it… Another year of OOW came and went… As always, it was a great experience and besides beer provided many interesting insights into Oracle’s current features and future plans.
Well, finally I’m writing the third part of the blog. The thing that pushed me to finish this was a talk I had with Tim Hall of Oracle-base fame after his Unconference presentation in Oracle OpenWorld. Tim told me that his Java developers are claiming that adding user context information in an already existing application (Swing) is a non trivial task. You know, I’ve been hearing this from a lot of our customers and while I agree it is not trivial, I will try to outline a method of doing so without changing application code. In this day and age when there are advanced tools such as AspectJ and Spring framework, adding cross-cutting concerns to an application should not be an insurmountable task.
So, without further ad0, I will detail an AspectJ aspect that will wrap around an Oracle connection and add user context information to every statement. This aspect can be used with existing programs and also adapted and extended to catch login information in a Swing based application. I will build of the previous examples in providing the necessary infrastructure of domain and DAO classes.
I promised to blog a bit about my traveling, so here I go:
I was visiting customers in India and the US and giving presentations to Oracle user groups in the US. Amazingly, the state of US airports is just getting worse every month. Flying from Israel to India and from India to NY went without any problems. However not did a 35 minute flight from NY to Boston take 3 hours, but they managed to lose my suitcase in the process. Every flight I had in the US in the previous week was late.
Enough moaning and back to Oracle security… I would like to share with you some insights I had while giving presentations. First, it looks as if database security is getting more and more attention from both DBAs as well as IT managers. By show of hands at the presentations, I could see that at least some of the DBAs are handling security issues as part of their day-to-day job. But still, DBAs are not hearing the following from their managers - “last year you met your MBOs because no database breach had occurred. Here is your bonus…” - though many have heard the bonus speech for HA or performance MBO achievements.
Second, almost no one had deployed the July 2007 Critical Patch Update from Oracle. From a crowd of about 50, only 2 raised their hands.
Third and most startling of all, only about a third of the DBAs have ever deployed an Oracle CPU. Let me repeat that again - more than two thirds of DBAs in this small but significant sample have never deployed an Oracle CPU. Ever.
So this got me thinking - do we care about Oracle CPUs at all? Oracle was getting a lot of heat from security researchers for not providing security patches or providing them with irregular intervals. Finally, Oracle is stepping up to the plate with the patches. They provide them on regular basis, they announce the the patch before issuing it so organizations can prepare for them. They are improving coding techniques and code vulnerability scanning tools. And after all that, customers are still not protected. The reason for this is that the database is an extremely complicated piece of software and is the life-line of the organization. An enterprise will need to test the CPU thoroughly before deploying and testing takes a lot of time (months). This is further complicated by the fact that many organizations have applications running on top of Oracle databases, and those applications are not “forward compatible” and certified by their vendors to run on future Oracle versions.
Ironically, from a security standpoint the situation after a CPU is announced is actually worse than before it is announced: The hackers get a road-map of all the vulnerabilities while most organizations have not yet plugged those holes. This is a similar notion to hacking IPS software in order to retrieve vulnerabilities (see this black hat presentation).
I’m not saying that Oracle should stop providing CPUs. Quite the contrary, I’m saying that organizations must deploy CPUs as quickly as possible to keep this sensitive period short. Even considering the objective difficulties in applying patches, it seems that enterprises are not taking database vulnerability seriously enough. Also, organizations must have other solutions to mitigate the threat in post-CPU release period. Those solutions must not change the Oracle software at all or else they will fall into the same trap of interdependency, stability issues and so forth. They must provide virtual patches to externally test for attacks and plug the security holes from the outside.
I am curious to know other people’s experiences and views on this topic - so fire away…
This is a personal as well as a commercial posting for me… Tomorrow is a special day in the short history of my company - after long months of R&D, we are finally releasing our product, named Hedgehog (there’s already some coverage in Dark Reading). These are very exciting times both for me personally and for the entire team at Sentrigo, who’ve made this possible through a lot of hard work and well applied knowledge - I feel very lucky to have such a great team working with me.
Hedgehog is database security monitoring software that monitors DB transactions in real-time, and generates alerts based on a highly flexible set of policy rules. A light-weight sensor is installed on the database machine and monitors the shared memory. It doesn’t use redo logs or DBMS APIs - those would be too slow… The trick is to do it so that it doesn’t use up CPU power.
Hedgehog can be downloaded from Sentrigo’s website, and while it supports only Oracle for the moment, in the coming months we will release versions for MS SQL, DB2 and other major DBMSs. There are basically two version - Hedgehog Standard, which is totally free to use, and Hedgehog Enterprise, which is not free but available for free evaluation. The differences are explained in some detail on the website, but basically it boils down to prevention capabilities and enterprise scalability and integration.
My sense is that we’re bringing something new to this space, and I’m anxious to see how this will be received. Feedback is of course welcome. Give it a try!
As promised, this is the second of a three part blog entry discussing the propagation of middle-tier users to the database. This post will mainly concentrate on the Java side of things. I will show how to use Spring-framework’s excellent transactional support using AOP to add an additional advice, relying on ThreadLocal to pass application user identities from the web tier all the way to the database, and using annotations on the service layer to specify the module and action.
I strongly advise downloading the linked zipped source code at the end of the post if you’re planning on seriously reviewing the code.
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.
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.
This feature makes testing so much easier.
