@variable('BOUSER')

building semantic layer
1

Hi All,

I have this piece of code in one of my report query,

dash_top_n_by_team.team_id = dim_team_security.team_id and dim_team_security.user_code = @variable(‘BOUSER’)

What role the @variable(‘BOUSER’) plays here?
Can some please provide me some information on this. (I know this is row level security)

Thanks…

JohnJustus :us: (BOB member since 2007-06-25)

2

That takes the user id of whoever is logged in, makes it available in your SQL call. I’m doing this now to limit a user to geography by searching a security table, based on BOUSER. Same as CurrentUser(), but I don’t think that would be available to the database engine.

You can also use BOUSER in the connection to login to your database as a specific user, limit data at the database level (which only works if your user is authenticated to the db).

Good luck,
B.

bdouglas :switzerland: (BOB member since 2002-08-29)

3

Thanks, thats a valuable information,
Then, what is the “Manage Access Restrictions” option in the universe? (It has Available Restrictions on the left and Available Groups and Users on the Right)

Thanks…

JohnJustus :us: (BOB member since 2007-06-25)

4

That’s a big topic - you can use it to create a restriction based on SQL you create, for one thing. This lets you create a rule and apply it to a group, but it also will let you create a custom connection, swap out a table for certain users, and more.

I set up a rule to apply a filter based on country code in one, applied that to users I wanted to limit by their IDs - this is a medium easy way to do row restrictions.

Good luck,
B

bdouglas :switzerland: (BOB member since 2002-08-29)

5

Thanks for the information.
So either you can restrict by @variable(‘BOUSER’) or by Manage Access Restrictions in the universe, correct?
Thanks…

JohnJustus :us: (BOB member since 2007-06-25)

6

it’s more like you can use them in conjunction - plug in SQL to limit a table, apply that to a table, apply the restriction to users:

Resort.country_id in  ( select country_id from  sales_person_geog_lookup where BO_USER= @variable('BOUSER')  )

That’s a sample I created on Island Resorts (resort table), using another table to store user / country information (lookup). Restriction applied to “country” users.

Gotta run, good luck - B

bdouglas :switzerland: (BOB member since 2002-08-29)

7

Is it possible to use both of them together?

For e.g. We have the following condition…
Employee.Employee_Name = @variable(‘BOUSER’)

Can I use the above condition under Manage Access Restriction -> Row Level Restriction and apply it on a group.

For e.g. The requirement is to apply the restriction on some users of the Sales team whose name does not reflect under Employee.Employee_Name.

bharatahuja :india: (BOB member since 2007-10-02)

8

Yes you can apply.

Rakesh_K :india: (BOB member since 2007-12-11)

9

Hi,

@variable(‘BOUSER’) is used to bring the currently logged user name from the repository.

Regards,
subramanyam

Subbu557 (BOB member since 2008-03-09)

10

you can use for row level restriction also. If you have security tables in your database. like Peoplesoft.

Rakesh_K :india: (BOB member since 2007-12-11)