delete users via db

36 views
Skip to first unread message

Deirdre Kirmis

unread,
Mar 28, 2020, 4:14:41 PM3/28/20
to dataverse...@googlegroups.com
Hi all ... I am wondering if anyone has found a successful and safe way to delete users .. is it okay to delete a user from the authenticateduser table if I first delete the associated records from usernotification, authenticateduserlookup and confirmemaildata? if i know that user has no outstanding dataverses, datasets, or files? 

we are required to delete obsolete users because of auditing, and also ...

I have recently changed my shib IDP from our dev to prod servers, and prod will not authenticate users if they already exist ... it tries to add a new record for them but fails because the email is already in place ... so I need to delete the users that have already created shib accounts

any help is appreciated ...

Deirdre Kirmis
ASU Library
Arizona State University 

James Myers

unread,
Mar 28, 2020, 5:40:39 PM3/28/20
to dataverse...@googlegroups.com

Deirdre,

One thing that may help – the authenticateduserlookup table stores the idp as part of the persistentuserid. If all you’ve done is change the idp, you can just replace the idp specific part of that persistentuserid. We’ve done that at QDR to switch from test to production idps.

 

I think you should be able to delete users as you say also, but I haven’t tried it/checked for all the tables that may have a user id in them. Others may know. I think there’s also been some discussion of just changing the name/email of users to anonymize them rather than deleting the whole user record  - not sure if that’s an option for you.

 

-- Jim

--
You received this message because you are subscribed to the Google Groups "Dataverse Users Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dataverse-commu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/dataverse-community/BYAPR06MB4869AA29C0E68698012DB94A87CD0%40BYAPR06MB4869.namprd06.prod.outlook.com.

Deirdre Kirmis

unread,
Mar 28, 2020, 8:30:04 PM3/28/20
to dataverse...@googlegroups.com
oh that helped immensely! I was able to change the idp for shib users and now those users can login!

