Help with Oracle SQL to yank select list of JIRA 7 users from array of groups

[Brad Taplin]

Hello. We need to remove now-inactive users in JIRA 7.10 from certain groups, e.g. jira-users, to effectively remove licenses. We can then re-enable the accounts so they may request fresh access as needed via JIRA Service Desk, which runs atop JIRA.

I have this code that seems to ID the accounts:

SELECT DISTINCT u.user_name, u.lower_email_address
FROM cwd_user u
JOIN cwd_membership m
ON u.id = m.child_id
AND u.directory_id = m.directory_id
WHERE m.lower_parent_name in ('jira-core-users', 'jira-users', 'jira-users-all','jira-servicedesk-users','confluence-users')
AND u.directory_id = 3
AND u.active = '0'
ORDER BY user_name;

The trick, assuming this correctly IDs who (a) is inactive and (b) is in of one or more of these groups, is how to bulk-remove them from those groups but not delete them. Once that is done I can easily flip em all back with a bit of SQL we know.

Nic, I know this isn't ideal, to hack the SQL, and that we have to restart to make it effective. I am working this on a test server first to make sure.

[Daniel Eads · Accepted solution]

Hey Brad!

Firstly, Andrew's answer is awesome for doing what you've asked. Huzzah!

However, I read your post a couple times and came up with another takeaway. Inactive users don't actually count against your license, even if they're still in those groups! If you're seeing different behavior, that sounds like a bug that we need to track down.

As an aside, I wrote an article earlier this year with some tips for cleaning up inactive users. Happy to help if you've got more questions!

Cheers,
Daniel

[Brad Taplin]

Daniel, good eye and thank you for that thought.

I may not have made clear why we may go this route. After removing licensing for all disabled users, we will re-enable them. Then they can use JIRA Service Desk to request a a license restore or just submit basic trouble tickets.

As we remove licensing for accounts not used in X days, both to stay below a cap and for security, some users come back 3-12 months later and need rights flipped back on, fast.

If we only had a few hundred users this would be no big deal, but with enterprise levels the drive-bys add up. We want to funnel all access requests through a JSD portal. The can't get there if disabled, with JSD running as an add-on atop the same JIRA.

BTW I note the caveat that this may be problematic if depending on AD groups. That is another challenge we may have to deal with, but not quite yet for this instance.

[Daniel Eads]

Ah - thanks for that extra context! Andrew's approach is what you need then, and hopefully the extra info will dissuade anyone from doing this unless they really need to.

[Brad Taplin]

Daniel, nice Spring Cleaning article. :) We have used one or two of those methods.

BTW my boss found an alternate way via the API. Not sure whether we can share that now, but it's probably safer than a direct SQL hack.

[Andy Heinzer]

Your existing query does appear to correctly identify these inactive user accounts that exist as members of those groups.   These group memberships are stored in the cwd_membership table.   Each membership is a unique entry in that table.  

To test this out, I turned on the SQL logging in Jira and then using the UI in Jira, I went to my inactive user, selected edit users groups, and then removed him from that group.  In my logs I found only one delete statement in sql where it was a

delete from cwd_membership where id='10300';

In this case that id number of 10300 was the record in that table.  I was able to tweak this existing query further to delete based off its results.   You can first just add that id value into your existing query to see its value:

SELECT u.user_name, u.lower_email_address, m.id
FROM cwd_user u
JOIN cwd_membership m
ON u.id = m.child_id
AND u.directory_id = m.directory_id
WHERE m.lower_parent_name in ('jira-core-users', 'jira-software-users', 'jira-users-all','jira-servicedesk-users','confluence-users')
AND u.directory_id = 1
AND u.active = '0'
ORDER BY user_name;

This would give you back a list of all the users, but in this case each user could belong to multiple groups, so we don't really want a select distinct result, we need all the results to be able to see all the group memberships here. So I removed the 'Distinct' term in the select.   I also then used that select statement as a qualifier in my delete statement.  I know this works in postgres, I'm not sure if the syntax is different for Oracle, it very well might be:

delete from cwd_membership where id in (select m.id from cwd_user u
JOIN cwd_membership m
ON u.id = m.child_id
AND u.directory_id = m.directory_id
WHERE m.lower_parent_name in ('jira-core-users', 'jira-software-users', 'jira-users-all','jira-servicedesk-users','confluence-users')
AND u.directory_id = 1
AND u.active = '0'
ORDER BY user_name)

I also tweaked the group 'jira-users' into 'jira-software-users' for my own system.   This appears to remove only the group memberships for inactive users of those specific group names.  And not the account itself, or other group memberships.

Granted, I would recommend that you stop Jira and have a backup before running a sql delete statement.  I'm sure you already know this is not really supported, but for other users that run across this kind of modification, it's very important to state that directly modifying the Jira database can cause unexpected behavior and more-so when Jira is running.

The other major thing to note is that if your Jira is using Crowd or an LDAP/AD user directory (ie any external user directory), it's possible that you could delete these memberships via SQL successfully, but the next time Jira syncs with one of these external directories (on startup or regular interval) your users group memberships would just get updated again, and replaced in the database once more.    Only the Jira internal user directory should be in use for these accounts if you are going to try to hack away at the SQL here successfully long term.

[Brad Taplin]

Thanks, Andrew! Will test sometime soon.