Introducing Database Security

For a database application, all security in the end becomes a question of who can read and write to what tables. While this is obvious to veteran database programmers, it is not always so obvious to relative newcomers. A programmer who thinks primarily in terms of code will be used to conceptualizing actions, steps, procedures and so forth. This programmer will therefore think of security in terms of who is allowed to perform what actions. However, when you examine the actions performed by database application code, you find that, no surprise, it is a lot of database reads and writes.

This week in The Database Programmer we are going to see the basics of table-based security. This is a blog for anybody who wants to learn the practical realities of working with databases. A new essay is published every Monday morning, and the Complete Table of Contents is here.

Table Permissions 101

The most basic unit of storage for a database is the table, and so security begins with deciding who can do what to a table. The SQL language has one read command and three write commands (INSERT, UPDATE, and DELETE), so there are four possible permissions that can be granted to a user. Most databases out there provide syntax that is pretty close to this, which makes some direct permission assignments to a group named "ARSTAFF"

CREATE GROUP ARSTAFF
GRANT SELECT ON RECEIVABLES TO ARSTAFF
GRANT INSERT ON RECEIVABLES TO ARSTAFF
GRANT UPDATE ON RECEIVABLES TO ARSTAFF
GRANT DELETE ON RECEIVABLES TO ARSTAFF

These commands should be run at the same time that your tables are built, they are part of the basic database build.

A Basic Security Dictionary

A very basic security dictionary will list all tables and show who can do what to what. The dictionary begins with a single table, which lists tables and groups and permissions, something like this:

TABLE        | GROUP     | SELECT | INSERT | UPDATE | DELETE
-------------+-----------+--------+--------+--------+--------
AR_INVOICES  | AR_STAFF  | 1      | 1      | 1      | 0
AR_PAYMENTS  | AR_STAFF  | 1      | 1      | 1      | O
CUSTOMERS    | AR_STAFF  | 1      | 1      | 1      | 0
CUST_TYPES   | AR_STAFF  | 1      | 0      | 0      | 0
CUST_TYPES   | AR_ADMIN  | 1      | 1      | 1      | 1

Security permissions should always be defined in terms of groups, not individual users. This is important because it lets you make the group definitions part of the basic definition of the system, leaving the system administrator free to add users to groups at his own discretion.

Deny By Default And Determining a User's Permissions

Most security systems make use of the idea of deny by default. This means you do not have to specify who cannot see a table, the system starts out by assuming that nobody can see anything. Users can then only work with a table if they are in a group that can work with that table.

Another key idea is this: if a user is in even one group that allows a permission, the user gets the permission, even if his other groups are not allowed in.

With these two ideas, deny-by-default, and any-group-gets-you-in, then assuming you have a table that lists what groups each user is in, you can find a particular user's permissions on a table with this query:

SELECT MAX(select) as select
      ,MAX(insert) as insert
      ,MAX(update) as update
      ,MAX(delete) as delete
 FROM secure_dictionary
WHERE table = 'table name'
  AND group IN (list,of,groups,user,is,in)

If the query comes back empty, the user has no rights, otherwise you will get the user's permissions for that table.

Enhancing Our Security Dictionary

The security dictionary portrayed above can become tedious to work with because it often happens that you want to make similar assignments to groups of tables. For instance, in an accounting application you may an "APSTAFF" group that has basic permissions on Accounts Payable tables, and an "APADMIN" group that can do anything to any table in Accounts Payable. However, those groups have no permissions on the Accounts Receivable tables, so you have two new groups, "ARSTAFF" and "ARADMIN". Likewise for inventory, general ledger, and so forth.

This can all be made much easier by organizing your tables into modules and defining default permissions within a module to each group. The expanded dictionary would have these tables:

THE TABLE OF TABLES

TABLE        | MODULE
-------------+---------
AP_INVOICES  | AP
AP_CHECKS    | AP
AR_INVOICES  | AR
AR_PAYMENTS  | AR
GL_ACCOUNTS  | GL
GL_LEDGER    | GL

