How To Find Security Roles Granted by Group Membership in AppScript

[Kaen01]

SBM 10.1.5.1

I have a function I made called CheckRole(). I can successfully find if a user has a directly-granted role (i.e. not through a group, but from actually checking a particular box in the Admin Portal->Users->[User]->Roles area).

I have been banging my head against the database trying to find how to determine if a user has a role granted by group membership. I have now absurd knowledge of the tables, except for the one I apparently need that will tie together TS_GROUPS, TS_MEMBERS, and TS_ROLES. I cannot find the tying column that would tell me that Group ID 28 has Roles 1, 4, 7, 12, 57, and 101. Or whatever.

Does anyone know this off-hand? I'm starting to go a little crazy and am beginning to dream in SQL.

[jmalin · Accepted solution]

Taking a look at the DB Schema doc, it looks like the TS_SECURITYCONTROLS table is the key. Rows can correspond to either Group or User assignments to roles.

TS_ SECURITYCONTROLS

This table contains the three-way relationship between a Subject, Permission and Context object. We are building a generic structure that can be expanded to cover a wide range of permissions. For Vail, Subjects will be Users or Groups, Permissions will be Roles and Contexts will be Projects. The “Granted” flag denotes whether the permission is being granted or disabled for the Context object (Project).

Here's a query that should show the assigned roles:

select

	ts_groups.ts_name as 'Group'

	,ts_users.ts_name as 'User'

	,ts_roles.ts_name as 'Role'

	,ts_projects.TS_NAME as 'Project'

from

	ts_securitycontrols

	left join TS_USERS on

		ts_users.TS_ID = TS_SECURITYCONTROLS.TS_SUBJECTID

		and TS_SECURITYCONTROLS.TS_SUBJECTTYPE = 1

	left join ts_groups on

		ts_groups.TS_ID = TS_SECURITYCONTROLS.TS_SUBJECTID

		and ts_securitycontrols.TS_SUBJECTTYPE = 2

	join TS_ROLES on

		ts_roles.TS_ID = TS_SECURITYCONTROLS.TS_PERMISSIONID

	join TS_PROJECTS on

		TS_SECURITYCONTROLS.TS_CONTEXTID = TS_PROJECTS.ts_id

[Kaen01]

SUBJECTTYPE is what I was missing. Thank you so much for pointing out the obvious.

My CheckRole() function obviously used this table, but I decided apparently to stop looking at columns before TS_SUBJECTTYPE.

Danke sehr / Takk fyrir / Thank you.

[Kaen01]

If anyone's interested, here's the AppScript function I came up with to check for a particular role in a particular project being granted, either through direct role assignment or through group membership:

NOTE: There's a couple commented lines in there, like the function declaration, if you wanted to pass in the Project and Solution names. I use string constants for those, defined in my 'header' file. You could use the alternate lines, though, if you wanted to pass your names into the function.

EDIT: Added Const lines

Const psconProjectsTable = "TS_PROJECTS"

Const psconRolesTable = "TS_ROLES"

Const psconSolutionsTable = "TS_SOLUTIONS"

Const psconSecurityTable = "TS_SECURITYCONTROLS"

Const psconGroupsTable = "TS_GROUPS"

Const psconMembersTable = "TS_MEMBERS"



Const psconProjectName = "Your Project Name Here"  '-- as it appears in the TS_PROJECTS table

Const psconSolutionName = "YOUR_SOLUTION_NAME" '-- as it appears in the TS_SOLUTIONS table



'================================================================================================================

'Function CheckRole( iUser, sPermissionName, sProjectName, sSolutionName )

Function CheckRole( iUser, sPermissionName )

	Dim objRoleCheck, objProject, objSolutions, objRoles, objGroups, objGroup, objMembers, objRolesTemp

	Dim sWhereClause, sProjectID, sRoleID, sContextID, sSolutionID, sGroupIDs

	Dim fGroup, fRole

	Dim	idGroup



	fGroup = False

	fRole = False

	sGroupIDs = ""



	Set objProject = Ext.CreateAppRecord( Ext.TableId( psconProjectsTable ) )

	Call objProject.ReadWithWhere("TS_NAME LIKE '" & psconProjectName & "'")

	'Call objProject.ReadWithWhere("TS_NAME LIKE '" & sProjectName & "'")

	sContextID = objProject.getId()



	Set objSolutions = Ext.CreateAppRecord( Ext.TableId( psconSolutionsTable ) )

	Call objSolutions.ReadWithWhere("TS_NAME LIKE '" & psconSolutionName & "'")

	'Call objSolutions.ReadWithWhere("TS_NAME LIKE '" & sSolutionName & "'")

	sSolutionID = objSolutions.getId()



	Set objRoles = Ext.CreateAppRecord( Ext.TableId( psconRolesTable ) )

	Call objRoles.ReadWithWhere("TS_NAME = '" & sPermissionName & "' AND TS_SOLUTIONID = " & sSolutionID )

	sRoleID = objRoles.getId()



	Set objGroups = Ext.CreateAppRecordList( Ext.TableId( psconSecurityTable ) )

	Call objGroups.ReadWithWhere( "TS_CONTEXTID = " & sContextID & " AND TS_PERMISSIONID = " & sRoleID &  " AND TS_SUBJECTTYPE = 2" )



	'-- check for roles granted through group membership

	For Each objGroup in objGroups

		Call objGroup.GetFieldValue( "SUBJECTID", idGroup )

		sGroupIDs = sGroupIDs & idGroup & ","

	Next



	If sGroupIDs <> "" Then



		If Right( sGroupIDs, 1 ) = "," Then sGroupIDs = Left( sGroupIDs, Len( sGroupIDs ) - 1 )



		Set objMembers = Ext.CreateAppRecordList( Ext.TableId( psconMembersTable ) )

		Call objMembers.ReadWithWhere( "TS_USERID = " & iUser & " AND TS_GROUPID IN ( " & sGroupIDs & " )" ) 

		

		If objMembers.Count() > 0 Then fGroup = True



	End If



	'-- check directly-granted roles (checked in the Roles section of User profile)

	sWhereClause = "TS_SUBJECTID = " & iUser & " AND TS_CONTEXTID = " & sContextID & " AND TS_PERMISSIONID = " & sRoleID & " AND TS_SUBJECTTYPE = 1"



	Set objRoleCheck = Ext.CreateAppRecord( Ext.TableId( psconSecurityTable ) )

	If objRoleCheck.ReadWithWhere( sWhereClause ) <> 0 Then fRole = True



	If fGroup Or fRole Then

		CheckRole = True

	Else

		CheckRole = False

	End If





End Function