Create a Workaround Solution to Enable Audit Trail for Permission Changes in Nintex Admin

Topic

Permission changes in Nintex Admin do not currently have an audit trail feature. The following solution will help to address this problem. This solution currently works for all versions of RPA.

When permissions are deleted, the records are hard deleted from the database, leaving no trace of what was deleted. This becomes a challenge for customers who have a governance/compliance process or audit for system changes.
 

Instructions 

Notice:
This is a workaround! The steps below need to be repeated every time an installation/upgrade happens or the DBUpCli is executed as it will overwrite the changes made in this solution.

The following steps can only be performed using SQL Management Studio. If you are using HeidiSQL, you will NOT be able to implement the solution correctly.
 
Create a New Database Instance & Database table

1. It is important to create the following table in a different database instance from the RPA or Keycloak (RPA_authentication) database to prevent the table/data from being overwritten. Create a new database instance RPA_Data.

2. Ensure that the database user that is used by RPA server to connect to the database has either db_owner rights or minimally db_datareader & db_datawriter roles with EXECUTE permission to the RPA_Data instance. To grant EXECUTE permission, run the query “GRANT EXECUTE TO <db_username>” on the RPA_Data instance.

3. Create a table with the following SQL in the RPA_Data instance.

CREATE TABLE [dbo].[RPA_Audit_Permissions]( [No] [int] IDENTITY(1,1) NOT NULL, [PermissionType] [bigint] NOT NULL, [LibraryID] [bigint] NOT NULL, [CategoryID] [bigint] NOT NULL, [EntityID] [bigint] NOT NULL, [EntityType] [bigint] NOT NULL, [ProductType] [bigint] NOT NULL, [ApprovalType] [bigint] NULL, [CreateUser] nvarchar NULL, [CreateDate] [datetime] NULL, [IsDeleted] nchar NOT NULL,  CONSTRAINT [PK_RPA_Audit_Permissions] PRIMARY KEY CLUSTERED ( [No] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
Update Stored Procedures

1. Go to the RPA database instance and locate the Stored Procedures section under Programmability.

2. First locate the AddPermissions stored procedure. Right click and select Modify.

3. In the last sentence (i.e. SELECT statement), add a semi-colon (;) at the end. Next add the following line after the SELECT statement.

INSERT INTO RPA_Data.dbo.RPA_Audit_Permissions ([PermissionType], [LibraryID], [CategoryID], [EntityID], [EntityType], [ProductType], [ApprovalType], [CreateUser], [CreateDate], [IsDeleted]) VALUES (@PermissionType, @LibraryID, @CategoryID, @EntityID, @EntityType, @ProductType, @ApprovalType, @CreateUser, @CreateDate, 'N');
 

4. Click on Execute (or F5) to complete the changes to the stored procedure.
5. Next locate the DeletePermissions stored procedure. Right click and select Modify.

6. In the last sentence (i.e. DELETE statement), add a semi-colon (;) at the end. Next add the following line after the DELETE statement.

INSERT INTO RPA_Data.dbo.RPA_Audit_Permissions ([PermissionType], [LibraryID], [CategoryID], [EntityID], [EntityType], [ProductType], [ApprovalType], [CreateUser], [CreateDate], [IsDeleted]) VALUES (@Original_PermissionType, @Original_LibraryID, @Original_CategoryID, @Original_EntityID, @Original_EntityType, @Original_ProductType, @Original_ApprovalType, @Original_CreateUser, @Original_CreateDate, 'Y');
 

7. Click on Execute (or F5) to complete the changes to the stored procedure.
    

Testing the Solution

1. To test the solution, you must first ensure that Permissions are enabled on the platform. If permissions are not enabled, there will NOT be data in this solution.
2. Log in to Nintex Admin and make changes to permissions at both the library and catalog level.
3. After making changes, run the following SQL to retrieve the results.
    

I. Studio Library Permissions
DECLARE @num_Duration bigint; SET @num_Duration = 30; --(Change the duration (days) here as necessary) SELECT B.Name AS LibraryName, CASE WHEN A.PermissionType = 8 THEN 'Edit' WHEN A.PermissionType = 16 THEN 'Add' WHEN A.PermissionType = 128 THEN 'Add Rule' WHEN A.PermissionType = 256 THEN 'Delete Rule' WHEN A.PermissionType = 512 THEN 'Generate Reports' WHEN A.PermissionType = 2048 THEN 'Add Read-Data Advanced Commands' WHEN A.PermissionType = 4096 THEN 'Add Write-Data Advanced Commands' WHEN A.PermissionType = 8192 THEN 'Add Execute-Action Advanced Commands' ELSE 'NA' END AS Permission, E.GroupName, C.UserName, CASE WHEN A.ProductType = 1 THEN 'Robot' WHEN A.ProductType = 2 THEN 'Studio' ELSE 'NA' END AS ProductType, A.CreateUser AS LastUpdated, A.CreateDate AS LastUpdatedDate, CASE WHEN A.IsDeleted = 'Y' THEN 'Deleted' ELSE 'Added' END AS [Action] FROM RPA_Data.dbo.RPA_Audit_Permissions A, LeoScriptsLibraries B, LeoUsers C LEFT OUTER JOIN LeoPermissionsGroupsMembers D on C.UserID = D.GroupMemberID LEFT OUTER JOIN LeoPermissionsGroups E ON D.GroupID = E.GroupID WHERE A.LibraryID = B.LibraryID AND A.EntityType = 1 AND A.EntityID = C.UserID AND A.LibraryID <> -1 AND A.CreateDate >= DATEADD(DD,-@num_Duration,GETDATE()) UNION ALL SELECT B.Name AS LibraryName, CASE WHEN A.PermissionType = 8 THEN 'Edit' WHEN A.PermissionType = 16 THEN 'Add' WHEN A.PermissionType = 128 THEN 'Add Rule' WHEN A.PermissionType = 256 THEN 'Delete Rule' WHEN A.PermissionType = 512 THEN 'Generate Reports' WHEN A.PermissionType = 2048 THEN 'Add Read-Data Advanced Commands' WHEN A.PermissionType = 4096 THEN 'Add Write-Data Advanced Commands' WHEN A.PermissionType = 8192 THEN 'Add Execute-Action Advanced Commands' ELSE 'NA' END AS Permission, C.GroupName, NULL AS UserName, CASE WHEN A.ProductType = 1 THEN 'Robot' WHEN A.ProductType = 2 THEN 'Studio' ELSE 'NA' END AS ProductType, A.CreateUser AS LastUpdated, A.CreateDate AS LastUpdatedDate, CASE WHEN A.IsDeleted = 'Y' THEN 'Deleted' ELSE 'Added' END AS [Action] FROM RPA_Data.dbo.RPA_Audit_Permissions A, LeoScriptsLibraries B, LeoPermissionsGroups C WHERE A.LibraryID = B.LibraryID AND A.EntityType = 2 AND A.EntityID = C.GroupID AND A.LibraryID <> -1 AND A.CreateDate >= DATEADD(DD,-@num_Duration,GETDATE())
 
II. User Catalog Permissions
DECLARE @num_Duration bigint; SET @num_Duration = 30; --(Change the duration (days) here as necessary) SELECT C.Name AS LibraryName, B.Name AS CatalogName, B.FullPathNames, CASE WHEN A.PermissionType = 1 THEN 'Do It' WHEN A.PermissionType = 2 THEN 'Guide Me' WHEN A.PermissionType = 4 THEN 'View' WHEN A.PermissionType = 8 THEN 'Edit' WHEN A.PermissionType = 16 THEN 'Add' WHEN A.PermissionType = 32 THEN 'Delete' WHEN A.PermissionType = 64 THEN 'Publish' ELSE 'NA' END AS Permission, F.GroupName, D.UserName, CASE WHEN A.ProductType = 1 THEN 'Robot' WHEN A.ProductType = 2 THEN 'Studio' ELSE 'NA' END AS ProductType, A.CreateUser AS LastUpdated, A.CreateDate AS LastUpdatedDate, CASE WHEN A.IsDeleted = 'Y' THEN 'Deleted' ELSE 'Added' END AS [Action] FROM RPA_Data.dbo.RPA_Audit_Permissions A, LeoLibrariesCategoriesTree B, LeoScriptsLibraries C, LeoUsers D LEFT OUTER JOIN LeoPermissionsGroupsMembers E on D.UserID = E.GroupMemberID LEFT OUTER JOIN LeoPermissionsGroups F ON E.GroupID = F.GroupID WHERE A.CategoryID = B.CategoryID AND B.LibraryID = C.LibraryID AND A.EntityType = 1 AND A.EntityID = D.UserID AND A.LibraryID = -1 AND A.CreateDate >= DATEADD(DD,-@num_Duration,GETDATE()) UNION ALL SELECT C.Name AS LibraryName, B.Name AS CatalogName, B.FullPathNames, CASE WHEN A.PermissionType = 1 THEN 'Do It' WHEN A.PermissionType = 2 THEN 'Guide Me' WHEN A.PermissionType = 4 THEN 'View' WHEN A.PermissionType = 8 THEN 'Edit' WHEN A.PermissionType = 16 THEN 'Add' WHEN A.PermissionType = 32 THEN 'Delete' WHEN A.PermissionType = 64 THEN 'Publish' ELSE 'NA' END AS Permission, D.GroupName, NULL AS UserName, CASE WHEN A.ProductType = 1 THEN 'Robot' WHEN A.ProductType = 2 THEN 'Studio' ELSE 'NA' END AS ProductType, A.CreateUser AS LastUpdated, A.CreateDate AS LastUpdatedDate, CASE WHEN A.IsDeleted = 'Y' THEN 'Deleted' ELSE 'Added' END AS [Action] FROM RPA_Data.dbo.RPA_Audit_Permissions A, LeoLibrariesCategoriesTree B, LeoScriptsLibraries C, LeoPermissionsGroups D WHERE A.CategoryID = B.CategoryID AND B.LibraryID = C.LibraryID AND A.EntityType = 2 AND A.EntityID = D.GroupID AND A.LibraryID = -1 AND A.CreateDate >= DATEADD(DD,-@num_Duration,GETDATE())