hi
We have a requirement to implement Query Subject Data Security, For example we have a table USER as shown below, if lets say user_id = 1 is logged in, he should only see the row his id belongs too, as his role = tester, but if the logged user has role manager, should be able to see all the data.
USER_ID NAME ROLE
1 abc tester
2 xyz manager
3 aaa tester
Any thoughts or suggestion, would be great help to us!!
thanks
Mohnish
What data needs to be secured? Is it only this user table that you've identified or is there fact data perhaps that needs to be secured?
To simply secure the USER table, put a filter on it in the business layer something like below, assuming that the name in that table matches the name on the account the user logs into.
lower( [Your Namespace].[USER].[NAME] ) = lower ( #sq($account.personalInfo.userName)# )
and
lower ( [Your Namespace].[USER].[ROLE] ) <> 'manager'
More typically it is another table that needs to be secured based on data stored in a user table such as yours. If that is the situation you face then it would help for you to describe more such as what the table or tables are, how they relate and how the data within might be restricted for user 1 vs. user 3, for example.
It would be unusual to see a fact table with user ID information as the basis for security because users come and go, and often there are multiple users allowed to see the same information. A dimension is often the place where security comes into play such as only certain users may see information about particular products, or stores, or regions, or whatever. If that is the case then your user table would need to have multiple rows per user with each row identifying a product (or store or region or whatever) that the user is permitted to see.
There are a variety of ways to implement security so you may get more suggestions if you explain the entire scenario that you are facing.
hi Lynn,
thanks for the reply,
The below filter will not pull any data if the role is manager.
lower( [Your Namespace].[USER].[NAME] ) = lower ( #sq($account.personalInfo.userName)# )
and
lower ( [Your Namespace].[USER].[ROLE] ) <> 'manager'
Quote from: mohnish45 on 23 Jun 2016 11:00:38 AM
hi Lynn,
thanks for the reply,
The below filter will not pull any data if the role is manager.
lower( [Your Namespace].[USER].[NAME] ) = lower ( #sq($account.personalInfo.userName)# )
and
lower ( [Your Namespace].[USER].[ROLE] ) <> 'manager'
Hi,
If you look at the expression, you will see it says the role is
not equal to manager. This means the expression will omit rows for anyone whose role is manager. If you want to include rows for anyone whose role is manager, change the expression to = instead of <>
Lynn gave you a concept assuming you would look at it, figure out what it's doing and adapt it to suit your requirements. It isn't a "blindly copy this and all your problems will be solved" situation. You need to take some time to understand what the logic is and to apply the same kinds of logic to your model.
Cheers!
MF.
Quote from: mohnish45 on 23 Jun 2016 11:00:38 AM
hi Lynn,
thanks for the reply,
The below filter will not pull any data if the role is manager.
lower( [Your Namespace].[USER].[NAME] ) = lower ( #sq($account.personalInfo.userName)# )
and
lower ( [Your Namespace].[USER].[ROLE] ) <> 'manager'
Yes, I am off the mark here with the logic but surely you can adapt as needed. You also didn't respond about any of the other points I raised or questions I asked so it isn't clear that any alternative I could offer would suit your situation anyway.
Thanks for the suggestion, but i am looking for logic, something like this..
table USER
USER_ID NAME ROLE
1 abc tester
2 xyz manager
3 aaa tester
if (user_id = 2 and role = 'manager') then select * from USER ) else if (user_id = 1 and role = 'tester' then select * from USER where user_id = 1
looking for something like this in framework.