A Fairly Capable Authorization Sub-System with Row-Level Security Capabilities (AFCAS)

• Download source (.NET v2.0 Visual Studio 2008) - 168.45 KB

• The Fundamentals
• Row Level Security Implementation
• Maintenance of Access Predicates and Resource Hierarchies
• The Application Interface
• Administration
• Implementation
• Active Directory (AD) Synchronization and Administration
• ASP.NET Membership and Role Providers
• Emulating Multiple Labels for Common Scenarios
• Performance and Scalability
• Final Words

In my previous article A Model to Represent Directed Acyclic Graphs (DAG) on SQL Databases, I have described a way of representing hierarchies, and DAGs in general, in SQL databases and an efficient way to access it.

In this article, I'll move further on to make use of the technique to tackle the authorization and row based access control problem. This is an important target to conquer because a good solution to the problem would, potentially, be reusable in many future projects as most serious enterprise applications need such a sub-system one way or another.

Here are the design goals that I set, at the beginning, for such a sub-system, which I called AFCAS:

1. The sub-system should be a general purpose component so that it is re-usable without significant modifications. This implies also that it should be able to stand alone, with no dependency to the objects that it is supposed to secure access to.
2. The sub-system should support MS Windows like Access Control Lists (ACL), which will then allow definition of operations like Add, Delete, Update, View, etc for each of resources such as rows in database tables. This is actually strongly related to goal #1 as such a system would be broadly applicable.
3. The sub-system should have negligible effect on the design of the rest of the system.
4. The sub-system must support row-level secured access to database tables with a reasonable performance hit i.e. the number of records on the secured tables must not have a major performance impact, even on tables with multi-million rows.
5. The sub-system should enable a transparent solution for reporting and analysis tools. More specifically, the sub-system must enable automatic authorization filtering using database views so that our reporting tools are transparently row-level security enabled.

   On top of these higher level goals, I set the following more specific requirements:

6. The sub-system should allow use of existing Active Directory Groups as the security principals. I.e. it should be able to use MS Windows identity of the users and also must support groups that have other groups as members. That feature is essential to support both enterprise level roles and application roles that have enterprise roles as members; and full support for nested Active Directory groups, in general.
7. The sub-system should support hierarchies for both Operations and Resources. This would make the component usable in more scenarios.
8. The sub-system should provide good support for off-line scenarios.

   The following are considered unnecessary or too costly to implement:

9. Capability to express arbitrary authorization rules: That would mean allowing a scripting language or some kind of authorization rule plug-in architecture. Both of which adds major complexity without a lot gains. Besides, it is usually quite difficult to classify such rules as authorization aspects; they usually are core business aspects and can best be implemented as part of the business layer.[1] Considerable performance hit it may incur in the case of row based access was another reason for omission of this capability.
10. Capability to secure individual attributes of a given row: on the grounds that this is not a very common requirement and would make the design considerably more complicated. Furthermore, it is probably a better database design to factor-out such columns into their own dedicated tables.
11. An authentication component to complement authorization: Because authentication is considered best handled on the OS and/or development platform level and there are built-in support in all major platforms.
12. A labelling schema that would allow associating multiple authorization markers with the resources: Because such a system would require a label evaluation procedure on the where clause of select statements and hence create a major performance hit for large datasets (due to table scans instead of index look-ups). Furthermore, resource hierarchies can be used to remove the need for such a schema. See the section "Emulating Multiple Labels" for an example.

Goal #6 cannot be easily achieved without writing extended stored procedures in SQL Server that would return groups of a given user and vice versa. Even if we write such extended stored procedures, it would then lock the component to SQL Server, and more importantly, would create a significant performance hit (because the SQL engine may not be able to use indices and falls back to table scans if such stored procedures are used in WHERE clauses). So, I decided to use an agent to regularly replicate the Active Directory users and groups to the SQL database. This has also the nice side effect of allowing seamless mixing of Active Directory users and groups with application defined users and groups, which is a common scenario for public facing web applications: Use of Active Directory and integrated windows security for intranet and application level defined users for external parties.

The Fundamentals

The general question that an authorization subsystem has to answer is the following: Is the user P authorized to perform the operation O on the resource R?

This question can be modelled as checking for existence of a tuple (P, O, R) from a repository, in our case an SQL database that stores authorization information in the form of such tuples. Obviously, the SQL required for checking the existence of such a tuple is quite easy to construct. If we assume that we represent the identities (primary keys) of the user with P, operation O, and resource with R and store such predicates in a table called AccessPredicate, then the SQL query in Listing 1 can be used to get the required answer: If it returns a non-empty set then the authorization is granted otherwise not.

       SELECT PrincipalId, OperationId, ResourceId
          FROM AccessPredicate
          WHERE PrincipalId = 'P'
            AND OperationId = 'O'
            AND ResourceId = 'R' )

