Security design suggestions for a database access control list?
I have users for my app with access control list (these are both tables and schemas / objects). They are currently being read from the database, Boolean values are used to indicate that they can be viewed / manipulated. However, anyone can still navigate to the database and change the data. Can anyone suggest some suggestion on what I can do? I hope I understood that we have users (uname + pass) and acl (empui_access, empdat_manipulate). Any kind of security solutions through code, etc.
a source to share
Sort of
table users
username: string
password_hash: hex
acl: bit array
username is the username, password_hash is the password hash, with salt. It is wrong to store a simple password, but you already knew that, didn't you?
The ACL is declared as a string, but used as a bit array. Each bit represents a specific resolution. 1 means the user has permission, 0 means he doesn't. To check the value of a specific bit, you do bit-wise AND on acl. If the result is nonzero, access is granted. If the result is zero, access is denied.
For instance:
// 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 support for groups, add a couple of tables.
table group
groupname: string
acl: bit array
table user_group
user_id: id
group_id: id
And besides testing for user level permission, check the groups the user belongs to. Of course you will write some helper functions, perhaps a stored procedure.
I hope you succeeded. If not, I can give you a clearer example or more real code or other help.
a source to share