Implementing Data Level Security in Oracle Analytics Cloud Using Identity Cloud Service

February 16, 2019 | 17 minute read
Text Size 100%:

Last Validation January 20, 2021 for OAC 5.9

Introduction

This post describes implementing Data Level Security in Oracle Analytics Cloud (OAC) using Identity Cloud (IDCS) groups. It focuses on what steps are needed. Links to relevant documentation are provided for details on how to perform the steps.

One case for using IDCS satisfies a corporate requirement specifying that the assigning of users to data-level security groups/roles must be performed by an Identity administrator in the Identity provider rather than by an OAC Administrator in OAC.

This post also describes an OAC-only method that does not use IDCS groups.

Validations

January 20, 2021 for OAC 5.9

November 8, 2020 for OAC 5.8

February 15, 2019 for OAC 5.1

Topics

Before you Begin
Creating IDCS Groups and Assigning Users
Creating OAC Custom Application Roles and Updating Memberships
Viewing Data Sources containing both Data and User Responsibilities
Implementing User Responsibilities and Custom Application Roles in the RPD
Validating Data Level Security


 Before you Begin

About Data Level Security

Refer to About Data Level Security for an introduction to Data Level Security.  Data Level Security is often referred to as Row Level Security and vice-versa.

Data-level security defines what rows are returned to a user inside an analysis or DV project view. The same view, when run by two different users, can bring up different data. The structure of the view is the same for all users, unless a user does not have access to the subject area, in which case the report displays an error.

For Data Level Security to be applied to a DV project, the project must use an RPD subject area as the data set.

A typical set of enterprise data security candidates is shown below:

The following diagram shows interactions between the security components.

Implementing Data Level Security using IDCS requires experience in four distinct areas:

  • IDCS Administration

  • OAC Administration

  • Database Development

  • OAC Metadata (RPD) Development


What Do You Need?

The latest OAC Development Client Tool from Developer Client Tool for OAC 

Access to IDCS, OAC, and ADW Oracle cloud instances.

Sufficient Authorization to:

  • Create IDCS Users and Groups

  • Create OAC Application Roles

  • Replace OAC Data Models

  • Create Database Objects

Roles: Identity Domain Administrator, OAC BI Service Administrator, and an ADW Admin account or an ADW Schema Owner with the DWROLE privilege)


Understanding the Initial State of IDCS, OAC, Database, and the RPD

IDCS Initial State

Create or use existing users. Note: Users with the OAC BI Service Administrator role do not have data level security applied to their sessions. Ensure that all users with access to secured data do not have this role. 

The following example user accounts are used in the post:

lisa.jones - no initial privileges required

john.dunbar - no initial privileges required

For administration, an OAC user account assigned the IDCS Application Role of Service Administrator or the BI Service Administrator pre-defined role in OAC

Note: The IDCS Application Role of Service Administrator implicitly contains the BI Service Administrator pre-defined role in OAC.


OAC Initial State

The SH schema provided with Oracle databases is used for examples. It is not required. Other databases accessible by OAC that have the SH schema may be used to follow the steps.

Wallet

This post uses an ADW database for examples. Ensure the wallet is in OAC for use by the RPD. For additional details about uploading a wallet for the RPD to use see Connecting Oracle Analytics Cloud Version 5 to the Autonomous Data Warehouse

Application Roles

Only pre-defined application roles exist with default memberships. Note: The OAC BI Service Administrator application role inherits the users of the IDCS Service Administrator Application Role.


DB Initial State

A table named User Responsibilities exists with country assignments for John.Dunbar and Lisa.Jones. 

Sample SQL to Create this table and insert rows:

CREATE TABLE ADMIN.USER_RESPONSIBILITIES 
(
  USER_NAME VARCHAR2(80) 
, COUNTRY_ISO_CODE VARCHAR2(20) 
);

INSERT INTO "ADMIN"."USER_RESPONSIBILITIES" (USER_NAME, COUNTRY_ISO_CODE) VALUES ('JOHN.DUNBAR', 'GB');
INSERT INTO "ADMIN"."USER_RESPONSIBILITIES" (USER_NAME, COUNTRY_ISO_CODE) VALUES ('JOHN.DUNBAR', 'US');
INSERT INTO "ADMIN"."USER_RESPONSIBILITIES" (USER_NAME, COUNTRY_ISO_CODE) VALUES ('LISA.JONES', 'NZ');
INSERT INTO "ADMIN"."USER_RESPONSIBILITIES" (USER_NAME, COUNTRY_ISO_CODE) VALUES ('LISA.JONES', 'AU');

