row level security on multiple fields with * for all

[Former Member]

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.

[Former Member]

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

[former_member285534]

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