Unfortunately, the solution to this problem is not that simple. As you might have already seen, there is a fundamental flaw in the query in Listing 1: What if the user itself is not granted access, but one of the groups that the user is a member of is? E.g. the user is a member of technicians group and the access was granted to that group[2].

Of course, the query would return an incomplete list in this case and hence incorrect authorization.

It is possible to deduce that the user indeed has the access rights by first querying all the groups the user has a membership to, then running the query in Listing 1 against all the groups that user is a member of, then returning the union of all query results while replacing all groups with P. If the resulting set contains the tuple (P, O, R) then the access is granted, otherwise not. Unfortunately, there is still a problem with this approach: What if the groups that the user are a member of are themselves members of other groups, and the access was actually granted to one those grand parent groups? In short, all such attempts are futile because SQL does not support querying such data which have recursive relations.

To solve this recursion problem and accommodate SQL's inability to express recursive relations, AFCAS relies on the storage of full closure of relational information of DAGs. The design here uses a slightly modified (using the deletion method described by Guozhu Dong et al. to minimize the impact on database size) version of the DAG closure storage technique that I described in my previous article, for all of principal, operation, and resource DAG structures. The technique allows us to use plain SQL to get answers to any question about group, operation, or resource recursive relationships. (Please refer to the section How to Use the Edge Table in that article and Figure 5 and Listing 4 there to see an example.) For example, the following SQL can be used to get all the memberships of the user Jale in Figure 5 of that article:

    SELECT EndVertex
        FROM Edge
        WHERE StartVertex = 'Jale'

This would return

EndVertex       
--------------- 
ABCTechnicians  
Technicians     
Users

(3 row(s) affected)

Therefore the authorization problem can now be reduced to creation of a query that somehow combines the queries in Listing 1 and Listing 2.

Please note that the query in Listing 1 implicitly assumes that operations and resources are simple objects without any of the recursive relationships that the users and groups have. That is certainly a big restriction, especially for the case of operations, because allowing a hierarchy of operations would certainly reduce the administration and programming efforts spent for authorization, tremendously. So, let us try to get a formula for the more general case that both the operations and the resources are allowed to have recursive relations, as well, like the simple hierarchies shown in Figure 1.

Assume that hierarchies of operations and resources also have the same semantics as the group hierarchies i.e. Operation O includes O1; Resource R includes R1, etc. Assume also that a grant is issued to group G for access to resource R for the operation O. Let’s denote that with the tuple (G, O, R). Then the existence of the tuple (G, O, R) implies the existence of tuples composed of all the possible combinations of the entities in Figure 1, like (G1, O, R), (G2, O, R), (U, O, R), etc. Therefore, any check for authorization for any of the children of G, O, or R must ensure checks for existence of tuples made of all of combinations of vertices in Figure 1. Conversely, we would have to form all combinations of direct and implied parent vertices of these entities, if we want to check just the existence of an implied or direct authorization tuple e.g. (U, O1, R2).

With the principles laid out in the previous paragraph, let us now construct the query that would list all direct or implied authorizations for the user U1 for the operation O1 on resource R2 i.e. the tuple (U1, O1, R2). Here is the resulting query in Listing 3:

    SELECT PL.PrincipalId, OL.OperationId, RL.ResourceId
     FROM ( SELECT EndVertex as PrincipalId -- groups of U1
                FROM Edge 
                WHERE Source = 'Principal'
                  AND StartVertex = 'U1'
            UNION
            SELECT 'U1' as PrincipalId  -- U1 itself
            )  PL -- Part 1
            CROSS JOIN (
                SELECT EndVertex as OperationId -- parent operations of O1
                    FROM Edge
                    WHERE Source = 'Operation'
                      AND StartVertex = 'O1'
                UNION
                SELECT 'O1' as OperationId -- O1 itself
            )  OL -- Part 2
            CROSS JOIN (
                SELECT EndVertex as ResourceId -- parent resources of R2
                    FROM Edge
                    WHERE Source = 'Resource'
                      AND StartVertex = 'R2'
                UNION
                SELECT 'R2' as ResourceId  -- R2 itself
            ) RL -- Part 3 
              INNER JOIN AccessPredicate ACL
                 ON PL.PrincipalId = ACL.PrincipalId
                AND OL.OperationId = ACL.OperationId
                AND RL.ResourceId = ACL.ResourceId

Please note that, we assumed hierarchy for all entity types are expressed in the same Edge table, using a Source column to separate different types of entities. This query runs fast for a given tuple of (P, O, R). So, it is a good candidate for writing an IsAuthorized stored procedure or an SQL function. It, however, cannot be used for row-level security, as it is necessary to know, in advance, for which tuple we are looking for.

Row Level Security Implementation

An efficient row level security mechanism can only be implemented on the database level because moving data to the client or middle-tier and then filtering unauthorized data has the following consequences:

