Stored Procedure Permissions

Posts   
 
    
Ian avatar
Ian
User
Posts: 511
Joined: 01-Apr-2005
# Posted on: 19-Jun-2013 15:42:42   

I was working with someone who prefers stored procedures over an ORM. He made the point that you can set individual user permissions on a stored procedure thus locking down a specific query at the database level.

Is it possible to do something like this with LLBLGen? simple_smile

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 19-Jun-2013 17:12:20   

You mean, calling a stored proc under a given user? Yes, in adapter, specify a connection string with a given user, and pass that adapter to the method to call the proc.

With a dyn. sql query this isn't possible: the tables accessed by the query running under user X have to be accessible (select right) by user X. You can use roles to limit this though.

The proc P can access a lot of tables but the user executing P only has to have access to P, not to the tables.

This is at first nice and 'more secure'. However it's a bit misleading. The proc api allows me to execute all actions by simply passing input, I can still access the data through the proc api. I just have to sniff connection strings over the network or in the app code and I can do what the api offers me.

Therefore if you want to secure a proc API, always make sure the caller has to pass a security token, only known at runtime to the proc, to verify the caller is indeed allowed to do what is supposed to be done.

So dyn. sql queries do indeed require table-wide permissions. To mitigate this: - use views, accessible by specific users so the tables don't have to have access rights - use different users for CUD and selects - use table valued functions (which offer the security of procs, but you can use them in an ORM)

Frans Bouma | Lead developer LLBLGen Pro