+5For our project there are various roles created like :
employee, employee_manager, hr_employee, hr_manager, admin
We have a functionality where the employee can use his privacy setting to hide his personal data from other employees. Currently the time sheet of one employee is hidden from other employee using prefilter with dbo.tsf_usr() functionality.
My question is, if it is a manager role then he should be able to see his team’s time sheet.
How can this be achieved?
Best answer by Mark Jongeling
Hi Rucha,
You could create a Function of your own that uses the dbo.tsf_user() function but incorporates the business logic that the end product requires. It depends a bit on how you determine to show data to users.
For example, a dbo.app_user() Table function with some code like this. It will return a list of all users that one users may see data for:
return (
select u.user_id
from user_table u
where u.user_id = dbo.tsf_user()
or u.manager_id = dbo.tsf_user()
--and so on
)Now the function will return a list of users with which you can use for any purpose to determine which users’ data is visible to the logged-in person (tsf_user).
Prefilter example:
user_id in (select user_id
from dbo.app_user())Hope this gives a good idea 😄
+23Hi Rucha,
You could create a Function of your own that uses the dbo.tsf_user() function but incorporates the business logic that the end product requires. It depends a bit on how you determine to show data to users.
For example, a dbo.app_user() Table function with some code like this. It will return a list of all users that one users may see data for:
return (
select u.user_id
from user_table u
where u.user_id = dbo.tsf_user()
or u.manager_id = dbo.tsf_user()
--and so on
)Now the function will return a list of users with which you can use for any purpose to determine which users’ data is visible to the logged-in person (tsf_user).
Prefilter example:
user_id in (select user_id
from dbo.app_user())Hope this gives a good idea 😄
+5Thanks Mark for the quick reply. :)
This helps!! I will try and incorporate it and get back. I also saw below from community. So wanted to check if this can be used too?
exists ( select 1 from [IAM_database].[dbo].[usr_role] where usr_id = dbo.tsf_user() and role_id = [your_role] )
from the link :
+23That may be an option although it being not so bulletproof as the IAM name has to be specified inside the prefilter. May the IAM database name change, the query will fail.
In case you have a User table inside your application, it's more easy to query that one to determine the current user (using tsf_user) and obtaining which users should see data from other users and such.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.