Auditing ITA users' and groups' permissions

Products

• IT Analytics

Issue/Introduction

Is there a method to compile a list of all IT Analytics (ITA) users, groups, and their permissions via the database?

Environment

Release : 2.9.1

Component : Security

Resolution

For older versions of ITA that did not include Portal Groups, run the following query in SQL Server Management Studio (SSMS):

USE ITAnalytics;
GO
SELECT PortalUserId AS [ID],
  CredentialTypeId,
  Username AS [Name],
  NULL AS Identifier,
  'User' AS EntityType,
  ASDBAuthenticationTypeId,
  ASDBCredentialId,
  Administrator,
  KPIManager,
  ViewsManager,
  Preferences
FROM [dbo].[PortalUsers] WITH (NOLOCK)
;

For versions that support Portal Groups, run this query in SSMS:

USE ITAnalytics;
GO
SELECT PortalUserId AS [ID],
  CredentialTypeId,
  Username AS [Name],
  NULL AS Identifier,
  'User' AS EntityType,
  ASDBAuthenticationTypeId,
  ASDBCredentialId,
  Administrator,
  KPIManager,
  ViewsManager,
  Preferences
FROM [dbo].[PortalUsers] WITH (NOLOCK)

UNION ALL

SELECT PortalGroupId,
  CredentialTypeId,
  GroupName,
  GroupIdentifier,
  'Group' AS EntityType,
  ASDBAuthenticationTypeId,
  ASDBCredentialId,
  Administrator,
  KPIManager,
  ViewsManager,
  Preferences
FROM [dbo].[PortalGroups] WITH (NOLOCK)
;

Note that permissions are inherited via Active Directory (AD) group membership, so any AD user assigned to an AD group that has been granted access to ITA implicitly possesses the privileges of that group even if the user in question has never accessed ITA and thus has no corresponding entry in the ITA database.