What are the first necessary actions needed to help secure a database's schema & data? [closed]

Question

This smells like an open-ended, opinionated, wiki free-for-all, but I'm not trying to make this a battle of opinions.

It seems like there should be a good checklist out there for all to agree on that addresses the security aspect of database design & implementation.

I'm not asking about the details of storing salted/encrypted data such as passwords... I'm asking about the general guidelines one should follow before and while setting up a database so that it naturally inherits a reasonable degree of security & privacy from unwelcome company.

Can security/privacy be implemented on a schema level? Or is the safety of the database dependent on the code used to manipulate it?

I am looking for some answers that are somewhere between setting up db user and table rights, creating stored procs, using views, etc.... and encrypting stored data.

Can table relations, field dependencies, data types, lock modes, indexes, keys, etc... be set up to actively help protect a database against attacks and/or theft?

Answer 1

General guidelines:

• Server physical security: behind a locked door, with a very short list of people able to access it (as a point of interest, I myself can't access our server room without being escorted).
• Backup security: Make sure your backups are encrypted (if you use physical media for backups, make sure they're stored with a similar level of security to the server).
• 'sa' login: Paranoia dictates that you disable/remove it, practicality says "that'll break everything" (particularly when it comes to disaster-recovery time). I generally leave it enabled, but with a long, randomly-generated password (either stored with KeePass or a similar solution, or a post-it note inside the server room*). Create another account with the server admin role and use that for your maintenance requirements.
• Permissions - in production, no-one except the DBAs and the application logins should have access to the server.
  • The application logins should not have any rights on the base tables, only execute rights on stored procedures used to access those tables.
  • Alternatively, if you want to get fancy, group all the stored procedures into schemas then grant execute on the schemas to the application logins. (This may not apply to non-SQL Server/Sybase RDBMSs, not sure if they have schemas with the same level of functionality.)
  • Ideally, DBAs should have two logins, one with read access and one SA. Monitor the use of the SA login to make sure they're only elevating to it when needed. In practice, your DBAs are probably going to back themselves not to be stupid and consequently will use their SA roles for everything.
• Check your stored procedures to make sure they're not vulnerable to SQL injection.
• Audit your stored procedures regularly, particularly the ones that issue DML commands (insert, update, delete etc).

(* I'm aware this would probably fail any security audit going, but to my mind if we've let an intruder into the server room that knows to look in the third drawer down for the unlabelled 'sa' password post-it, then we're screwed anyway.)