4.5 Centralizing Authorization Logic

4.5.1 Problem

You wish to centralize the logic for your authorization policy. You want to be able to query for an individual trader's permissions, but you don't want that query to contain the logic that handles conflicting permissions and individual exceptions. Your goal is to be able to make reasonable changes to your permissions policies and model without forcing a change to queries already running in production programs.

4.5.2 Solution

Create a view into which you embed an authorization query. The benefit of this is that you can easily change the underlying query of the view without having to change the code in all the programs that query the view. The following view implements the authorization logic shown in the first recipe titled "Creating a Permission Hierarchy":

CREATE VIEW orderAuthorization 
AS
SELECT AccountId, ProductType, MIN(Limit) Limit
FROM GroupMembership m JOIN GroupPermissions g
   ON m.groupId=g.groupId 
WHERE Status='V'
GROUP BY AccountId, ProductType

Using this view, you can now obtain the permissions for a specific trader by executing a simple query such as the one shown in the following example:

SELECT AccountId, ProductType, Limit
FROM OrderAuthorization
WHERE AccountId = 'Alex0001'

AccountId            ProductType Limit        
-------------------- ----------- ------------ 
Alex0001             Bill        10000.00
Alex0001             Bond        2000.00
Alex0001             Future      200.00
Alex0001             Option      100.00
Alex0001             Share       1000.00

You can also issue a query against this view to check for a specific permission. For example, suppose an order to trade 3,000 shares for the account Alex0001 comes into the system. You can use the simple query shown in the following example to retrieve the maximum limit for this type of order:

SELECT Limit 
FROM orderAuthorization 
WHERE AccountId='Alex0001' AND productType='Share'

Limit        
------------ 
1000.00

By keeping your queries simple and writing them against a view such as this, you can easily modify your permission model and logic. In the event that you make such modifications, the view will insulate your existing queries from the changes.

4.5.3 Discussion

This solution demonstrates a good separation of business logic from the data model. Imagine that these permissions can be any type of permissions for a large amount of accounts. Having the interpretation of the data model embedded within a view is very likely to pay off for you. Changes in real-life systems are frequent, and often unexpected, so it is usually not wise to hand-code the interpretation (embedding the query) of a data model into your application system. With the view-based solution shown in this recipe, you are free to change the underlying permission model and the interpretation policy during production without going through the additional trouble of recompiling the system or changing the code.