Using Nodes in a Tree Structure to provide Record-Level Security

Tony Marston - 4th November 2001

This article is a follow-up to A Flexible Tree Structure in which I explained how to create a hierarchical tree structure from only 3 database tables. This design can hold any number of structure (tree) types, with any number of levels and any number of nodes, and also has the ability to enable levels to be added or deleted from a structure without requiring that the whole structure be rebuilt.

A tree structure is a hierarchy of NODES to which other objects known as LEAVES can be attached. A typical example is where the nodes are points in a company's organisational hierarchy (companies, divisions, departments, etc.) and the leaves are people who occupy positions within this hierarchy.

What do I mean by record-level security?

When examining leaf details it is often a requirement that rather than being able to access the details of every leaf within the system a particular user is only able to access a subset of those details. The way to achieve this is to give each leaf a security code and to give each user a security key that only allows access to some of those codes.

Generating Security Codes

Take as an example the tree structure shown in figure 1. It shows an arrangement of nodes in a typical a hierarchy, each node being identified by a unique number (the Node ID).

Figure 1 - A typical hierarchical structure


When a leaf is attached to a node all that is required to be held in the leaf details is the value of the selected Node ID. However, if a security code for record-level security is required one can be constructed by tracing that node's ancestry all the way back up to the root node. This is possible because each node can have only one parent, and the identity of that parent is held within the node's details. It is therefore a simple matter of stepping through the parent of each node until there are no more parents. For the structure in figure 1 this would produce the set of security codes as shown in table 1:

Table 1 - Security Codes for each Node

1 1
2 1-2
3 1-3
4 1-4
5 1-2-5
6 1-2-6
7 1-3-7
8 1-3-8
9 1-4-9
10 1-4-10

In this example the separator between each Node ID is a hyphen, but it could be anything. A separator would not be needed if each Node ID were to be given the relevant number of leading zeros so that each portion were of identical length.

Allocating Security Keys

Each user who is allowed to access the leaf details is given a security key which identifies a node in the structure. This user is then able to access only those leaves which are attached to the designated node and any of its children, and is prevented from accessing any leaves that are attached to nodes which are either higher in the structure or which belong on other branches.

Assuming that the security code on each leaf record is called SECURITY_CODE, and that the current user's security key is held in a global variable called $$SECURITY_KEY this can be achieved by using code similar to the following before firing the <read> trigger of the leaf entity:

    security_code.LEAF/init = $$security_code
    retrieve/e "LEAF"

Note that $$SECURITY_CODE must be adjusted to include the profile characters <GOLD>* at the end in order to include any child nodes.

This means that anyone with a security key of "1" will be able to access all the nodes from 1 downwards. A key of "1-2" will give access to nodes 2,5 and 6, while a key of "1-2-5" will give access to node 5 only.

A simple technique, but I think quite effective.

Tony Marston
4th November, 2001

Back to TOP.