Posted by: Arian Eigen Heald
Compliance, Database, Database security, IT audit, PCI DSS, Security, SQL Server, Steps to an Easy Audit
Remember that commercial (I’m dating myself, I know) where the little old lady lifts the top of the burger bun and says, “Where’s the beef?” All things considered, we have to ask the same sorts of questions about data.
Usually we’re looking at a nice fat application wrapped around data. It looks great, manipulates the data into all sorts of interesting reports, and adds a lot of value to the data. But ultimately, without the hamburger, the bun is useless.
Many IT Auditors and business managers tend to approach security by testing the application controls – synonymous with testing the hamburger by sampling the bun. The bun looks great, controls are all set, no one can see what they shouldn’t, right?
BIG Wrong. This is why the PCAOB is now requiring SOX auditors to examine the configuration of databases. I’m delighted to see that this is finally a requirement, because that’s where the beef is, and always will be. Inside the databases.
First question to ask: How does the application talk to the database?
Every connection to the database requires a username and password (even if the password is blank). EVERY CONNECTION. So, what is the application using? I’ve found IDs and passwords hard-coded inside applications (well, you won’t be changing the password on that one!), inside ASP code on the web server that serves up the application (hack the web server, get the database, too!) and sniffed it online using port 1433 and/or ODBC connections (IDs and passwords run in clear text). Another fun one is to examine a user’s workstation and find the ID in the ODBC configuration (pushed out via script) so that the user can use their Excel application or nice Access database code. (Of course, I’m talking Microsoft SQL server here, but these items are applicable across the variety of databases.)
Second question to ask: What rights does the application ID have?
It’s ironic that so many software companies cut expenses by enabling an application ID that has db-owner rights to the database. That way everything works, right? I’ve seen it dozens of times, and it’s always painful. Often there is no DBA at the company, or it was installed and the DBA is stuck with it. If it’s in production, removing that access will probably break the application, and no one wants that. So everyone crosses their fingers and toes that no one discovers this easy in. And, PS, hopefully there is a password to that ID.
Third question to ask: Is logging enabled to critical database tables?
Don’t believe anyone who says, “Logging can’t be turned on due to performance issues!” Sure, if you turn EVERYTHING on to be logged, the server will tank. But setting up triggers that send reports and logging access to half-a-dozen tables is not going to impact the server (unless, of course, the application is already a hog). It means more work for the DBA, but if she is skilled, it shouldn’t be a problem. A good DBA can do that in his sleep.
This way you can watch who is getting to the beef. Why does the application ID matter so much? Because if I have that ID and password, I don’t need that application to get into the database. The application is just a pretty face – I can connect via Excel, Access, or any other database-connecting application as long as I have a username and password. Just to see what I can get. And if that application ID has dbowner access, or even better, sysadmin access, I can get everything in the database.
The application may have good controls, but outside the application, or using MY application, those controls won’t exist. Beef, lots of it, sans bun. In short, hacker hamburger.
So, OK, how does this make a good audit, Eigen, you ask? Sounds like a pain in the neck, right? Two magic words for you: compensating controls.