Database security

Question

What aspects should be considered in order to have a secure database?

Along with avoiding sqlInjection, what else should be considered? What should not be done?

Answer 1

Shut it down and wipe the disks....several times. Slightly less extreme is

Firstly secure the server, storage, network and OS level

1. The database server should have minimal open ports (eg just SSH on port 22 and the listener on 1521). Also beware out outgoing connections. The db can make HTTP requests, send emails and all sorts of things....which you probably don't want in a secure environment.
2. No-one should have access the the database server. Actually the most senior DBA probably needs access, and one deputy in case the Senior DBA is ill or otherwise unavailable. You may want to audit all access. You may want to force the DBAs to actually request access from a sysadmin before they can access the server. You probably want to lock down where they can access it from (ie specific work PC) and possibly times as well.
3. Transparent Data Encryption should be used so that all data in the database is encrypted before it hits the files. So even if someone gets the disks or the backups they can't read them
4. You might want to encrypt network traffic between the database and the application.

Next you want to lock down user database access

1. Generally access by schema owners should only be required for installation/upgrades. Apart from that the schema should be locked.
2. it is safer to separate out an application layer in the DB so that the application accounts can only execute pre-defined functions (ie can't delete every row in a table). A DB application layer is a very good defense against SQL injection because the connected user simply doesn't have select permissions on the tables.
3. Store encrypted values in preference to clear-text, and hashed values in preference to encrypted values and store nothing that you don't need. Do you actually need people's names, addresses, phone numbers, credit cards, SSNs, passwords ?
4. Depending on your architecture you may benefit from passwords that expire on a regular basis, or authentication though another mechanism.

Don't forget DR. While I was joking about wiping the disks, if your risk assessment is that someone would do very well putting you out of business for a few days (or permanently), burning down your data center is a security matter.

Answer 2

Here is a checklist for SQL Server security which seems focused on the configuration of the SQL server itself: http://www.sqlsecurity.com/FAQs/SQLSecurityChecklist/tabid/57/Default.aspx

Additionally (if you are unable to rely on the windows only security model) you would also want to go through the required excercise of encrypting connection strings.

Answer 3

A pretty open question.

A good start is to read some of the basics, Wikipedia has a nice article on it. Then you have to look into the details of your specific system. Nearly all database engines provide a heap of information how to specifically secure their systems. And don't forget to look over the horizon: not only has the database be "secured", also the system it runs on.

SQL Injection is a common, but one of many security concerns to be taken into notice.

Answer 4

In Oracle, a frequent design choice I see being made is that the application always logs in as a single database user/schema - the owner of all the objects for that application.

A better design is to have one user/schema that owns the objects for the application, then REVOKE CREATE SESSION from that user. Then, create another user/schema which will own no objects (except perhaps for synonyms) that will be used by the application to log into the database. Then, issue the minimum necessary grants (SELECT, INSERT, UPDATE, DELETE, EXECUTE, etc) to the application user.

That way, you know that even if a bug in the application might allow a user to do something that they shouldn't (e.g. delete records from a table), your database privileges will ensure that it won't happen.

Otherwise, if the application logs in as the owner, it is not possible to REVOKE any privileges on any of the objects.