Automatic archival of users based on LDAP/AD disabled status
I saw this article here: https://www.itninja.com/blog/view/kace-sma-automatation-of-userarchiva
And that MySQL code looks like a mess. I don't want to run it on our server. Quite a bit of it seems completely superfluous, so I wanted to rewrite it.
FIELD_ID is custom field 4. This is supposed to check field 4 for a trailing 2 in hex, which should indicate the UserAccountControl imported from LDAP is set to disabled. LDAP Custom 4 mapped to LDAP UserAccountControl
UPDATE USER AS USR1,
(SELECT uID.ID FROM USER uID WHERE uID.USER_NAME = 'admin') AS USR2,
(SELECT uFV.USER_ID FROM USER_FIELD_VALUE uFV WHERE uFV.FIELD_ID = '4' AND RIGHT(HEX(uFV.FIELD_VALUE), 1) = '2') AS USR3
SET USR1.MODIFIED = now(), USR1.IS_ARCHIVED = '1', USR1.ARCHIVED_DATE = now(), USR1.ARCHIVED_BY = USR2.ID
WHERE USR1.ID = USR3.USER_ID AND USR1.IS_ARCHIVED = '0';
I was hoping someone would be willing to take a look and see if there are any obvious problems with it. I plan on running it against a copy of the database first.
Answers (2)
Why don’t you copy the code into sql workbench and run it to see what results you get? That would confirm your SQL is correct
SQL Workbench won't even simulate commands against the server because the R1 account doesn't have read access.
I'm thinking archiving might not be a good idea. Docs say that importing a user who is archived will create a new user. I'm thinking that having it revoke all permissions to the user instead would be the better way to go.