Our project requires the business framework to enforce security per user not only by row (which can be handled using predicates/relations) but also by column. For example, a user may want a collection of employee entities, but should only be able to view the salaries of certain employees (under them in the management chain for example). Salaries that this user shouldn't be able to view get a "fake" salary, which is a calculated field. There is also the case that certain users should not be able to view any salaries.

Our old system of dealing with this situation was by dynamically building a SQL statement inside a stored procedure based on lookups into some security tables that we maintain. I really like how LLBLGEN can deal with row level security (predicates/relations) but how does it deal with what I'm talking about? Or, how can we modify it to deal with what I'm talking about? Would the column level security have to be enforced at the application layer?

I definitely would do it on the layer which offers interaction with the data. The reason is that if you pass on an entity to another user, which DOES have the rights to see some fields (or vice versa: you pass an entity to a user which doesn't have the rights!) you miss data or have too much data.

The application itself is the interface to the data, so the user itself can't reach the data. You can of course argue that by attaching a debugger the data potentially can be viewed.

I don't know how you enforced user checking in the procedures, though if that was based on the userid passed in that could be faked as well.

there are a couple of other options: 1) On the entity level, you can do it by deriving a class from each entity and in there overriding each property. In the getter, perform a user check, if the user shouldn't see hte property value -> return a fake value, otherwise return the base' value. There are templates for generating derived entities in adapter, so it should be fairly painless. though there is another option which IMHO is easier and has 100% failproof (as option 1) leaves open the Fields[index].CurrentValue/DbValue ) 2) Derive a class from DataAccessAdapter and override OnFetchEntityComplete and OnFetchEntityCollectionComplete. In there, you get the fetched fields object (OnFetchEntityComplete) or the fetched entitycollection (OnFetchEntityCollectionComplete). In there, you can pass it to your security object which checks user and entity and replaces fields not accessable for the user with fake values. These two methods are always called after a fetch (single entity fetches call OnFetchEntityComplete, collection fetches call OnFetchEntityCollection complete), also in prefetch paths, so it should be safe.

the advantage is that you can just plug into your DataAccessAdapter derived class instance the user data and it's somewhat transparent. To detect which entity the fields belong to, check fields[0].ContainingObjectName, which is the name of the entity the field belongs to, like 'CustomerEntity'.

the disadvantage is of course like I said in the beginning, you can't pass on an entity to another user.

Otis wrote:

I don't know how you enforced user checking in the procedures, though if that was based on the userid passed in that could be faked as well.

On our db server, we exposed a server dll via COM+ to our application server (which was also the UI server). This server dll had a way to know the username of who was calling it from the UI/app server, this username would be the username of the person running the app. This username would be passed into the stored procedures.

there are a couple of other options: 1) On the entity level, you can do it by deriving a class from each entity and in there overriding each property. In the getter, perform a user check, if the user shouldn't see hte property value -> return a fake value, otherwise return the base' value.

This was the best idea I could come up with...but its no fun having to implement these checks in every property of every derived class.

2) Derive a class from DataAccessAdapter and override OnFetchEntityComplete and OnFetchEntityCollectionComplete. In there, you get the fetched fields object (OnFetchEntityComplete) or the fetched entitycollection (OnFetchEntityCollectionComplete). In there, you can pass it to your security object which checks user and entity and replaces fields not accessable for the user with fake values. These two methods are always called after a fetch (single entity fetches call OnFetchEntityComplete, collection fetches call OnFetchEntityCollection complete), also in prefetch paths, so it should be safe. the advantage is that you can just plug into your DataAccessAdapter derived class instance the user data and it's somewhat transparent. To detect which entity the fields belong to, check fields[0].ContainingObjectName, which is the name of the entity the field belongs to, like 'CustomerEntity'.

I really like this idea. This was actually the first idea our team came up with to solve the problem, but we were afraid to override the OnFetch routines for fear that updates to LLBLGEN may use different methods of retrieving the data. Do you think it is safe to assume you will not be changing this behavior in the DataAccessAdapterBase class?

mikeg22 wrote:

2) Derive a class from DataAccessAdapter and override OnFetchEntityComplete and OnFetchEntityCollectionComplete. In there, you get the fetched fields object (OnFetchEntityComplete) or the fetched entitycollection (OnFetchEntityCollectionComplete). In there, you can pass it to your security object which checks user and entity and replaces fields not accessable for the user with fake values. These two methods are always called after a fetch (single entity fetches call OnFetchEntityComplete, collection fetches call OnFetchEntityCollection complete), also in prefetch paths, so it should be safe. the advantage is that you can just plug into your DataAccessAdapter derived class instance the user data and it's somewhat transparent. To detect which entity the fields belong to, check fields[0].ContainingObjectName, which is the name of the entity the field belongs to, like 'CustomerEntity'.

I really like this idea. This was actually the first idea our team came up with to solve the problem, but we were afraid to override the OnFetch routines for fear that updates to LLBLGEN may use different methods of retrieving the data. Do you think it is safe to assume you will not be changing this behavior in the DataAccessAdapterBase class?

OnFetch* methods are precisely meant for this purpose, so they are there to be overriden by your code to perform actions So these methods won't go away or be changed, they're there to hook up code to be called when some action takes place.