Section 4.3. Changing Individual Permissions

4.3 Changing Individual Permissions

4.3.1 Problem

You've implemented the permission solution described in the previous section, and you are happy with the ability to assign permissions to groups of users, but you also want the ability to grant specific exceptions directly to specific traders.

4.3.2 Solution

The solution is twofold. First, create an additional table to record exceptional permissions granted directly to a trader. Second, modify the query that you use to obtain the permissions that are currently valid for a given trader . The additional table could look like the following AccountPermissions table:

 CREATE TABLE AccountPermissions(    AccountId VARCHAR(20) NOT NULL,    ProductType VARCHAR(20) NOT NULL,    Status CHAR(1) CHECK(Status in ('V','S')) DEFAULT('V'),     Limit NUMERIC(10,2) NULL,    PRIMARY KEY(AccountId, ProductType) ) 

In this table, insert records to record all the permissions that you want to assign to traders that are in addition to the permissions those users inherit from their groups. For example, suppose that Alex0001 had a share limit of 1,000 because he belonged to the equities group. Further suppose that you wanted to make Alex0001 an exception to the rule for that group and raise his share limit to 5,000. To do this, insert the following row into the AccountPermissions table:

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

With the AccountPermissions table in place, you need to modify the query that you use to retrieve permissions for a given user . That query now needs to take into account this new table. The query in the following example, which returns permissions for Alex0001, will do that:

 SELECT m.AccountId, g.ProductType,     CASE WHEN isnull(MIN(a.Limit),0) > MIN(g.Limit)        THEN MIN(a.Limit)        ELSE MIN(g.Limit)     END Limit FROM GroupMembership m JOIN GroupPermissions g        ON (m.GroupId=g.GroupId)    LEFT OUTER JOIN AccountPermissions a        ON (m.AccountId=a.AccountId AND g.ProductType=a.ProductType) WHERE  m.AccountId='Alex0001'  GROUP BY m.AccountId, g.ProductType HAVING MIN(g.status)='V' AND isnull(MIN(a.status),MIN(g.status))='V' AccountId            ProductType Limit         -------------------- ----------- ------------  Alex0001             Bill        10000.00 Alex0001             Bond        2000.00 Alex0001             Future      200.00 Alex0001             Option      100.00  Alex0001             Share       5000.00  

Note the emphasized line in the output. That line represents the permission to trade 5,000 shares that came from the AccountPermissions table.

4.3.3 Discussion

The idea behind the permissions model described in this chapter is to have permissions set through group memberships whenever possible. However, in practice, you'll often find that it is useful to have a tuning facility of sorts that allows you to directly change a specific permission for a specific user. To that end, we created the additional table named AccountPermissions in which such exceptions are recorded. Our new query joins the GroupPermissions table with the GroupMembership table to return permissions set at the group level. Those results are then joined with the AccountPermissions table, which adds account-specific exceptions to the result set. An outer join is used for this third table to make the account-specific exceptions optional.

The GROUP BY clause is again used in this new version of the query, for the same reason as before ” we only want one permission to be returned for each product type. There is one difference, though: this time, the GROUP BY function includes more columns in its column list:

 GROUP BY m.AccountId, g.ProductType 

The CASE statement that you see in the query is used to decide which value to take if both individual and group permissions for the same account and product are present. It checks both values and reports just one:

 CASE WHEN isnull(MIN(a.Limit),0) > MIN(g.Limit)        THEN MIN(a.Limit)        ELSE MIN(g.Limit)     END Limit 

In our case, our authorization policy is that account-specific permissions only take precedence over permissions granted at the group level when the account-specific limit is greater than the group-specific limit. The isnull( ) function takes care of cases where individual permissions are not set. It does this by supplying a zero value for those cases. Using a CASE statement like this is a very flexible approach, because you can easily implement different authorization policies. It would be trivial, for example, to change the CASE statement such that account-specific permissions always took precedence over group-level permissions, regardless of whether the account-specific permission specified a higher or lower limit.

Unlike the query shown previously in this chapter in which the Status flag was checked in the WHERE clause, this query takes the different approach of checking the Status flag in the HAVING clause. In fact, the query checks to be sure that both flags ” for the group-level permission and for the account-specific permission ” are set:

 HAVING MIN(g.status)='V' AND isnull(MIN(a.status),MIN(g.status))='V' 

In this case, if only one of the two applicable flags is set to 'S' , the permission is revoked .

The solution shown in this recipe is very useful when you need to make exceptions for existing permissions set at the group level. However, it has one significant problem: you cannot define an account-specific permission for a trader in cases where a permission for the same product has not also been granted at the group level.