Extracting the Core Access Assurance Suite ARM Catalog using MSSQL

Migrating the ARM Catalog from one Core AAS implementation to the other has been a major pain point for many customers. Sometimes, service providers will simply advise that the catalog be rebuilt from scratch in order to circumvent the intricacies of this task.

The need to extract the ARM catalog usually comes about when a clean install approach is used during a solution upgrade from a previous version of AAS  to newer version, e.g. upgrade from AAS 8.2, 8.3 to 8.4 and higher.

Below is a process for accomplishing this task.

Supported AAS version

This solution is tested and adapted for extracting ARM Catalog from AAS 8.2. There is little difference between version 8.2, 8.3 and 8.4, so it will work fine for those versions as well and with little modification it will work for version 8.5

Database Objects

The following database objects will be created on the Core AAS Database

  1.  – TempReturnTableList_Tag – Function to return the list of tags assigned to a Role
  2.  – TempReturnUniqueID – Function to return either the EmployeeID or Email address of a Profile record based on input parameter supplied to it.
  3.  – TempReturnTableList_RoleApprover_definition – Function to return the EmployeeID or Email address of a Profile record.
  4.  – TempReturnTableList_RoleApprover_Access – Function to return the EmployeeID or Email address of a Profile record.
  5.  – TempReturn_Accesscatalog – View to return the Access Catalog in a table format.

 

Once the objects have been created, launch the View “TempReturn_accesscatalog” to see the Access catalog in a tabular form which can be exported to a spreadsheet, cleaned up and imported into a new server using a custom bulk upload process.

Prerequisite

The Profile table must contain the email address or employee ID of user Profiles that are assigned as Owner of Roles or Approver of items within the Access Catalog.

Database Schema

CREATE FUNCTION [dbo].[TempReturnUniqueID](@ProfileUID nvarchar(50), @Attribute nvarchar(50))
RETURNS nvarchar(200)
AS
BEGIN
DECLARE @RetVal nvarchar(200)	
	IF (@Attribute ='Email')
		BEGIN
		SELECT Top 1 @RetVal= Email FROM dbo.[Profile] WHERE ProfileUID=RTRIM(LTRIM(@ProfileUID))
		END
	ELSE IF (@Attribute ='EmployeeID')
		BEGIN
		SELECT Top 1 @RetVal=EmployeeID FROM dbo.[Profile] WHERE ProfileUID=RTRIM(LTRIM(@ProfileUID))
		END
	ELSE
		BEGIN
		SELECT @RetVal=NULL
		END
		RETURN (@RetVal)
END

CREATE FUNCTION [dbo].[TempReturnTableList_Tag](@Role_key int)
RETURNS VARCHAR(max) AS
BEGIN
  DECLARE @List varchar(1000)
   SELECT @List = COALESCE(@List + '; ', '') + Tag.Name  from Tag   inner join  dbo.Role_Tag ON tag.Tag_key = Role_Tag.Tag_key WHERE Role_Tag.Role_key=@Role_key
   RETURN @List
END

CREATE FUNCTION [dbo].[TempReturnTableList_RoleApprover_Definition](@Role_key int, @Attribute nvarchar(50))
RETURNS VARCHAR(max) AS
BEGIN
  DECLARE @List varchar(1000)
   SELECT @List = COALESCE(@List + ', ', '') + dbo.TempReturnUniqueID(ProfileUID,@Attribute) from RoleApprover where dbo.RoleApprover.IsDefinitionApprover=1 and dbo.RoleApprover.role_key=@Role_key
   RETURN @List
END

CREATE FUNCTION [dbo].[TempReturnTableList_RoleApprover_Access](@Role_key int, @Attribute nvarchar(50))
RETURNS VARCHAR(max) AS
BEGIN
  DECLARE @List varchar(1000)
   SELECT @List = COALESCE(@List + ';', '') + dbo.TempReturnUniqueID(ProfileUID,@Attribute) from RoleApprover where dbo.RoleApprover.IsAccessApprover=1 and dbo.RoleApprover.role_key=@Role_key
   RETURN @List
END

CREATE VIEW [dbo].[TempReturn_AccessCatalog]
AS
SELECT     TOP (100) PERCENT dbo.Role.isActive,dbo.Role.Version, dbo.Role.Name AS RoleName, dbo.Role.Description AS RoleDescription, 
dbo.TempReturnUniqueID(dbo.Role.Owner, N'Email') AS Owner, 
dbo.TempReturnTableList_RoleApprover_Access(dbo.Role.Role_key, N'Email') AS AccessApprover, 
                      dbo.TempReturnTableList_RoleApprover_Definition(dbo.Role.Role_key, N'Email') AS DefinitionApprover, 
					  dbo.TempReturnTableList_Tag(dbo.Role.Role_key) AS TagName, 
                      dbo.Entitlement.BusinessName AS EntitlementName, dbo.EntitlementConfiguration.Description AS EntitlementNameDescription, 
                      dbo.Entitlement.Name AS AttributeName, dbo.EntitlementConfiguration.Value AS AttributeValue, dbo.Mapping_Target.TargetID, dbo.EntitlementConfiguration.isEditable, 
                      dbo.EntitlementConfiguration.SingleValueAttribute, dbo.EntitlementConfiguration.Automated, dbo.EntitlementConfiguration.ControlType, 
                      dbo.EntitlementConfiguration.ControlLabel, dbo.EntitlementConfiguration.ControlValues, dbo.EntitlementConfiguration.Required, dbo.EntitlementConfiguration.HelpText,
                       dbo.EntitlementConfiguration.UserVisible
FROM         dbo.Role INNER JOIN
                      dbo.Role_Entitlement ON dbo.Role.Role_key = dbo.Role_Entitlement.role_key RIGHT OUTER JOIN
                      dbo.Mapping_Target INNER JOIN
                      dbo.Entitlement INNER JOIN
                      dbo.EntitlementConfiguration ON dbo.Entitlement.DefaultEntitlementConfiguration_key = dbo.EntitlementConfiguration.EntitlementConfiguration_key INNER JOIN
                      dbo.Entitlement_Mapping_Target ON dbo.Entitlement.entitlement_key = dbo.Entitlement_Mapping_Target.entitlement_key ON 
                      dbo.Mapping_Target.ZTargetIDPk = dbo.Entitlement_Mapping_Target.ZTargetIDPk ON dbo.Role_Entitlement.entitlement_key = dbo.Entitlement.entitlement_key
WHERE dbo.Role.isActive = 1 ORDER BY RoleName, dbo.Role.Version
GO