Table – InstanceAccess

Purpose

To maintain row-level access control on records in the Instances table.

Definition

CREATE TABLE [dbo].[InstanceAccess](
	[InstanceGUID] [UNIQUEIDENTIFIER] NOT NULL,
	[UserGroupGUID] [UNIQUEIDENTIFIER] NOT NULL,
 CONSTRAINT [PK_InstanceAccess] PRIMARY KEY CLUSTERED 
(
	[InstanceGUID] ASC,
	[UserGroupGUID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Column Definitions

The InstanceGUID identifies the records which are granted access.

The UserGroupGUID identifies the InstanceGUID of the User or Group which are granted access.

Additional Indexes

We have found the following indexes on the Instances table to be of use in optimizing general database and user experience performance.

CREATE NONCLUSTERED INDEX [InstanceAccess_UserGroup] ON [dbo].[InstanceAccess]
(
	[UserGroupGUID] ASC,
	[InstanceGUID] ASC
)

Key Functionality

When defining the Form Definition for a Concept, the Optimization tab allows you to select either Ancestor or Instance for the “Check Access” field.

If “Check Access” is set to Instance, then the InstanceAccess table will contain the records that define which users and groups have access to this record. When a new instance is created, any InstanceAccess records from the parent are copied down for the new record.

The code generation for Get stored procedures will make an explicit check before returning a row:

SELECT *
FROM dbo.vw_ZGI_Customer I WITH (NOLOCK)
WHERE I.ParentGUID = @InstanceGUID
AND (EXISTS(SELECT *
  FROM dbo.InstanceAccess A WITH (NOLOCK)
  WHERE(A.InstanceGUID = I.InstanceGUID)
  AND A.UserGroupGUID = @UserGUID)
  OR EXISTS(SELECT *
    FROM dbo.InstanceAccess A WITH (NOLOCK)
    INNER JOIN dbo.UserGroups U  WITH (NOLOCK) ON A.UserGroupGUID = U.GroupGUID
    WHERE U.UserGUID = @UserGUID
    AND A.InstanceGUID = I.InstanceGUID))
ORDER BY I.InstanceName

If “Check Access” is set to Ancestor, then there should be no matching records for this record in the InstanceAccess table.

The code generation for Get stored procedures will not make an explicit request at the record level but will make a request to validate access to an ancestor:

EXECUTE @ReturnStatus = dbo.GI_ValidateReadAccess @SessionGUID, @UserGUID, @InstanceGUID
IF @ReturnStatus > 0
BEGIN
  EXECUTE @ReturnStatus = dbo.GI_LogEvent @UserGUID, 'dbo.Get_ZGI_GIInvoice - No Read Access', 1, NULL
  RAISERROR ('dbo.Get_ZGI_GIInvoice - No Read Access.', 16, 1)
  RETURN 100 + @ReturnStatus
END

SELECT *
FROM dbo.vw_ZGI_GIInvoice I WITH (NOLOCK)
WHERE I.ParentGUID = @InstanceGUID
ORDER BY I.InstanceName

When access to record is requested (using dbo.GI_ValidateReadAccess), a check is made starting at the Instance and working back through the Instance’s ancestors to find the first Instance with corresponding InstanceAccess records. These InstanceAccess records are then used to determine whether this user has access to the record.

Any attempt by the user experience to read an Instance should first make a call to dbo.GI_ValidateReadAccess to ensure that the user has access to the record.

The InstanceAccess table also has an optional trigger called InstanceAccessPull that is used to track changes to the InstanceAccess table in the InstanceChanges table. These changes can then be transferred to a target data storage as required.

CREATE OR ALTER TRIGGER [dbo].[InstanceAccessPull]
ON [dbo].[InstanceAccess] FOR INSERT, UPDATE, DELETE
AS 
BEGIN

	DECLARE @InstanceGUID AS UNIQUEIDENTIFIER

	DECLARE DeletedRecords CURSOR FOR 
	SELECT InstanceGUID FROM Deleted

	OPEN DeletedRecords

	FETCH NEXT FROM DeletedRecords INTO @InstanceGUID

	WHILE @@FETCH_STATUS = 0
	BEGIN
		EXECUTE dbo.GI_InstanceChanged @InstanceGUID, 'Access'
		FETCH NEXT FROM DeletedRecords INTO @InstanceGUID
	END

	CLOSE DeletedRecords;
	DEALLOCATE DeletedRecords;

	DECLARE InsertedRecords CURSOR FOR 
	SELECT InstanceGUID FROM Inserted

	OPEN InsertedRecords

	FETCH NEXT FROM InsertedRecords INTO @InstanceGUID

	WHILE @@FETCH_STATUS = 0
	BEGIN
		EXECUTE dbo.GI_InstanceChanged @InstanceGUID, 'Access'
		FETCH NEXT FROM InsertedRecords INTO @InstanceGUID
	END

	CLOSE InsertedRecords;
	DEALLOCATE InsertedRecords;

END