Dear @kerzel,
I see three contexts where I could search for to identify if certain user is using or was using openBIS instance.
- Log files related to authentication, authorization, groups synchronization, or any other actions. Current release of openBIS does not include easy switch to disable logging of personal data. We are concerned about that and we will refactor our default settings for logs to make it easier.
- Data itself or naming conventions of project or experiments or descriptions. For example metadata of XLS files uploaded to openBIS contain people names.
- And finally user’s data needed for authentication and permissions what we focus on below examples.
I will use complete docker commands examples to show complete queries from where clean SQL could be taken.
User’s data are in “openbis_prod” database in table “persons”.
docker exec -i -u postgres openbis-db psql openbis_prod -c "select first_name,last_name,user_id,email from persons where is_active = 'true';"
When user is deactivated from openBIS it will change in database to “is_active = ‘false’”. So I could use a SQL query to update fields I want to anonymize. It could be:
docker exec -i -u postgres openbis-db psql openbis_prod -c "update persons set last_name = (substr(md5(random()::text), 0, 10)), first_name = (substr(md5(random()::text), 0, 10)) where is_active = 'false';"
The “(substr(md5(random()::text), 0, 10))” gives 10 characters of random string. Above update modify the “last_name” and “first_name”, so to have complete query I should add also “user_id” and “email”.
In case of requirement to anonymize the names of user’s space, in the table “persons” there is a “space_id” to identify which space should be analogically modified. The field to rename will be “code”.
Example update to change name of space with id “56”:
docker exec -i -u postgres openbis-db psql openbis_prod -c "update spaces set code = (substr(md5(random()::text), 0, 10)) where id = '56';"
All random strings in my example will result with something like
docker exec -i -u postgres openbis-db psql openbis_prod -c "select code from spaces where id = '56';"
code
-----------
897f31223
(1 row)
)