Hello ,
I have a database from GRC(governance, risk , security) software and I want to create a RSL to restrict the visibility to a user.
In the application a user can have one or multiple roles that gives a visibility on one or several Organization(s) and it's entities. With visibility on organization user also have visibility on one or several processes in organization and it's entities.
I need to recreate security filter to resctrict visibility of the user only to the data that he/she has acces in application.
Currently Organization and process tree doesn't have direct link, but could be linked via user roles (URE_ID). I just wasn't able to write correct formula to do so.
I have tried many things but seems that there are some limits when I want to create a filter to restrict visibility on organization and another one on process.
Maybe someone has already worked on the similar case and can help me
Currently we have different tables that contain the information about the user role and perimeter of their visibility. I can change tables, add new columns etc
Here are the tables that contains all necessary information
Entry point is an email of a user.
OBJ_USER | Table that contains information about the user |
USER_ID | Unique user id |
EMAIL | User email |
OBJ URE | Tables that contains information about users roles |
USER_ID | Unique user id |
URE_ID | Unique id that is created based on the user role |
ROLE_ID | Role id |
COL_URE_BU and COL_URE_PROCESS contains all the roles of all users and their perimeter of visibility
For exemple id user 1 has a role of administrator of a group he/she'll have unique ure (URE_ID) and the ID of organization units ans etities to which a user has visibility. But this tables do not contain hierarchy,
If user has visibility on whole group he'll have the highest level in every columns from ORGA 1 to ORGA10 , but if he has visibility on one entity, the columns of ORGA wil contain path to that entity
Same logic for process
COL_URE_BU/COL_URE_OU | Association table between users unique URE_ID and Organization/Entity ID |
URE_ID | Unique id that is created based on the user role |
ORGA_1 to ORGA10 | |
COL_URE_PROCESS | Association table between users unique URE_ID and process tree |
URE_ID | Unique id that is created based on the user role |
PROCESS_ID | |
Tables OBJ_ORGA_CONSO_FULL_10 and OBJ_PROCESS_CONSO_FULL_10 contains the columns with id of each hierarchy level and include all the possible combinations of the organization hierarchy that contains in dataset same for process
OBJ_ORGA_CONSO_FULL_10 | Table that contains organization hierarchy |
ORGANIZATIONUNIT_ID | Last entity ID |
| |
OBJ_PROCESS_CONSO_FULL_10 | Table that contains process tree |
| |
These tables should help to restrict visibility of user to different objects like action plans, risks etc.
As an exemple if an entity has an action plan, then in the correspondent table action plan is linked to that particular entity . The problem is if a user has a wider perimeter of visibility , we can't directly see the children of the organization of the user as well as the parents of the entity that has an action plan.
I need either measure or columns to be able to compare entity with organization levels that are contained in OBJ_ORGA_CONSO_FULL_10
And the same for process
Link to the pbix file with the tables describes above
https://drive.google.com/file/d/1emI3ymFPw_eigdZCfihbFyHAJ2RNFOus/view?usp=sharing
