XF 2.1 GDPR SAR subject access request queries

[Stuart Wright]

When someone puts in an SAR (subject access request) in accordance with GDPR, forum owners have a legal responsibility to reply with a lot of specific information.
Following such a request and a subsequent complaint by a forum member for non compliance to the UK data commissioner’s office, I need to ask this question.
What SQL queries do we need to run to supply all of a member’s information?
We need their user record and all the records from associated user tables including user change logs and IP logs.
Also any records from addons such as the user notes system.
I think it’s reasonable to say that publicly posted information does not need to be sent since this was posted by the person. Similarly private messages including the member are deemed private and so shouldn’t be sent. However, private conversations between moderators about that member may need to be included.

In the above case, I got direct help from a person working in the data commissioner’s office (who was extremely helpful) and though the member’s account has already been deleted, so there was no information to send, that didn’t mean I could send ‘nothing’ as a response. I had to amend our privacy policy significantly to comply with GDPR (including adding their address and phone number) and then reply addressing each specific request.

Also I was told that it is not acceptable to delete someone’s account upon receiving an SAR in order to avoid sending the information.

So could someone help specify the queries to run please?
And ideally there should be a function in Xenforo to output all the data in a relatively easy-to-read format.

 

[Kirby]

I think that XenForo developers believe that ACP / Users / Data portability / Export a user is sufficient for GDPR compliance.
However, I think that this is not the case - as you've pointed out an export would have to include all user related data which is currently not the case.

Given the current design of XenForo I'd even say that it is impossible to answer your question without exactly knowing your installation and tailoring queries for that.
Depending on the Add-ons you use it might not even be possible to achieve a full export with just SQL (eg. if user related data is stored in JSON/serialized blobs, etc.)

If XenForo would like to be fully GDPR ready for this scenario (there are a lot of other scenarios where it falls short as well), it woul be necessary to have a system in place where every Add-on somehow registers every user data relation in order for XenForo to be able to automatically generate an export of the required data.
However, it would probably still not be possible to technically enforce this, but could be added to Resource Standards to not allow Add-ons that do not fully/correctly implement registration.

 

[Stuart Wright]

I think that XenForo developers believe that ACP / Users / Data portability / Export a user is sufficient for GDPR compliance.

It's definitely not. It doesn't even include all the information from the user table like the registration date.
The XF team will have a thorough list of core tables as they will be processed when deleting a user, but I've gone through the list of tables (it took some hours) and I reckon at first glance, we need the data from the list below.
@Mike I can imagine this is a hell of a lot of work, but being asked for an SAR is something that can potentially happen to all EU forum owners, and by GDPR law they have to comply with (roughly) the relevant information form the tables below (and possibly more that you know about).

xf_attachment_data
xf_bookmark_item
xf_bookmark_label
xf_conversation_master (any fields containing any information about recipients should be excluded)
xf_conversation_message (sent by the user with any fields containing any information about recipients being excluded)
xf_conversation_recipient
xf_conversation_user (any fields containing any information about recipients should be excluded)
xf_data_registry - anything needed from here?
xf_deletion_log
xf_draft
xf_edit_history
xf_email_bounce_log
xf_email_bounce_soft
xf_error_log
xf_flood_check
xf_forum_read
xf_forum_watch
xf_ip
xf_notice_dismissed
xf_permission_entry
xf_permission_entry_content
xf_poll_vote
xf_post
xf_profile_post (posted by the user)
xf_profile_post_comment
xf_purchase_request (no idea what this is)
xf_reaction_content (reaction_user_id)
xf_report (content_user_id)
xf_report_comment
xf_session_activity (needed?)
xf_spam_cleaner_log
xf_spam_trigger_log
xf_tag_content
xf_tag_result_cache
xf_thread
xf_thread_read
xf_thread_reply_ban
xf_thread_user_post
xf_thread_watch
xf_user_alert (redact the alerted user)
xf_user_alert_optout
xf_user_authenticate (redact the hash in the data field?)
xf_user_ban
xf_user_field & xf_user_field_value
xf_user_follow
xf_user_group_change
xf_user_group_promotion_log
xf_user_group_relation
xf_user_ignored (for who ignoring - probably not who ignored by as this is private)
xf_user_option
xf_user_privacy
xf_user_profile
xf_user_push_optout
xf_user_push_subscription
xf_user_reject
xf_user_remember
xf_user_tfa
xf_user_tfa_trusted
xf_user_trophy
xf_warning