1. It is not scalable: Although it may be acceptable to move all records from database to client or middle-tier for small sets of data, it just will not work if the data to be secured is more than several thousand records.
2. It will not allow reporting and analysis software work as intended: I am not sure if such reporting applications for ad-hoc reporting and analysis can even function as intended with row-level security requirements, if we do authorization checks in the middle-tier or the client.
3. It is less secure: When we want to secure data, we usually do it for some good reason. Allowing un-authorized data to be transferred to the client, would pose a security threat as the client software can easily be manipulated to reveal all data it receives from the server, or network sniffing techniques can be used.

Therefore, it is not an option to delegate responsibility of authorization checking to the middle-tier or the client for serious applications. Authorization checks must be done in database level.

However, our query in Listing 3 is not suitable for filtering out unauthorized rows, as noted before. It just allows us to determine whether a user is authorized for a specific resource or not. We need a mechanism that would list all the authorized resources for a given user.

A database view is a good candidate as it can be used to filter-out unauthorized rows from the resource tables. In fact, a very robust security wall can be constructed on the database level using this technique along with an integrated DB server authentication schema.

Listing 4 is the query that generalizes the query in Listing 3. It returns all direct and implied tuples for the given set of explicit authorization grants stored in the AccessPredicate table.

    CREATE VIEW FlatGrantList 
    AS
    SELECT DISTINCT
        PL.StartVertex AS PrincipalId
       ,OL.StartVertex AS OperationId
       ,RL.StartVertex AS ResourceId
     FROM ( SELECT E.EndVertex, E.StartVertex 
            FROM Edge E
                INNER JOIN AccessPredicate AP
                    ON E.EndVertex = AP.PrincipalId
                WHERE E.Source = 'Principal'
            UNION 
            SELECT PrincipalId, PrincipalId 
            FROM AccessPredicate) PL
            CROSS JOIN (
                SELECT E.EndVertex, E.StartVertex 
                FROM Edge E
                    INNER JOIN AccessPredicate AP
                       ON E.EndVertex = AP.OperationId
                WHERE E.Source = 'Operation'
                UNION 
                SELECT OperationId, OperationId 
                FROM AccessPredicate) OL
            CROSS JOIN (
                SELECT E.EndVertex, E.StartVertex 
                FROM Edge E
                    INNER JOIN AccessPredicate AP
                       ON E.EndVertex = AP.ResourceId
                WHERE E.Source = 'Resource'
                UNION 
                SELECT ResourceId, ResourceId 
                FROM AccessPredicate) RL
            INNER JOIN AccessPredicate ACL 
               ON PL.EndVertex = ACL.PrincipalId
              AND RL.EndVertex = ACL.ResourceId
              AND OL.EndVertex = ACL.OperationId

Please note that the query in Listing 4 also assumes that hierarchy of resources are stored in the same Edge table, but, obviously, that can be done on a separate table.

You may have already started to think that for most database applications resources are not hierarchies but a simple row in a table. For this case, we can simplify the query in Listing 4 and make it a bit faster. The simplified query is shown in Listing 5:

    CREATE VIEW FlatGrantListWithFlatResources
    AS
    SELECT DISTINCT
        PL.StartVertex AS PrincipalId
       ,OL.StartVertex AS OperationId
       ,ACL.ResourceId
     FROM ( SELECT E.EndVertex, E.StartVertex 
            FROM Edge E
                INNER JOIN AccessPredicate AP
                   ON E.EndVertex = AP.PrincipalId
                WHERE E.Source = 'Principal'
            UNION 
            SELECT PrincipalId, PrincipalId 
            FROM AccessPredicate) PL
            CROSS JOIN (
                SELECT E.EndVertex, E.StartVertex 
                FROM Edge E
                    INNER JOIN AccessPredicate AP
                       ON E.EndVertex = AP.OperationId
                WHERE E.Source = 'Operation'
                UNION 
                SELECT OperationId, OperationId 
                FROM AccessPredicate) OL
            INNER JOIN AccessPredicate ACL 
               ON PL.EndVertex = ACL.PrincipalId
              AND OL.EndVertex = ACL.OperationId

A further simplification would be elimination of hierarchy from the operations. That makes a lot of sense if we already know that all the operations for viewing the data are not hierarchical and this view is to be used only for row level security. The resulting SQL code is in Listing 6:

    CREATE VIEW FlatGrantListWithFlatResourcesAndOperations 
    AS
    SELECT DISTINCT 
        PL.StartVertex AS PrincipalId
       ,ACL.OperationId
       ,ACL.ResourceId
     FROM ( SELECT E.EndVertex, E.StartVertex 
            FROM Edge E
                INNER JOIN AccessPredicate AP
                   ON E.EndVertex = AP.PrincipalId
                WHERE E.Source = 'Principal'
            UNION 
            SELECT PrincipalId, PrincipalId 
            FROM AccessPredicate) PL
        INNER JOIN AccessPredicate ACL 
           ON PL.EndVertex = ACL.PrincipalId

