SLS: Subledger Security

I am in the proces of implementing security for Oracle BI Apps 7.9.6 in a Oracle eBS R12 environment. One of the requirements is Subledger Security (User Guide, My Oracle Support).

As per the documentation; Subledger Security is an extension to Oracle Financials that enables the user to selectively partition data within a single install of Oracle Financials. Subledger Security provides a system where all business units can access their own financial information only.

In my clients case it makes it possible to secure parties, customers and suppliers. Certain customers are only visible for selected responsibilities within the same operating unit.

For implementing the security I am interested in the database-implementation of SLS. Check the following diagram:

For all the Subledger Security tables you have to refer to the IGI-scheme in Oracle eBS:

Whether Subledger Security is applied depends on two profiles;

  • ‘Subledger Security : Security Group’
  • ‘Subledger Security : SLS Responsibility’

A query to retrieve the values of these profiles could be;


SELECT r.responsibility_id ,
  r.responsibility_key ,
  r.responsibility_name ,
  nvl(sr.profile_option_value, 'N') sls_responsibility ,
  nvl(sg.profile_option_value, 'No SLS Responsibility') sls_security_group ,
  nvl(i.sls_group, 'No SLS Responsibility') sls_group
FROM
  (SELECT t.profile_option_name ,
    t.user_profile_option_name ,
    v.level_id ,
    v.profile_option_value ,
    r.responsibility_id ,
    r.responsibility_key ,
    r.responsibility_name
  FROM fnd_profile_options_tl t ,
    fnd_profile_options p ,
    fnd_profile_option_values v ,
    fnd_responsibility_vl r
  WHERE t.profile_option_name    = p.profile_option_name
  AND p.application_id           = v.application_id
  AND p.profile_option_id        = v.profile_option_id
  AND r.responsibility_id        = v.level_value
  AND v.level_id                 = 10003 -- Responsibility
  AND t.user_profile_option_name = 'Subledger Security : Security Group'
  AND t.language                 = 'US'
  ) sg ,
  (SELECT t.profile_option_name ,
    t.user_profile_option_name ,
    v.level_id ,
    v.profile_option_value ,
    r.responsibility_id ,
    r.responsibility_key ,
    r.responsibility_name
  FROM fnd_profile_options_tl t ,
    fnd_profile_options p ,
    fnd_profile_option_values v ,
    fnd_responsibility_vl r
  WHERE t.profile_option_name    = p.profile_option_name
  AND p.application_id           = v.application_id
  AND p.profile_option_id        = v.profile_option_id
  AND r.responsibility_id        = v.level_value
  AND v.level_id                 = 10003 -- Responsibility
  AND t.user_profile_option_name = 'Subledger Security : SLS Responsibility'
  AND t.language                 = 'US'
  ) sr ,
  igi_sls_groups i,
  fnd_responsibility_vl r
WHERE sr.responsibility_id = sg.responsibility_id (+)
AND sr.responsibility_id (+)  = r.responsibility_id
AND i.sls_group (+)        = sg.profile_option_value ;

Other queries to find out which data a responsibility is allowed to view:

The query below gives insight in the available SLS Security Groups, eg”.: ‘AR Security for Dept A’

SELECT sls_groups --'AR Security for Dept A' 
FROM   igi_sls_groups
WHERE  sls_group_type = 'S';

The query below gives insight in which tables are ‘SLS Security’-enabled within a certain SLS Security Group.

SELECT sls_group --'AR Security for Dept A'
,      table_name -- HZ_PARTIES 
FROM   igi_sls_enabled_alloc_v;

The query below gives insight in which ‘IG_SLS_#’-table the ‘SLS-Security’-details are stored. Depending on the configuration, multiple ‘IG_SLS_#’-tables  (IG_SLS_1, IG_SLS_2, IG_SLS_3, IG_SLS_4, IG_SLS_5, etc.) can exist.

SELECT owner -- AR
,      table_name -- HZ_PARTIES
,      sls_table_name -- IG_SLS_1 
FROM igi_sls_secure_tables;

The query below gives insight in the actual id’s which are secured by the ‘SLS-Security’-settings. The rowid of the source table is stored in the ‘IG_SLS_#’-table.

