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:

/// <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:

    /// <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);
    }

~~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

Thanks for sharing. I might use this soon, and then I'll tell you, if something goes wrong. But from the first look the code looks fine.

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!

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.