COMMIT;

A set of pre-defined tables exist under the SH schema of an Autonomous Data Warehouse (ADW). These tables are queried via OAC and are the tables to have data-level security applied in the RPD.


RPD Initial State

An RPD exists containing a connection to an ADW instanceFor additional details about creating an ADW connection in the RPD see Creating an Oracle Analytics Cloud RPD Connection to the Autonomous Data Warehouse Cloud


 Creating IDCS Groups and Assigning Users

Using IDCS Groups allows user assignments to be performed in IDCS by an Identity Administrator rather than in OAC by an OAC Administrator.

Topics

Creating IDCS Groups

Assigning Users to Groups


Creating IDCS Groups

This section creates an IDCS group. Refer to Creating Groups for detailed instructions.

Enter a Name

Enter a Description

Click Finish

The IDCS group is created.


Assigning Users to Groups

This section assigns the John Dunbar user account to the group.

Refer to Assigning User Accounts to the Group for detailed instructions.

Select John.Dunbar

Click OK

The user account is assigned to the group.


 Creating OAC Custom Application Roles and Updating Memberships

This section creates an OAC custom application role and updates the role's memberships

Topics

Viewing the IDCS Group in OAC
Creating an OAC Custom Application Role
Adding the IDCS Group to the Role Membership
Adding a User to the Role Membership
Adding the Custom Application Role to the DV Content Author Pre-Defined Application Role Membership
Validating the Users' Application Roles


Viewing the IDCS Group in OAC

Log into or navigate to the OAC home page with a user account that has OAC administrative privileges.

From the menu icon, select ConsoleUsers and Roles > Roles

IDCS Groups are displayed in OAC as Roles.

All IDCS groups are displayed. Enter F* into the Search with * as wildcard box and press Enter to display the FinanceDataGroup group created in IDCS.

Notice that there is one user under Members.

Click the numeral 1 next to Users to see the John.Dunbar user account assigned to the group in IDCS.


Creating OAC Custom Application Roles

Privileges and permissions are assigned in OAC and the OAC Development Client using OAC pre-defined and custom application roles.

To create:

Navigate to ConsoleUsers and Roles

Click the Application Roles tab and click Add.

Enter a Name e.g. FinanceDataRole, Display Name, and Description.

Click Save.

The application role is created and displayed.

**Repeat this section to create a ProjectsDataRole that is not associated with an IDCS group.

Visit Add Your Own Application Roles for more information on creating custom application roles.


Adding an IDCS Group to an Application Role Membership

For the new FinanceDataRole to be useful, the IDCS group with its members must be added to it. To do this:

Navigate to ConsoleUsers and Roles > Application Roles

In the row displayed for the new application role, click Members.

Select Roles from the Type dropdown.

Enter F* for the Name

Click Search

Select the new IDCS group and click the > icon to move it to the Selected Roles tab.

Click OK

The IDCS group is added to the application role membership.


Adding a User to an Application Role Membership

If IDCS groups are not used, users are added directly to the application role membership. In this example the Lisa.Jones user account is added to the ProjectsDataRole

Navigate to ConsoleUsers and Roles > Application Roles

In the row displayed for the new application role, click Members.

Select Users from the Type dropdown.

Enter L* for the Name

Click Search

Select the Lisa Jones account and click the > icon to move the user to the Selected Users tab.

Click OK

The user account has been added to the application role membership.


Adding Custom Application Roles to the BI Content Author Pre-Defined Application Role Membership

Adding a custom application role to the DV Content Author membership ensures that users assigned to a custom application role have the privileges to create Analyses, Dashboards and DV projects. To do this:

Navigate to Console > Users and Roles > Application Roles

In the row displayed for the BI Content Author, click Members.

Select Application Roles from the Type dropdown.

Enter *Data Role for the Name

Click Search

Select the new custom application roles and click the > icon to move them to the Selected Application Roles tab.

Click OK

The custom application roles now contain the BI Content Author privileges.

Visit Manage Members for more information on adding IDCS groups (roles), users, and pre-defined application roles to application role memberships.