And from addons on AVForums:

Post Macros by @Liam W
xf_liam_post_macros_global_enrolment
xf_liam_post_macros_shared

Bookmark Essentials by @Xon
xf_sv_bookmark_ess_content_stats

Content Ratings by @Xon
xf_sv_content_mentions (in the mention_ids blob)
xf_sv_content_rating_action_log

Conversation Essentials by @Xon
xf_sv_convess_auto_response
xf_sv_convess_conversation_user
xf_sv_convess_group
xf_sv_convess_group_user
xf_sv_convess_label
xf_sv_convess_label_user_stat
xf_sv_convess_user_options

Signup abuse detection and blocking by @Xon
xf_sv_multiple_account_event
xf_sv_multiple_account_log
xf_sv_multiple_account_token
xf_sv_user_registration_log

Steam Authentication by @Assadi
xf_user_steam_games

Another from @Xon
xf_sv_user_unc
xf_sv_user_unc_cron
xf_sv_thread_lock

Tag Essentials (mine)
xf_tagess_tag_watch

Featured threads from @ThemeHouse
xf_thfeature_feature_dismissed

Xencentral Feedback system by @XCentral
xf_xc_feedback_activity
xf_xc_feedback_comment
xf_xc_feedback_feedback
xf_xc_feedback_notification_queue
xf_xc_feedback_preference
xf_xc_feedback_user

Member notes by @Z61
xf_z61_member_note (redact user info about who left the notes)

 

[Kirby]

xf_email_bounce_log
xf_email_bounce_soft

Those tables are problematic anyway as deleting a user does not remove associated data.

Not a bug - Deleting user does not delete all PII from database

Steps to Reproduce Setup Bounce Handling and make sure that email validation is enabled in user registration options On the frontend, register a new user with a non-existent email address outside of the domains handled by the SMTP-Server that is used by that board to trigger a bounce Manually...

xenforo.com

 

[Stuart Wright]

This might have been quicker:

SELECT DISTINCT TABLE_NAME AS 'TableName',COLUMN_NAME AS 'ColumnName' FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%user_id%' ORDER BY TableName ASC

 

[Kirby]

Yeah, that's a start. But the relevant field is not always (just) user_id.

 

[Stuart Wright]

Yeah, that's a start. But the relevant field is not always (just) user_id.

Yeah it's a start. LIKE '%user_id%' should get everything, but you never know.

 

[Stuart Wright]

I've got a pending SAR, so I need to get cracking with this.
I wonder how many SQL statements it will take. I'm looking at using phpmyadmin as I can't code for Xenforo.

 

[Robust]

However, private conversations between moderators about that member may need to be included.

I'd consider that a stretch. It's the equivalent to having to include a conversation in Slack or through email about a member. It isn't really part of the data you're logging about a user, unless for some reason you're logging a member's personal data in conversations.

Again, I'd look to larger companies for examples. The Facebook, Instagram, Google exports are nowhere near as comprehensive as the list of tables you shown. I know a couple of large forums that have incredibly basic exports, practically limited to stuff like email addresses and IPs. A list of all threads made by the user isn't really personal information, and shouldn't be included in an export. Same for posts and profile posts, etc. I would not include content in such an export personally. Facebook does not include a list of every profile post you've made in its exports if I recall correctly. I don't even think they include a list of friends. My Facebook GDPR export is pretty small.

 

[Kirby]

Again, I'd look to larger companies for examples. The Facebook, Instagram, Google exports are nowhere near as comprehensive

