OBIEE Complex Data Security in Action

Earlier, I wrote a blog post about the presentation (Complex Data Security Made Easy in Oracle BI) that I delivered at Kscope17. I used a typical use case from a marketing organization as to how complex data security like team and position based hierarchies can be implemented via four different approaches in Oracle BI.

When preparing for my Kscope17 presentation, I created a demo from scratch. In this blog post, I am going to share that demo with you so that you can recreate it in your own local environment and see the four different approaches in action for modeling the complex data security in Oracle BI.

What’s in the demo?

The demo consists of the following artifacts

• demo.rpd : RPD file that implements all four data security approaches
  • Dimension Data Filter
    • Session Variables: LOGIN_USER, HIER_LEVEL
    • Logical column: “Dim – Position Hierarchy”.”Hierarchy Based Login”
  • Reverse Star Schema
    • Logical Table Sources: Dim – Sales Person, Dim – Position, Dim – Position Hierarchy
  • Row-wise Session Variable
    • Session Variables: HIER_OPTY_LIST, TEAM_OPTY_LIST
  • Opaque View
    • For securing facts: Dim_Opportunity_Position_Visibility, Dim_Opportunity_Team_Visibility
    • For securing Opportunity dimension: Dim_Opportunity_Dim_Position_Visibility, Dim_Opportunity_Dim_Team_Visibility
    • Logical Table Sources: Dim – Opportunity Position Security, Dim – Opportunity Team Security, specify Left Outer join for each securing fact and dimension LTS
• usergroups.ldif : security data file for importing users and groups setup into the Weblogic default Authenticator.  Total nine users and four user groups are configured.  The four different data security approaches are mapped to the four security user groups accordingly.
  
• demo_ssi.bar : snapshot file for setting up the demo in 12c
• demo_data.sql : database script file for seeding the demo data

How to set up the demo

To set up the demo, make sure that you have an OBIEE 12c already installed.  I am using a Windows install as an example.  The install home is: C:\Apps\Oracle\obiee

1. Download the demo.zip file, and unzip it to C:\
2. Set up the demo data
   1. To seed the demo data, use C:/KScope17_Demo/scripts/demo_data_mssql.sql for SQL Server, or use C:/KScope17_Demo/scripts/demo_data_orcl.sql for Oracle
   2. Create a new database user/pwd: KScope17_Demo/KScope17_Demo that can access the demo data properly
   3. Create a new ODBC DSN: KScope17_Demo using the corresponding database ODBC driver
3. Start BI server and all system components
4. Open wlst (wlst.cmd or wlst.sh is located at ./oracle_common/common/bin)
   1. Run the following command to import the demo BI application to BI Server, replace ‘C:/Apps/Oracle/obiee‘ with your actual 12c install path

      > importServiceInstance('C:/Apps/Oracle/obiee/user_projects/domains/bi','ssi','C:/KScope17_Demo/bar/demo_ssi.bar',true,true,true,'Welcome1')

   2. Run following wlst commands to import users and groups configuration to Weblogic default Authenticator, then exit wlst

      > connect('weblogic','Welcome1', 't3://localhost:9500')
      > domainRuntime()
      > cd('/DomainServices/DomainRuntimeService/DomainConfiguration/bi/SecurityConfiguration/bi/DefaultRealm/myrealm/AuthenticationProviders/DefaultAuthenticator')
      > cmo.importData('DefaultAtn','C:/KScope17_Demo/ldif/usergroups.ldif', Properties())
      > disconnect()
      > exit()

5. Restart BI server and all system components

How to run the demo

Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud

Explore key considerations, integrating the cloud with legacy applications and challenges of current cloud implementations.

Get the Guide

The demo has following security filters pre-configured in RPD. Security data filters are configured with application roles accordingly.

The table shows

• User skiplingeb and rmatzkekz are assigned with application role KScope17SecurityDemo_DDF, which has security filters only applied to Opportunity Team fact, to demo Dimension Data Filter approach
• User drivallandak and jgillmorft are assigned with application role KScope17SecurityDemo_RSS, which has security filters only applied to Opportunity fact, to demo Reverse Star Schema approach
• User pslopiercs and mtuplin5p are assigned with application role KScope17SecurityDemo_RSV, which has security filters applied to both Opportunity Team and Opportunity facts, to demo Row-wise Session Variable approach
• User mobraddenm0, mgoskar6w and dloniep7 are assigned with application role KScope17SecurityDemo_VDM, which has security filters applied to both Opportunity Team and Opportunity facts, and Opportunity dimension as well to demo Opaque View approach

The demo data has also following position hierarchy and sales teams pre-configured.  Use any non-grayed out login user to test each data security approach.

For example,

• Login as the Marketing VP mobraddenm0, the KScope17 dashboard will show all seven opportunities (6, 26, 49, 78, 135, 198, 421) in both Opty Team and Opty reports, as it sees all its direct (Marketing Director) and indirect (Sales Account Manager) reports’ opportunities.
• Login as any of the Marketing Director, it will see all its direct (Sales Account Manager) reports’ opportunities.  e.g.
  • as rmatzkekz, the KScope17 dashboard will show three (26, 135, 198) opportunities in Opty Team (Right) report, and all seven in Opty (Left) report, since the security filters are only applied to the Opportunity Team fact.
  • as drivallandak, the dashboard will show four (6, 135, 198, 421) opportunities in Opty (Left) report, and all seven in Opty Team report, since the security filters are only applied to the Opportunity fact.
  • as pslopiercs, the dashboard will show two (49, 78) opportunities in both Opty and Opty Team reports, since the security filters are applied to both Opportunity and Opportunity Team facts
  • as mgoskar6w, the dashboard will show three (26, 49, 78) opportunities in both Opty and Opty Team reports, since the security filters are applied to both Opportunity and Opportunity Team facts, and also to Opportunities dimension.
• Login as any of the Sales Account Manager, it will see only its own opportunities for the report that has the security filters applied.

The demo could be considered as a reference implementation for modeling the data security in Oracle BI. It’s worthy of bookmarking for future reference.