Validating User's Application Roles

To validate that a user has the appropriate application roles do this:

Sign out of OAC.

Log into OAC as John.Dunbar

From the Page Menu in the upper right corner, click Classic Home.

Highlight the User Account in the Signed In As and click My Account

Click Application Roles.

Notice the Finance Data Role and the DV Content Author application roles.

**Repeat this section to view Lisa Jones's application roles.


 Viewing the Data Sources containing Data and User Responsibilities

This section shows the data schema that OAC queries and the table that is used to filter the data based on the user's responsibilities.


Database Schema

This post uses the SH schema that is provisioned with each instance of ADW. These are the tables the post uses:

The COUNTRIES table is secured.  It is also secured with a join to the SALES table. The COUNTRY_ISO_CODE values are used to filter the data returned to an OAC query.


User Responsibilities Table

This table has a row for each country a user is allowed to view.


 Implementing User Responsibilities and Custom Application Roles in the RPD

This section describes what is done in the OAC Developer Client tool to update the RPD.

Topics

Assigning User Responsibilities to OAC User Sessions via Initialization Blocks
Adding the OAC Custom Application Roles to the RPD
Creating the RPD Data Filters to Restrict Query Results
Saving the RPD and Checking Consistency
Replacing the RPD


Open the OAC Client Tool

The command to open the client tool is found below:

< Install Folder >\bi\bitools\bin\admintool.cmd

Assigning User Responsibilities to OAC User Sessions via Initialization Blocks

Session Initialization Blocks execute each time a user logs into OAC. This post uses a Session Initialization Block to query the countries each user is allowed to see.


Creating a Connection Pool for Initialization Blocks

If a physical database is used for both OAC data queries and initialization blocks, then it must have at least two connection pools. One for the data queries and one for the initialization blocks.

The connection pool for initialization blocks must not be the first pool under the database. 

If you need to create a second pool, In the Physical pane under the Database Right-Click the Connection Pool then click Duplicate.

A second connection pool now exists.

For additional information about connection pools see About Connection Pools


Creating a Session Initialization Block

To create a session initialization block for data security:

From the Menu bar click Manage > Variables

From the Menu bar click Action > New > SessionInitialization Block

Enter Name

Click Edit Data Source

Select Database as Data Source Type.

Enter the Default initialization string as SELECT DISTINCT 'USER_RESPONSIBILITIES', COUNTRY_ISO_CODE FROM USER_RESPONSIBILITIES WHERE UPPER(USER_NAME) = UPPER(':USER')

Click Browse for Connection Pool.

Select the Connection Pool created above and click Select 

Click OK.

Note: the constant 'USER_RESPONSIBILITIES' becomes the variable name used in the data filters.

Click Edit Data Target

Check the Row-wise initialization box.

See About Row-Wise Initialization for more information.

Click New

Enter USER_RESPONSIBILITIES for the Name

Check the box Enable any user to set the value

Set '99' as the Default Initializer

Click OK on each screen until the block is created.

The initialization block is created.

Close the Variable Manager dialog.

For additional information on creating initialization blocks see Creating Initialization Blocks


Adding OAC Custom Application Roles to the RPD

Create placeholder application roles in the RPD with the exact same names used in OAC.

From the Menu bar click Manage > Identity

Select Identity Management. Click Action > New > Application Role

Enter a Name

Click OK

The placeholder role is created in the RPD and is synchronized with the role in OAC when the RPD is uploaded.

**Repeat for the ProjectsDataRole


Creating the RPD Data Filters to Restrict Data Selection

The Data Filter is where the expression is created that is appended to the OAC Where clause for each query.

Note: Data Filters are not executed for users who explicitly or implicitly have the BI Service Administrator role.

Create a Data Filter on the COUNTRIES table

From the Menu bar click Manage > Identity

Right-Click the FinanceDataRole then click Properties

Click Permissions

Click Data Filters

Click the green plus sign  to add a data filter

Select Logical Table for the Type

Select the COUNTRIES table and click Select

Highlight the new filter.

Click the Expression Builder icon

Click the COUNTRIES Logical Table

Click the COUNTRY_ISO_CODE Column

Click the > to move it to the expression.

Type or click equals (=)

Enter VALUEOF(NQ_SESSION.USER_RESPONSIBILITIES)