And, finally, Listing 7 is the query with flat operations and hierarchical resources. This would be the perfect fit for creating row-level-secured views of hierarchical resources.

    CREATE VIEW FlatGrantListWithFlatOperations 
    AS
    SELECT DISTINCT
        PL.StartVertex AS PrincipalId
       ,ACL.OperationId
       ,RL.StartVertex AS ResourceId
     FROM ( SELECT E.EndVertex, E.StartVertex 
            FROM Edge E
                INNER JOIN AccessPredicate AP
                   ON E.EndVertex = AP.PrincipalId
                WHERE E.Source = 'Principal'
            UNION 
            SELECT PrincipalId, PrincipalId 
            FROM AccessPredicate) PL
            CROSS JOIN (
                SELECT E.EndVertex, E.StartVertex 
                FROM Edge E
                    INNER JOIN AccessPredicate AP
                       ON E.EndVertex = AP.ResourceId
                WHERE E.Source = 'Resource'
                UNION 
                SELECT ResourceId, ResourceId 
                FROM AccessPredicate) RL
            INNER JOIN AccessPredicate ACL 
               ON PL.EndVertex = ACL.PrincipalId
              AND RL.EndVertex = ACL.ResourceId

Once we formulated our FlatGrantListXXX views, it is now quite easy to filter out unauthorized resource rows by simply constructing a view for that specific resource that joins with FlatGrantListXXX and puts a where clause on the user identity and operation and a special view operation defined for each of the secured tables). There are two ways of achieving this, both with some advantages and disadvantages:

1. Use a stored procedure that would return the authorized rows, which expects the user identity as a parameter: This solution is applicable only if there is a middle-tier component that is the only passage to the database. All the users can share the same database connection string and hence a scalable solution due to possibility of connection pooling. However, it cannot be used if the users are required to have direct access to the database due to ad-hoc reporting and data analysis requirements as they can send just any identity they want to the server. Furthermore, ad-hoc reporting tools cannot effectively use stored procedures and make SQL joins in this scenario (they would have to pull all the data and then make joins on reporting server, which may not be feasible)

2. Use database integrated security and build secured views for each resource: This solution requires all the database access rights be removed from the underlying tables and update and delete operations should be made using stored procedures. The main advantage of this solution is its transparency to both the application code and to the ad-hoc reporting and analysis tools. The big disadvantage is scalability. The database server would have to maintain a connection for each of the clients, which may be a problem for a large number of concurrent users. It also has a higher administration overhead, and increases attack surface of the database. The technique also requires use of Kerberos delegation on windows networks, if the application design is multi-tier. I note this here because it seems that Kerberos delegation does not work flawlessly in Windows networks (at least for me, with a Windows Server 2003 network and SQL Server 2005)

The SQL code in Listing 8 shows how both of these options can be implemented.

    -- a stored procedure that implements option 1
    CREATE PROC GetAuthorizedResourceXYZ (
        @UserId varchar(256)
    ) AS
    BEGIN
        SELECT R.*
            FROM ResourceXYZ R
                INNER JOIN FlatGrantListWithFlatOperations G
                   ON R.Id = G.ResourceId
            WHERE G.PrincipalId = @UserId
              -- the ‘view’ operation defined for ResourceXYZ          
              AND G.OperationId = 'View.ResourceXYZ' 
    END
 
    -- a view that implements option 2
    CREATE VIEW SecurityEnabledResourceXYZView
    AS
        SELECT R.*
            FROM ResourceXYZ R
                INNER JOIN FlatGrantListWithFlatOperations G
                   ON R.Id = G.ResourceId
            WHERE G.PrincipalId = SYSTEM_USER  
              -- the ‘view’ operation defined for ResourceXYZ          
              AND G.OperationId = 'View.ResourceXYZ'

Please note that the code in Listing 8 is not limited to for viewing rows. One can imagine that such code can be used, for example, on a delete screen where the user is presented with the list of items that he is authorized to delete, etc. All we need is just to define an operation and grant the relevant groups to execute the operation on the desired resources.

Maintenance of Access Predicates and Resource Hierarchies

Please note that code in Listing 8 only answers the question of getting the data from the database. Row-level security must also have a pillar for supporting database updates and inserts. Obviously, there must be some mechanism for maintaining the data in AccessPredicate table as well as resource hierarchies. Unfortunately, there is no generic solution available to this problem as it is highly application dependent. However, AFCAS provides the basic facilities for maintaining the resource hierarchies and Access Predicates. Each application would then decide whether to provide explicit authorization grant/revoke use cases with devoted screens, or make automatic authorizations depending on the properties of the user and/or resources. A better place, arguably, for automatic resource hierarchy maintenance or automatic granting would be an application specific stored procedure that is used for insert/update operations on the resource tables. But, all that depends on particular scenario.

