Tonight I am doing a bit of work on the EPFarms User Panel and Effin, our financial database. The Panel is a Continuity application running under FastCGI and suexec. The security model is different from most other web applications I've done, we use suexec to run the application as the individual Eggplant Farms user.

Philosophically, running the panel as the user is an extension of our overall security model, which is to build as directly upon the unix security model as we can. The interesting aspect of this is that, since the panel is running without any special privileges, the user could modify or replace the panel and still have all the same security access. They also have all the same access from the command line.

Sharing dynamic data with the user, such as their current account balance, is a bit tricky. We also need the ability for the user to modify some of their own metadata. Our current plan is to create command line tools that allow them user-specific access to our data, which is stored in MySQL.

Another way, the one that I'm going to switch to after tonight's research, is to utilize some new features of MySQL 5. We'll use views to emulate row-level security, and MySQL's own column-level security to allow them to directly read, and as appropriate write, to our database. Though it's always handy to have a command line interface, the extra intermediary was a bit cumbersome.

Here's how to do it.

Lets say we have a single userinfo table, which just holds their username, unix id, full name, and emergency contact info. And heck, let's insert a few rows.

create table userinfo (
  userinfo_id int primary key auto_increment,
  unixid int,
  username varchar(100),
  fullname varchar(100),
  contact varchar(100)
);

insert into userinfo set
  unixid = 1001,
  username = 'awwaiid',
  fullname = 'Brock',
  contact = 'a@b.org';

insert into userinfo set
  unixid = 1001,
  username = 'aardvarq',
  fullname = 'David',
  contact = 'x@y.org';

Now we create a view, limiting to the current logged in user with the USER() function.

create view userview_userinfo as
  select *
    from userinfo
   where username = SUBSTRING_INDEX(USER(),'@',1);

Finally we grant some access to user 'awwaiid' (though really we could grant access to '%' if we were so inclined).

grant select on userview_userinfo to 'awwaiid' 
grant update (fullname, contact) on userview_userinfo to 'awwaiid'

Now when I log in as awwaiid I have access to my own rows, and can update my full name and contact info. The panel application logs into the database as the user, accessing and editing their data in a much more direct fashion than the command-line-wrapper method.