However, I am unable now to convert local users to shib users ... when I try I get a 500 internal error (ie: local users clicks the institution login ... it goes through the screens to verify he wants to change his account ... puts pw and accepts agreement ... clicks convert and then 500 error. Have you run into this when switching from dev to prod?

Deirdre Kirmis
ASU Library
Arizona State University 
From: dataverse...@googlegroups.com <dataverse...@googlegroups.com> on behalf of James Myers <qqm...@hotmail.com>
Sent: Saturday, March 28, 2020 2:40 PM
To: dataverse...@googlegroups.com <dataverse...@googlegroups.com>
Subject: [Dataverse-Users] RE: delete users via db
 

James Myers

unread,
Mar 28, 2020, 8:37:46 PM3/28/20
to dataverse...@googlegroups.com

Sorry – I haven’t done that. With info from the log, I/others might be able to help track down what’s breaking (if its clear something is broken, might be better to make this a github issue and discuss it there.)

Durand, Gustavo

unread,
Mar 28, 2020, 9:58:21 PM3/28/20
to dataverse...@googlegroups.com
Hi Deidre,

There are three main things to worry about when deleting users:
• they have created objects, e.g. dataverses, datasets
• they have downloaded something and have info recorded in the download table
• they have been assigned roles

The last one is more of a technical issue and you should be able to use the superuser dashboard to remove all roles first, before deleting a user.

There is an API for delete users, but you  currently need to be careful with it, as it doesn't check for any of the above, It won't actually check (and in the case of role assignments, may leave some orphaned db roles). It also may also fail for something like notifications (and fail in a not pretty way).

That said, there is an issue currently in QA ( https://github.com/IQSS/dataverse/issues/6697 ) to improve this API. After that gets released then you could call this API and either it will delete, or will tell you which of the reasons above didn't allow it to delete.

Hope that helps.
Gustavo

On Sat, Mar 28, 2020 at 5:40 PM James Myers <qqm...@hotmail.com> wrote:

Deirdre,

One thing that may help – the authenticateduserlookup table stores the idp as part of the persistentuserid. If all you’ve done is change the idp, you can just replace the idp specific part of that persistentuserid. We’ve done that at QDR to switch from test to production idps.

 

I think you should be able to delete users as you say also, but I haven’t tried it/checked for all the tables that may have a user id in them. Others may know. I think there’s also been some discussion of just changing the name/email of users to anonymize them rather than deleting the whole user record  - not sure if that’s an option for you.

 

-- Jim

 

From: dataverse...@googlegroups.com [mailto:dataverse...@googlegroups.com] On Behalf Of Deirdre Kirmis
Sent: Saturday, March 28, 2020 4:15 PM
To: dataverse...@googlegroups.com
Subject: [Dataverse-Users] delete users via db

 

Hi all ... I am wondering if anyone has found a successful and safe way to delete users .. is it okay to delete a user from the authenticateduser table if I first delete the associated records from usernotification, authenticateduserlookup and confirmemaildata? if i know that user has no outstanding dataverses, datasets, or files? 

--

You received this message because you are subscribed to the Google Groups "Dataverse Users Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dataverse-commu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/dataverse-community/BYAPR06MB4869AA29C0E68698012DB94A87CD0%40BYAPR06MB4869.namprd06.prod.outlook.com.

Durand, Gustavo

unread,
Apr 2, 2020, 2:37:10 PM4/2/20
to dataverse...@googlegroups.com
FYI, the delete API issue / PR I was referring to was merged and released in 4.20.

Gustavo

On Sat, Mar 28, 2020 at 9:58 PM Durand, Gustavo P <gdu...@iq.harvard.edu> wrote:
Hi Deidre,

There are three main things to worry about when deleting users:
• they have created objects, e.g. dataverses, datasets
• they have downloaded something and have info recorded in the download table
• they have been assigned roles

The last one is more of a technical issue and you should be able to use the superuser dashboard to remove all roles first, before deleting a user.

There is an API for delete users, but you  currently need to be careful with it, as it doesn't check for any of the above, It won't actually check (and in the case of role assignments, may leave some orphaned db roles). It also may also fail for something like notifications (and fail in a not pretty way).

That said, there is an issue currently in QA ( https://github.com/IQSS/dataverse/issues/6697 ) to improve this API. After that gets released then you could call this API and either it will delete, or will tell you which of the reasons above didn't allow it to delete.

Hope that helps.
Gustavo

On Sat, Mar 28, 2020 at 5:40 PM James Myers <qqm...@hotmail.com> wrote:

Deirdre,

One thing that may help – the authenticateduserlookup table stores the idp as part of the persistentuserid. If all you’ve done is change the idp, you can just replace the idp specific part of that persistentuserid. We’ve done that at QDR to switch from test to production idps.

 

I think you should be able to delete users as you say also, but I haven’t tried it/checked for all the tables that may have a user id in them. Others may know. I think there’s also been some discussion of just changing the name/email of users to anonymize them rather than deleting the whole user record  - not sure if that’s an option for you.

 

-- Jim

 

From: dataverse...@googlegroups.com [mailto:dataverse...@googlegroups.com] On Behalf Of Deirdre Kirmis
Sent: Saturday, March 28, 2020 4:15 PM
To: dataverse...@googlegroups.com
Subject: [Dataverse-Users] delete users via db

 

Hi all ... I am wondering if anyone has found a successful and safe way to delete users .. is it okay to delete a user from the authenticateduser table if I first delete the associated records from usernotification, authenticateduserlookup and confirmemaildata? if i know that user has no outstanding dataverses, datasets, or files? 

--

You received this message because you are subscribed to the Google Groups "Dataverse Users Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dataverse-commu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/dataverse-community/BYAPR06MB4869AA29C0E68698012DB94A87CD0%40BYAPR06MB4869.namprd06.prod.outlook.com.

--
You received this message because you are subscribed to the Google Groups "Dataverse Users Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dataverse-commu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/dataverse-community/BL0PR07MB39210E63B6B9FE23253E43C4BFCD0%40BL0PR07MB3921.namprd07.prod.outlook.com.

--
You received this message because you are subscribed to the Google Groups "Dataverse Users Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dataverse-commu...@googlegroups.com.
Message has been deleted

Durand, Gustavo

unread,
Apr 2, 2020, 3:51:50 PM4/2/20
to dataverse...@googlegroups.com
Of course! And if you find any issues with the API, please let me know. It is only a step, currently, in that it was a small batch to clean up the existing API so it could be used by less technical folk. It still is limited and we plan on removing at least some of those limitations in the future.

Gustavo

On Thu, Apr 2, 2020 at 3:48 PM Deirdre Kirmis <Deirdre...@asu.edu> wrote:

Thanks so much for the update on this. It will be good to have something reliable that can be used to delete users.

 

I had tried the current API to delete the ones that I needed to delete (there were only 4), but they would not delete, even after I removed everything in the system that could possibly be related to them (I thought). I went to the database and tried to manually remove them from the authenticateduser table, but got error messages each time that they had primary key links in the usernotification, authenticateduserlookup and confirmemaildata tables. I removed the records there, and then could delete their user accounts.  However, for a couple of the users, I also had to first remove the link to their ID in the authenticationproviderrow table for oauth … I had changed them to Microsoft users and then changed them back, but apparently it kept a record in that table that they had once used that authentication method.

 

Thanks so much for the help! This is a great community!

 

Deirdre Kirmis

Technology Services

Arizona State University Library

480-965-7240

--
You received this message because you are subscribed to the Google Groups "Dataverse Users Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dataverse-commu...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages