Effective role member

[Kerasit]

Hi.

I am sorely missing a database view table which in unison with a stored procedure, can show the always actual combined effective role memberships of users. In a script/programming I would usually do this:

function RoleChild(rolekey){
  return sqlQuery('SELECT childrolekey FROM childroles WHERE parentrolekey = ' + rolekey);
}
function effectiveRoleMembner(userkey){
  myArr = sqlQuery('SELECT rolekey FROM role_user_account WHERE userkey = ' + userkey);
  myRoles = [];
  for(i = 0; i <= myArr.length; i += 1){
    myRoles.push(myArr[i]);
    x = RoleChild(myArr[i]);
    while(x){
      myRoles.push(x)
      x = RoleChild(x)
    }
  }
}
effectiveRoleMember(userKey);

Use case: We are maintaining a user property based on role custom properties, in a concatenated format (comma delimited). However this uses the role_user_account table to find the membership roles for the specific user, and this table does not return enherited role memberships. So we have had to do some complex SQL with several LEFT joins on the childroles table. However this has the limitation, that we need to hard-fix the amount of enheritance levels we would like to support, as MySQL does not support arbitrary queries.
There is two possible ways of solving this (if purely MYSQL):

1:
Allow only a certain amount of enheritance levels, for example 3.
Pros: Can be achieved with the current features.
Cons: Is resource heavy.

2:
Create a stored procedure and combine that with a case based query for "looping" so that as long as stored procedure does not return NULL, add returned rolekey (childrolekey) and call stored procedure again with the childrolekey as the parentrolekey parameter. Continue until all results from role_user_account values is iterated and all childs for each of those as well. Then clean concatenated string so only unique values are present.
Pros: Simple SQL query. Resource heavy only for users with many nested roles.
Cons: Not many.

Option 2 is not really an option as it is not supported by Saviynt to create own Stored Procedures. However I actually think this should have been a native product feature.

Do you have any other idea? Remember that this has to be used in either connector or customqueries.

[rushikeshvartak]

What are you trying to achieve here ?

[Kerasit]

Example:

Roles:
Rolekey 1
Enterprise
Customproperty1 = Birthright
Customproperty2 = Null

Enterprise
Rolekey 2
Customproperty1 = Birthright
Customproperty2 = null

Enterprise
Rolekey 3
Customproperty1 = Facility
Customproperty2 = Door1

Enterprise
Rolekey 4
Customproperty1 = Facility
Customproperty2 = Door2

Role Hierarchy:
Parent - Child
1 - 4
2 - 3

Identity:
Direct role memberships:
Rolekey 1
Rolekey 3
Customproperty60 = Door1, Door2

I try to maintaine a multivalue string delimited by comma, with values from Customproperty2 of all the roles effectively assigned to Identities. The above is the desired result. This is achieved by simply joining the table roles with the role_user_account table. However this table ONLY provides the direct assignments, hence in order to achieve the above, I will need to add iteration of the childroles tables as well. As my example here is very simple and only contains 1 "level" of inheritance, then in truth, I have no idea if I at one point will end up having more than one "level" of inheritance:

RoleA (Assigned to User) -> RoleB (This one potentially has the Customproperty I need to add) -> RoleC (No value if interest) -> RoleD (this one has the Customproperty value I need).

But forget my own usecase. Look at this from both a pragmatical as well as from a compliance perspective, then I am surprised that there is no feature in the database layer which can provide you an "easy" data set of Users to effective role assignments. Not only will it make troubleshooting Waaaay easier, but also reporting for use with analytics, would this make sense.

[Kerasit]

I must then conclude that Saviynt does not have a SQL table or possible way of retrieving a dataset of effective role assignments for each user in the system. This is a pity, and very sad news. 😞