The Application Interface

As we are finished with the database side, we can now turn to the application interface of the system. AFCAS targets mainly database applications with tables that need to be secured for controlled access. However, AFCAS is not bound to database applications that require row-level security. It has a very sophisticated authorization schema that can satisfy most authorization needs.

Obviously, AFCAS cannot make a lot of assumptions about the resources that it is going to secure. But, there has to be a link to the resources that is understood by both the client application and AFCAS itself. The AFCAS database uses a single string identifier for each of the resources it secures for efficiency reasons. This is safe, as each persisted object row must have a unique set of attributes that can usually be converted to a string without any problems. If a conversion is required to generate the string identifier, it must be defined by the clients of AFCAS. So, Listing 9 is the basic interface that AFCAS implements:

/// <summary>
/// The main interface to be used by the clients that need to make authorization decisions.
/// An instance of this interface is provided by <see cref="Afcas"/> class.
/// </summary>
public interface IAuthorizationProvider {
    // the method to justify the existence of this interface
    bool IsAuthorized( string principalId, string operationId, ResourceHandle resource );

    // these method also have uses for authorization purposes
    bool IsMemberOf( string groupId, string memberId );
    bool IsSubOperation( string opId, string subOpId );
    bool IsSubResource( ResourceHandle resource, ResourceHandle subResource );
  
    // These two methods are for offline support
    IList< ResourceAccessPredicate > GetAuthorizationDigest( string principalId );
    IList< Operation > GetAuthorizedOperations( string principalId,
        ResourceHandle resource );

    // This can be used to allow the user to browse authorized resources
    IList< ResourceHandle > GetAuthorizedResources( string principalId,
        string operationId );
}

ResourceHandle is the abstract base class that encapsulates the logic to convert a given resource to a string identifier and vice versa. The users of AFCAS would have to write sub-classes for each of the resources they want to secure and register with AFCAS. Please see the attached source code for examples.

ResourceAccessPredicate is the structure that holds the tuple (PrincipalId, OperationId, ResourceId) data for specific access grant (or denial). Its definition is in Listing 10:

    public struct ResourceAccessPredicate {
        // ...
        public string PrincipalId { get { return _PrincipalId; } }
        public string OperationId { get { return _OperationId; } }
        public ResourceHandle ResourceId { get { return _ResourceId; } }
        public ResourceAccessPredicateType AccessPredicateType { get {
            return _AccessPredicateType; } }
    }

The class Operation serves a similar purpose and maintains properties such as Id, Name, and Description of a particular operation.

Listing 11 is the petty enumeration ResourceAccessPredicateType that I used to specify the type of access. As you see there, AFCAS supports explicit denials which are considered to have higher precedence than grants, as is in MS Windows's ACL. I initially thought that would be a useful feature to simplify administration. However, it turned out to be one of those not-so-required features and consequently I removed its database implementation because of its performance hit[3]. However, I still left it intact on the .NET code so that it can be turned on afterwards if it really is needed.

    public enum ResourceAccessPredicateType {
        Grant = 0,
        Deny = 1
    }

The IAuthorizationProvider interface is plain and simple to understand and use. It allows the dependent sub-systems to ask questions using string IDs of the principal (most probably the logged-in user), the operation, and optionally a ResourceHandle. The ResourceHandle is considered optional because some operations do not have a meaningful resource context. In such cases, NullResource.Instance should be set as a convention. Here is the expected way to use AFCAS:

        public void SomeUsefulWork( ResourceTypeXyz someResource ) {
            IAuthorizationProvider provider = Afcas.GetAuthorizationProvider( );
 
            // A resource handle factory for this particular type must be defined
            // and registered
            // using Afcas.RegisterHandleFactory( )
            // prior to calling this method, preferrably during application startup
            ResourceHandleFactory fac = Afcas.GetHandleFactory( "ResourceTypeXyz" );

            // here the app should replace hard coded userid and operationIds.
            if( !provider.IsAuthorized( "domain\\kemal", "SomeUsefulWorkOpId",
                fac.GenerateResourceHandle( someResource ) ) ) {
                throw new SecurityException(
                    "You are not authorized to perform SomeUsefulWork" );
            }

            // authorized, do the work
            ...
        }

Administration

Please note that IAuthorizationProvider does not define any method for the maintenance of users, groups, and operations. That is the job of an administration console component for AFCAS. An interface called IAuthorizationManager is defined for management of Users, Groups, and Operations, as well as their hierarchies. An implementation of the interface is also provided. Please refer to the attached source code for details.

The only missing part is an UI that would use IAuthorizationManager interface for an administration console, which is composed of the following parts:

