MySQL 5.0 Fine-Grained Access Control (FGAC)

NOTE: This article may be circulated freely as long as the copyright notice is included.

1. The problem

When you want to do fine-grained access control to your data (that is : at the row level) in your application, you don't want to put the access control code into every SQL statement (while people often do this...). Doing so could lead you to a maintenance nightmare. How can you extract all this security stuff out of your application code and put this in a single place.

2. Objectives

Your DBMS is in charge of managing your data, it's the place of choice for your fine grained access control mecanism. The main goal is to let database users think they see all data in tables and they have the rights to select/update/delete/insert into tables without worrying about security.

3. Means

In MySQL 5.0 you have database objects that allows you to implement seamlessly your FGAC solution. MySQL has a lot of limitions on theses objects but you can circumvent them by tricking MySQL... It's easier to implement a FGAC system in other databases such as Oracle or Postgres as their objects have less conceptual limitions.

4. The example and general process

To show you how FGAC works in MySQL, I'll describe it through an example. First, not all your data should be protected. You must select which tables you wish to protect. Let's say we have an application database named applidata, using an administror account like root, you can type :

mysql> create database applidata;
Query OK, 1 row affected (0.00 sec)

This is where authorized users of your database will see the application data.

4.1 What your administrator lets you do

There are two cases to consider :

your MySQL administrator gives you the right to create a MySQL user per application user (one to one relationship between the users you manage in your application and the users declared in MySQL).
your MySQL administrator don't want you to create a lot of MySQL users for an obscure reason (there's really no good reason to forbid it).

I will cover now the second case. The first case ease the implementation of the FGAC system and I will describe it at the end of this article. Say you have a limited number of MySQL users available for your application to connect to your application database (in our case, only one : user1).

mysql> create user user1 identified by 'user1';
Query OK, 0 rows affected (0.00 sec)

mysql> grant select, insert, update, delete on applidata.* to user1@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

You're kind of forced to create a Users Table which store all your different application users. Let's put all this security stuff into a particular database named fgac :

mysql> create database fgac;
Query OK, 1 row affected (0.00 sec)

mysql> use fgac
Database changed

mysql> create table my_users (username varchar(81) primary key, password
    -> varchar(81)) engine = memory;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> insert into my_users (username, password) values ('userA', 'userA');
Query OK, 1 row affected (0.00 sec)

mysql> insert into my_users (username, password) values ('userB', 'userB');
Query OK, 1 row affected (0.00 sec)

mysql> insert into my_users (username, password) values ('userC', 'userC');
Query OK, 1 row affected (0.00 sec)

mysql> insert into my_users (username, password) values ('userD', 'userD');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> alter table my_users add index(username, password);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

You'll see later why password should be in an index too. You can notice I've put this table in memory in order to quicken all this.

4.2 A database for all your protected data

