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.
January 20, 2021 for OAC 5.9
November 8, 2020 for OAC 5.8
February 15, 2019 for OAC 5.1
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
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.
IDCS Administration
OAC Administration
Database Development
OAC Metadata (RPD) Development
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)
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.
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.
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
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.
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.
An RPD exists containing a connection to an ADW instance. For additional details about creating an ADW connection in the RPD see Creating an Oracle Analytics Cloud RPD Connection to the Autonomous Data Warehouse Cloud
Using IDCS Groups allows user assignments to be performed in IDCS by an Identity Administrator rather than in OAC by an OAC Administrator.
Creating IDCS Groups
Assigning Users to 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.
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.
This section creates an OAC custom application role and updates the role's memberships
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
Log into or navigate to the OAC home page with a user account that has OAC administrative privileges.
From the menu icon, select Console > 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.
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 > 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.
For the new FinanceDataRole to be useful, the IDCS group with its members must be added to it. To do this:
Navigate to Console > Users 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.
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 Console > Users 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 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.
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.
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.
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.
This table has a row for each country a user is allowed to view.
This section describes what is done in the OAC Developer Client tool to update the RPD.
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
The command to open the client tool is found below:
< Install Folder >\bi\bitools\bin\admintool.cmd
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.
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
To create a session initialization block for data security:
From the Menu bar click Manage > Variables
From the Menu bar click Action > New > Session > Initialization 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
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.
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.
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.
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.
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.
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
Validate the Data Level Security setup by creating an Analysis and a Project using the subject area from the RPD.
Sign into OAC as John.Dunbar. John Dunbar is only allowed to see data for the United Kingdom and the USA.
Using the COUNTRY_NAME column from the COUNTRIES table and the QUANTITY_SOLD column from the SALES table. Clicking Results shows the following:
Using the COUNTRY_NAME column from the COUNTRIES table and the AMOUNT_SOLD column from the SALES table. The default visualization shows the following:
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)
Lisa Jones may only view data for Australia and New Zealand.
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.
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
Previous Post