User and Group Management: Relying simply on AD synchronization saves us from writing and administrating console applications for user/groups management, as standard Active Directory tools can be used for such purposes. But, it is still necessary to write such a console application for outwards facing internet applications with application level users. I did not write such a tool simply because the need has risen yet.

Operations Management: The list of operations for a given application is generally fixed when the application is released to the production. The operations list changes only if there are added/removed functionality, meaning changes to the source code. So, for most applications, there is no need for an administration console for the management of operations that is intended to be used by IT operations staff.

Resource Hierarchy Management: The hierarchy of resources is generally an application specific task and most applications have their own UI for management of resource hierarchies. IAuthorizationManager interface, however, provides functionality for maintaining the hierarchies. The UI should be written by client applications.

Granting/Revoking Access: This part is highly application dependent too, and most applications should create its own UI that would also display meaningful context information about the resources to be secured. The actual work is done by AFCAS thru its IAuthorizationManager implementation.

In short, the basic AFCAS component shown here completely avoids writing an administration console. However, there still exists a need for a management console of users and groups. I'll address the issue in the future whenever I have some time to spare for it.

Implementation

All the important calculations are performed at the database level. Most of its authorization calculations are delegated to the database server in the forms of SQL queries that were presented in the previous sections. The reason is that SQL is better than any imperative language when it comes to performing set algebra which is the basis of all the work done by AFCAS.

The AFCAS database is composed of four tables:

1. Principal: Stores the basic information about both the users and groups.
2. Operation: Stores the names and codes of the operations that is to be secured, the data there, once setup, is mostly static
3. Edge: This table stores the DAG model for the Principals, Operations, and also Resources. See my previous article for a full explanation of its uses.
4. AccessPredicate: Stores the explicitly granted (ort denied) access rights. This is the association table to link resources to the access rights

The complete structure of the tables as well as the code for stored procedures and C# code can found in the attached zip file to this article.

Active Directory (AD) Synchronization and Administration

As noted before, AFCAS aims to use existing Active Directory user and group accounts. We also noted that AFCAS would do all authorization calculations in the database using plain SQL queries. To remedy the problem of associating existing AD users and groups with the ones that are defined in the AFCAS database, I created a synchronization (only from AD to SQL database) module that would replicate users and groups from AD into SQL server. It uses the objectID property of AD users and groups as the pivot for synch comparison. The frequency of synchronization can be adjusted according to needs, down to minutes. For our case, a daily sync was just fine.

Please refer to the attached source code for details.

ASP.NET Membership and Role Providers

ASP.NET has a fairly usable authentication and authorization infrastructure whose use, by the way, is not actually limited to the web applications. Please refer to the MSDN topic Securing ASP.NET Web Sites for more information. However, its authorization part provides a lot less than AFCAS does, especially when it comes to low-level security. Therefore, it would be very nice to use the existing authentication part of ASP.NET security and replace authorization part with AFCAS.

It seems that integration of AFCAS into ASP.NET membership and role provider model is not difficult. As I noted before, I believe using platform facilities for authentication is the best path to go. So, I do not have any plans to replace authentication part. However, an AFCAS based RoleProvider can be quite useful and I am planning to write one when I have some time to spare for it.

Emulating Multiple Labels for Common Scenarios

Many organizations that have to deal with sensitive information have adopted a categorization schema that rates how sensitive the information is like “Not classified,” “Sensitive,” “Confidential,” and “Highly Confidential.” They have further divided the information into clusters to facilitate need-to-know principle, such as geography: User A is working only on Country X data, so he can be granted access only to Country X.

The resource nesting ability of AFCAS makes it quite easy to support such categorization schemas. The sample resource hierarchy in Figure 2 shows how it can be used to support such scenarios.

Please note the “Root” node: It enables administrators to grant access to “all” data for high level executives easily and without human error. See how the country data is kept in a separate branch of its own. Once the users are ready to save a “new document” to the system, they have to determine to which country the data belongs to and then the security classification level. They can then use the AFCAS API to put the appropriate relation between the classifications and the document.

It does not make sense to put a document under more than one security category, but it is conceivable that some documents would have to be categorized under more than one country like “Doc11” in Figure 2: It is not a problem for AFCAS. But, the users must be fully aware of security implications when an item is filed under more than one major category.

Figure 2 also emphasizes the importance of an automatic granting mechanism. Without such a mechanism any authorization schema would fail if the number of resources to be secured is in the order of tens of thousands. A good default policy would be to analyse the current application roles of the user and grant view rights to all these roles automatically at the time of committing brand new data. However, that is all application dependent and certainly is not applicable in many cases. Nonetheless, the requirement for setting up an access granting policy remains as a common issue.

Performance and Scalability

There are a number of potential bottlenecks in the database design of AFCAS for applications that require very high throughput. One obvious place is the Edge table as it is used to store relation information for all of Principal, Operation, and Resources. In high throughput environments, race conditions may occur to get database locks on the Edge table.

