0

Good afternoon,

I am relatively newer to MySQL and I am looking for help to change a database structure up to allow more specific permissions based on sub roles.

Currently, my database is only using a "departments" table with the following setup:

DEPT_ID | DEPT_NAME | DEPT_COLOR | DEPT_ICON | DEPT_WEIGHT
      1 | Presenter |     C0392B |  comments |           1
      2 |    Writer |     8E44AD |    pencil |           3
      3 |    Editor |     16A085 |    wrench |           2

My "user" table is setup as follows:

USER_ID | USER_NAME | USER_EMAIL | USER_PASS | USER_ROLES
      1 |    User A |    x@x.com |      Pass |        1,2
      2 |    User B |    x@x.com |      Pass |        1,2
      3 |    user C |    x@x.com |      Pass |      1,2,3

Using explode and in_array on certain pages, I'm able to grab the necessary department IDs based on USER_ROLES and restrict permission.

In the above, only User C is able to edit anything, but User B is actually a Manager in the "2" role of Writer, so should be able to edit User A's data just in the Writer department.

What I'd like to do is the following:

Alter the setup of this to also allow sub roles and restrict permission based on the sub role. Here are two examples:

User A is in the Presenter and Writer departments. In the Presenter department, they could be "Presenter Level 1", and in the Writer department, they could be "Writer Level 1".

User B is in the Presenter and Writer departments. In the Presenter department, they could be "Presenter Level 1", and in the Writer department, they could be "Writer Manager".

Even if User B isn't an Editor, he would be allow to edit User A's data tied to the Writer department, since they are a manager.

Would I need a roles table tied to department? Would I need both a roles and permissions table?

  • You may have: permissions table (permission SET('view', 'create', 'edit', 'delete')), roles table, join table (deptID, roleID, permissionID). Would I need There is a lot of possible schemes. Some of them needs, some not. – Akina Mar 1 at 4:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.