Hmm, personally I wouldn't take those companies as a good example for GDPR (or any other regulatory) compliance.
In fact those companies pretty regularly do get fined for not being compliant and they more or less happily pay the fines.
This will continue until the fines are higher than the loss of revenue caused by really being compliant (which is currently not the case).

 

[Chris D]

You're conflating EVERY bit of content that the user has ever created that is stored about the user with personally identifiable information (PII).

The date they registered, for example, is not personally identifiable information. The content they have created is not personal information.

@Stuart Wright I strongly recommend, before setting a precedent that doesn't need to exist, that you contact the ICO if you're unsure about what obligations you have as a data controller.

It is our belief that under their guidelines (and therefore GDPR in general) that the data export is sufficient. It would be for them to advise you otherwise and if they make any recommendations then we can consider them in the future.

I've got a pending SAR, so I need to get cracking with this.

You have a month. Use that time to get confirmation of the details that need to be sent.

 

[Robust]

It is our belief that under their guidelines (and therefore GDPR in general) that the data export is sufficient. It would be for them to advise you otherwise and if they make any recommendations then we can consider them in the future.

The data export doesn't include IP addresses which is one thing I believe should be included in that export.

 

[Kirby]

The date they registered, for example, is not personally identifiable information. The content they have created is not personal information.

It is personal data, at least by the guidance we got from Landesbeauftragter für Datenschutz und Informationsfreiheit Rheinland-Pfalz which is pretty much the Rhineland-Platine equivalent of ICO.
We therefore did build export functions (though not yet for XenForo as we luckily did not have a request for our XF forums yet) the way @Stuart Wright described.

For the purposes of this Regulation:
‘personal data’ means any information relating to an identified or identifiable natural person (‘data subject’); an identifiable natural person is one who can be identified, directly or indirectly, in particular by reference to an identifier such as [...] an identification number [...] of that natural person;

The registration date is an information. It does relate to the profile of a user through the userid. The profile does relate to an email address. This email address does (usually) relate to a natural person.

It would therefore really be interesting to see if the ICO does give different guidance.

 

[webbouk]

I had such a SAR from a problematic member who was banned, and tbh I very much doubt if anyone would send a SAR to do anything with the information other than cause you, the forum owner, a lot of work trying to fill it in.
The ex-member of our forums was so far up his own arrus that he even signed his name with a string of letters after it to embellish his stature.

Anyway, here's how it was dealt with at this end...

First off I created a Data Export using his member name - this was a record of information we kept at the time.
Then changed his membername to some obscure random name, possible within the software when deleting a member.
Then ran searches using his past member name in the query for information relating to that name as requested by the SAR. As you will know this then returns 'The following members could not be found'

A brief excerpt is below:




Once done the results were emailed to him as a pdf, and the member's username was added to the profanity censor so that any reference to it displayed 'Deleted member'

No further correspondence was entered into.


It's also worthwhile checking to make sure that any requests come from the same email address that the member registered with, and if not politely refuse such a request.
Below is the text that I used to reply to such request received from the member but not using the email in his member details........

In line with best practices we keep an absolute minimum of personal data related to an account on record.

To authenticate any requests relating to an account, we ask for proof of ownership of that account, which, can either be by
a) Sending the request while logged into the account, or
b) Sending the request from the email registered to that account.

Since these are the only ways the user has to securely identify that it is their account if they can not do either we can not be sure the request is legitimate and in line with protecting the data of our users, we decline the request



At the end of the day you are running a forum, not a confidentiality service, nor a banking or insurance institution, and as a result any SAR's are likely to come from people that are agreived in some way and out to try to cause trouble.
Whether they carry any threats or action though is up for debate, I very much doubt it.

 

[Stuart Wright]

@Stuart Wright I strongly recommend, before setting a precedent that doesn't need to exist, that you contact the ICO if you're unsure about what obligations you have as a data controller.

