Manually delete a user

Documentation Technical Docs
1

Hi,

I was not able to delete some users from Specify 7 interface (delete button). There was indeed some linked tables that were not properly removed or not in the right order. I think it was mainly old depencies from SP6.

Therefore, I did it manually and created this script so maybe some of it could be added to the delete button to remove a user from the app.

Best regards,

Alexis

-- Define the user to delete
SET @usernametodel = 'xxx';

-- First clear the link from agent
UPDATE specifyuser su
LEFT JOIN agent a 
       ON a.SpecifyUserID = su.SpecifyUserID
SET a.SpecifyUserID = NULL
WHERE su.`Name` = @usernametodel;

-- Now delete from dependent tables in the right order
DELETE sapd
FROM spappresourcedata sapd
JOIN spappresource sap 
     ON sap.SpAppResourceID = sapd.SpAppResourceID
JOIN spappresourcedir sad 
     ON sad.SpecifyUserID = sap.SpecifyUserID
JOIN specifyuser su 
     ON su.SpecifyUserID = sad.SpecifyUserID
WHERE su.`Name` = @usernametodel;

DELETE sap
FROM spappresource sap
JOIN spappresourcedir sad 
     ON sad.SpecifyUserID = sap.SpecifyUserID
JOIN specifyuser su 
     ON su.SpecifyUserID = sad.SpecifyUserID
WHERE su.`Name` = @usernametodel;

DELETE sad
FROM spappresourcedir sad
JOIN specifyuser su 
     ON su.SpecifyUserID = sad.SpecifyUserID
WHERE su.`Name` = @usernametodel;

DELETE susp
FROM specifyuser_spprincipal susp
JOIN specifyuser su 
     ON su.SpecifyUserID = susp.SpecifyUserID
WHERE su.`Name` = @usernametodel;

DELETE supol
FROM spuserpolicy supol
JOIN specifyuser su 
     ON su.SpecifyUserID = supol.specifyuser_id
WHERE su.`Name` = @usernametodel;

DELETE surol
FROM spuserrole surol
JOIN specifyuser su 
     ON su.SpecifyUserID = surol.specifyuser_id
WHERE su.`Name` = @usernametodel;

DELETE rsi
FROM recordsetitem rsi
JOIN recordset rs 
     ON rs.RecordSetID = rsi.RecordSetID
JOIN specifyuser su 
     ON su.SpecifyUserID = rs.SpecifyUserID
WHERE su.`Name` = @usernametodel;

DELETE rs
FROM recordset rs
JOIN specifyuser su 
     ON su.SpecifyUserID = rs.SpecifyUserID
WHERE su.`Name` = @usernametodel;

DELETE wbr
FROM workbenchrow wbr
JOIN workbench wb 
     ON wb.WorkbenchID = wbr.WorkbenchID
JOIN specifyuser su 
     ON su.SpecifyUserID = wb.SpecifyUserID
WHERE su.`Name` = @usernametodel;

DELETE wb
FROM workbench wb
JOIN specifyuser su 
     ON su.SpecifyUserID = wb.SpecifyUserID
WHERE su.`Name` = @usernametodel;

DELETE wbtmi
FROM workbenchtemplatemappingitem wbtmi
JOIN workbenchtemplate wbt 
     ON wbt.WorkbenchTemplateID = wbtmi.WorkbenchTemplateID
JOIN specifyuser su 
     ON su.SpecifyUserID = wbt.SpecifyUserID
WHERE su.`Name` = @usernametodel;

DELETE wbt
FROM workbenchtemplate wbt
JOIN specifyuser su 
     ON su.SpecifyUserID = wbt.SpecifyUserID
WHERE su.`Name` = @usernametodel;

DELETE spqf
FROM spqueryfield spqf
JOIN spquery spq 
     ON spq.SpQueryID = spqf.SpQueryID
JOIN specifyuser su 
     ON su.SpecifyUserID = spq.SpecifyUserID
WHERE su.`Name` = @usernametodel;

DELETE spq
FROM spquery spq
JOIN specifyuser su 
     ON su.SpecifyUserID = spq.SpecifyUserID
WHERE su.`Name` = @usernametodel;

DELETE sts
FROM sptasksemaphore sts
JOIN specifyuser su 
     ON su.SpecifyUserID = sts.OwnerID
WHERE su.`Name` = @usernametodel;

-- Finally, delete the user itself
DELETE su
FROM specifyuser su
WHERE su.`Name` = @usernametodel;
1 Like
2

Hi @alexis.beck,

Thanks for sharing!

This is something that has come up from time to time, and I’ve used some variation of the script below when needed.

It assumes that there is a SpecifyUserID of 1, which is usually a safe assumption, but keep that in mind and double check!

This also supports a list of users (in this example, users named example and example2) by name, not ID.

-- Always do this in a transaction!

-- Cleanup: Drop the temporary table if it already exists
DROP TEMPORARY TABLE IF EXISTS TempSpecifyUserIDs;

-- Create a temporary table to store the SpecifyUserIDs
-- of the users we want to delete. You can just enter a list of
-- user names here in an array.
CREATE TEMPORARY TABLE TempSpecifyUserIDs AS
WITH SpecifyUserNames AS (
    SELECT SpecifyUserID FROM specifyuser
    WHERE Name IN (
        'example', 'example2'
    )
)
SELECT SpecifyUserID FROM SpecifyUserNames;


-- Now perform the updates and deletes using the temporary table
UPDATE agent SET SpecifyUserID = NULL
WHERE SpecifyUserID IN (SELECT SpecifyUserID FROM TempSpecifyUserIDs);

UPDATE spuserpolicy SET specifyuser_id = NULL
WHERE specifyuser_id IN (SELECT SpecifyUserID FROM TempSpecifyUserIDs);

