Suggestions for Security Design for Access Control List in database?

Question

I have users for my application with access control list (these are both tables/schema/objects). Currently these are read from the database, Boolean values are used to indicate what they can view/manipulate. However, anyone can still go to the database and change the data. Can someone offer some suggestion on what kind of things I can do? I hope I am clear we have users (uname + pass) and acl (empui_access, empdat_manipulate). Any kinda security solutions via code etc...

Answer 1

Something like

table users
username: string
password_hash: hex
acl: bit array

username is the username, password_hash is the hash of the password, with a grain of salt. It's wrong to store a plain password, but you already knew that, didn't you?

ACL is declared as a string but used as a bit array. Each bit represents a certain permission. 1 means the user has the permission, 0 means he doesn't. To check for a certain bit's value, you do a bit-wise AND on the acl. If the result is non-zero, access is granted. If the result is zero, access is denied.

For example:

// permission to read employee data
public const long READ_EMPL_DATA = 0x01

...
{
    User user = database.GetSomeUser();
    // test for READ_EMPL_DATA permission
    if (0 != (user.ACL & READ_EMPL_DATA)) {
        // access granted
    } else {
        // access denied
    }
    // give READ_EMPL_DATA permission
    if (0 != (user.ACL & READ_EMPL_DATA))
        user.ACL = user.ACL & READ_EMPL_DATA
}

To add group support, add a couple tables.

table group
groupname: string
acl: bit array

table user_group
user_id: id
group_id: id

And in addition to testing for the user-level permission, test the groups to which the user belongs. Of course, you'll write some helper functions, maybe a stored procedure.

I hope this got you started. If not, I can give you a more descriptive example, or more actual code, or other help.

Answer 2

This is specific to PostgreSQL, but you can probably get some good ideas from Veil.

Answer 3

You probably want to be dealing with access control in a different way. Rather than setting up your one access control list, I would use the database permissions system built into your database engine. You run less risk of forgetting something that way, and you can let your users use just about anything do reporting with, and still be guaranteed they can only see the data you want them to see.

How to do this would depend on the particular database you are using.