Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Safely removal of shadow users

Until now, we have used Jira Internal Directory for user management of all users.

I did now add a Microsoft Active Directory (Read Only, with Local Groups), and given that the highest priority.

This directory contain all company users. The usernames are the same as in the internal directory.

Now I want to remove all internal users that now have became shadow duplicates of those in the new Microsoft AD Directory.

However, I don't want to remove/disable the internal directory, because that contain all customer users, admin user, and some inbox accounts. Those shall remain internal.

I suppose I need to run a delete query on the db, and I need advise in order to do a safe removal.

3 answers

1 accepted

0 votes
Answer accepted

Thanks! As I thought.

I think I need to do the db delete. Otherwise the effect may be that internal accounts turns active again, if corresponding AD accounts are removed or changed.

delete records from this tables, in this order:

 

select cwd_membership.* from cwd_membership inner join
(
select id from cwd_user where user_name in (
select user_name from cwd_user group by user_name having count(user_name)>1
)
and directory_id in (select id from cwd_directory where directory_type='INTERNAL')
and user_name not like '%admin%'
order by directory_id,user_name
)a on child_user_id=a.id;

select cwd_user_attribute.* from cwd_user_attribute
inner join
(
select id from cwd_user where user_name in (
select user_name from cwd_user group by user_name having count(user_name)>1
)
and directory_id in (select id from cwd_directory where directory_type='INTERNAL')
and user_name not like '%admin%'
)a on user_id=a.id;

select * from cwd_user where user_name in (
select user_name from cwd_user group by user_name having count(user_name)>1
)
and directory_id in (select id from cwd_directory where directory_type='INTERNAL')
and user_name not like '%admin%'
order by user_name;

0 votes
Janet Albion Atlassian Team Jan 01, 2013

Hi Peter,

Since the username in the AD is the same as in the Internal Directory, I would say that it is safe to ignore the duplicate users in the internal. If you would like to remove them in the db manually, note that deleting from the DB is not really recommended nor supported.

If you really like to remove the shadow user manually from the database, test it first in the staging environment and make sure to backup the database.

Generally, the tables that you would need to look into are:

  • cwd_users
  • cwd_memberships
  • cwd_directory

eg:

delete from cwd_user where user_name='user' and directory_id=<internal_directory_id>;

delete from cwd_membership where child_name='user' and directory_id=<internal_directory_id>;

Suggest an answer

Log in or Sign up to answer
TAGS

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you