Click OK

A data filter is created on the COUNTRIES table to restrict what rows are returned to the user.

Create a Data Filter on the SALES table

In case the user does not choose the COUNTRIES table for the analysis, the SALES table needs to be filtered so sales totals are displayed only for the countries the user is entitled to see. The appropriate joins to the COUNTRIES table are added to the filter.

From the Menu bar click Manage > Identity

Right-Click the FinanceDataRole then click Properties

Click Permissions

Click Data Filters

Click the green plus sign  to add a data filter

Select Logical Table for the Type

Highlight the SALES table and click Select

Highlight the new filter.

Click the Expression Builder icon

Click the SALES Logical Table. Click the CUST_ID Column. Click >
Type or click equals (=)
Click the CUSTOMERS Logical Table. Click the CUST_ID Column Click >
Click AND
Click the CUSTOMERS Logical Table. Click the COUNTRY_ID Column Click >
Type or click equals (=)
Click the COUNTRIES Logical Table. Click the COUNTRY_ID Column Click >
Click AND
Click the COUNTRIES Logical Table. Click the COUNTRY_ISO_CODE Column Click >
Type or click equals (=)

Enter VALUEOF(NQ_SESSION.USER_RESPONSIBILITIES)

Click OK three times

Close the Identity Manager

A data filter is created on the SALES table with joins to the CUSTOMERS and COUNTRIES tables.

**Repeat this section for the ProjectsDataRole


Saving the RPD and Checking Consistency

Save the RPD and run the consistency checker when prompted or from the Tools menu.

Warnings are mostly OK but ensure there are no errors. Note: Replacing the RPD in OAC with an RPD that contains errors may cause serious problems.


Replacing the OAC RPD

Replace the OAC RPD with the RPD that contains the data level security artifacts. Refer to Upload Data Models from a File (.rpd) Using Console  in the Modeling Enterprise Data in Oracle Analytics Cloud documentation for detailed instructions


 Validating Data Level Security

Validate the Data Level Security setup by creating an Analysis and a Project using the subject area from the RPD.

Sign out of OAC

Sign into OAC as John.Dunbar. John Dunbar is only allowed to see data for the United Kingdom and the USA.

Create an Analysis

Using the COUNTRY_NAME column from the COUNTRIES table and the QUANTITY_SOLD column from the SALES table. Clicking Results shows the following:


Create a Project

Using the COUNTRY_NAME column from the COUNTRIES table and the AMOUNT_SOLD column from the SALES table. The default visualization shows the following:


View the Session Log

Sign in with a user account that has the BI Service Administrator application role.

Refer to Analyze SQL Queries and Logs for detailed instructions.

In the log for John.Dunbar find the SQL sent to the database. Notice the assigned countries in the where clause.

WITH
SAWITH0 AS (select sum(T70.AMOUNT_SOLD) as c1,
T58.COUNTRY_ISO_CODE as c2,
T58.COUNTRY_NAME as c3
from
SH.COUNTRIES T58,
SH.CUSTOMERS T109,
SH.SALES T70
where ( T58.COUNTRY_ID = T109.COUNTRY_ID and T70.CUST_ID = T109.CUST_ID and (T58.COUNTRY_ISO_CODE in ('GB', 'US')) )
group by T58.COUNTRY_ISO_CODE, T58.COUNTRY_NAME)


Optionally Repeat the above for the Lisa.Jones user account

Lisa Jones may only view data for Australia and New Zealand.


Next Steps

Refer to Managing Presentation Services Privileges Using Application Roles for details about Object level Security. At a minimum, secure the subject areas that Data Level Security is protecting.


Summary

This post described what is required to implement Data Level Security in OAC using IDCS groups.

For more OAC, BICS and BI best practices, tips, tricks, and guidance that the A-Team members gain from real-world experiences working with customers and partners, visit http://www.ateam-oracle.com/dayne-carley

For additional details on OAC visit Oracle Analytics Cloud. For additional details on IDCS visit Oracle Identity Cloud Service

 

 

Dayne Carley


Previous Post

Restricting Access to Oracle Analytics Cloud (OAC) by IP Range

Richard Williams | 5 min read

Next Post


Engagement Cloud - Outbound REST Using IDCS OAUTH

Tim Bennett | 5 min read