Big news! The community will be moving to a new platform April 21. Read more.
Big news! The community will be moving to a new platform April 21. Read more.
Absent Member.
Absent Member.
4372 views

How To Find Security Roles Granted by Group Membership in AppScript

Jump to solution
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.
0 Likes
1 Solution

Accepted Solutions
Absent Member.
Absent Member.
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

View solution in original post

3 Replies
Absent Member.
Absent Member.
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

View solution in original post

Absent Member.
Absent Member.
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.
0 Likes
Absent Member.
Absent Member.
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
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.