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?
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:
To do this I have come up with the following database design.
role
I store the role name here
+----------+
| Field |
+----------+
| id |
| roleName |
+----------+
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 |
| permissionName |
| permissionKey |
+----------------+
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.