SELECT sls1.sls_rowid    
,      sls1.sls_sec_grp    
,      p1.party_id    
,      p1.party_number    
,      p1.party_name 
FROM   igi_sls_1 sls1 -- HZ_PARTIES    
,      hz_parties p1
WHERE  p1.ROWID = sls1.sls_rowid;

Using these queries, you should be able to select which id’s are available to a selected responsibility. Within the Oracle eBS R12 application, this is managed by policies (VPD – Virtual Private Database, RLS – Row Level Security)

Check the following query, to see how it’s setup;

select object_owner --'AR'
,      object_name  -- 'HZ_PARTIES'
,      policy_name  -- 'IGI_SLS_3_POL'
,      pf_owner     -- 'APPS'
,      function     --'IGI_SLS_1_FUN'
from   dba_policies
where  policy_name like 'IGI%'

With this information I am able to built ‘SLS Security’ into Oracle BI Applications.

UDML – Scripting

One of my clients wants to add loads of Security Groups into the repoitory. Of course this can be done manually, but in this case it would be better to script these groups into the database. I knew that UDML is an undocumneted feature in Oracle BI 10g. If you perform a search on Google, you could find enough information. I used the information provided by Venkat and Andreas.

There was one thing I couldn’t find out directly. I was looking for a possibility to nest Security Groups. It was not clear to me what the syntax should be. Again UDML to the rescue. You can use to script something into the repository. The other way around works as well. What I did was, I created a nested Security Group manually.

No I was able to extract the UDML-syntax from the repository;

G:\Oracle\10g\OracleBI\server\Bin\nQUDMLGen.exe -U Administrator -P Administrator -R "Y:\webLog\OBIBB\OBIBB - UDML\groupImport.rpd" -O "Y:\webLog\OBIBB\OBIBB - UDML\securityUDML.txt"  -S

 The ‘-S’  is for generating script for only security objects.

Output for ‘securityUDML.txt’ is as follows;

DECLARE REPOSITORY PROPERTIES (
 'CustomPresentationLayer' = '01',
 'PersistedNextUpgradeID' = '0A000000');
VERSION 1.1.184;
DECLARE SECURITY ROLE "Administrators" AS "Administrators" UPGRADE ID 2
 HAS USERS (
    "Administrator" )
 PRIVILEGES ( READ);
DECLARE SECURITY ROLE "Group01a" AS "Group01a" UPGRADE ID 4
 INHERITS FROM (
    "ManualGroup" )
 PRIVILEGES ( READ);
DECLARE SECURITY ROLE "Group01b" AS "Group01b" UPGRADE ID 6
 INHERITS FROM (
    "ManualGroup" )
 PRIVILEGES ( READ);
DECLARE SECURITY ROLE "ManualGroup" AS "ManualGroup" UPGRADE ID 9
 PROPAGATES TO (
    "Group01a",
    "Group01b" )
 PRIVILEGES ( READ);
DECLARE USER "Administrator" AS "Administrator" UPGRADE ID 3 FULL NAME {} PASSWORD 'D7EDED84BC624A917F5B462A4DCA05CDCE256EEEEEDC97D5213DF9555A8D6E566A4A72028AAD1FC28AA7433B66F722D0CEE88C996D2D894F' NEVER EXPIRES
 HAS ROLES (
    "Administrators" )
 PRIVILEGES ( READ);

Looking add this output, you see that the subgroup ‘INHERITS FROM’ the parentgroup. The parentgroup ‘PROPAGATES TO’ the subgroup.

Now using the following script I should be able to import subgroups and parentgroups into the repository:

DECLARE SECURITY ROLE "Group01a" AS "Group01a"
 PRIVILEGES ( READ);
DECLARE SECURITY ROLE "Group01b" AS "Group01b"
 PRIVILEGES ( READ); 
DECLARE SECURITY ROLE "Group02a" AS "Group02a"
 PRIVILEGES ( READ);
DECLARE SECURITY ROLE "Group02b" AS "Group02b"
 PRIVILEGES ( READ);
