Row level security based on dynamic attribute

[sanjay_chaturvedi]

Suppose n number of users n1, n2, n3 .... are mapped to a role G_USR_CLIENT in teiid-security-roles.properties. Is there any way to get the information about which user is logged in  ?

Need is not only to get that information, actually requirement is to pass that information to source models to provide row level security.

Let's say n2 should read records where clientcode=n2, I am aware about row level security by using condition but for that value should be known in advance while creating VDB. here there can

be hundreads of user, so cannot create 100 roles for all user and assigning condition for their specific clientcode.

Please suggest.

Thanks.

Sanjay

[van.halbert]

You can use the system User() function:  System Functions · Teiid Documentation

[rareddy]

>Need is not only to get that information, actually requirement is to pass that information to source models to provide row level security.

How? in your query? or while making the connection to the source?

[sanjay_chaturvedi]

Thanks, Van and Ramesh for responding.

My concern is to pass that information to the query dynamically. Lets say for a model view internal definition is :

Select name, age from SourceView.ActualView where clientcode=<Here, need to pass that user's name or some other value related to user>

as Van suggested that we can have logged in user name from function. Can we have some mechanism to pass some other value like clientcode wherer clientcode will be getting from some other source view with the help of this "name" value returned by system function ?

[sanjay_chaturvedi]

Admin role -

Username : usr-dv_admin

Role name ; Admin Role

Mapped Group : G USR - ROLE - ADMIN - DAL READ ONLY

  User lying under this group can be considered as a super user having readonly access to all kind of data.

UK role

Username : usr-dv_uk

Role name ; UK Role

Mapped Group : G USR - ROLE - UK - DAL READ ONLY

  Users from this group can view data for records having atleast one column declaring country as UK.

  No access to view/tables where country is not mentioned

  For this we can have two ways :

  A separate virtual db contains models which are fetchin data only for UK user. In brief this vdb will fetch only UK specific data.

  And we can add more securioty constrains on top of that UK data.

  A Generic VDB has one of the role as UK role,

  User belongs to this role can only view Filtered rows of results from table/view based condition provided for this data-role

GCN role

Username : usr-dv_gcn

Role name ; GCN Role

Mapped Group : G USR - ROLE - GCN - DAL READ ONLY

  Users from this group also can view any country data but subject to their GCN only.

  As there would be multiple GCNs, It would not be an efficient way to create multiple roles specific to GCN.

  So this group user can view data for all GCNs and to query a specific gcn info, gcn code must need to be passed in query.

  A view in VDB will be accessible for this group's user. But to limit only client code based data, client code/gcn number would need to pass in where clause. This can be done via application like tableu to send logged in client' gcn to the query.

[rareddy]

Can we have some mechanism to pass some other value like clientcode wherer clientcode will be getting from some other source view with the help of this "name" value returned by system function ?

Yes, but this would need a custom translator. Say for example, your java JDBC client can set "setPayload" on Statement object, then this can be accessed in the translator in CommandContext object and rewrite your query how you see fit. Where as using the "user()" seems like you got that how it works.

For the next question, this is perfect for using the column masking and column filtering, see Permissions · Teiid Documentation Note for condition you can use function called "hasRole('Admin') OR hasRole('UK')". Also define your view you can use "Partitioned Union" based transformation where data is segregated by the ROLE name. Then based the "where" clause introduced by the column masking, this partition union will kick in and only specific sources that meet the criteria will be executed.