How to use SQL on ServiceManager DB to check User Permissions from User Roles

Conner_Wood

Hi all,

I am completely stuck regarding having a user/group and generating a list of all roles they are in and then seeing if they are part of certain "Operations"

SELECT PO.[ProfileOperationId]
      ,PO.[ProfileId]
      ,UR.[UserRoleId]
      ,P.[ProfileName]
      ,UR.[UserRoleName]
      ,O.[OperationName]
      ,O.[OperationNumericId]
FROM [ServiceManager].[dbo].[ProfileOperation] PO

JOIN [ServiceManager].[dbo].[Operation] O
ON O.OperationId = PO.OperationId

JOIN [ServiceManager].[dbo].[Profile] P
ON P.[ProfileId] = PO.[ProfileId]

JOIN [ServiceManager].[dbo].[UserRole] UR
ON UR.[ProfileId] = P.[ProfileId]

WHERE O.[OperationName] IN (
                              'User_IsAdministrator__Check'
                             ,'User_IsIncidentResolver__Check'
			     ,'User_IsServiceRequestAnalyst__Check'
                             ,'User_IsChangeManager__Check'
                             ,'User_IsProblemAnalyst__Check'
                             ,'User_IsReleaseManager__Check'
                           )
  
ORDER BY O.[OperationNumericId]

This gives me a piece of the information I require as this allows me to have all the UserRoleIds or ProfileIds to match against a generated list from a user/group.

However when it comes to getting the User Roles for a particular user, I query the UserRole table which seems like the right thing to do, yet all the rows returned for a user point to some "UserRoleImplied~~~~" prefixed User Role.

So naturally I've been joining all sorts of tables together but I still can't figure out what the following tables do and if I need them somehow and how to use them....:

