Skip to Content
avatar image
Former Member

row level security on multiple fields with * for all

Hi,

we want to implement row level security in a universe to accomodate following:

* users will only be able to see some entities

* users will see only one group of accounts or all

The first thing is easy to overcome and is explained in all kind of fora: you create a table like below

Username Entitycode
JVDD

BRUZT

JVDD CPHZT BPO BRUZT BPO MANZQ PBK BRUZT PBK MANZQ PBK CPHZT FSC

CPHZT

Now as a second level of security people are not allowed to see all Financial accounts in their reports.

We thought on solving this as well through the above security table by adding in a second column.  There are however 15 groups of accounts that should be secured.

For people only having the need to access a single account group (e.g. Revenue = accounts starting with 3), we think that it is still manageable.

Username Entitycode Account BPO BRUZT 3 BPO MANZQ 3 FSC

CPHZT

1

For people that should see all accounts we think that the table will become much to complicated (due to the number of account groups and the number of entities (>300)) and we would like to replace 'ALL Accounts' with a *

to have a table like this:

Username Entitycode
Account JVDD BRUZT * JVDD CPHZT * BPO BRUZT 3 BPO MANZQ 3 PBK CPHZT * PBK MANZQ * PBK BRUZT * FSC CPHZT

1

The question now is how we can link the * to all accounts in the join syntax, so that user JVDD can see all accounts without any limitation.

Are there other solutions that you see?  Any help is appreciated.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Sep 28, 2015 at 12:25 AM

    Hi,

    Suppose your account group universe is limited to the following set: { 1, 2, 3, 4, 5 }

    A solution would be to take advantage of the SQL's IN operator in your universe's join condition(s), so that BOBJ generates JOIN or WHERE clauses like:

    -- SELECT ... FROM ...

    ... WHERE table.account_group IN ( account )

    Here "account" is the 3rd column of your account security table.

    If you change the asterisk for a comma separated list of all your possible accounts you will achieve the desired result for people allowed to see all the financial accounts:

    Username Entitycode
    Account JVDD BRUZT 1,2,3,4,5 JVDD CPHZT 1,2,3,4,5 BPO BRUZT 3 BPO MANZQ 3 PBK CPHZT 1,2,3,4,5 PBK MANZQ 1,2,3,4,5 PBK BRUZT 1,2,3,4,5 FSC CPHZT

    1

    For example if user is BPO, built SQL clause will look like

    -- (something)

    ... WHERE account IN ( 3 )

    If user is JVDD built SQL clause will be

    -- (something)

    ... WHERE account IN ( 1,2,3,4,5 )


    This would also be a straight-forward way to allow two, three or n account groups to be assigned to each user.


    Remember your join conditions will be complex joins.

    I hope this approach leads you to the solution

    Best regards,

    Fernando

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 10, 2015 at 11:50 AM

    Hi,

    You have to define a Case condition in your universe between actual table which contains data and a table which define security.

    Lets say there are two tables

    1) Actual table :

    It has columns like username & entity code

    2) Security table :

    It has columns like Username & Accounts

    Like you said for the users having access to only 1 group of account define numbers like 1,2,3...

    & for users having access to all accounts apply some number like 9999 (Which will never occurs)

    Now define join between both the tables on universe like

    Case when Security_table.accounts = 9999 then 1 else Security_table.username end =

    case when Security_table.accounts = 9999 then 1 else Actual_table.username

    I think this join condition will work for you in all the scenarios.

    Please apply this solution and share your feedback.

    Thanks,

    Swapnil

    Add comment
    10|10000 characters needed characters exceeded