CODEWITHSUNDEEP
phpmysqlarrayspermissionsstore
Reado
Reado

Reputation: 1452

Storing application permissions in a database

I'm developing an application for our company that eventually will have lots of ways of restricting users to particular sections/modules. While the application is still small, I'd like to move to a new method of storing permissions that, as the application grows, will remain easy to maintain and query.

Currently in our MySQL database we have a table called "user" which stores the user's ID, username and password. In a separate table called "user_acl" is the following:

user_acl_id
acl_root
acl_news_read
acl_news_write
acl_news_modify
acl_reports_read
acl_reports_write
acl_reports_modify
acl_users_read
acl_users_write
acl_users_modify

We only have 3 modules at the minute, but over time more will be created and permissions for each will need to be added.

Rather than create a column for each permission, is there any other way or storing this information?

Upvotes: 17

Views: 21570

Answers (4)

James Anderson
James Anderson

Reputation: 27478

I think you should have five tables:

user
user_x_profile
profile
profile_x_function
function

You set up various "generic" profiles "viewer", "employee", "manager" etc.

You set up a "function" entry for each object you want to control.

Then link the functions to the profiles in profile_x_function.

Then assign one or more profiles to each user.

This cuts the administration effort. Say you want to add another function that only "managers" can use -- you just add a new entry in the function table then add an entry in the "profile_x_function" table which links to the "manager" profile the permission to the manager profile and its available to all managers.

To query access you will need a five table join, but you are only selecting one permision attribute.

Upvotes: 0

Ibrahim Azhar Armar
Ibrahim Azhar Armar

Reputation: 25745

I would do it this way.

table name: permission
columns: id, permission_name

and then I can assign multiple permissions to the user using a many to many relationship table

table name: user_permission
columns: permission_id, user_id

This design will allow me to add as many permission as I want, and assign it to as many user as i want.

While the above design go with your requirement, I have my own method of implementing ACL in my application. I am posting it here.

My method of implementation of ACL goes like this:

  1. User will be assigned a role (Admin, guest, staff, public)
  2. A role will have one or many permissions assigned to them (user_write, user_modify, report_read) etc.
  3. Permission for the User will be inherited from the role to which he/she is
  4. User can be assigned with manual permission apart from the permission inherited from role.

To do this I have come up with the following database design.

role
I store the role name here 
+----------+
| Field    |
+----------+
| id       |
| role_name |
+----------+

permission:
I store the permission name and key here 
Permission name is for displaying to user.
Permission key is for determining the permission.
+----------------+
| Field          |
+----------------+
| id             |
| permission_name |
| permission_key  |
+----------------+

role_permission
I assign permission to role here 
+---------------+
| Field         |
+---------------+
| id            |
| role_id       |
| permission_id |
+---------------+

user_role
I assign role to the user here 
+---------------+
| Field         |
+---------------+
| id            |
| user_id       |
| role_id       |
+---------------+

user_permission
I store the manual permission I may allow for the user here 
+---------------+
| Field         |
+---------------+
| id            |
| user_id       |
| permission_id |
+---------------+

This gives me more control over the ACL. I can allow superadmins to assign permission by themselves, and so on. As I said this is just to give you the idea.

Upvotes: 50

Ray Eldath
Ray Eldath

Reputation: 423

In a very famous MySQL performance book High Performance MySQL, the author specifically mentioned ACL as where data type like SET could be used. They use following example to demonstrate such use cases:

CREATE TABLE acl (
    permissions SET('CAN_READ', 'CAN_WRITE', 'CAN_DELETE') NOT NULL
);

INSERT INTO acl VALUES ('CAN_READ,CAN_WRITE');
SELECT permissions FROM acl WHERE find_in_set('CAN_READ', permissions);

The problem is that, mentioned in the book as well, you can't modify the permissions set easily (you have to use ALTER TABLE), neither can you declare a column typed SET as an index, which may causes performance issue.

You can also use something like TINYINT to "wrap" the ACL list, the cons is that it's harder to read as well as to code SELECT sentence.

Upvotes: 1

JT Smith
JT Smith

Reputation: 741

Like Ibrahim says, create a new table specifically for your permissions. Assign a numerical value to a user which represents their permission level, say 1 = read, 2= write/read, 3 = modify/write/read. Then in your code, check for proper permission level before allowing a user to perform a specific task. If they don't have the required value (3 to modify or >=2 to write) then you block that ability.

Upvotes: 0

Related Questions