Handling of security in 11g is very different from how it was done in 10g. The differences are due to the integration of OBIEE 11g with the Oracle Fusion Middleware. In OBIEE 11g you have to deal with users –> Groups –> Application Roles –> Application policies. To know more about these terms please refer to the Official Oracle docs.
Managing security is carried out using the following tools.
- WebLogic console –> manage users, groups and providers.
- Fusion Middleware enterprise manager –> mange Application Roles and Policies
- BI Administration Tool –> Manage RPD level security, Row Level Access, Data restriction, Logical column restriction, etc.
- Administration page in Analytics –> restrict features of OBIEE Analytics, Subject Area Access.
Discussing or covering up the entire topics on security is an overkill for a blog post and so I am going to cover it in parts. This part deals with row level access or showing different sets of data for different users based on their access rights.
Summary of row-level security access.
- Import the database table which has got information about the data access rights.
- create a session variable and define corresponding initialization block or row-wise initialization blocks in case multiple values have to be used in the filter.
- apply filter condition at the Application role level to restrict data.
- bounce the services and test the security access.
I’m making use of the BISAMPLE schema that is provided by Oracle.
create a database table that contains the mappings for the users and their access rights. In this case, I’m creating a table for users who have access to specific Regions. You can create this table in the BISAMPLE schema.
create table RT_USER_REGION ( username varchar(255) NOT NULL, region varchar(25) NOT NULL, CONSTRAINT userregion_unique UNIQUE(username, region) );
populate some data into this table.
insert into rt_user_region VALUES('rtuser1','AMERICAS'); insert into rt_user_region VALUES('rtuser2','APAC'); insert into rt_user_region VALUES('rtuser3','APAC'); insert into rt_user_region VALUES('rtuser3','EMEA');
create users rtuser1, rtuser2 and rtuser3 in the WebLogic administration console. create group called RTUSERS for these users and add all these users into this group. Using the enterprise manager, add this group to a newly created SalesPersonnel Application Role.
Open your RPD using the BI Admin Tool. Import this table into the physical layer. Also create a new connection pool to be used later for session variable initialization.
create a session variable USER_REGION and its corresponding initialization block
in the initialization block dialog, click ‘Edit Source Data’ button and select the connection pool you created earlier. give the default initialization string as
select region from rt_user_region where upper(username) = upper(':USER')
Note: the above query assumes that a single value is returned. If we have users assigned to multiple regions, then we should use a row-wise initialization block, I will show this at the end of this blog post. rtuser3 is specifically created for this purpose.
please see below the screen shot of the completed initialization block dialog.
In the BI Admin tool go to Manage –> Identity and select the Application Role you have created earlier.
Double click or right click –> properties on the SalesPersonnel Application Role, then click the Permissions button.
In the User/Application Role Permissions dialog box, click the green color ‘Plus’ Add button.
Next, you will apply the filter: you can apply the filter at the presentation level or at the logical model level.
- If applied to the table at the presentation level, the filter is applied only to the table that belongs to that specific subject area, even if you have multiple subject areas from the same logical model.
- If we apply the filter to the D3 Customers table (where the Region column is present), then the filter is applied to an analysis which contains columns from the D3 Customers table.
- If you want to filter the analysis even if no columns from D3 Customers was selected , then you should apply the filter to the associated fact table which in this case is F1 Revenue.
- If applied at the logical model level, then the filter will be applied to every subject area that uses that logical table from the model.
In this scenario, Apply the filter the Logical Model level, so that the access restriction is enforced throughout (and in any future analyses). Double click on D3 Customers or use the select button.
define the expression by clicking the white area under the Data Filter heading clicking the edit expression button.
use this expression.
"RTSales"."D3 Customers"."REGION" = VALUEOF(NQ_SESSION."USER_REGIONS")
Click on OK and finish the steps and save your RPD. Bounce the BI Services and test the data security access you just implemented.
logging in as rtuser1 will only show the Region values for AMERICAS. logging in as rtuser2 will only show APAC values.
Row wise initialization block:
If multiple regions are allocated to the Sales Personnel, then you will have to use the row wise initialization block so that the the multiple values returned by the query are available to be used as the filter.
follow these steps for using row-wise initialization block. This concept is also used in this blog post.
In the BI Admin Tool, go to Manage –> Variables. In the left hand side part of the dialog box select Session –> Initialization blocks.
create a new session initialization block by right clicking on an empty area on the right hand side and selecting the menu option.
click the ‘Edit Data Source’ button and select the connection pool as shown
give the default initialization string as
select 'USER_REGIONS', region from rt_user_region where upper(username) = upper(':USER')
The variable is automatically defined in the ‘USER_REGIONS’ part of the query. The completed dialog box looks like this.
if you login as rtuser3 you will be able to view regions APAC and EMEA.
