Log in


   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Stored Procedure Permissions

Pages: 1
LLBLGen Pro Runtime Framework
Stored Procedure Permissions

  Print all messages in this thread  
Poster Message

Hertfordshire, UK
Joined on:
01-Apr-2005 16:37:36
511 posts
# 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? Regular Smiley
LLBLGen Pro Team

The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
37870 posts
# 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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
Pages: 1  

Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.