Implementing row level security in IDT using Data Security Profile
The document is intended to explain the way row level security made possible through Data Security Profile capability in Information Desing Tool , SAP BI 4.0.
In one of our implementations we have done in the following way.
In the underlying system, all the transactions are happening at Territory level. Hence we have a surrogate key TERRITORY_SK present in all the fact tables.
We designed a user table for User-Territory alignment with USER_LOGIN_ID and TERRITORY_SK. Users will use this USER_LOGIN_ID to login to BO.Using this table we would know which are users coming under a particular territory. These two tables are added in the Data Foundation Layer and inner joined.
After exporting the Universe to the server, come to IDT and click on the security editor icon on the top.
This will list all the folders in the server. Go to the folder where the Universe was previously published and select the Universe.
Once the Universe is selected you can see that the add button for Data Security Profile is enabled.
Click on this button to add a data security profile. A new editor will be Poped-Up
Go to the rows tab and click on the Insert button at the bottom.
On clicking the insert button a new Pop Up window will appear. There we have to select the User table
Now we have to give the condition in the ‘where clause’ part as USER_LOGIN_ID=@Variable(‘BOUSER’). This condition will restrict the rows for the current BO user who is viewing the reports.
Click all the OK buttons and come back to the Universe folder. We can see that the Data Profile is created adjacent to the Unverse.
In the next step we have to add the User Groups to this profile. Just select the Data Profile then in the right side list of User/User Groups will be listed.
Select the necessary users or user groups from right to left.
Now the Universe level settings are done. Also create the necessary objects in Business Layer of User and Fact tables.
When creating report queries we must select the object for USER_LOGIN_ID in the select along with other other relevant objects. This will forcefully create a join with User Dimension and thereby induse security.