One remedy for such problem would be maintenance of a separate Edge table for each entity: namely Principal, Operation, and each of the resources that the application wants to secure. Obviously, that would require changes in the views and also duplication of stored procedure code for closure maintenance operations in the database level.

One further performance improvement would be maintenance of separate Principal and Operation entities along with separate Edge tables for each of the resources. Principals and Operations would have to be maintained in a master copy and then replicated to the resource specific copies in regular intervals. This would further reduce possible race conditions and hence improve performance. It also avoids the need for duplication of DB code as it is only necessary to maintain the master copies of Principal and Operation tables. The rest is handled by replication code (which would do some smart replication such as replicating only a sub-set of applicable operations and allowed groups of users for a given resource)

Final Words

AFCAS has proven to be a useful sub-system for my development, I am hoping the same for the readers. Bug reports, suggestions, and opinions are most welcome.

[1] For example, it is not clear whether the rule “application xxx is allowed to execute only  09:00-17:00” is in authorization domain or business domain. Under close scrutiny, many such complex authorization rules turn out to be just another set of business rules to implement.

[2] A popular approach is that the authorization rules are never changed: Once the roles in the system are defined, access is granted only to those groups that resemble the application roles at the very beginning. Afterwards, only the members of the groups are allowed to change. That would simplify the administration and auditing of authorization sub-system, as this policy keeps only one variable in the authorization equation.

[3] It is quite easy to incorporate explicit denials to the queries presented before. We just need to construct the set of grants (along with their implied tuples) and then remove all the explicit denials (along with their implied tuples) using standard SQL difference operation or simply a left outer join with a where clause. I removed it from the SQL views and from IsAuthorized stored procedure because of performance concerns.

History

• October 23, 2008 -- Article Posted
• February 12, 2009 -- Updated source with significant performance enhancements on the SQL side.

You must Sign In to use this message board.

FAQ    Noise Tolerance Very HighHighMediumLowVery Low   Layout Expand Posts & RepliesThread ViewNo JavascriptNo JS + Preview   Per page 102550

Msgs 1 to 15 of 15 (Total in Forum: 15) (Refresh) FirstPrevNext