The fgac database should contains all your protected data which comprise : - business data to protect - identification/authentification data used to implement the FGAC. So now that you have your directory of users with their associated password, let's create a table to store your business data you want to protect. For example, let's create a credit_cards table to store all your credit_cards (I really don't recommand storing credit cards informations in your own database with MySQL as it requires a much stronger emphasis on security to protect them) :

mysql> create table credit_cards(num int primary key, expire_date date, owner varchar(81));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into credit_cards(num, expire_date, owner) values (1234, NOW(), 'userA');
Query OK, 1 row affected (0.00 sec)

mysql> insert into credit_cards(num, expire_date, owner) values (5678, NOW(), 'userB');
Query OK, 1 row affected (0.00 sec)

mysql> select * from credit_cards;
+------+-------------+-------+
| num  | expire_date | owner |
+------+-------------+-------+
| 1234 | 2006-12-22  | userA |
| 5678 | 2006-12-22  | userB |
+------+-------------+-------+
2 rows in set (0.00 sec)

So now, I want to leave a restricted access to this table to my users. But they use all the same MySQL account ! Let's pretend all your application data is accessible from the applidata database :

mysql> use applidata
Database changed

First, you have to invent a mean for application users to identify themselves. Once identified, we could construct a view of the credit_cards table which would show only data available for the identified user.

4.3 MySQL limitations

One way to implement the solution would have been to use an authenticate procedure which would create some kind of a session through a temporary table like this :

delimiter //

drop procedure authenticate;
//

create procedure authenticate(name varchar(81), password varchar(81))
begin
	declare the_password varchar(81);

	select password
	into the_password
	from my_users
	where username = name;

	if password = the_password then
	   create temporary table if not exists fgac.session
	   (username varchar(81)) engine = MEMORY;
	   delete from fgac.session where username = name;
	   insert into fgac.session (username) values (name);
	end if;
end;
//

The problem is that you cannot use this session table to form the view because MySQL cannot create view from temporary tables. Another mean for the user to identify himself is to let him set two variables : one containing his username, the other containing his password. The problem, now, is that MySQL cannot create views using variables !

4.4 A Solution

Let's trick MySQL to use variables in where clause of views : you can use functions in views and your functions can return variables values ! Let's say the use must set the name variable with the name of the application user and the pwd variable to the password.

delimiter //

drop function getusername;
//

create function getusername() returns varchar(81)
begin
	return @name;
end;
//

drop function getpassword;
//

create function getpassword() returns varchar(81)
begin
	return @pwd;
end;
//

create or replace view applidata.credit_cards as
       select num, expire_date
       from fgac.credit_cards, fgac.my_users
       where username = fgac.getusername()
       and password = fgac.getpassword()
       and owner = username;

It's seams great, but unfortunately, you cannot delete from this view as you use a join of two tables (MySQL strangly allows updates on data of this view...) So better way to write your view is :

create or replace view applidata.credit_cards as
       select num, expire_date
       from fgac.credit_cards
       where 1 = (select 1 from my_users
			 where username = getusername()
			 and password = getpassword())
       and owner = fgac.getusername();

No more join ! just a nested select in the where clause. Now, connect to your database through the user1 account (the one and only account your administrator let's you employ for nominal operation of your application) and try to select some credit cards :

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| applidata          |
+--------------------+
2 rows in set (0.00 sec)

mysql> use applidata
Database changed
mysql> show tables;
+---------------------+
| Tables_in_applidata |
+---------------------+
| credit_cards        |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from credit_cards;
Empty set (0.00 sec)

mysql> set @name = 'userA';
Query OK, 0 rows affected (0.00 sec)

mysql> set @pwd = 'userA';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from credit_cards;
+------+-------------+
| num  | expire_date |
+------+-------------+
| 1234 | 2006-12-22  |
+------+-------------+
1 rows in set (0.00 sec)

Great, you only see your own credit card without knowing that this "table" stores all credit cards of all users ! No more security code in you where clauses ! You can even update credit card information directly from this view.

4.5 Aargh ! no rollback possible in triggers !

The problem with this view, is that you can insert a new credit card but the owner attribute will be set to null as MySQL is unable to know you're a particular user. To do this, it would have been great to create one trigger to set the owner of the new credit card before inserting the credit card into the fgac.credit_cards table. You cannot do this into one trigger because you must check if the user is correctly identified (@name and @pwd valid). If not valid you want to do a rollback of the insert. Unfortunatly MySQL 5.0 don't support transaction statements (commit, rollback...) into triggers. You have to do it in another way and kind of "simulate a rollback" :

delimiter //

drop trigger credit_cards_before_insert_trg;
//

create trigger credit_cards_before_insert_trg before insert on credit_cards
for each row set new.owner = @name;
//

drop trigger credit_cards_after_insert_trg;
//

-- simulate rollback when user is not correctly identified !
create trigger credit_cards_after_insert_trg after insert on credit_cards
for each row begin
    declare userok boolean default false;

    select true into userok from fgac.my_users
    where username = @name
    and password = @pwd;

    if not userok then
       delete from fgac.credit_cards where num = new.num;
    end if;
end;
//

drop trigger credit_cards_after_delete_trg;
//

-- simulate rollback when user is not correctly identified !
-- Performance is poor here because it recreates all delete rows.
-- Clearly : it's horrible.
create trigger credit_cards_after_delete_trg after delete on credit_cards
for each row begin
    declare userok boolean default false;

    select true into userok from fgac.my_users
    where username = @name
    and password = @pwd;

    if not userok then
       insert into fgac.credit_cards(num, expire_date, owner)
       values (old.num, old.expire_date, old.owner);
    end if;
end;
//

Well, performance matters, and this solution should be investigated in front of numerous user connections... but, functionnality is here ! you can now insert into applidata.credit_cards without problem ! Using user1 connection:

mysql> select * from credit_cards;
+------+-------------+
| num  | expire_date |
+------+-------------+
| 1234 | 2006-12-22  |
+------+-------------+
1 row in set (0.00 sec)

mysql> insert into credit_cards (num, expire_date) values (9012, now());
Query OK, 1 row affected (0.00 sec)

mysql> select * from credit_cards;
+------+-------------+
| num  | expire_date |
+------+-------------+
| 1234 | 2006-12-22  |
| 9012 | 2006-12-22  |
+------+-------------+
2 rows in set (0.00 sec)

If you use your root connection, you can examine your fgac.credit_cards table :

mysql> select * from fgac.credit_cards;
    -> //
+------+-------------+-------+
| num  | expire_date | owner |
+------+-------------+-------+
| 1234 | 2006-12-22  | userA |
| 5678 | 2006-12-27  | userB |
| 9012 | 2006-12-22  | userA |
+------+-------------+-------+
3 rows in set (0.00 sec)

Let's try to update some credit card info using the user1 connection :

mysql> update credit_cards set expire_date = adddate(now(), interval 1 day);
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from credit_cards;
+------+-------------+
| num  | expire_date |
+------+-------------+
| 1234 | 2006-12-23  |
| 9012 | 2006-12-23  |
+------+-------------+
2 rows in set (0.00 sec)

And using the root account :

mysql> select * from fgac.credit_cards;
+------+-------------+-------+
| num  | expire_date | owner |
+------+-------------+-------+
| 1234 | 2006-12-23  | userA |
| 5678 | 2006-12-22  | userB |
| 9012 | 2006-12-23  | userA |
+------+-------------+-------+
3 rows in set (0.02 sec)

And now, let's delete all the userA credit cards using the user1 connection:

mysql> delete from credit_cards;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from credit_cards;
Empty set (0.01 sec)

And on the root side, you have :

mysql> select * from fgac.credit_cards;
+------+-------------+-------+
| num  | expire_date | owner |
+------+-------------+-------+
| 5678 | 2006-12-22  | userB |
+------+-------------+-------+
1 row in set (0.00 sec)

What if a user (userA) try to insert a duplicate key through the use of the credit_cards view :

mysql> select * from credit_cards;
Empty set (0.01 sec)

mysql> insert into credit_cards (num, expire_date) values (5678, now());
ERROR 1062 (23000): Duplicate entry '5678' for key 1

Now, a well-identified user (@name and @pwd correctly set) can now select, update, insert and delete rows into credit_cards table without knowing this table stores all credit_cards of all users. This is what we call Fine-Grained Access Control.

4.6 Security considerations

What if the user tries to abuse the mecanism : he can't, the username and the password must be correctly set to see anything into credit_cards view.

mysql> set @name = 'foo';
Query OK, 0 rows affected (0.00 sec)

mysql> set @pwd = 'bar';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from credit_cards;
Empty set (0.02 sec)

mysql> insert into credit_cards (num, expire_date) values (9999, now());
ERROR 1329 (02000): No data - zero rows fetched, selected, or
processed

mysql> delete from credit_cards;
ERROR 1329 (02000): No data - zero rows fetched, selected, or
processed

5. If your admin is cool

If your administrator let's you create how many users you want, you can decide to create a MySQL user for each application user. When that's the case, the view you build on fgac.credit_cards is much simpler. Using root, try this :

use applidata

create or replace view applidata.credit_cards(num, expire_date) as
select num, expire_date
from fgac.credit_cards
where owner = SUBSTRING_INDEX(USER(),'@',1);

That's all ! you can drop all the triggers and now you do Fine Grained Access Control the right way (the MySQL way in fact). You can try select, update, delete, insert : it all works fine. That's definitively the way you want to implement Fine Grained Access Control in MySQL 5.0.

6. Conclusion

Clearly, MySQL should extends its triggers/views/security functionalities in order to better implement a Fine Grained Access Control like you would expect to be possible in any respectable RDBMS.