What is Row Level Security Per Person?
Row level security limits the rows seen by a user based on a set of criteria. Two users will see the same fields but different counts based on their security assignments.
Step 1 - Create your security assignments
This can be an excel you upload into your workbook or a data source you connect your workbook to. At the very least, the security assignments must include (1) Active Directory ID Current and (2) key field that you want to assign.
Example By Department:
Department Code | Department | Identity Active Directory ID Current | Employee Display Name AsOf |
003009 | 9 EAST CRC UNIT | rcmadden | Russia Madden |
000382 | ADV CELL THERAPY LABORATORY | rcmadden | Russia Madden |
000429 | CAMPUS PLANNING OFFICE | saparnell | Sarah Parnell |
003077 | CARDIOLOGY CLINIC | saparnell | Sarah Parnell |
950000A | CHANCELLOR | saparnell | Sarah Parnell |
Example By Project
Project Code | Project | Identity Active Directory ID Current | Employee Display Name AsOf |
2024234 | SP MALAS COOK MEDICAL 22-0401-2H | rcmadden | Russia Madden |
2004510 | NSA CONSULTING SPIRAL THERA 2020 0292 MAREK | rcmadden | Russia Madden |
1500561 | RCR 4310120 15000 C61570 | rcmadden | Russia Madden |
2001757 | OTHR Gen Proj Thornton Wireless Telecomm | rcmadden | Russia Madden |
1024514 | FSU CR HEVNER COMMITTED DISCRETIONARY | saparnell | Sarah Parnell |
1500634 | RCR 4310152 15000 000000 | saparnell | Sarah Parnell |
2028379 | SP Aronoff-Spencer YALE UNIVERSITY Advanced Spending | saparnell | Sarah Parnell |
1006709 | DNU SP FDNU SP NSF DGE 1650112 BARRETT | saparnell | Sarah Parnell |
1003133 | CP MC UCMC Th Convert Frozen Sect Lab Gm | saparnell | Sarah Parnell |
1026241 | CRV DEAN CMT IDEKER GRAD STIPEND (MEDTIGS) | saparnell | Sarah Parnell |
Step 2 - Add your data and your security assignments
You will be blending two data sources. Add your data source and your security assignments to your workbook.
Example:
Step 3 - Add your Username Check
On your security data source create a calculated field to compare the username in your security assignments username against the username of the person using your workbook.
Confirm that your calculated field is working by adding your Active Directory ID Current and Security Username Check to your worksheet. You should see ‘True’ next to your username and ‘False’ next to all of the other Usernames. Clear your worksheet now or after applying the Security Username Check filter.
Step 4 - Add your Username Data Source Filter
Navigate to the Data Source tab for your Security Assignments. Add a filter for Security Username Check = True.
Note: If you are not in your Security Assignment data source do this step last b/c you will not be able to see any data after applying this filter.
Clear your worksheet if you have not yet.
Step 4 - Connect your data sources
Add the key field from your primary data source to the worksheet, then add the key field from your security data source.
Create a calculated field on your primary data source to connect the key fields between your data source. You will need to adjust the aggregation to match between the data sources.
Add your calculated field to the filter area and filter where your calculated field = TRUE. You should immediately see a reduction in rows in your display.
Apply this filter to all worksheets that you would like to have Row Level Security.
Additional Notes
Steps 5 and 6 can be done in opposite order.
You can create multiple Data Source Connection (step 6) fields and filter on them as long as the key field is in both data sources.