Skip to Content
Author's profile photo Former Member

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.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Kailas Kurup
      Kailas Kurup

      Nice job shybu. A must have implementation for any reporting or dashboard project.

      Author's profile photo Former Member
      Former Member

      Very helpful document

      Author's profile photo Former Member
      Former Member


      Author's profile photo Former Member
      Former Member

      Useful info

      Author's profile photo Former Member
      Former Member

      Very Helpful

      Author's profile photo Former Member
      Former Member


      this solutions works, if the table e_dim_user is in the query. If a user designs his own report and only takes measures for the table e_fact_sales his gets all data. If I want to restrict this, I must go to every measure definition and has to add under tables the additional table e_dim_user. Only if I do this, the query-builder integrates the restriction.



      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Andreas,

                        This solution was developed for a BO dashboard where user has no option to edit the queries. What you said is correct. If we want to restrict this at Fact table level, all the Fact tables should be listed with the row level restriction condition in the Data Security Profile. If th Universe is requested for an Ad-hoc purpose we have to go in that route.

      thanks and regards,

      Shybu raphel