Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Architecture> Row Level Authorisation LLBLGen
 

Pages: 1
Architecture
Row Level Authorisation LLBLGen
Page:1/1 

  Print all messages in this thread  
Poster Message
TomDog
User



Location:
Wellington, New Zealand
Joined on:
25-Oct-2005 22:21:17
Posted:
570 posts
# Posted on: 04-Feb-2009 09:59:06.  
Hi I am attempting to implement Row Level Authorisation in the DAL and am wondering what peoples thoughts are on what I’m trying. Our situation is:
1.    We are doing a ASP.NET MVC web app with a single/shared login
2.    Web app runs side by side with out existing (non .NET)desktop application
3.    LINQ is preferred query method
4.    Not concerned about securing data access that is not through our code
5.    Unlikely to do batch updates
6.    Using adapter against SQL Server and Oracle
7.    About a 5th of our tables are securable
8.    We don’t want to have to remember to apply/write an authorisation filter every time we write code to access the DB

The LLBLGenPro Authorization feature is of some help and we will probably use it as a backstop but it is of no use with typed lists and LINQ projections.

I have read these forum threads but none offered up a suitable solution but they gave me some clues:
Row Level Security based on Views
apply a where clause to EVERY select by LLBL code
Filter every table by field CustomerID

The solution I’ve come up with is to override DataAccessAdapter.CreateSelectDQ and add Authorisation predicates just before the SQL is generated. Hope you can follow this long listing, it works with everything I've thrown at it so far.
DataAccessAdapter:
Code:
/// <summary>
/// Given an array of fields, the relations and the predicates in a query or sub query this adds authorisation predicates
/// (and relations if required).
/// </summary>
public delegate void RowAuthorisationFilterCreater(IEntityFieldCore[] fieldsToFetch, IRelationPredicateBucket bucket);

partial class DataAccessAdapter
{
    public RowAuthorisationFilterCreater RowAuthorisationFilterToApply;

    protected override IRetrievalQuery CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects,
                                                     IPredicateExpression filter, long maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk,
                                                     bool allowDuplicates, IGroupByCollection groupByClause, int pageNumber, int pageSize)
    {
     if (RowAuthorisationFilterToApply != null)
     {
        // put relations and predicates in a bucket, add filter and then take them back out again
        var bucket = CreateRelationPredicateBucket(filter, relationsToWalk);
        AddRowAuthorisationFilter(fieldsToFetch.GetAsEntityFieldCoreArray(), bucket);
        if (bucket.PredicateExpression.Count > 0)
        {
         InsertPersistenceInfoObjects(bucket.PredicateExpression);
         filter = bucket.PredicateExpression;
        }
        if (bucket.Relations.Count > 0)
        {
         InsertPersistenceInfoObjects(bucket.Relations);
         relationsToWalk = bucket.Relations;
        }
     }
     var query = base.CreateSelectDQ(fieldsToFetch, persistenceInfoObjects, filter, maxNumberOfItemsToReturn, sortClauses, relationsToWalk,
                                     allowDuplicates, groupByClause, pageNumber, pageSize);
     TraceHelper.WriteIf(AQDLinqHelper.ShowExecutableSQL, AQDLinqHelper.GetQuerySqlServerOutput(query), "SqlServer ready query");
     return query;
    }

    private static RelationPredicateBucket CreateRelationPredicateBucket(IPredicate filter, IRelationCollection relations)
{
     var bucket = new RelationPredicateBucket(filter); //alas this does not take relations as well
     bucket.Relations.AddRange(relations as RelationCollection);
     if (relations != null)
     {
        bucket.Relations.SelectListAlias = relations.SelectListAlias;
        bucket.Relations.ObeyWeakRelations = relations.ObeyWeakRelations;
     }
     return bucket;
    }

    /// <summary>
    /// Recursivly adds the row authorisation filter to the entire query starting with any sub queries.
    /// </summary>
    /// <param name="fieldsToFetch">The fields to fetch.</param>
    /// <param name="bucket">The bucket.</param>
    private void AddRowAuthorisationFilter(IEntityFieldCore[] fieldsToFetch, IRelationPredicateBucket bucket)
    {
     if (RowAuthorisationFilterToApply != null)
     {
        if (bucket.Relations != null)
         for (var i = 0; i < bucket.Relations.Count; i++)
            AddRowAuthorisationFilter(bucket.Relations[i]); //Recursive
        RowAuthorisationFilterToApply(fieldsToFetch, bucket);
     }
    }

    /// <summary>
    /// Adds the row authorisation filter to all derived tables in a relation.
    /// </summary>
    /// <param name="relation">The relation.</param>
    private void AddRowAuthorisationFilter(IRelation relation)
    {
     var relationAsDynamicRelation = relation as IDynamicRelation;
     var relationIsDynamicRelation = (relationAsDynamicRelation != null);
     if (relationIsDynamicRelation)
     {
        if (relationAsDynamicRelation.LeftOperandIsDerivedTable)
         AddRowAuthorisationFilter((IDerivedTableDefinition)relationAsDynamicRelation.LeftOperand);
        if (relationAsDynamicRelation.RightOperandIsDerivedTable)
         AddRowAuthorisationFilter((IDerivedTableDefinition)relationAsDynamicRelation.RightOperand);
     }
// else
// Should probably handle this as well
    }

    /// <summary>
    /// Adds the row authorisation filter to a derived table.
    /// </summary>
    /// <param name="derivedTable">The derived table.</param>
    private void AddRowAuthorisationFilter(IDerivedTableDefinition derivedTable)
    {
     var bucket = CreateRelationPredicateBucket(derivedTable.Filter, derivedTable.Relations);
     AddRowAuthorisationFilter(derivedTable.GetFieldsAsArray(), bucket); //Recursive
     if (bucket.PredicateExpression.Count > 0)
        derivedTable.Filter = bucket.PredicateExpression;
     if (bucket.Relations.Count > 0)
        derivedTable.Relations = bucket.Relations;
    }
}
And in some other part of the code:
Code:

    /// <summary>
    /// Adds the authorisation filter to the bucket. In the most complicated scenario its need to join Occurrence to OccurrenceTypeSecurity.
    /// Must set the alias on all predicates and to the StartEntity of any relationships added.
    /// </summary>
    /// <param name="bucket">The bucket.</param>
    /// <param name="alias">The alias.</param>
    private void AddAuthorisationFilter(IRelationPredicateBucket bucket, string alias)
    {
     var restrictedFalse = OccurrenceFields.Restricted == 0;
     restrictedFalse.ObjectAlias = alias;
     var restrictedNull = OccurrenceFields.Restricted == DBNull.Value;
     restrictedNull.ObjectAlias = alias;
     var restrictedFalseOrNull = restrictedFalse | restrictedNull;
     if (Convert.ToBoolean(OrganisationCustomisation.OccTypeSecurityOn))
     {
        if (OccurrenceTypeSecurity.Count(otc => otc.OccurrenceType == "ZZZ") == 0)
        {
         var OccurrenceTypeClassificationEntityUsingOccurrenceNo = OccurrenceEntity.Relations.OccurrenceTypeClassificationEntityUsingOccurrenceNo;
         OccurrenceTypeClassificationEntityUsingOccurrenceNo.SetAliases(alias, string.Empty);
         bucket.Relations.Add(OccurrenceTypeClassificationEntityUsingOccurrenceNo);
         bucket.Relations.Add(OccurrenceTypeClassificationEntity.Relations.OccurrenceTypeEntityUsingOccurrenceTypeCode);
         bucket.Relations.Add(OccurrenceTypeEntity.Relations.OccurrenceTypeSecurityEntityUsingOccurrenceType);
         bucket.PredicateExpression.Add(restrictedFalseOrNull | OccurrenceTypeSecurityFields.AllowOccRestrictedOccs != 0);
        }
        else if (OccurrenceTypeSecurity.Count(otc => !Convert.ToBoolean(otc.AllowOccRestrictedOccs)) > 0)
         bucket.PredicateExpression.Add(restrictedFalseOrNull);
     }
     else if (StaffAccessLevel == null || !Convert.ToBoolean(StaffAccessLevel.RestrictedOccs))
        bucket.PredicateExpression.Add(restrictedFalseOrNull);
    }

    /// <summary>
    /// Creates the row authorisation filter. Currently only does Occurrence Entities.
    /// Finds all Occurrence tables alias for Occurrence fields that are not from a Derived Table, usually zero or one, and
    /// add an authorisation filter to that aliased table.
    /// </summary>
    /// <param name="fieldsToFetch">The fields to fetch.</param>
    /// <param name="bucket">The bucket.</param>
    private void CreateRowAuthorisationFilter(IEntityFieldCore[] fieldsToFetch, IRelationPredicateBucket bucket)
    {
     var occurrenceAliases = (from entityField in fieldsToFetch
                             where entityField.ActualContainingObjectName == "OccurrenceEntity" && !entityField.ActAsDerivedTableField
                             select
                                 new {entityField.ObjectAlias}).Distinct();

     if (occurrenceAliases.Count() > 0)
        foreach (var occurrenceAlias in occurrenceAliases)
         AddAuthorisationFilter(bucket, string.IsNullOrEmpty(occurrenceAlias.ObjectAlias) ? bucket.SelectListAlias : occurrenceAlias.ObjectAlias);
    }
