Home
Help
Register
Log in

Search

 
   Active Threads  

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

Pages: 1
LLBLGen Pro Runtime Framework
Stored Procedure Permissions
Page:1/1 

  Print all messages in this thread  
Poster Message
Ian
User



Location:
Hertfordshire, UK
Joined on:
01-Apr-2005 16:37:36
Posted:
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
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
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
 
Top
Pages: 1  


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

Version: 2.1.12172008 Final.