Frans,

In an earlier thread you mentioned that 2.0 will have support for using store procedures for entity persistence. Can you elaborate on this a bit? Will there be generated sprocs/can we point at custom ones? Will they be used for both fetching and saving data? Thanks

I won't generate procs, as that would be backwards: people who want to use procs for persistence do that for a list of reasons and the top one is often 'security'. While I don't necessarily agree with that, it is a reason for them so I respect that. Though security is mitigated if the procs are generated as well.

So custom procs it is. The design on this has to be finalized though, as I first develop the high priority features. Most likely the procs are selectable in the designer and therefore it's not a lot of work. But I haven't finalized the design yet, so I can't give more details now.

Well, I can tell you that we use procs for security. We implement column and row level security so that certain users and/or roles can only view a subset of data.

We could have implemented our security schemes by fetching what LLBLGEN wants to fetch and then going through eliminating rows and nulling out columns that the user should not be able to see, but it was decided that for performance reasons, we should do this in the data fetching stored procedures.

We also could have attempted to implement row level security by using predicates and relations, but this has a whole host of problems. One of which is that for each table, there is a stored procedure that returns a list of primary keys that a given user can see. We could have called this stored procedure, and used a fieldcomparerangepredicate to filter on only these rows, but the list of primary keys could be so enormous (100,000 is not out of the question), that the predicate generated by this fieldcomparerangepredicate would be huge!

We have implemented this now by basically hijacking your select DQ and turning it into a stored procedure call, but there are many problems that keep occuring. One, as you would expect, is that the columns returned by the stored procedure aren't always what LLBLGEN expects. This is typically caused by errors in the column metadata we keep in a seperate table in our database (and which our data access stored procedure uses to generate a SELECT field list with).

Another problem is that, in order to allow some capability to filter entity collections, we strip off the WHERE clause in the DQ and send it in as a parameter into our data-access stored procedure. You can imagine the problems this can cause as enables many possible inference attacks (we allow the client machine to construct relationpredicate buckets and send them to the server for processing).

So, those are our issues. I thought this information may be useful to you.

mikeg22 wrote:

Well, I can tell you that we use procs for security. We implement column and row level security so that certain users and/or roles can only view a subset of data.

Ok, that's a start.

We could have implemented our security schemes by fetching what LLBLGEN wants to fetch and then going through eliminating rows and nulling out columns that the user should not be able to see, but it was decided that for performance reasons, we should do this in the data fetching stored procedures.

We also could have attempted to implement row level security by using predicates and relations, but this has a whole host of problems. One of which is that for each table, there is a stored procedure that returns a list of primary keys that a given user can see. We could have called this stored procedure, and used a fieldcomparerangepredicate to filter on only these rows, but the list of primary keys could be so enormous (100,000 is not out of the question), that the predicate generated by this fieldcomparerangepredicate would be huge!

That would not work indeed.

The question now is of course, how does the proc get the pk values for user X, as that filter can be applied to the dyn. sql as well.

The real problem always is with the security question is: if I login as user X I see a given set of procedures. If there's a pr_deleteCustomer proc, with a parameter @customerID, I can specify whatever I want and it's gone. UNLESS you have user-based security code INSIDE the proc, like you have done: custom code checking the user credentials again.

In general this isn't done in procedures, so unless a system like the one you're using is in place, procs aren't more secure. To offer users who have such a system in place or who have to use procs because the DBA thinks it's required / company policy says it has to be done, support for procs is added, though it wont be as flexible as with the dyn. sql of course, as programming with the procs will be pretty arcane: if you need a new filter, the proc has to be written first etc. etc. But for some scenario's it's perhaps worth the effort, though not in every app it is required (most apps)

We have implemented this now by basically hijacking your select DQ and turning it into a stored procedure call, but there are many problems that keep occuring. One, as you would expect, is that the columns returned by the stored procedure aren't always what LLBLGEN expects. This is typically caused by errors in the column metadata we keep in a seperate table in our database (and which our data access stored procedure uses to generate a SELECT field list with).

The code works like this: (1.0.2005.1) it uses a set of fields to produce a query, and those fields are the select list. The fields are thus in that order in the select list. This is always the case. First getting the ordinal, then getting the value is extremely slow compared to this method. All DataAdapters in ado.net do the same thing: they simply grab all values of a row and dump it in a datarow object. If you've setup your columns differently in the datatable, you've a problem.

So I don't think I can solve that in all situations.

Another problem is that, in order to allow some capability to filter entity collections, we strip off the WHERE clause in the DQ and send it in as a parameter into our data-access stored procedure. You can imagine the problems this can cause as enables many possible inference attacks (we allow the client machine to construct relationpredicate buckets and send them to the server for processing).

That's indeed a problem, though that's a problem related to a fixed API of procs: what you can filter is what's offered by the procs. V2 will offer very flexible in-memory filtering techniques with normal predicate objects, but it's perhaps not enough for all situations, however it can help in a lot of situations.

Thanks for your feedback, I'll take this into account when finalizing the design of the proc support