removed users are still in the database

JeRoen shared this question 12 months ago
Answered

Hello,

We started to use LDAP for authentication and authorization but before that some users already got access to the YellowFin instance. When configuring LDAP I first removed all users in Administration > Admin console > Users (migrating all content to the admin@yellowfin.com.au user). I thought I cleaned up my userbase. Only 3 users left.

I then configured LDAP which is working correctly for users that never before logged in the system. For users that did I get the following error.

/cd3abe57c83021f68861fb57ae98d56c

(I removed the username)

The error message is in Dutch but means it is unable to find a unique user for the given user-ID.

Looking at the person table in the yellowfin database I do see all 26 users that where once logged in before we enabled LDAP. The provisioned users that logged in after we enabled LDAP are also there.

For 20 users I would like to remove the old user entries and let LDAP provision them again. Is it enough to just remove them from the person table?

Regards,

JeRoen

Comments (6)

photo
1

We are already thinking about removing the current database en replace with a new empty DB. Or is there an alternative way to clear out old users in DB ?

JeRoen

photo
1

Hi JeRoen,

Thanks for reaching out. If you only delete user data from the person table, you'll likely still see this error message because user data stored in the IpClass table as well. If you delete from both places I believe this should work.

Please attempt doing so and let me know how goes.

Regards,

Mike


Regards,

Mike

photo
1

Hallo Mike,

Thanks for responding but we did indeed find the emailaddress (in parts) in the ipclass table and deleted that also but no change in the error. Users that did not login in the time the server was configured without LDAP can login and are being added to the users in the database (and I see them in the YF GUI). So the connection to LDAP seems to work correctly.

Any other table which might cause this. We remove the the records for that user from the events and events_archive tables also but I do not think those made any difference.

Regards,

JeRoen

photo
1

Hi JeRoen,

Thanks for your response. The removal aspect from the original post, got me going down what I think is the wrong track. Let's focus on the error message being seen. There are a couple possibilities here. From one previous case where this error message appeared, we discovered that the User ID was used for two users in LDAP. So please check your LDAP for any duplicates.

The other possibility is that Yellowfin still sees your "deleted" user in the Config DB, as these users aren't dropped from the database, which seems likely to be the case here. In order to fix this, you'll have to work a bit in the Configuration Database directly.

When running any queries directly against the Yellowfin Configuration Database it's possible to cause irrevocable data loss. This process isn't directly supported nor recommended unless otherwise advised to do so. Before running any queries against your Yellowfin Configuration Database, please make sure you have full, up-to-date backups of both your Yellowfin Configuration Database and your Yellowfin Installation folder. This way, if something goes wrong you can revert back to your current state.

You're going to want to restore this user and set them to an LDAP user.

First, find your user with the following query:


SELECT * FROM IpClass;
When a user is "deleted" from Yellowfin, the 'EndDate' value is set to the day prior to deletion. This effectively removes their access from Yellowfin. Take note of the 'IpId' of your user and issue:

UPDATE IpClass SET EndDate='9999-12-31' WHERE IpId=xxxx; 
UPDATE IpRltshp SET EndDate='9999-12-31' WHERE IpChild=xxxx;
UPDATE StaffMemberRole SET EndDate='9999-12-31' WHERE IpEmployee=xxxx; 
where xxxx is the 'IpId' listed above for the user in question. You'll want to ensure that the password for this user account is the SAME as the one set in LDAP.

Now convert them to an LDAP User:


UPDATE Person SET PrivateKeyId='LDAP' WHERE IpPerson=xxxx;
If the password set for the Yellowfin user account is the same as that in LDAP, you can now log in as this user. This is the supported method in converting an existing user to LDAP. For users you've already deleted from the IpClass table, you should be able to just re-create the users then convert them to LDAP users via the steps listed above as well.

Please give this a go and let me know your results!

Thanks,

Mike


Regards,

Mike

photo
1

We finally opted for the option to reset the database and first after installation enable LDAP before users are logged in. This fixed the issue for now.

JeRoen

photo
1

Hi JeRoen,

Thanks for letting us know. Glad to hear this is sorted for now. This considered, I'll go ahead and close this case out, but if you a similar issue re-appears, please don't hesitate to reach back out in this ticket and I'll be happy to assist further.

Regards,

Mike


Regards,

Mike