Good evening,
I have a small issue concering RLS on a Financial Report I am building.
Background
It an Income/Expenditure report
RLS added via User Role from the Active Directory
Upper managment should see salary values
Lower management should not see salary values
Table of user roles includes a "salary view" column which is either 1 or 0:
- 1 = can view salaries
- 0 = cannot see salary values
I set up two roles in the Manage Roles section:
Non Salary viewing
Salary Viewing
added the lookupvalue for the Organisation Roles:
[organizationalPerson.title] = LOOKUPVALUE(
user[organizationalPerson.title],
user[user.mail],
USERPRINCIPALNAME())
added the [Salary view] filters to the appropriate roles:
- [SalaryView] = 0 to the non-viewing role
- [SalaryView] = 1 to the viewing role
added the filer to exclude Salary costs from the main data in only the non-viewing role:
- [Management Accounts Category] <> "Salary Costs"
After saving, publishing to my test site and adding the organisations all user group to both roles,
Testing as role works.
The salary is gone for the Non viewing version and appear for the Viewing version.
I then removed the role flags and added a user I know should not be able to view the salaries.
The salary values are still there.
RLS didn't channel the user toward the Non-viewing role and restrict the values as expected.
my data is complex, really simplified version below:
| id | user.email | organisation.role |
| 1 | data | Upper Manager |
| 2 | data | Lower Manager A |
| 3 | data | Lower Manager B |
| 4 | data | Upper Manager |
| organisation.role |
| Upper Manager |
| Lower Manager A |
| Lower Manager B |
| organisation.role | DirectorateLink | SalaryView |
| Upper Manager | A1 | 1 |
| Upper Manager | A2 | 1 |
| Upper Manager | B1 | 1 |
| Upper Manager | B2 | 1 |
| Lower Manager A | A1 | 0 |
| Lower Manager A | A2 | 0 |
| Lower Manager B | B1 | 0 |
| Lower Manager B | B2 | 0 |
| DirectorateLink | Directorate | CostCenter | Mgmt Acc Cat | Value |
| A1 | A | 1 | Salary Costs | 1 |
| A1 | A | 1 | Other Costs | 1 |
| A2 | A | 2 | Salary Costs | 1 |
| A2 | A | 2 | Other Costs | 1 |
| B1 | B | 1 | Salary Costs | 1 |
| B1 | B | 1 | Other Costs | 1 |
| B2 | B | 2 | Salary Costs | 1 |
| B2 | B | 2 | Other Costs | 1 |
Can anyone help me restrict the user's data view based on a 1/0 setting whilst using RLS?
Any help will be great!
