Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

closed as not constructive by gbn, Mark Storey-Smith, Jack Douglas Nov 30 '11 at 20:43

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance.If this question can be reworded to fit the rules in the help center, please edit the question.

It's easier to answer for a specific RDBMS: any one in particular? – gbn Nov 30 '11 at 5:49
I'm afraid I can easily imagine a whole book being written in answer to this question - even on a specific platform (see the faq) – Jack Douglas Nov 30 '11 at 20:44

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.)

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.