DECLARE SECURITY ROLE "Group01" AS "Group01"
PROPAGATES TO ("Group01a", "Group01b")
PRIVILEGES ( READ);
DECLARE SECURITY ROLE "Group02" AS "Group02"
PROPAGATES TO ("Group02a", "Group02b")
PRIVILEGES ( READ)
;

By using the nqudmlexec-executable, you should be able to import the parentgroups (“Group01” , “Group02”) and subgroups(“Group01a”, “Group01b”, “Group02a”, “Group02b”) into the repository.

I hope the same is possible for Catalog Groups. More to come.

Implementing Row-Level-Security in Oracle BI EE

Sometimes there is a need to restrict data access to certain groups of users. Oracle provides a mechanism called Row-Level-Security. You could achieve similar functionality from within Oracle BI EE.

Picture this; You have a table of Sales Managers which are responsible for a certain region. Each Sales Manger may only see the data for his / her region.

First you have to know which user has logged on and to which region this user belongs. Therefore you should use Session variables. To set this up properly you could refer to the documentation. You validate the logged on user to a table of Sales Managers. This way you could also select the region a Sales Manager is responsible for. The principle of this solution is that you have the Sales Manager and their regions in a table which you can select from. Let’s say we now have a ‘REGION’ session variable.

We can go on to the Security Groups. Create a new Security Group called; ‘Sales Managers’. Assign all the Sales Managers (Repository Users) to this newly created group.

The final step is to set Business Model Filters on this group. The concept of these filters is thatb you add all Logical Tables to this group, which you want to restrict on a Sales Managers’ region. You could achieve this by following the next steps;

  1. Open the ‘Sales Manager’-Security Group,
  2. Click on; ‘Permissions’,
  3. Click on the Tab; ‘Filters’,
  4. Click; ‘Add’,
  5. Select the table you want to restrict, eg.; “Sales”.”Dim Region”.”Region Name”,
  6. Use the Expression Builder to create the actual filter; “Sales”.”Dim Region”.”Region Name” = VALUEOF(NQ_SESSION.”REGION”).

Now when you use the “Sales”.”Dim Region”-table in an Oracle BI Answers query, the Business Model Filter will be applied. This filter only applies to this Security Group. User which do not belong to this group will see all the regions.

Similar functionality is used when implementing Oracle BI Apps Security.

Security issues when upgrading a Web Catalog from 10g to 11g

I blogged about upgrading from Oracle BI EE 10g to Oracle BI EE 11g R1 earlier. Although this is a very straight forward process, you could end up with some security issues.

Picture the following. You are an administrator user with the appropriate security roles to act as an (Presentation Server) Administrator. You are able to login and manage the Weblogic Console and the Enterprise Manager. When you log into the upgraded Web Catalog you are not able to see the Administration-link.

There already a lot of good blogpost about the new Oracle BI 11g security setup. Just to name a few;

When upgradin a WebCatlog you could be forced to do a work-around  for the security, thanks to René Kuipers. The workaround is as follows;

  • Do the upgrade according to the documentation
  • Make a backup via the Catalog Manager or upgrade a second time so you have a copy of the Web Catalog
  • Throw away the user folders via the Catalog Manager
  • Login again into the Web Catalog via; http://localhost:9704/analytics (a new user folder should be created)
  • If necessary you could move the reports from the backup to the online Web Catalog

It’s a workaround and could be very time-consuming when you have to upgrade a Catalog with a lot of users. Hopefully this issue will be solved in a future release.

How to solve – nqsError:13041

When I was working on multiple catalogs, trying to merge them into one, I was presented with the following error:

“[nQsError 13041] The GUID of user ‘Administrator’ does not match user reference GUID at the repository. Please ask the administrator to delete the old user reference at the repository and login again”

My Oracle Support has an Doc Id; 1265802.1 on this error. The solution to this problem is in the documentation. There is one minor error in the documentation. You have to add the following line into the instanceconfig.xml-file;

<ps:UpdateAccountGUIDs>UpdateAndExit<ps:UpdateAccountGUIDs>

This should be;

<ps:UpdateAccountGUIDs>UpdateAndExit</ps:UpdateAccountGUIDs>

Check the ‘/’ in the closing ‘ps:UpdateAccountGUIDs’-tag.

The rest of the chapter works as documented.