OBIEE 11g: Object Level Security Implementation

Object Level Security Implementation

Object Level Security in OBIEE deals with access restriction to various OBIEE objects for different application roles and users.

Object level security controls the access to different objects based on user roles.

Object level security is achieved by granting or denying access to application role or user. The properties applied to application role gets  applied to all the users under it.

We have already seen the Application role, Groups and Users management in my previous post here.

We can restrict access to following objects using object level security,

        1.  Presentation Tables

        2.  Presentation table columns

        3.  Subject area

        4.  Reports

        5.  Dashboards

        6.  Dashboard Pages

        7.  Catlog Folders

If a user is a direct member of an application role, they will have access to the reports allowed by that application role. If a user is not a member of an application role, they will not have access to the reports allowed by that application role.

Object level security can be implemented at presentation layer of repository and web catlog.

Repository Level :

We can set object level security at repository on presentation layer.

We can grant/deny access to user/application roles to access subject area, table or column.

Object level security applied on columns is also called as Column Level Security.

In presentation layer go to properties of a subject area,table or column.

Select permissions.

Select ‘Show all users/application roles’

Here you can see all the users and application roles and properties such as read, read/write, no access and default.

You can set these properties as per your requirements and achieve object level security.

  

Web Catlog Level:

We can set object level security at web catlog level on folders, dashboards, dashboard pages and reports. User can only see object for which it possess authorization.

Similar to object level security on repository level, we can set permissions for application role or users.

Select any folder, dashboard, dashboard page or report.  

Go to its Permissions.

  

Here you can see the list of application roles/users and permissions set for them.

Following is the list of permissions we can set,

We can also set the custom permissions.

Following are the Permissions and their description.

Permission	Description
Read	Use this option to give authority to access, but not modify, the object.
Write	Use this option to give authority to edit the object.
Delete	Use this option to give authority to delete the object.
Traverse	Use this option to give authority to access objects in folders within the selected folder when the user does not have permission to the selected folder. For example, if you grant usersTraverse Folder permission to the /Shared Folders/Test folder, they cannot access objects in the/Shared Folders/Test folder but can access objects stored in lower-level folders, such as the /Shared Folders/Test/Guest folder.
Run Publisher Report	Use this option to give authority to read, traverse the folder that contains the object, and regenerate the report so that it includes the most recent data.
Schedule Publisher Report	Use this option to give authority to read, traverse the folder that contains the object, and schedule the report.
View Publisher Report	Use this option to give authority to read, traverse the folder that contains the object, and view, but not regenerate the report.
Execute	Use this option to give authority to run an object, such as an action, agent, or a briefing book.
Change Permissions	Use this option to give authority to change the object's permissions.
Set Ownership	Use this option to give authority to reassign ownership of the object.
Full Control	Use this option to give authority to perform all tasks (modify and delete, for example) on the object.
No Access	Use this option to deny access to the object. Explicitly denying access takes precedence over any other permission.
Modify	Use this option to give authority to read, write, and delete the object.
Open	Use this option to give authority to access, but not modify, the object. If you are working with an Oracle BI Publisher object, this option enables you to traverse the folder that contains the object.
Custom	Use this option to display the Custom Permissions dialog, where you grant read, write, execute, and delete permissions.
Granted	Use this option to give authority to access a section in a dashboard. This permission can be set in the dashboard, only. This permission overrides any catalog permissions set on the section's objects that would prevent the corresponding roles, Catalog groups, and users from accessing them (for example, No Access).
Denied	Use this option to deny access to a section in a dashboard. This permission can be set in the dashboard, only. This permission overrides any catalog permissions set on the section's objects that would allow the corresponding roles, Catalog groups, and users to access them.

Source : Oracle Documents.

Here we can see more options such as,

Apply effective permissions - It applies set permission to role/user.

Replace with parent’s folder permissions – It inherits the permissions of parent folder.

Apply permissions to sub-folders allows permissions to get applied on all sub folders under that folder.

Apply permissions to items within folders  allows permissions to get applied to the objects under that folder.

Also we can add more application roles and users as,

Relevant Links :

1.  Application Role, Groups and Users Management

2.  Data/Row Level Security

OBIEE 11g: Application Roles, Groups and Users Management

In this post I am going to explain Groups and user management in OBIEE 11g.

Users can be created in Weblogic Server which is common with all Oracle Fusion Middleware 11g Based Products.

Application roles are nothing but collection of policies and it is defined in Fusion Middleware.

Application role defines the type of accesses given on various type of resources.

Groups and users are mapped with these application roles

 

 OBIEE comes with a pre created set of roles called, Application Roles.

