Skip to Content

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.

Table Structure.JPGJoin.JPG

After exporting the Universe to the server, come to IDT and click on the security editor icon on the top.

Security Editor.JPG

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.

Add Security Profile.JPG

Click on this button to add a data security profile. A new editor will be Poped-Up

Adding Security Profile Editor.JPG

Go to the rows tab and click on the Insert button at the bottom.

Rows Insert.JPG

On clicking the insert button a new Pop Up window will appear. There we have to select the User table

Selecting User Table.JPG

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.

Putting Condition.JPG

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.

Final Data Profile.JPG

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.

Selecting Users.JPG

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.

You must be Logged on to comment or reply to a post.