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 | firstname.lastname@example.org | Pass | 1,2 2 | User B | email@example.com | Pass | 1,2 3 | user C | firstname.lastname@example.org | 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?