Small but significant error in AddEdgeWithSpaceSavings procedure PortalCoder 12:24 29 Jun '09   The insert into Edge from #Candidates should only exclude those for which there isn't one with hops = 1. The statement therefore is missing condition: E.Hops = 1 WHERE NOT EXISTS ( SELECT Hops FROM Edge E WHERE E.StartVertex = C.StartVertex AND E.EndVertex = C.EndVertex AND E.Hops = 1) Verification: exec addEdgeWithSpaceSavings 'a', 'b', 'x' exec addEdgeWithSpaceSavings 'a', 'c', 'x' exec addEdgeWithSpaceSavings 'b', 'c', 'x' with as result: StartVertex EndVertex Hops Source a b 0 x a c 0 x b c 0 x Missing but required entry is: StartVertex EndVertex Hops Source a c 1 x Which becomes clear when you execute: exec removeEdgeWithSpaceSavings 'a', 'c', 'x' with as result: StartVertex EndVertex Hops Source a b 0 x b c 0 x But logically 'a' of course still is connected to 'c' via 'b'. Adding the check E.hops = 1 as shown above fixes this omission. Sign In·View Thread·PermaLink Re: Small but significant error in AddEdgeWithSpaceSavings procedure [modified] Kemal Erdogan 22:17 1 Jul '09   Dear PortalCoder, Thank you very much for your effort for reporting and fixing the problem. It is indeed a bug and your fix also does not break anything else. I updated the source code and added a new test into the unit tests procedure for protecting the fix. The article was also updated accordingly Kemal modified on Monday, February 1, 2010 5:39 AM Sign In·View Thread·PermaLink 2.00/5 (1 vote) excellent Donsw 7:26 30 Jan '09   excellent, that is all I have to say, great work. Sign In·View Thread·PermaLink Sql 2008 Hierarchy ID Dankarmy 13:11 13 Jan '09   I've noticed that MS have introduced a hierarchyid datatype - are you planning to port your implementation to use the new datatype? John Sign In·View Thread·PermaLink Re: Sql 2008 Hierarchy ID Kemal Erdogan 6:48 15 Jan '09   John, The hierarchyid field only supports trees, which are not suitable for group management: A group or user can only be member of a single group, which is obviously a very serious limitation. Actually, that is the reason why I had to invent a method to store DAGs in SQL databases. In short, it is not possible to port the implementation due to limitations of hierarchyid data type. In the mean time, however, I improved the implementation and gained quite considerable speed. I'll update the article when I have time. If you are currently using AFCAS, or planning to use it, I can send you the latest source code via e-mail. Kemal Sign In·View Thread·PermaLink Re: Sql 2008 Hierarchy ID Dankarmy 10:53 15 Jan '09   Thanks for the reply. It would be great if you could send the source code as I have an implementation in development at the moment. If you could email to john [at] rogeronclose.co.uk that would be great. Cheers Sign In·View Thread·PermaLink Re: Sql 2008 Hierarchy ID josealtamirano 12:59 4 Feb '09   could you send me the last version to my email: jxaxl@hotmail.com thanks. Sign In·View Thread·PermaLink Re: Sql 2008 Hierarchy ID Kemal Erdogan 0:35 11 Feb '09   Jose, No problem. I'll update the article download with the latest version. But, I'll send it to you via e-mail, as well. Kemal Sign In·View Thread·PermaLink Simply...Fantastic Dankarmy 14:13 27 Oct '08   This is a great article! I have downloaded the source code and run the SQL provided to build the tables /sps and views. Do you have a sample database pre populated with data you could perhaps add to the article? Thanks Sign In·View Thread·PermaLink 2.00/5 (1 vote) Re: Simply...Fantastic Kemal Erdogan 22:34 27 Oct '08   Hello, Thanks for the nice words. The unit tests included with the source code generates some small set of data. You can use the active directory replicator to create a lot of users and groups if you have access to a production active directory. Unfortunately, that is all I have as test data. Kemal Sign In·View Thread·PermaLink A good start zlezj 1:31 27 Oct '08   The proposed solution is a good start for the authentication module for the framework I'm currently developing. Thanks! Regarding footnote 1 the following: Authorization rules are business rules. I don't agree that complex rules should be implemented in the business layer. Take for instance the following example: In a project registration system, each project has a project manager and each project manager has an assistant. The assistant is allowed to see only the the projects that are assigned to the project manager of the assistant. Furthermore the project has a standard start-up form, in which all the projects for which the user is authorized are shown. Implementing these requirements in the business layer would result in a performance hit. These requirements could be fulfilled by adapting the queries for the project form. This solution has several drawbacks: It mingles authorization with normal business rules; authorization will be scattered throughout the code I suspect it is not quite scalable: the queries probably will get more complex when more roles with respect to the project row will be added Dynamic roles (i.e. roles defined by the user, not by the data model) are particularly difficult to add I am very curious about your thoughts. What strategy would you use for developing this kind of authorization? Sign In·View Thread·PermaLink Re: A good start Kemal Erdogan 7:25 27 Oct '08   Zlezj, When I say that complex authorization rules are actually business rules, I do not imply that they can be scattered around the business layer. What I meant was that they are just of different type than authorization concerns and that they should be tackled seperately. Obviously, it is always better to group similar aspects into seperate modules and make sure that module dependencies are carefully managed. Regarding your example, it is actually completely possible to define your authorization rule within AFCAS without any custom coding of authorization rules. Here is how: 1. First define the groups Project Manager (PM), and Project Manager Assistant (PMA), and create a sub group Project Manager for Project X, and Project Manager Assistant for Project X, groups where X stands for each project. Having a a parent project manager group will allow you to do checks in generic code using generic roles PM and PMA 2. Define the operation "View Project" (VPO) 3. Create the grants (PMX, VPO, PX) and (PMAX, VPO, PX) where PMX, PMAX, and PX represents the specific project manager, project manager assistant groups, and the project on hand, respectively 4. Use the standard AFCAS "GetAuthorizedResourceXYZ" view that you have constructed to populate your start page. Please note that steps 1 and 3 requires some "smart" actions by the client code of AFCAS. You should use AFCAS API at the creation of a project to create the relevant groups and assigning them as subgroups of the standard application roles project manager and project manager assistant. You should also make sure that the grants at step 3 are made at the project creation. That is what I was referring to "Maintenance of Access Predicates and Resource Hierarchies" section. You can use the associations created by the AFCAS predicates for viewing projects to track which project manager is the boss of which assistant. But it is probably best to use a naming convention in the names of project management groups that you have to create for each project. The rest is handled by AFCAS. The current implemantation that I use for a productipon system uses the basic form of AFCAS and the performance is great. However, you can use the performance and scalability tips that I provided for other scenarios. As always, testing is the best way to tell before making any assumtions. Sign In·View Thread·PermaLink Source code zlezj 10:36 23 Oct '08   This article is very interesting and I'll study it over the weekend. Is there a place where I can download the source code? Sign In·View Thread·PermaLink Re: Source code Kemal Erdogan 11:47 23 Oct '08   There must be a mistake. I'll check with code project team and make sure that source code is attached to the article Sign In·View Thread·PermaLink Re: Source code zlezj 9:56 24 Oct '08   It's there now. Thnx! Sign In·View Thread·PermaLink

Last Visit: 17:16 9 Feb '10     Last Update: 17:16 9 Feb '10 1