1. ProfileOperationImplicitScope
2. UserRoleUserAssignment (It's got a ScopeId column)
   
3. UserRoleGroupType
4. UserRoleScopeHandle (It's got a ScopeId column)
5. UserRoleScopeView

The farthest I've gotten is getting some roles from the [ProfileOperationImplicitScope] table, but if the user is in the Administrators Role in SCSM, I cannot seem to find out how to query it.

Ex.  In User Role "Administrators" there is "SCSM Service Account" which gives me UserId of "21002" from the [UserRoleUser] table.  This account is only a member of the "Administrators" User Role in the SCSM Console. On my first join to [Profile] I only get back 'ImpliedUserPreference'.  I join on the same column names and I join the tables as follows.

[UserRoleUserAssignment] --> [UserRole] --> [Profile] --> [ProfileOperationImplicitScope] --> [ProfileOperation] --> [Profile]

The list of Profiles that I get are:

• ActivityImplementer
  
• AdvancedOperator
• Author
• ChangeInitiator
• ChangeManager
• EndUser
• IncidentResolver
• Operator
• ProblemAnalyst
• ReadOnlyOperator
• ReleaseManager
• ServiceRequestAnalyst
• Workflow

However it's not showing the Administrator Profile which I need it to be able to do, so frustrating!!!!!!

I feel it shouldn't have to be this hard but I can't really figure out the solution.  I figured Cireson is most familiar with User Roles and perhaps can shed some light on this.

Conner_Wood

Looked through all Functions and Stored Procedures, only thing remotely helpful regarding [UserRole] was:

EXEC [ServiceManager].[dbo].[p_UserRoleSelectAllInfo]
	 @UserRoleId = '597F9D98-356F-4186-8712-4F020F2D98B4', -- UserRoleId of Administrators
	 @LanguageCode1 = N'ENU';

But none of the results returned involved the members.....

Found some other tables partially thanks to Christopher Mank...

[ServiceManager].[dbo].[AzMan_AzRoleAssignment] --> [Name] column can be linked to [UserRole].[UserRoleId]

[ServiceManager].[dbo].[AzMan_Role_SIDMember] --> [RoleID] column can be linked to [AzMan_AzRoleAssignment].[ID]


Using this knowledge it appears I should be able to search against my first query returning valid user roles I'm looking for, and retrieve the members Hexadecimal-SID for the corresponding UserRoles.

There's a Stored Procedures that l can use:

DECLARE	@Return int;
EXEC	[ServiceManager].[dbo].[AzMan_SPS_Enum_AzRoleAssignment_SIDMembers]
		@Return = @Return OUTPUT,
		@RoleID = 2 -- UserRoleId of Administrators via [AzMan_AzRoleAssignment].[ID]

Sadly it still returns Hexidecimal-SID instead of in the standardized SID notation, but I have all the pieces I need now.

Conner_Wood

I should mention that if you're using C# like me, you make a byte array from the sid and add it to the SQL statement via the SQL Parameters feature.  You can verify your C# byte array is the same as the displayed SQL varbinary hex string when you query the table in SSMS via the following C# function:

private string GetHexadecimalFormatSID(SecurityIdentifier sid, string hex_prefix = "0x")
{
	//Convert the SID to its binary form
	byte[] bSID = new byte[sid.BinaryLength];
	sid.GetBinaryForm(bSID, 0);

	//Loop through all bytes and convert to hexadecimal values
	StringBuilder hex = new StringBuilder(bSID.Length * 2);
	foreach (byte b in bSID)
	{
		hex.AppendFormat("{0:X2}", b); //X2 formats a byte to a hex value string.
	}
	return hex_prefix + hex.ToString();
}

P.S.  If you want to do a quick SQL search using the VarBinary MemberSID, you can do the following:

SELECT [RoleID]
      ,[MemberSID]
FROM [ServiceManager].[dbo].[AzMan_Role_SIDMember]
WHERE MemberSID = CONVERT(varbinary(max),'0x010100000000000512000000', 1)
ORDER BY RoleID

Conner_Wood

@merlenette_jones - Thanks, it's a lot of effort!

My grand purpose was to see if the user should be able to see private comments on a particular Ticket Type, so basically be marked as an Analyst for said Ticket Type.

Being that ServiceManager is undocumented to this extent makes sense, but I was hoping there would be an easy way to pass a user SID and get a list of all user roles including any user roles that the user is an indirect member of if a group is added instead of each individual account.  However I need to pass the the users AD Group SIDs as well as there appears to be no such Stored Procedure or Function to do all that.  I have resorted to using the extremely mighty and powerful '#tempTable' feature in SQL from C#.

Details: I get the user SID Bytes and the AD DirectoryEntry TokenGroups which has a list of AD Security Group SID Bytes and make that a data table to bulk copy into a created temp table.  Then I select [RoleID] from [dbo].[AzMan_Role_SIDMember] that is JOINED to the TempTable ON [MemberSID].

I then determine if they have a particular role id present, and that's how I know if they have a permission.  Obviously there's other things I could use this for besides Private Comments, like allowing a different view to be shown based on being in a custom created role but for now, this is what I was after.

Conner_Wood

To put it simply, I need to know how SCSM shows the users/groups of the User Role from the ServiceManager database in the SCSM Console.  Somehow it's being stored in the database, I just need to know where and how.

I know Travis Wright had contributed a few things for User Roles, so I'm researching to see if I can learn from it:

• Viewing a User’s Service Manager (SCSM) Permissions Using PowerShell
• Service Manager (SCSM) User Role PowerShell Scripts

I have to admit this is far more difficult than I thought it would be for something as simple as showing the members in a user role.

Conner_Wood

Looked through all Functions and Stored Procedures, only thing remotely helpful regarding [UserRole] was:

EXEC [ServiceManager].[dbo].[p_UserRoleSelectAllInfo]
	 @UserRoleId = '597F9D98-356F-4186-8712-4F020F2D98B4', -- UserRoleId of Administrators
	 @LanguageCode1 = N'ENU';

But none of the results returned involved the members.....

Found some other tables partially thanks to Christopher Mank...

[ServiceManager].[dbo].[AzMan_AzRoleAssignment] --> [Name] column can be linked to [UserRole].[UserRoleId]

[ServiceManager].[dbo].[AzMan_Role_SIDMember] --> [RoleID] column can be linked to [AzMan_AzRoleAssignment].[ID]


Using this knowledge it appears I should be able to search against my first query returning valid user roles I'm looking for, and retrieve the members Hexadecimal-SID for the corresponding UserRoles.

There's a Stored Procedures that l can use:

DECLARE	@Return int;
EXEC	[ServiceManager].[dbo].[AzMan_SPS_Enum_AzRoleAssignment_SIDMembers]
		@Return = @Return OUTPUT,
		@RoleID = 2 -- UserRoleId of Administrators via [AzMan_AzRoleAssignment].[ID]

Sadly it still returns Hexidecimal-SID instead of in the standardized SID notation, but I have all the pieces I need now.

Conner_Wood

I should mention that if you're using C# like me, you make a byte array from the sid and add it to the SQL statement via the SQL Parameters feature.  You can verify your C# byte array is the same as the displayed SQL varbinary hex string when you query the table in SSMS via the following C# function:

private string GetHexadecimalFormatSID(SecurityIdentifier sid, string hex_prefix = "0x")
{
	//Convert the SID to its binary form
	byte[] bSID = new byte[sid.BinaryLength];
	sid.GetBinaryForm(bSID, 0);

	//Loop through all bytes and convert to hexadecimal values
	StringBuilder hex = new StringBuilder(bSID.Length * 2);
	foreach (byte b in bSID)
	{
		hex.AppendFormat("{0:X2}", b); //X2 formats a byte to a hex value string.
	}
	return hex_prefix + hex.ToString();
}

P.S.  If you want to do a quick SQL search using the VarBinary MemberSID, you can do the following:

SELECT [RoleID]
      ,[MemberSID]
FROM [ServiceManager].[dbo].[AzMan_Role_SIDMember]
WHERE MemberSID = CONVERT(varbinary(max),'0x010100000000000512000000', 1)
ORDER BY RoleID

merlenette_jones

Hello Conner,

I see you are putting a lot of work into this. I'm quite impressed. If I understand you correctly, you are trying to figure out the schema of how the user to user-role relationship is stored in the SCSM DB?

Can you elaborate on your purpose for this?

Merle

Conner_Wood

@merlenette_jones - Thanks, it's a lot of effort!

My grand purpose was to see if the user should be able to see private comments on a particular Ticket Type, so basically be marked as an Analyst for said Ticket Type.

Being that ServiceManager is undocumented to this extent makes sense, but I was hoping there would be an easy way to pass a user SID and get a list of all user roles including any user roles that the user is an indirect member of if a group is added instead of each individual account.  However I need to pass the the users AD Group SIDs as well as there appears to be no such Stored Procedure or Function to do all that.  I have resorted to using the extremely mighty and powerful '#tempTable' feature in SQL from C#.

Details: I get the user SID Bytes and the AD DirectoryEntry TokenGroups which has a list of AD Security Group SID Bytes and make that a data table to bulk copy into a created temp table.  Then I select [RoleID] from [dbo].[AzMan_Role_SIDMember] that is JOINED to the TempTable ON [MemberSID].

I then determine if they have a particular role id present, and that's how I know if they have a permission.  Obviously there's other things I could use this for besides Private Comments, like allowing a different view to be shown based on being in a custom created role but for now, this is what I was after.