Implementing Organization Permissions in SQL Server – Basic Tables

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.