Chris, I exchanged emails and phone calls with a lady at the ICO who sounded like she knew what she was talking about (which hasn't always been the case). She was handling the complaint made to the ICO about AVForums when I sent a reply to an SAR from a deleted member stating that we held no information after his account had already been deleted. This was not sufficient. I had to reply with information on how we stored data etc. I updated our privacy policy to reflect the non-member-specific information broadly required by an SAR so I can point to that in the future.
It's not just personally identifiable information, either. It is all the data including IP information.
I will, however, double check and reply with that information.

I had such a SAR from a problematic member who was banned

Yes it's been this in both our cases so far and it matters not to the ICO. The request still has to be treated properly.
Because these people are deliberately trying to make our lives harder, I would very much like to be able to respond with a single button click taking precisely ten seconds of inconvenience.

Whether they carry any threats or action though is up for debate, I very much doubt it.

It's actually easy to complain to the ICO and we've had one person do it following our first SAR.

 

[Chris D]

It's not just personally identifiable information, either. It is all the data including IP information.
I will, however, double check and reply with that information.

It may be true that it includes IP information. The ICO's own website, however, is very clear what is required from an SAR. It's very clear that it includes only "personal information".

This does not include anywhere near the tables you implied in your earlier post. It may be that the data export is sufficient, apart from the IP history.

If the user is deleted, you do not any longer have any relevant personal information so, I'm sorry, she's wrong. And, actually, you wouldn't have any IP address history for a deleted user either so that being missing from the user export is irrelevant.

She may be right that deleting a user isn't acceptable to get out of a data access request, but if the user has already been deleted then it's unavoidable.

 

[Alpha1]

Once done the results were emailed to him as a pdf

The GDPR considers email insecure and its not allowed to send PII through email without explicit permission.

I had such a SAR from a problematic member who was banned

This is quite an important matter, because privacy laws are trumped by your need to protect your members against abuse. I deny such requests if they impair my ability to protect my community from abuse. Especially when it comes to hate speech and other violations of EU directives and national laws. For example: banned members do not need to know what methods we used to detect their abusive behavior or what comments are in reports about the matter.

 

[djbaxter]

For the vast majority of forum members, IP is not personal identifying data, especially with increasing numbers using mobile devices to access the forum. Many members will share the same IP. Moreover, IP at best can identify a general region but very often what it identifies is the head office of the ISP, which may be many miles away from where you live and often not even in the same state or province in the case of mobile IPs.

 

[Kirby]

It's very clear that it includes only "personal information".

Personal Data, yes. Personal data is everything related directly or indirectly to a natural person.
In case of a forum everything linked to the user account through userid, ip or email address.

This is what we've been told.

For the vast majority of forum members, IP is not personal identifying data

It is.

CJEU Confirms Dynamic IP Addresses To Be Personal Data

On Wednesday October 19, 2016 the Court of Justice of European Union (“CJEU”) issued its judgment in Case C-582/14, Patrick Breyer v Germany. The CJEU

www.insideprivacy.com

CURIA - Documents

curia.europa.eu

 

[Stuart Wright]

It may be true that it includes IP information. The ICO's own website, however, is very clear what is required from an SAR. It's very clear that it includes only "personal information".

This does not include anywhere near the tables you implied in your earlier post. It may be that the data export is sufficient, apart from the IP history.

If the user is deleted, you do not any longer have any relevant personal information so, I'm sorry, she's wrong. And, actually, you wouldn't have any IP address history for a deleted user either so that being missing from the user export is irrelevant.

She may be right that deleting a user isn't acceptable to get out of a data access request, but if the user has already been deleted then it's unavoidable.

Personal information is different to personally identifiable information, but I will check this on Monday.
If the user is deleted, we have no information to send, but her point was that we still have to reply to the request in a ‘proper’ manner. The user wrote a list of the information he wanted and I had to reply with ‘no information’ for each.
It’s not sufficient to reply with a short message stating we have no stored information about the person. An official, clear reply is necessary.