So trying to set up Security Access for a data model.
In the data model there are several tables, many with their Access Control field which shows 0 or 1. I have created an access table which has the users listed with an asterix in each of the Access Control fields (I have ensured there are two rows for the system user so that there is a 1 for all columns in one row, and a 0 for all columns in the second row).
This works well in the data model where for the Key field where there is a 0 or 1 for each of the Access Control fields. But many of the Access Control fields are on different tables and not all values in the Key field have rows of data on the other table.
So a person who has "access" to all rows of data (0s and 1s) doesn't see all the data because: - For an id where we dont have a 1 or a 0 for for a particular Access Control field because there isnt a row in the other table, then that id wont show anywhere
You could stuff the other table with rows that are blank except for the access control field and whatever keys are needed to ensure the users see whatever they're supposed to see. Alternatively, you could modify your data structure so permissions aren't based on fields that may result in missing information, if that's a feasible option for the data structure and permissions requirements. This can sometimes be done by creating a table that holds nothing but the access control fields and keys to the other table, depending on the specifics of the data structure and whether you can avoid circular loops when doing this.