Oracle BI Applications – Security

I recently had to digg into the standard Oracle BI Applications Security Oracle delivers out of the box. The clients had two security requirements.

The first one was a Data Security requirement. When a user logs in he is presented with his / her organization’s data only. So a user from organization ‘001’  only sees data from organization ‘001’ . Organization user ‘002’ only sees organization ‘002’ and so on.

The second requirement was Object Security. Each function has access to a group of objects, regardless of their organization. So all ‘General Ledger Super Users’ have access to the same objects whether they are in organization ‘001’ or ‘002’

 

The client has the following installation:

  • Oracle eBS R12 (12.1.1)
  • Oracle BI Apps  (7.9.6)
  • Oracle BI EE (10.1.3.4.1)

In general the standard Oracle BI Applications security solution is built around;

  • Groups (Repository, Web Catalog)
  • Session Variables
  • Business Model Filters
  • Permissions
  • Priviliges

In more detail the following steps have to be performed:

Set the application context

The Oracle BI Applications session should get the same security context as Oracle eBS, where you navigate from.

 During logon the ‘EBS Security Context’-Initialization Block is called and executed. The Oracle eBS session cookie is used to set the context. The Initialization block ‘fills’ the variables with information about which user / responsibility combination is logged on. These variable will be used in other Initialiation Blocks along the road.

call APP_SESSION.validate_icx_session('valueof(NQ_SESSION.ICX_SESSION_COOKIE)')

 If all goes well, the Oracle BI Apps session will get the same context as Oracle eBS. Otherwisse there are 3 options :

  • SESSION_DOES_NOT_EXIST,
  • SESSION_NOT_VALID,
  • SESSION_EXPIRED.

Repository Groups

There are two important Groups;

  • Responsibility Groups (Should the Responsibilities in Oracle eBS)
  • Security Groups (These will be used for the Data Security)  
    • –> Examples:
    • Ledger-based Security
    • Inventory Org-based Security
    • Operating Unit Org-based Security

 Variables

The security group someone belongs to is detemined by session variables, which are set during logon.