BIAdministrator which has all administrative privileges. The BIAuthor is able to create dashboards and also create analyses. Finally, the BIConsumer is a role where the user can only view existing dashboards and analysis. There is also an additional role that can be created called BIAnalyst. This role enables the user to create an analyses, but not the dashboards and also has viewing privileges like the BIConsumer.

Following are the steps to create user and assign it to Groups and Application role.

1.     Goto Oracle WebLogic Server Administration Console, http://[localhost]:7001/console

2.     click on the Security Realms menu item in the Domain Structure navigation tree menu.

3.     Under Summary of Security Realms page, click on the myrealm.

4.      Click on the Users and Groups tab to create new user.

5.     Press the New button, and then enter the details for the new user:

6.     Similarly you can create new group as well.

7.     To add this user to one of the groups, and therefore grant them the corresponding application role, click on the user in the list of users that’s displayed, and then click on the Groups tab.

8.   Select any group, for example BIConsumer, from the Parent Groups Available pane, then move it across to the Chosen pane.

We have created a new user and added them to the BIConsumers group. The policies within Oracle Platform Security Services are granted to  this group as group is added to BIConsumer Role in background.

Steps to create the Application Roles in EM (http://[localhost]:7001/em),

          1.      Login to Enterprise Manager with Admin User.

          2.      Click on Business Intelligence and coreapplication

          3.      Go to Security tab and Single Sign on

          4.      Click on Configure and Manage Application Roles

          5.      Click on Create and Provide the Role name and OK

Steps to Add User’s or Groups on Application Role in EM,

1.      Click on Role and Edit the Created Application role

2.      Add the Existing User’s and Groups to Application Role and OK.

Thats the process to create application roles and assigning groups and users under it.

OBIEE 11g: Data/Row Level Security

In this blog I will explain how row level security can be implemented in OBIEE 11g.

Row-level security is where groups of users have access to a set of reports, but they all see different sets of data within the reports due to filters being applied silently in the background. As a report author you can of course apply filters to an analysis, but in this case I think what people generally associate row-level security with is filters that are applied automatically, according to a set of rules, without the user being aware of it or even filters being visible when you add a filter view to the analysis’ compound layout.

Row level security is implemented by the BI server on the background automatically allowing users to see data or rows to which they are authorized to.

In this example I am going to implement data level security for DIM_PRODUCTS table.

DIM_PRODUCTS

AUTH_DIM_PRODUCTS

Initially without implementing security, I can see all the Products for user ‘TEST1’

We will authorize user TEST1 only for following Products,

Authorization table 'AUTH_DIM_PRODUCTS' will contain following data for user TEST1,

We also need one more authorization table which contains authorization details for all user i.e. in this table we map users with products to which they are authorized to.

  

Open repository in online mode and create 2 different connection pools

First connection pool (‘conpool’)is to import all dimensions and facts from AUTH_UAT schema and second connection pool (‘AUTH_CONN_POOL’) to import authorization tables (AUTH_DIM_PRODUCTS).

We create a dedicated connection pool for importing authorization tables as for data level security we create Initialization blocks which cannot be used with default connection pool used for general database queries.

Now, go to Manage>variables,

Here we will create an Initialization Block for session variable.

We create initialization block as ‘GET_PRODUCT_IB’

 Now we set the properties of this initialization block,

Select ‘Edit Data Source’.

Set connection pool as ‘AUTH_CONN_POOL’ which we used to import authorization table.

Select ‘Data Source Type’ as Database.

Select Default Initialization String. Enter following SQL in text box,

select 'PRODUCTS',product from dim_products pd,auth_dim_products up where pd.product_id=up.product_id and LOWER(up.user_name)=LOWER(':USER')

In above query we are checking for the products authorized for particular user.

‘PRODUCTS’ as a variable which we will be using in filters for fetching Poduct values from database.

‘:USER’ is a session variable used to fetch active user name.

Now select ‘Edit Data Target’ under Variable Target.

Select ‘Row Wise Initialization’ and ‘Use Caching’.

Row Wise Initialization needs to be selected when we are authorizing a user for multiple Product values.

That completes our Initialization Block for Session Variable.

 Next, we create Data Filter for Product.

For this go to Identity Manager in Manage tab.

Go to Application Roles.

I have added user ‘Test1’ under BI Consumer role.

So we create filter for this user under BI Consumer role.

Go to properties of BI Consumer and select Permissions.

Select Data Filters tab and add new data filter.

Select DIM_PRODUCTS from list.

Under Edit Expression add below formula,

"Auth_Demo"."DIM_PRODUCTS"."PRODUCT" = VALUEOF(NQ_SESSION.PRODUCTS)

This formula matches Products authorized for the user and adds filter for the same.

Now check for global consistency and save repository.

That finishes implementation of Data/Row level security for user TEST1 for DIM_PRODUCTS table.

When user logs into dashboard, he will be able to see only products authorized to it.

Thank you :)