MODULE PERMISSION

MODULE  | GROUP     | SELECT | INSERT | UPDATE | DELETE
--------+-----------+--------+--------+--------+--------
AP      | AP_STAFF  | 1      | 0      | 0      | 0
AP      | AP_ADMIN  | 1      | 1      | 1      | 1
AR      | AR_STAFF  | 1      | 0      | 0      | 0
AR      | AR_ADMIN  | 1      | 1      | 1      | 1
GL      | GL_STAFF  | 1      | 0      | 0      | 0
GL      | GL_ADMIN  | 1      | 1      | 1      | 1

The permissions above basically let the regular in each group see anything, but write nothing. The admin users in each group can do anything. Specific table assignments to particular tables can then override these defaults to give the *STAFF members increased access on a table-by-table basis.

Our First Secure Table Pattern: The Read-Only Lookup Table

We are now going to see the first table design pattern that is based on security: the read-only lookup table. Consider a table of customer types and discounts. This table does not change that often, and regular staff are not permitted to make changes to the table, only managers can do that.

I call this pattern the "Read-Only Lookup Table." The table holds values that everybody needs to see, but only a few people can modify. The SQL to create a table might look like this:

CREATE GROUP AR_STAFF
CREATE GROUP AR_ADMIN
CREATE TABLE CUSTTYPES (
   custtype char(10) primary key
  ,description char(25)
  ,discount numeric(4,2) 
)
DENY ALL     ON CUSTTYPES TO AR_STAFF
GRANT SELECT ON CUSTTYPES TO AR_STAFF
GRANT ALL    ON CUSTTYPES TO AR_ADMIN

By the way, this is yet another argument against the deep tendency that code grinders have to combine tables that have similar structures, especially lookup tables. We know in theory that combining lookup tables is bad because like things should be stored together in a table and unlike thing should be stored in separate tables. Now we see the practical impact, which is that you cannot implement security on tables that have been combined (just like you can't implement constraints, foreign keys, calculations...) The crucial point here is that table design is not so much about identifying column names and widths, at its heart it is about organizing data. When you mistake a superficial resemblance (similar column structure) for an actual similarity, you confound your own ability to manage either entity correctly.

The Connection, Users, and Groups

You can certainly implement security in your framework by doing on-the-fly lookups to see who is allowed to do what. However, your run-time framework code will be much simpler if the security definitions are built into the server when the tables are built. The drawback is that it requires a dramatic change to how you connect to the database.

Many programmers use frameworks (or write their own) that make only one connection to the database. This connection has the maximum allowed permissions that any user of the system has, and the framework determines what particular users can actually do. This system is so common that many programmers probably assume it is the 11th commandment, and these same programmers are unaware that there is any other way to do it.

But in fact databases are able to create users and groups just the same way that operating systems are, and then to allow you to connect as these various users. Making all connections as a super-user and trusting your code to get it right puts a huge burden on the programmer, and we have a ready example of what can go wrong by looking at a very popular operating system produced in Redmond, Washington. By contrast, a simple table-based system reduces the task to careful review of the much simpler security dictionary.

The decision on which way to go is not necessarily cut and dried. If you have a site with a very simple database and very simple security, and thousands or millions of users, there is much to be gained by caching connections and arbitrating security in code. However, if you are producing line of business programs like accounting, ERP, medical billing or any other app where your users are limited, there is nothing to be gained by caching connections and everything to be gained by simplifying the system and making it easier to code and maintain.

Beyond Table Security

While table security can be vastly simpler than trying to secure programs one by one, it cannot in fact handle all of the cases that might come up. In later essays we will examine column-level security and row-level security. These are not well supported in the popular products available today, so you may have to work it into your framework (which is what I did for my Andromeda framework).

Also, now that we have seen an introduction to database security, we will be seeing more secure table design patterns in the future.

Next Week: Database Triggers, Encapsulation and Composition.