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

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.

Updates

Validated February 15, 2019 for OAC version 105.1.1-14 and IDCS version 18.4.2-1812230208

Published on February 27, 2019

Versions

OAC                                          105.1.1-14
IDCS                                         18.4.2-1812230208
OAC Development Client Tool 19.1.1 12.2.5.1.0
Windows 7

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?

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

Sufficient Authorization to Create IDCS Groups, Create OAC Application Roles, Replace OAC Data Models and Create Database Objects
(Identity Domain Administrator, OAC BI Service Administrator, and an ADW Admin account or an ADW Schema Owner with the DWROLE privilege)

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

_________________________________________________________________________________

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

IDCS Initial State

This post uses a GSE tenancy. If you don’t have access to a GSE instance, create your own initial users. The following user accounts are used in the post:

lisa.jones

john.dunbar

Any Administrative user account assigned the IDCS Application Role of Service Administrator or the BI Service Administrator pre-defined role in OAC

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

__________________________________________________________________________________________

OAC Initial State

An ADW database is used in this post. It is not required. Other databases accessible by OAC that have the SH schema may be used.

Wallet

An ADW wallet is in OAC for use by the RPD below. 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. The 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. Data returned for each user is filtered by country.

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

The RPD has no application roles, data filters, initialization blocks, or variables.

_________________________________________________________________________

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 Console > Service Administration > Administer Users 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 Console > Service Administration > Administer Users 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.

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

_________________________________________________________________________________

Adding the IDCS Group to the Application Role Membership

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

Navigate to Console > Service Administration > Administer Users and 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 the 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.

Navigate to Console > Service Administration > Administer Users and 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 the Custom Application Role to the DV Content Author Pre-Defined Application Role Membership

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

Navigate to Console > Service Administration > Administer Users and Roles 

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

Select Application Roles from the Type dropdown.

Enter *Data Role for the Name

Click Search

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

 

 

 

 

 

 

Click OK

 

 

The custom application role now contains the DV 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 the User’s Application Roles

To validate 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.

 

 

 

 

 

___________________________________________________________________________

 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.

_________________________________________________________________________________

Data 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 the table that is secured by itself and as 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 Data Selection
Saving the RPD and Checking Consistency
Replacing the RPD

_________________________________________________________________________________

Assigning User Responsibilities to OAC User Sessions via Initialization Blocks

Session Initialization Blocks may be set to execute each time a user logs into OAC. This post uses a Session Initialization Block to query the countries the 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 initial RPD contains just one connection pool. A copy is created to serve as the second connection pool. To create the copy:

In the Physical pane under the ADW Database Right-Click the Connection Pool then click Copy.

Right-Click the ADW Database then click Paste.

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 

 

 

 

 

 

 

 

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

 

 

 

 

 

 

 

Set ’99’ as the Default Initializer

 

 

 

 

 

 

Click OK

 

Click OK again.

The initialization block is created.

For additional information on creating initialization blocks see Creating Initialization Blocks

_________________________________________________________________________________

Adding the OAC Custom Application Role to the RPD

Create a placeholder application role in the RPD with the exact same name 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.

_________________________________________________________________________________

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.

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

To 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 (=)

 

 

 

 

 

 

 

 

 

 

 

Type 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.

To create another 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

Select 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 the > to move it to the expression.
Type or click equals (=)

 

 

 

Type VALUEOF(NQ_SESSION.USER_RESPONSIBILITIES)

Click OK

Click OK again and close the Identity Manager

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

_________________________________________________________________________________

Saving the RPD and Checking Consistency

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

Ensure there are no errors. 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 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. Refer to Create Your First Analysis for detailed instructions on creating an analysis. Refer to Create a Project and Add Data Sets for detailed instructions on creating a project.

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 Oracle A-Team Chronicles for BICS and Oracle A-Team Chronicles for OAC.

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

References

About Data Level Security

Developer Client Tool for OAC 

Connecting Oracle Analytics Cloud Version 5 to the Autonomous Data Warehouse

Creating an Oracle Analytics Cloud RPD Connection to the Autonomous Data Warehouse Cloud

Creating Groups

Assigning User Accounts to the Group

Add Your Own Application Roles

Manage Members

About Connection Pools

Creating Initialization Blocks

About Row-Wise Initialization

Upload Data Models from a File (.rpd) Using Console

Create Your First Analysis

Create a Project and Add Data Sets

Analyze SQL Queries and Logs

Managing Presentation Services Privileges Using Application Roles

Oracle Analytics Cloud

Oracle Identity Cloud Service

Oracle A-Team Chronicles for BICS

Oracle A-Team Chronicles for OAC

 

Add Your Comment