Initialization Blocks and Variables are the necessary objects to examine. If we relate to the example in the previous section, we could state that the following three Initialization Blocks are most important;

  • Ledgers
  • Inventory Organizations
  • Operating Unit Organizations
  •  

    Data Security

    Data Security is being set up via, Security Groups and Business Model Filters.

     

    As from now on, each query which is composited with a table linked to a Security Group a “Where-clause” is added.

    Presentation Catalog Groups

    The groups as they are created in the repository should also be created in the Web Catalog.

     

     Object Security

    You can use the Catalog Groups to grant or revoke acces to certain objects (Folders, Answers, Dashboards, etc) in the Web Catalog. The same groups an be used to mange the privilliges within the Web Catalog. Use the Security Groups in the Repository to control the Access to the Subject Area’s in the Presentation Layer.

    Check my previous post about navigating from Oracle eBS to Oracle BI EE.

    Oracle MOS HTML Update 28112010

     

     Document ID   Title   Doc Type   Modified Date 
    1182063.1 Applying Patch 9492821 OBIEE Filters No Longer Work PROBLEM 22-NOV-10
    1268460.1 Issues Upgrading Obiee 10.1.3.4 To 10.1.3.4.1 HOWTO 22-NOV-10
    1269144.1 Obiee Upgrade 10.1.3.4.1 HOWTO 24-NOV-10
    1269549.1 Bi Publisher Deployment Failure During Obiee Installation PROBLEM 26-NOV-10
    1268693.1 The Section Heading Is Distorted.Background Colouration HOWTO 23-NOV-10
    1268865.1 Error When Excluding Column HOWTO 23-NOV-10
    1268451.1 An OBIEE 10g report not working on 11g after upgrade: [NCR][ODBC Teradata Driver][Teradata Database] The user does not have CREATE TABLE access to database PROBLEM 22-NOV-10
    1269613.1 After Applyling Patch 9492821 Filter Is Changed In Dashboard From Is Between to Is Less & Variable Not properly Displayed in Prompt PROBLEM 26-NOV-10
    1269303.1 Bi Application Hangs And Its Not Responding Until Restarting Of Services PROBLEM 24-NOV-10
    1269002.1 The Writeback Checkbox Is Disabled In 11g Obiee PROBLEM 24-NOV-10
    1154429.1 A brief explanation of EnableXmlValidation tag Javahost config FAQ 24-NOV-10
    1268946.1 Error Wile Setting Up Preferences PROBLEM 23-NOV-10
    1268621.1 Starting OBIEE 11g (11.1.1.3) On Windows Via The ‘Start BI Service’ Menu Continues To Prompt For Username And Password With boot.properties Configured PROBLEM 22-NOV-10

    Oracle MOS HTML Update 21112010

     Document ID   Title   Doc Type   Modified Date 
    1266745.1 Upgrading Webcat And Rpd To Obiee 11g Failure PROBLEM 15-NOV-10
    1266613.1 Obiee 11g Install Using Existing Weblogic Deployment HOWTO 15-NOV-10
    1266888.1 The Installation of Oracle BI EE 11g 11.1.1.3 has Overview and Scorecard Errors in Sample Database Lite in Linux PROBLEM 16-NOV-10
    1266999.1 OBIEE server crashes Aftter Upgrade From 10.1.3.4.0 To 10.1.3.4.1 HOWTO 16-NOV-10
    1233233.1 OBIEE and OBI apps on Windows 2008 R2 with Processor Intel Xeon HOWTO 18-NOV-10
    1266670.1 Can we Configure Our Obiee Application With Asp.Net HOWTO 15-NOV-10
    1267930.1 Install Taking Very Long PROBLEM 19-NOV-10
    493202.1 OBIEE Scheduler and Fusion Intelligence PROBLEM 15-NOV-10
    1266746.1 Invalid Subscribers Skipped In Ibot Log And Do Not Get The Delivers Report PROBLEM 15-NOV-10
    1266743.1 Invalid Subscribers Skipped Error In Ibot Log – Users Do Not Get Emailed The Delivers Report PROBLEM 15-NOV-10
    1266583.1 How To Change The Default Settings For The Thousands Separator and the Decimal Separator? HOWTO 15-NOV-10
    974195.1 About NQSERROR: 46103 PROBLEM 15-NOV-10
    1266894.1 Obiee11g: Variable Not Set After Action Link (If Variable Has No Default Values) PROBLEM 16-NOV-10
    1267405.1 Path Not Found Error Code ‘U9kp7q94’ When Navigating To Dashbord With Norwegian Characters In Path PROBLEM 17-NOV-10
    979712.1 Cannot Log In To BI Publisher From Answers Using SSO PROBLEM 15-NOV-10
    1268187.1 Ragged Hierarchy In 11g Obiee PROBLEM 19-NOV-10
    1267603.1 WebLogic Repository becomes corrupted and the OBI EE 11g Services will not start with a ‘The menu element does not contain any child elements’ error in the ’emoms.log’ file PROBLEM 18-NOV-10
    1267957.1 Error Code 10058 When Displaying Joined Tables In Answers PROBLEM 19-NOV-10
    1268086.1 Obiee Presentation Service Won’T Start Up PROBLEM 19-NOV-10
    954836.1 Why Does Not Legend Of A Line Graph Show Different Line Types PROBLEM 15-NOV-10
    962802.1 How To Issue Database session specific Commands From OBIEE HOWTO 14-NOV-10
    1267493.1 Obiee Server Generates Incorrect Sql HOWTO 17-NOV-10
    1265441.1 Master Note for OBIEE Essbase Integration issues ANNOUNCEMENT 18-NOV-10
    1266297.1 How to generate Metadata dictionary in OBIEE 11g BULLETIN 18-NOV-10
    1267971.1 Oracle BI Server Crashes Sporadically PROBLEM 19-NOV-10
    1071961.1 Error in Catalog Manager After Transfering the Web Catalog from Unix to Windows: Path not found PROBLEM 17-NOV-10
    1267934.1 How To Change The List Of Available Languages And Locales In The OBIEE Login Screen HOWTO 19-NOV-10
    1267073.1 Oracle Em 11g Does Not Work In Ie8 HOWTO 16-NOV-10