How to set user rights for a tree structure (adjacency) database?

Question

We are using mysql. Suppose we have a table of address list models. (I know this is not standardized.) For example, a staff table with fields such as:

Personnel |employee|boss |salary| -------------------------- |CEO |null |999999| |boss1 |CEO |99 | |boss2 |CEO |99 | |slvdrvr1|boss1 |9 | |slvdrvr2|boss1 |9 | |slave1 |slvdrvr1|1 | |slave2 |slvdrvr1|1 | |slave3 |slvdrvr1|1 | |slave4 |slvdrvr2|1 | |slave5 |boss2 |1 | |slave6 |boss2 |1 | 

We also have a table with some users. For instance,

 Uers |userid|role | ---------------- |super1|b1 | |super2|b2 | |user1 |sd | |ruut |admin | 

Suppose salary information is confidential. Thus, some users may see salary information in a particular industry.

For example, the role b1 can see / edit all salaries from boss1 and down. Role b2 can see / edit all salaries from boss2 and down. The sd role can see / edit all salaries from slvdrvr1 and slvdrvr2 and down. Therefore, we need to set up a table of user and personnel permissions for its implementation.

The staff table is large with tens of thousands of rows.

My question is, how do we set up a permissions table that we can easily maintain and query?

For example, option 1: we can configure the permission of the role for each person. But then the permission table will be huge.

Option 2: we can set the permission of the role only for the parent node. But every time the user wants to see a certain number of records, we must look for permissions for each record right down to where we have the settings.

The number of users is about a hundred. The number of roles is less than 100.

Please ask me questions if this is not clear. Thanks in advance.

Answer 1

To make life more interesting, think about the presence of a tree structure among Users!

Getting started, create another table "User_EMPL_ACCESS", and it should look like this:

Table USER_EMPL_ACCESS (User_ID char (..), ACCESS_PATH char (..))

In the access path, save the full path to the topmost node of each user who has the right to view.

Then create a UDF named hasAccess (userID, EMPLID).

The hasAccess function should do the following:

• Start with UserID
• Get user access paths from USER_EMPL_ACCESS
• Expand the Adjacency table to fully qualified paths using CTE logic. If your database does not provide CTE, convert the Adjacency Model to NestedSet.
• Get the intersection of user access paths and access paths starting at the root
• If the input EMPLID is a leaf node of any of the above intersection paths, return 1 else returns 0.

Your queries now look like this:

  select * from SALARY_TABLE where MONTH = 'JAN' and hasAccess('myUserID', SALARY_TABLE.EMPLID) = 1