The diagram below shows our starting tables (which will evolve).
Fine level Access Rights are grouped into Roles through [RoleAccess]. A user is given a role on an organization unit in a specific hierarchical tree through [UserOrganizationRole].
The Organization Tree are kept correct by triggers that check any new insert against the allowed patterns stored in OrganizationTypeHierarchy. This will be our first piece of real TSQL
CREATE TRIGGER tr_OrganizationTree_Upsert ON OrganizationTree AFTER INSERT,UPDATE AS BEGIN SET NOCOUNT ON; IF (Select Count(1) FROM OrganizationTree INSERTED) <> (SELECT Count(1) FROM OrganizationTree INSERTED JOIN Organization Parent ON ParentOrganizationID=Parent.OrganizationID JOIN Organization Child ON Inserted.OrganizationID=Child.OrganizationID JOIN OrganizationTypeHierarchy ON Parent.OrganizationTypeID=ParentOrganizationTypeID AND Child.OrganizationTypeID=OrganizationTypeHierarchy.OrganizationTypeID AND OrganizationTypeHierarchy.TreeID=Inserted.TreeID) BEGIN RAISERROR('Organization Tree upsert is invalid',16,1) ROLLBACK END END GO
What we are doing is taking the parent/child organizational units and looking up their types. We then see if these types of organizations may be parent/child in the specific tree.
At the top of the trees, we have the ParentType and ChildTypes being the same which allows the top of the organization tree to be an organizational unit that is it’s own parent. This approach is actually helpful to locate hierarchical roots with OrganizationId=ParentOrganizationID.
Our first modification will be to RoleTypeDef.
- We will add a Bit indicating if the Role applies to:
- the Organizational Unit OR
- the Organizational Unit and all of it’s children in the specific tree.
- We will assume that we will have < 127 different Access Rights (usually enough for most practical applications) and create two BIT MASKED columns of 64 bits which will be done as a BIGINT (instead of VARBINARY etc) because it is easier to manipulate in TSQL.
So the revised table is:
What we need to do next is update the bitmasks whenever a change happens in RoleAccess table. to do this we need to use a little function to convert our AccessRightID to a bit mask.
CREATE FUNCTION GetBitMask( @bitposition SMALLINT) RETURNS BIGINT AS BEGIN DECLARE @results BIGINT SET @results = 1 WHILE @bitposition > 0 BEGIN SET @results = @results * 2 SET @bitposition = @bitposition -1 END RETURN @results END
We now add equivalent to AccessRightDef
and add a trigger to update these two new columns from the value in AccessRightID.
CREATE TRIGGER [TR_AccessRight_Insert] ON [AccessRightDef] AFTER INSERT AS BEGIN SET NOCOUNT ON; UPDATE AccessRightDef SET BitMask= dbo.GetBitMask( (AccessRightID % 63)), MaskNo=AccessRightID/63 WHERE AccessRightID IN (SELECT AccessRightID FROM INSERTED) END
We calculate the bitmask once per AccessRight for performance gain.
The next trigger recalculates all Roles Bit Masks whenever there is a change. Since Roles tend to change very slowly – there should be little performance impact.
CREATE TRIGGER [TR_RoleAccess_Upsert] ON [RoleAccess] AFTER INSERT,UPDATE,DELETE AS BEGIN SET NOCOUNT ON; UPDATE RoleTypeDef SET BitMask1=RevBitMask FROM (SELECT RoleTypeID AS RID, SUM(ARD.BitMask) AS RevBitMask FROM AccessRightDef ARD JOIN RoleAccess RA ON ARD.AccessRightID = RA.AccessRightID WHERE MaskNo=0 GROUP BY RoleTypeID) Revised JOIN RoleTypeDef ON RID = RoleTypeDef.RoleTypeID UPDATE RoleTypeDef SET BitMask2=RevBitMask FROM (SELECT RoleTypeID AS RID, SUM(ARD.BitMask) AS RevBitMask FROM AccessRightDef ARD JOIN RoleAccess RA ON ARD.AccessRightID = RA.AccessRightID WHERE MaskNo=1 GROUP BY RoleTypeID) Revised JOIN RoleTypeDef ON RID = RoleTypeDef.RoleTypeID END
That’s it for today. We now have Roles that have bit masks of access rights (we have two columns for bitmasks but can easily add more) that are automatically maintained by triggers.