Conditional logic for accessing stored procedures

Question

Conditional logic for accessing stored procedures

rolandpater

Old Hand

Points: 318
More actions
September 21, 2018 at 1:15 am

#408482

Hi,
I'm working on a database driven (web) application where I rely heavily on stored procedures and contained users.
I want to give users access to stored procedures through roles. I give a stored procedure a database role and I make a user member of that role. The user can now execute the stored procedure.
So far this works.
But now I want to have a more fine-grained control over what users can do. Is it possible to have conditional logic somehow?
For example, I have 6 stored procedure:
sp_module_a
sp_module_b
sp_module_c
sp_module_a_admin
sp_module_b_admin
sp_module_c_admin

- if a customer has bought module a and c (not b) all users can access sp_module_a and sp_module_c
- if a customer also has bought the admin module, and the user is member of admin, he has access to sp_module_a, sp_module_c, sp_module_a_admin and sp_module_c_admin
Extra info: In a former version of our application I've made a view (with the conditional logic) which supplied all the roles to asp.net. Now I want to integrate as much as possible in the database.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99133 More actions · Answer 1

Permissions can't be dynamic, they're static. it sounds like you're saying the products/permissions of the users can be fluid, so sounds more like you want logic within the S. You could check a permissions table and if they user doesn't have the permissions then raise an error.

CREATE PROC SomeProc1 AS
  
  IF EXISTS(SELECT 1 FROM dbo.UserRoles WHERE UserName = CURRENT_USER) BEGIN
    SELECT 1; --Do your SP Stuff
   END ELSE BEGIN
    RAISERROR(N'You do not have permission to run this procedure.',11,-1);
   END
GO

Effectively what you're looking at here is using application style permission management. You might, also, want to just completely handle the permissions in the application, if they're all being run from there.

It's also worth noting that this isn't going to stop a sysadmin from just changing their permissions; or even altering the SP.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

rolandpater Old Hand Points: 318 More actions · Answer 2

Thom A - Friday, September 21, 2018 3:29 AM
Effectively what you're looking at here is using application style permission management. You might, also, want to just completely handle the permissions in the application, if they're all being run from there.
It's also worth noting that this isn't going to stop a sysadmin from just changing their permissions; or even altering the SP.

Thanks for your solution. In the previous application all was indeed handled by the application. But we ended up with a controller with 300 procedures each mapping to a unique stored procedure. So 300 mappings to 300 stored procedure. I decided I could also create 300 stored procedures and add to each stored procedure extended properies called 'url' and 'method'. Now whenever a request comes from the client, I find the stored procedure with the right url and method (get/post), and call that stored procedure. With some magic I map the request parameters to the stored procedure parameters. The stored procedure is als called using 'execute as user = [username]' so the permissions are handled that way.

The downside of handling the permissions programmatically, is that it isn't possible to know the permissions on forehand (except maybe when using FMTONLY, but I would not go that far). If I know the permissions on forehand I can choose whether or not to display a link that results in accessing the stored procedure.

rolandpater Old Hand Points: 318 More actions · Answer 3

I think I'm going to investigate if the one-role-per-storedprocedure is good enough. In the end, it has the most fine-grained control. I can always create scripts that sets roles for all users, if needed.