(Using: LLBLGen 2.5 April 23rd, Oracle 10g r2 - odp.net driver, .Net framework 3.5, Adapter based templates)

Hi, We're desiging a new infrastracture for our data access security, with the main goal is to prevent access to shared data of a user which does not "own" the data (row). We're using LLBLGen for most of our data access, and most of it using entities (currently mapped to tables).

I'll demonstrate what we're trying to achiveve by example:

We have an "Orders" table containing: OrderID (PK) Amount ShopID (FK Shops)

We want to prevent on the lowest possible level (i.e. the database) access from a user of ShopID=X to rows with ShopID=Y.

Now, to make the transition as transparent as possible, we thought of "wrapping" each table with a view, that will filter according to the current session context (we already have code that sets the sessions's ShopID when a connection is retrieved from the connection pool): [pseudo sql code] CREATE VIEW Orders_View AS SELECT * FROM Orders WHERE ShopID = GetCurrentContextShopID()

We then create a user with access only to the wrapping views, and replace all direct tables access with views access.

The problem is, when we use entities mapped on views to replace those mapped on tables, we lost a very important part: All the Primary keys are missing (affecting the generated code entity constructors for example), and all the Foreign keys are missing (causing all the relations, sub collections, etc to be missing from generated code).

I can think of a few solutions, but i can see serious disadvantages to them: 1) Creating customs Relations, primary keys as , by hand - tedious work, hard to maintain, misses lots of the generator abilities. Also, i'm not sure i can set a custom PK.

2) Changing the actual mapped object on the generated code, to use the views instead of the tables. for example, changing

base.AddElementMapping( "OrderEntity", "MyUser", @"MySchema", "Orders", 14 );

to

base.AddElementMapping( "OrderEntity", "MyUser", @"MySchema", "Orders_View", 14 );

This can cause some runtime errors, some hard to maintain code, breaking compatability and "it doesn't feel right" effect.

It seems oracle 10g supports creating FK, PK and Unique constraints on views, but LLBLGen doesn't seem to use them.

Does anyone have any ideas as to a possible solution?

By the way, we're also looking the oracle's RLS (row level security) to achieve this data security, anyone had any expirience with it?

Thanks! Amitay

Hi Amitay,

IMHO, I wouldn't use neither 1) or 2) because of the disadvantages you mentioned.

I would use (at Data Access Layer) the LLBLGenPro Authorization feature:

/// <summary>
/// Class which is used to authorize AdventureWorks entity access. 
/// </summary>
/// <remarks>Used as a singleton.</remarks>
[DependencyInjectionInfo(typeof(OrderEntity), "AuthorizerToUse", 
    ContextType=DependencyInjectionContextType.Singleton)]
public class MyOrderFetcherAuthorizer : AuthorizerBase
{
    /// <summary>
    /// Gets the result hint what to do when authorization fails when fetch a new entity.
    /// </summary>
    /// <returns>
    /// any of the FetchNewAuthorizationFailureResultHint values
    /// </returns>
    public override FetchNewAuthorizationFailureResultHint 
        GetFetchNewAuthorizationFailureResultHint()
    {
        // default is throw away. Here you could (if you want) return "ClearData" (that would gives you empty entities instead).
        return FetchNewAuthorizationFailureResultHint.ThrowAway; 
    }

    /// <summary>
    /// Determines whether the caller is allowed to load the data into the entity instance specified.
    /// </summary>
    /// <param name="entity">The entity instance to fill with data</param>
    /// <returns>true if the caller is allowed to load the data in the entity specified.</returns>
    /// <remarks>Data inside the entity is the data fetched from the db. If the method 
    /// returns false, the entity will be thrown away from the collection</remarks>
    public override bool CanLoadEntity(IEntityCore entity)
    {
        bool toReturn = ((EntityType)entity.LLBLGenProEntityTypeValue == EntityType.OrderEntity);
        OrderEntity orderToAuthorize = (OrderEntity) entity;

        int currentShopId = (int) SomeContextSessionHelper.GetCurrentContextShopID();
        if(orderToAuthorize.ShopId != currentShopId)
        {
            // deny
            toReturn = false;
        }
        return toReturn;
    }
}

Above is for securing the access at DAL, now for GUI/BL I would make methods that fetch orders, that methods will check for GetCurrentContextShopID and make the filter (PredicateExpression).

I don't see the need of views, maybe I'm missing something about your scenario.

Hi David, Thanks for your input. While i like the the idea of the authorizers, I think it's better to enforce this kind of security for several reasons:

1) Future applications may require access to the data. They are not guarenteed to use LLBLGen. 2) Current application might expose access to the database in other means. 3) Authorizers won't support dynamic and typed lists. 4) Authorizers won't prevent batch updates on other shops data. 5) Authorizers trigger after the data was fetched, which might be too expansive, and harder to determine whether [entity X belongs to Shop Y]. 6) Providing security in the Data Layer (i.e. the RDBMS) rather than the the Data Access Layer (LLBLGen) is safer.

Does anyone have any ideas for a solution for my question: How do i change mapping from a current table to a view containing the same fields, without using Primary Keys, Unique Key, Relations, etc? Can anyone think of an elegant way to change just the mapping? (Plugin, A different template, any direction?)

Thanks, Amitay

I'm not sure I understand your question completely, and I use Sql Server.

In the designer map the view as an entity. Edit the entity in the designer, set the fields that are actually primary keys to indicate that the are primary keys.

While this is possible, what we really want to achieve is Entities based on views which are exact projection of the underlying table, with some additional where clause restrictions. Since we already have a large codebase using entities based on existing tables, which will be replaced with views with the exact structure, we'd want to maintain all the existing relations and keys, and just change the underlying mapped database object.

Otherwise, it's a lot of work just recreating the relations, and we lose LLBLGen ability to keep track of changes in the schema.

The place i find to address this problem is the DBSpecific's PersistenceInfoProviderSingleton class. However, it looks sealed and hard (impossible) to extend in my code, so i see an option to generate it differently using the views.

BTW: thanks for showing me where to define a custom PK. does the same functionality present for custom Unique keys?

does the same functionality present for custom Unique keys?

I don't think so.

If you build a dummy schema with tables where you want to have views, then at run time point to the real database with the views, I think it would work.