Styles

vendredi 22 décembre 2006

MySQL 5.0 Fine-Grained Access Control (FGAC)

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

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

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 applidataDatabase 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 :

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.

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 :

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_cardsfor each row set new.owner = @name;//

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:

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.

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 :

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.