Section 4.2. Creating a Permission Hierarchy

4.2 Creating a Permission Hierarchy

4.2.1 Problem

You need to create a system for maintaining a simple permission hierarchy for a trading system. This hierarchy needs to be similar to the one described earlier in Section 4.1.1. Permissions should be manageable in groups so a set of permissions can be granted to a trader easily. In addition, your solution needs to allow for permission types to be added dynamically while the system is in production.

4.2.2 Solution

Define two tables. In the first table, define groups of permissions; in the second table, link those groups to particular traders. Each trader can belong to more than one permission group . The table defining the permission groups can be defined as follows :

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

In our GroupPermissions table, there are two attributes describing trading permissions for each product group. One attribute is a status attribute. The status of each trading permission can be set to valid ( 'V' ) or suspended ( 'S' ). The second attribute is an order limit that defines the maximum permitted size for a trade involving the product group. If a trader is authorized to trade bonds and has a trading limit of 1,000, that trader is not allowed to initiate a trade involving more than 1,000 bonds . The following is a representative sample of the type of data that you could store in this table:

 GroupId              ProductType Status Limit         -------------------- ----------- ------ ------------  Debt                 Bill        V      10000.00 Debt                 Bond        V      10000.00 Derivatives          Future      V      200.00 Derivatives          Option      V      100.00 Equities             Share       V      1000.00 

The second table needs to keep track of the permission groups assigned to each trader. Its definition can be as follows:

 CREATE TABLE GroupMembership(    AccountId VARCHAR(20) NOT NULL,    GroupId VARCHAR(20) NOT NULL    PRIMARY KEY(AccountId,GroupId) ) 

This table simply links accounts with groups. Whenever a trader is assigned to a new group or removed from an existing group, the table must be updated to reflect that fact. The following sample shows the group assignments for three traders:

 AccountId            GroupId               -------------------- --------------------  Alex0001             Debt Alex0001             Derivatives Alex0001             Equities Betty0002            Derivatives Charles0003          Debt 

You can see from this example that a single trader can belong to multiple groups. In this case, Alex0001 belongs to three groups: Debt, Derivatives, and Equities. In the next section, we'll describe one way to handle cases where a trader's permissions from one group conflict with his permissions from another group.

4.2.3 Discussion

The main goal of our solution is to separate the definition of permissions and groups from the assignment of groups to traders. Please note that the GroupPermissions table is not fully normalized. To normalize it, you would have to separate the definition of groups from the definition of products and then use an intersection table to define the Limit and Status attributes. The lack of normalization here simplifies the example somewhat, but it doesn't affect the overall behavior of our design nor does it affect the queries that you'll see in this section.

The goal of normalization is to provide specific benefits to database design ” reduced redundancy being the foremost benefit. However, there are many situations in which reduced redundancy is less valuable than the performance gains you get from a denormalized structure.

4.2.3.1 Checking permissions for a trader

Queries regarding trading authorizations can easily be performed on the model that we've designed. For example, to list the trading permissions for the account Alex0001, all you need is the following simple join:

 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='Alex0001' GROUP BY m.AccountId, g.ProductType AccountId            ProductType Limit         -------------------- ----------- ------------  Alex0001             Bill        10000.00 Alex0001             Bond        10000.00 Alex0001             Future      200.00 Alex0001             Option      100.00 Alex0001             Share       1000.00 

While the results of this query are straightforward, the use of the GROUP BY clause together with the aggregate function MIN deserves some additional explanation. The grouping allows us to deal with cases where more than one of a trader's permission groups defines a limit for the same product. In our example data, both the Debt and Equities groups define a trading limit for bonds. Alex0001 is a member of both groups, so an ungrouped query would return the following two records:

 AccountId            ProductType Limit         -------------------- ----------- ------------  Alex0001             Bond        10000.00 Alex0001             Bond        2000.00 

With two limits for the same product, the question is which limit takes precedence. In our example query, we used the MIN function for the lower limit to take precedence over the higher limit. If you wanted the higher limit to take precedence, you would make that happen simply by using MAX instead of MIN. The GROUP BY clause is a requirement when using aggregate functions in this manner and ensures that only one permission is ultimately returned for each product that the trader is authorized to trade.

4.2.3.2 Revoking permissions for a group

The Status column in our design allows you to quickly and efficiently revoke a specific permission from a group or to suspend all permissions for a given group. For example, to suspend all trading permissions for the Debt group, you could execute the following statement:

 UPDATE GroupPermissions SET Status='S' WHERE GroupId='Debt' 

The previously shown query for checking permissions only looks at valid permissions ( Status = 'V' ); those you've just suspended ( Status = 'S' ) will automatically be excluded. Their definitions, however, will still exist in the database, so you can easily enable them again when you want to do so.

The solution shown in this recipe is a general solution and can be used for almost any type of permission hierarchy with a limited number of levels. It can be used for security hierarchies where different levels of security authorization are defined, for storing information regarding types of users and their authorizations in a web-based authorization system, or for any other type of permission hierarchy where a large number of users or accounts is used.