When evaluating the security of a database, and/or the security of an application, there are a few things that will make me start pounding my head against my desk. What prompted this? A customer asked me help evaluate a system that is about to go through an IG Audit and they want to be ahead of the curve. The good part is I was able to come up with a lockdown procedure that is specific to this customer and their needs. The bad part is, I keep seeing these same mistakes over and over again. So, lets sit down and go through five of the mistakes that I keep seeing over and over again.
- A COTS application required any of the following privileges to get installed or operating. I’m sorry guys, if you have not figured out what permissions your application really needs, then it’s not ready to be put on the market. I was able to do a privilege analysis and find out what this application really needed.
- DBA – I have a hard time justifying granting DBA to an application to be installed or operate.
- SELECT / CREATE / UPDATE / DROP ANY <FILL IN YOUR TYPE>. Hey, this is another privilege that just makes me shake my head. Because the ANY any privilege allows the grantee to operate on ANY object in the database, this just tells me you have not thought it through. Many databases have multiple applications running in an instance. Why would your application need to access schemas that are not part of your applications?
- Oh and as a side note, why did the application need both DBA and all these ANY privileges?
- Okay there are a whole bunch of privileges that should never be granted to an application, these are just a few.
- Not cleaning up users that have left. Please tell me why you need to keep a user in the system who left two years ago. If someone can give me a good excuse, I’m willing to listen, but I got to tell you, in thirty years in this business, I have not heard any good reason to keep a user in the system.
- Go ahead, drop those users, including the test, sit, and dev instances. They don’t need to be there.
- Connecting as the application to do your job. I see this so often that it really makes me wonder about the technical ability of the people doing the job. Please tell me why you need to connect to the application to do your work? If there are permissions that you need that are missing, we can resolve that.
- Mixing Data and Code in the same schema. Why do you need to do this? You really should separate your data and code. All you need is one little sql injection bug for a bad guy to find, then she owns your database. Oh, and it’s highly likely you have a sql injection bug, you just don’t know it yet. We have a plan to separate the code from the data, it’ll take a while, but this should keep the auditors happy.
- Writing queries in production. I get it, it happens; you are supporting your users and the user needs something from the database quick. There is not a report available that answers the users question. You connect to the production instance through sql plus, sqlcl, sql developer or some other tool. You then type your query, hit enter and production slams on the breaks. Yea’ I have lost count of the number of times I’ve seen this, you forgot to join those two really big tables and just got a cartesian product. A junior developer just walked up to me and asked the question, why is production so slow. My gut reaction was to chew him out. In the end, I did not chew him out, and I gave him an explanation of what happened (forgetting to put in a where clause) and gave him a stern warning about writing code in production. You have a test instance, please use it before running in production.