How to protect data in a relational database

The ability to allow or restrict access to data in a database can be crucial for companies dealing with a large amount of sensitive data, and with people of all different levels of the corporation accessing it.

The ability to control these accesses at different levels of granularity varies depending on which relational database management system (RDBMS) being used. Just about every commonly-used RDBMS allows a database administrator (DBA) to either "grant" or "revoke" the basic four privileges available to users at the table level:

  • Select
  • Insert
  • Update
  • Delete

Some databases will also allow you to apply those four basic privileges in an even more granular way.

For example, in some RDBMS, you can prevent individual columns of data in a table from being accessed or manipulated based on grants, and in others you can prevent individual rows of data from being accessed or manipulated based on grants.

Beyond basic mechanisms to allow or restrict access within a database, other common access control mechanisms include encryption and PKI Certificates. Another thing worth looking at is Oracle's Data Vault technology - it can actually prevent privileged users (for instance DBAs) from gaining access to confidential data.

If you aren't using a relational database, then you're pretty much left with normal POSIX security at the file level. But that isn't ideal.