OBIEE - Session variables and row level security
In this post we will look at creating and using a session variable to implement row level security in answers. Situations where this is useful is:
1. Allowing user to see data that she has access to.
2. Showing data based on current date.
3. A sales manager can be shown data in his region only. A CEO can be shown data for all regions.
In this post we look at showing units ordered in the current month. we use a security filter to filter data for the current month.
Steps:
1. The first step is to create the session variable for the current month. To do so
a. In the Administration window, click on Action - > New -> Session -> Variable.Give CURRENT_MONTH as the name of the variable. Click on 'New' near the initialization block.
b. Give CURRENT_MONTH_INIT as the name of the initialization block. Click on Edit Data Source.
c. A new window opens. Select the connection pool by using the browse button.
d. Use database as the data source type.
e. Type in the following query :" select month(curdate()); " in the default initialization string.
f. Click Ok to close the dialog.
g. In the Session variable initialization block, click on edit data target.
h. select the CURRENT_MONTH variable. CLick on Ok.
i. Click on ok to create the session variable.
2. The next step is to use this session variable to filter the result for this month.
a. In the Administration tool. click on Manage -> Security.
b. Create a new User called MonthlyUser.
c. Create a new group called MonthlyUserGroup. Assign MonthlyUser to this group.
d. Open the MonthlyUserGroup dialog and click on Permissions.
e. Click the tab that says filters. Click on 'Add'
f. In the name of the filter select the name of the table that you want to apply the filter on. in this case we select Foodmart.store
g. Click on the ellipsis in the business model filter column. Apply the following filter "FoodMart"."time_by_day"."month_of_year" = VALUEOF(NQ_SESSION.CURRENT_MONTH)
h. the group is now created.
3. Login to BI answers using the MonthlyUser user. Select the columns from the store database. view results. You will notice that the results show data for the current month only.
If you login by a user from the administrators group, data for all months will be visible.
1. Allowing user to see data that she has access to.
2. Showing data based on current date.
3. A sales manager can be shown data in his region only. A CEO can be shown data for all regions.
In this post we look at showing units ordered in the current month. we use a security filter to filter data for the current month.
Steps:
1. The first step is to create the session variable for the current month. To do so
a. In the Administration window, click on Action - > New -> Session -> Variable.Give CURRENT_MONTH as the name of the variable. Click on 'New' near the initialization block.
b. Give CURRENT_MONTH_INIT as the name of the initialization block. Click on Edit Data Source.
c. A new window opens. Select the connection pool by using the browse button.
d. Use database as the data source type.
e. Type in the following query :" select month(curdate()); " in the default initialization string.
f. Click Ok to close the dialog.
g. In the Session variable initialization block, click on edit data target.
h. select the CURRENT_MONTH variable. CLick on Ok.
i. Click on ok to create the session variable.
2. The next step is to use this session variable to filter the result for this month.
a. In the Administration tool. click on Manage -> Security.
b. Create a new User called MonthlyUser.
c. Create a new group called MonthlyUserGroup. Assign MonthlyUser to this group.
d. Open the MonthlyUserGroup dialog and click on Permissions.
e. Click the tab that says filters. Click on 'Add'
f. In the name of the filter select the name of the table that you want to apply the filter on. in this case we select Foodmart.store
g. Click on the ellipsis in the business model filter column. Apply the following filter "FoodMart"."time_by_day"."month_of_year" = VALUEOF(NQ_SESSION.CURRENT_MONTH)
h. the group is now created.
3. Login to BI answers using the MonthlyUser user. Select the columns from the store database. view results. You will notice that the results show data for the current month only.
If you login by a user from the administrators group, data for all months will be visible.
No comments:
Post a Comment