4.4 Adding New Individual Permissions

4.4.1 Problem

The previous recipe's solution allows you to define individual exceptions to permissions that a trader already has at the group level. However, you wish to grant permissions to specific traders without regard to whether permissions for the same products have been granted at the group level.

4.4.2 Solution

In the previous recipe, you could define an exception to a permission that a trader had already been granted at the group level. For example, you could create the following two rows in AccountPermissions:

AccountId            ProductType          Status Limit        
-------------------- -------------------- ------ ------------ 
Alex0001             Share                V      5000.00
Betty0002            Share                V      8000.00

The query in the previous recipe, however, will only respect these account-specific permissions in cases where the traders in question have also been granted permission to trade shares at the group level. This limitation, if you wish to call it that, comes about as a result of the three-way join used in the query.

You may wish account-specific permissions to take effect all the time. To do that, you can use the same data model as shown in the previous recipe, but with a different query. The query shown in the following example is a union query and correctly returns both group-level and account-specific permissions for Betty0002:

SELECT m.AccountId, g.ProductType, MIN(g.Limit) Limit
FROM GroupMembership m JOIN GroupPermissions g
   ON m.groupId=g.groupId 
WHERE Status='V' AND AccountId='Betty0002' 
   AND NOT EXISTS(SELECT * FROM AccountPermissions  a
      WHERE m.AccountId=a.AccountId AND g.ProductType=a.ProductType) 
GROUP BY m.AccountId, g.ProductType
UNION
SELECT a.AccountId, a.ProductType,a.Limit 
FROM AccountPermissions a
WHERE a.AccountId='Betty0002' AND a.Status='V'

AccountId            ProductType          Limit        
-------------------- -------------------- ------------ 
Betty0002            Share                8000.00
Betty0002            Future               200.00
Betty0002            Option               100.00

As you can see, even though Betty0002 has not been granted permission to trade shares at the group level, this query still picked up the account-level share permissions. The query in the previous recipe won't do that.

4.4.3 Discussion

The key to this query is that it is a union query. The first query in the union reports all those permissions that are defined only at the group level. The subquery in that query's WHERE clause ensures that group-level permissions for products are excluded when account-specific permissions exist for those same products. The second query in the union then returns account-specific permissions. The results from the two queries are combined as a result of the UNION clause.

There are two drawbacks to the solution shown in this recipe. One is that this solution is less efficient than the one shown in the previous recipe. This is because there are three SELECT statements involved instead of just one. Another drawback is that this solution is inflexible in terms of which permission to use when permission for the same product is granted at both the group and the account level. In such cases, the account-specific permission always takes precedence over the group-level permission. A way to overcome this latter limitation is to create a more general UNION query that includes both group- and account-level permissions, embed that query in a view, and then manipulate the view with an appropriate query. The following statement creates such a view:

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

This VIEW returns the group permissions expanded for each account and also includes any account-specific permissions that may exist. To list all permissions for a particular account, query the view and apply your interpretation policy in the query. For example:

SELECT ProductType, MIN(Limit) Limit FROM permissions 
WHERE AccountId='Alex0001'
GROUP BY ProductType

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

This query lists permissions for Alex0001. The MIN function resolves cases where multiple permissions exist for the same product type. When such cases occur, MIN ensures that only the lowest applicable limit is returned. To always return the highest-applicable limit, you could use the MAX function.

The solutions shown in this recipe are flexible, because they allow for easy addition of new levels to the permission structure. All you need is to add UNION clauses to your query or to your view.