Pass Multiple values in Row Level Security in OAC

Saroj Kumar Behera

Hi,

As per project requirement, am implementing row-level security in OAC.

I have used session variable and initalization block to implement row level security.

In initialization block - i have put default initialization string SQL - please find attached snapshot -

For Data filter - i have applied in one of LTS -

When i am logging with one user - i am getting only one value to filter. But as per SQL in initalization block - it contains multiple topdepartment values. How should i pass multiple values in row-level security.

Thanks in advance!!

Regards

Saroj

GayathriAnand-Oracle

In the init block SQL where clause, use UPPER for both left and right hand side:

WHERE UPPER(EMPLOYEE_ID) = UPPER('VALUEOF(NQ_SESSION.USER)'

This will ensure that the comparison is using upper case for employeeid column and the session variable.

Ram-Oracle

Hi @Saroj Kumar Behera ,It looks like query for init block is incorrect

Please refer section Initializing a Variable with a List of Values from below documentation

example:

SELECT 'LIST_OF_USERS', USERID FROM RW_SESSION_VARSWHERE NAME='STATUS' AND VALUE='FULL-TIME'

https://docs.oracle.com/middleware/12212/biee/BIEMG/GUID-7BA59188-C9FC-4169-B7AF-2ABFB1C9E891.htm#working_init_blocks

BalagurunathanBagavathy-Oracle

@Saroj Kumar Behera The query in the initialization block is not in the right format. Please refer

https://docs.oracle.com/en/middleware/bi/analytics-server/metadata-oas/work-initialization-blocks.html#GUID-7652D1B3-8604-4FCC-BDDD-622B73E34976

Federico Venturin

Hi @Saroj Kumar Behera ,

You have to use row-wise initialization:

https://docs.oracle.com/en/middleware/bi/analytics-server/metadata-oas/work-initialization-blocks.html#GUID-7652D1B3-8604-4FCC-BDDD-622B73E34976

It assumes that the first column in your query is a string with the name of the variable:

SELECT DISTINCT 'TOPDEPARMENT_SV', TOPDEPARMENT FROM etc.

You must also edit the data target and tick the row-wise checkbox.

Saroj Kumar Behera

Hi @Federico Venturin ,

I have used row-wise initialization. Please check snapshot -

I have used the variable in one of LTS to filter data -

Now i am getting the below issue - session variable has no value defination.

Could you please let me know what could be the issue.

BalagurunathanBagavathy-Oracle

@Saroj Kumar Behera I would suggest you to run the init block query directly against the database after substituting VALUEOF(NQ_SESSION.USER) with an appropriate value and check if it works fine. Also, make sure that the query does not return any NULL values.

Federico Venturin

Hi @Saroj Kumar Behera ,

I would replace 'VALUEOF(NQ_SESSION.USER)' with ':USER' in your query.

Saroj Kumar Behera

Hi @BalagurunathanBagavathy-Oracle,

I have substituted VALUEOF(NQ_SESSION.USER) with an appropriate value - it's coming all those list of values.

Hi @Federico Venturin,

I tried with replacing the 'VALUEOF(NQ_SESSION.USER)' with ':USER' - still the same issue is coming:

session variable has no value definition.

BalagurunathanBagavathy-Oracle

@Saroj Kumar Behera I would suggest you to log a SR so that we can analyze the log and check why the variable is not getting populated.

Federico Venturin

Hi @Saroj Kumar Behera ,

Do the EMPLOYEE_ID column and the USER session variable have the same data type? Also note that the comparison is case-sensitive.

Does your username have corresponding rows in EDQ_FINANCE_ADMIN?

If it works with a pre-defined value, it should work also with the session variable.