Jeremy Thomas
VS 2017 C#, LLBLGen v5.4, Winforms, WPF and ASP.NET MVCLLBL & LinqPad
 
Top
worldspawn
User



Location:
Melbourne, Australia
Joined on:
26-Aug-2006 06:56:13
Posted:
321 posts
# Posted on: 18-May-2010 08:44:32.  
Where do you set RowAuthorisationFilterToApply?

Do u do it manually before initiating a query? Have you done any work on automatically setting this based on the type of the entity being queried?


I read ur code more Laugh


--
Sam Critchley
Dotnet 3.5 - SQL Server 2005 - LLBLGen Pro 3.0
 
Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14569 posts
# Posted on: 20-May-2010 11:28:10.  
Thanks for sharing.
I might use this soon, and then I'll tell you, if something goes wrong. Regular Smiley
But from the first look the code looks fine.
  Top
neilx
User



Location:

Joined on:
02-Nov-2007 22:54:08
Posted:
267 posts
# Posted on: 06-Sep-2013 14:42:30.  
Hi TomDog - 4 years on and I am thinking of using your approach in this case for a similar requirement we have. How did it work out for you? You can email me direct on nb @ enhesa.com if you have time. Thanks - Neil

update: I assume you aren't around on the forum anymore. oh well!


  Top
TomDog
User



Location:
Wellington, New Zealand
Joined on:
25-Oct-2005 22:21:17
Posted:
570 posts
# Posted on: 19-Jul-2016 07:40:31.  
neilx wrote:
Hi TomDog - 4 years on and I am thinking of using your approach in this case for a similar requirement we have. How did it work out for you? You can email me direct on nb @ enhesa.com if you have time. Thanks - Neil

update: I assume you aren't around on the forum anymore. oh well!
Sorry, I missed your post. But I don't have anything useful to say - We didn't end using as it didn't quite meet our requirements.
Jeremy Thomas
VS 2017 C#, LLBLGen v5.4, Winforms, WPF and ASP.NET MVCLLBL & LinqPad
 
Top
Pages: 1  


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

Version: 2.1.12172008 Final.