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 "http://database-programmer.blogspot.com/2007/12/database-skills-complete-contents.html"
>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 "http://www.andromeda-project.org">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.
"http://database-programmer.blogspot.com/2008/05/database-triggers-encapsulation-and.html"
>Next Week: Database Triggers, Encapsulation and Composition.
