<!DOCTYPE html>
<html data-lt-installed="true">
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body style="padding-bottom: 1px;">
<p>Hello everyone,</p>
<p>I have a somewhat curious situation:</p>
<p>Some assignments don't seem to be deleted correctly if a user is
removed from a role.<br>
If this happens, the user is still shown as a role member in the
"Members" tab of the role details.<br>
However, the role no longer shows up in the "Assignments>Roles"
tab of the user details view.</p>
<p>Looking at the database I found some assignments in the
m_assignment table, that don't have a corresponding entry in the
m_ref_role_membership table.<br>
Maybe this is normal and I'm on the wrong track, as I'm not overly
familiar with the midPoint database schema, but this seems odd to
me.<br>
Here is the SQL query I ran to get the mentioned users and roles:</p>
<p>WITH user_role_mapping AS (<br>
SELECT <br>
owneroid AS userid,<br>
targetreftargetoid AS roleid <br>
FROM <br>
m_assignment <br>
WHERE <br>
owneroid NOT IN (<br>
SELECT <br>
owneroid <br>
FROM <br>
m_ref_role_membership <br>
WHERE <br>
targetoid = m_assignment.targetreftargetoid<br>
) <br>
AND ownertype = 'USER' <br>
AND targetreftargettype = 'ROLE'<br>
)<br>
<br>
SELECT <br>
m_user.nameorig AS username,<br>
m_role.nameorig AS groupname<br>
FROM <br>
user_role_mapping <br>
JOIN <br>
m_user ON user_role_mapping.userid = m_user.oid<br>
JOIN <br>
m_role ON user_role_mapping.roleid = m_role.oid;</p>
<p><br>
</p>
<p>From what I can tell after arbitrarily checking some of these
results in the GUI, these are indeed the affected user - role
pairs.</p>
<p>Has someone ever seen something similar?<br>
If so how did this happen, and how do I fix it?<br>
I would be very grateful for any pointers.<br>
I'm running midPoint 4.8.2 in case this is relevant.<br>
</p>
<p>Thanks, and kind regards</p>
<p>Sven<br>
</p>
<pre class="moz-signature" cols="72">
--
Sven Feyerabend
Referent für IT-Betreuung
stuvus – Studierendenvertretung Universität Stuttgart
Pfaffenwaldring 5c
70569 Stuttgart</pre>
</body>
<lt-container></lt-container>
</html>