0

I have a single database for multiple clients. There are tables that contain the UserName and SecurityAccess for each client's UI users. These 'users' are not users with logins in the actual database. 

Select u.userName, r.roleCode, ru.AppliesToTable, ru.noAccessField
From dbo.SUserList u with(Nolock) 
Join  dbo.SRoleList r with(nolock)  on 
    u.roleCode = r.roleCode
Join  dbo.SRoleRuleList ru with(nolock)  on 
    r.roleCode = ru.roleCode 
where u.username = 'ABC123\KSmith'

The code above will return the username, their role, the database table as AppliesToTable and a comma separated list of fields they cannot access as NoAccessField

When an SSRS report is run, the username requesting the report is passed via parameter in the execution of the stored procedure that is returning the dataset. The columns will still need to be returned but without data if the field exists in the NoAccessField field for that user.

What is the best way to accomplish this? I have considered calling a function from within the stored procedure:

Select company, userid, totalAmount, accrualRate, availableDollarAmount 
Into #EmployeeAccruals 
From EmployeeAccruals_tfn(@UserID) --proposed function to limit results

In the above scenario, that user would not have access to rates. The username would be passed to the function, it would return all 5 columns but the accrualRate would be null. From there, I could then use the data returned as normal to finish creating my dataset to return to the report.

With using a function, I am not sure of the best way to structure the function. I can create a function for each database table and within the function do a case statement on each field of the select, but is that the right way? 

Select 
company, 
userid, 
case when @NoAccessField like %totalAmount% then NULL else totalAmount end as totalAmount, 
case when @NoAccessField like %accrualRate%, then NULL else accrualRate end as accrualRate, 
case when @NoAccessField like %availableDollarAmount%, then NULL else availableDollarAmount end as availableDollarAmount
From dbo.EmployeeAccruals

That seems really inefficient. I could also build the table variable in the function and then do a series of updates 

Update @EmployeeAccruals 
set accrualRate = NULL 
where @NoAccessField like %accrualRate%

How would you approach this?

Edited to add: The thought process behind the functions is that security needs to be scalable. If something needs to be changed on the security, I don't want to update 1000 stored procedures (There is a sp for each ssrs report). I'd rather change it in 20 functions +/- that will affect the stored procedures where they are utilized.

MelindaC
  • 1
  • 1
  • Too much work for one function for each table. It sounds to me a SP with dynamic SQL is a good fit to handle this situation – PeterHe Dec 20 '19 at 15:32
  • There are about 20 tables that will need this type of security. I have considered using an SP instead, but I'm not sure how that would fit into my longterm solution. The security on these twenty +/- tables will be utilitzed by more than 1000 complex stored procedures for SSRS reporting. The thought process behind the functions is that security needs to be scalable. If something needs to be changed on the security, we don't want to update 1000 stored procedures. I'd rather change it in 20 functions +/- that will affect the stored procedures they are utilized in. – MelindaC Dec 20 '19 at 16:52

0 Answers0