UPDATE collectingevent SET VisibilitySetByID = NULL
WHERE VisibilitySetByID IN (SELECT SpecifyUserID FROM TempSpecifyUserIDs);

UPDATE spvisualquery SET SpecifyUserID = NULL
WHERE SpecifyUserID IN (SELECT SpecifyUserID FROM TempSpecifyUserIDs);

UPDATE taxon SET VisibilitySetByID = NULL
WHERE VisibilitySetByID IN (SELECT SpecifyUserID FROM TempSpecifyUserIDs);

UPDATE workbenchtemplate SET SpecifyUserID = 1
WHERE SpecifyUserID IN (SELECT SpecifyUserID FROM TempSpecifyUserIDs);

/* App Resources */

-- This checks to see if the user has any resources that are shared with other users.
UPDATE spappresource SET SpecifyUserID = 1
WHERE Name IN ('WebLinks','preferences','CollectionPreferences','DataObjFormatters') OR MimeType = 'jrxml/label'
AND SpecifyUserID IN (SELECT SpecifyUserID FROM TempSpecifyUserIDs);

-- Once checked, the resources are updated to be shared with the default Specify user.
UPDATE spappresourcedir SET SpecifyUserID = 1  
WHERE spappresourcedirID IN (  
    SELECT SpAppResourceDirID FROM spappresourcedir WHERE SpecifyUserID IN (SELECT SpecifyUserID FROM TempSpecifyUserIDs)  
) OR IsPersonal = 0;

-- This deletes the app resource data that is not shared with other users. 
DELETE FROM spappresourcedata WHERE SpAppResourceID IN (
    SELECT SpAppResourceID FROM spappresource 
    WHERE Name IN ('ExpressSearchConfig', 'QueryFreqList', 'QueryExtraList', 'DataEntryTaskInit', 'UserPreferences')
    AND SpecifyUserID IN (SELECT SpecifyUserID FROM TempSpecifyUserIDs)
);

-- Deletes the app resource data that is not shared with other users. 
DELETE FROM spappresource 
WHERE Name IN ('ExpressSearchConfig', 'QueryFreqList', 'QueryExtraList', 'DataEntryTaskInit', 'UserPreferences')
AND SpecifyUserID IN (SELECT SpecifyUserID FROM TempSpecifyUserIDs);

-- Delete personal directories from these users
DELETE FROM spappresourcedir
WHERE SpecifyUserID IN (SELECT SpecifyUserID FROM TempSpecifyUserIDs)
AND IsPersonal = 1;

/* Security */

-- Remove SSO records for users
DELETE FROM spuserexternalid
WHERE specifyuser_id IN (SELECT SpecifyUserID FROM TempSpecifyUserIDs);

-- Get rid of their attachment data sets
UPDATE attachmentdataset SET specifyuser_id = 1
WHERE specifyuser_id IN (SELECT SpecifyUserID FROM TempSpecifyUserIDs);

-- Transfer ownership of all reports and labels to the default user
UPDATE spreport SET SpecifyUserID = 1
WHERE SpecifyUserID IN (SELECT SpecifyUserID FROM TempSpecifyUserIDs);

-- Transfer ownership of all Secify 7 WorkBench data sets to the default user
UPDATE spdataset SET specifyuser_id = 1
WHERE specifyuser_id IN (SELECT SpecifyUserID FROM TempSpecifyUserIDs);

-- Transfer ownership of all record sets to the default user
UPDATE recordset SET SpecifyUserID = 1
WHERE SpecifyUserID IN (SELECT SpecifyUserID FROM TempSpecifyUserIDs);

-- Transfer ownership of all Secify 6 WorkBench data sets to the default user
UPDATE workbench SET SpecifyUserID = 1
WHERE SpecifyUserID IN (SELECT SpecifyUserID FROM TempSpecifyUserIDs);

-- Remove any locks for Specify 6 owned by this user
DELETE FROM sptasksemaphore
WHERE OwnerID IN (SELECT SpecifyUserID FROM TempSpecifyUserIDs);

-- Transfer ownership of all Specify 7 merging data sets to the default user
UPDATE spmerging SET specifyuser_id = 1
WHERE specifyuser_id IN (SELECT SpecifyUserID FROM TempSpecifyUserIDs);

-- Transfer ownership of all queries to the default user
UPDATE spquery SET SpecifyUserID = 1
WHERE SpecifyUserID IN (SELECT SpecifyUserID FROM TempSpecifyUserIDs);

UPDATE locality SET VisibilitySetByID = NULL
WHERE VisibilitySetByID IN (SELECT SpecifyUserID FROM TempSpecifyUserIDs);

DELETE FROM specifyuser_spprincipal
WHERE SpecifyUserID IN (SELECT SpecifyUserID FROM TempSpecifyUserIDs);

-- Delete all roles applied to these users
DELETE FROM spuserrole
WHERE specifyuser_id IN (SELECT SpecifyUserID FROM TempSpecifyUserIDs);

-- Delete all policies applied to these users
DELETE FROM spuserpolicy
WHERE specifyuser_id IN (SELECT SpecifyUserID FROM TempSpecifyUserIDs);

UPDATE attachment SET VisibilitySetByID = 1
WHERE VisibilitySetByID IN (SELECT SpecifyUserID FROM TempSpecifyUserIDs);

-- This removes all the messages associated with a specific user
DELETE FROM notifications_message
WHERE user_id IN (SELECT SpecifyUserID FROM TempSpecifyUserIDs);

-- Get rid of the users now that no constraints are left
DELETE FROM specifyuser
WHERE SpecifyUserID IN (SELECT SpecifyUserID FROM TempSpecifyUserIDs);

-- Cleanup: Drop the temporary table
DROP TEMPORARY TABLE TempSpecifyUserIDs;
1 Like