* Case Number: a20100131.1 * Status: closed * Claimants: * Respondents: CAcert * Case Manager: AlexanderPrinsier * Arbitrator: UlrichSchroeter * Date of arbitration start: 2010-11-03 * Date of ruling: 2011-02-21 * Case closed: 2011-02-21 * Complaint: User wishes Account removal {{{ Please delete my account and remove all information from your system and revoke all of my certificates associated with }}} * Relief: TBD Before: Arbitrator UlrichSchroeter (A), Respondent: CAcert (R), Claimant: (C), Case: a20100131.1 == History Log == . 2010-01-31 (UlrichSchroeter): [[https://issue.cacert.org/otrs/index.pl?Action=AgentTicketZoom&TicketID=2649&ArticleID=5270&QueueID=1|s20100131.11]] added to wiki, request for CM / A . 2010-01-31 (S): Account deleted, (C) have revoked all his certs before delete the account by (S) . (C) has no assurances points on his account, Case can closed by (A) . 2010-11-03 (CM): I'll take care about this case . 2010-11-03 (A): I'll take care about this case . 2010-11-03 (A): Note that the line dated 2010-01-31 was made by Support Engineer Martin and is not a ruling by an Arbitrator! Nevertheless I'll keep this information. See also the list of related cases. . 2010-11-03 (A): Requested SQL query from (Critical Team) . 2010-11-03 (A): rcvd notification from (A) of case [[Arbitrations/a20090703.2|a20090703.2]] that the sql query doesn't include infos about GPG. . 2010-11-04 (A): rcvd result set of sql query from (Critical Team) . 2010-11-08 (A): intermediate ruling about the PoJAM case . 2010-11-08 (A): request to support with the question, if there is a users.deleted date set and to reset this date with an empty date "0000-00-00 00:00:00" to reenable the account temporarely, so that Support is able to walk thru the proper "Delete Account" procedure . 2010-11-08 (A): rcvd 2nd result set of sql query from (Critical Team) . 2010-11-08 (A): request to Software-Assessment Team for assistance in temporarely rollback a delete account action . 2010-11-26 (A): reminder request to Software-Assessment Team for assistance in temporarely rollback a delete account action . 2010-12-04 (A): reminder #2 request to Software-Assessment Team for assistance in temporarely rollback a delete account action . 2010-12-04 (A): sent more detailed info about used queries (see below) and anonymized results to (Software-Assessor) after assistance offer, all regarding account recovery from state "Deleted" . 2011-01-14 (A): reminder sent to (Software-Assessors) for deployment of sql-query to recover a deleted account . 2011-01-25 (MT): from Software-Assessment team sent SQL query proposal to recover a deleted account, SQL query to be tested befor applying onto the production system . 2011-01-26 (A): Intermediate Ruling #2, with order request to (Critical Team) to execute the update query with exec report request . 2011-01-26 (CriticalTeam): The requested actions have been executed. . 2011-01-26 (A): exec request to (Support) following intermediate ruling #2, to process [[Arbitrations/Training/Lesson20/DeleteAccountProcSEv2|Delete Account Procedure for SE's]], w/ exec report req . 2011-01-27 (Support): [s20110126.45] Exec report, 1 client cert revoked 2010-01-31, 1 domain, 1 srvr cert revoked 2010-01-31. . 2011-01-28 (A): send (Software-Assessors) 2 proposed sql queries to answer the question how many delete account cases in total and how many delete account cases were not handled manualy thru SE delete my account procedure for review before applying onto the production system . 2011-01-28 (A): intermediate ruling #3 notification sent to (C), (CM), ruling part I finished. . 2011-01-28 (A): NDR rcvd on email #2 (ex primary email) of (C)'s account . 2011-01-28 (SA): replied with suggestion to remove one extra blank in date time string in sql queries . 2011-01-28 (A): exec req on 2 Adhoc sql queries to count effected cases, sent to (Critical Admins), CC (DRO) . 2011-01-28 (CA): sends exec report, sql1: 1074, sql2: 1057 == Discovery == * For discovery of the status of the account, the following SQL query can be used (see [[Arbitrations/a20090703.2|a20090703.2]]): {{{ SELECT id, fname, mname, lname, suffix, dob FROM `users` WHERE email = ''; SELECT n.* FROM `users` u LEFT JOIN `notary` n ON n.`from`=u.`id` OR n.`to`=u.`id` WHERE u.`email` = ''; SELECT d.`domain`, COUNT(dc.id) FROM `users` u LEFT JOIN `domains` d ON d.`memid`=u.`id` LEFT JOIN domaincerts dc ON dc.domid=d.id WHERE u.`email` = '' GROUP BY d.`id`; SELECT COUNT(ec.id) FROM `users` u LEFT JOIN emailcerts ec ON ec.memid=u.id WHERE u.`email` = ''; }}} * Database still contains privacy related informations about the user. This is caused by using probably a procedure for "Delete Account" requests, that are not appropiate for this procedural handling by a SE who was not authorized by an Arbitrator in deleting this users account. * On review of the sql query result this account is identified to be a PoJAM case (!) == Intermediate Ruling == The sql query result identifies this possible member as an PoJAM case. So therefor I have to intermediate rule that all personal identifiable informations about the user on arbitration file about the under 18 years user have to be anonymized immideatly. Frankfurt/Main, 2010-11-08 == Discovery II == * There is one email address information left in the database with privacy related user informations * Account data have not been anonymized as proposed "Delete my Account" procedure for SE's have been deployed starting January 2010 under [[Arbitrations/Training/Lesson20|Arbitrations Training Lesson 20 - Arbitration Case - Delete Account Request]] * No Assurances received or given. * GPG was not checked, but irrelevant because of assurances. * There is still one domain and one domaincert left to the account * There is still one email cert related to the users account * A ruling have to take care about anonymizing user data in the account database * 2nd sql query to verify delete status of account {{{ SELECT id, deleted FROM `users` WHERE email = ''; }}} * 2nd sql query result set lists; ''users.deleted'' field is set * 2011-01-25 SQL query proposal to recover a deleted account {{{ update `domains` SET `deleted`=0 WHERE `domains`.`memid`=''; update `email` SET `deleted`=0 WHERE `memid`=''; update `users` SET `deleted`=0 WHERE `id`=''; }}} * 2011-01-26 (A): tested on a local testserver image, works like a charme == Intermediate Ruling #2 == In the discovery phase of this arbitration case, I've found, that there still remains user identifiable data within the system about the user, after the user account has been deleted by the admin console delete function has pressed w/o anonymize the data before using the built-in delete function. This needs to be repaired, so therefor, the user account in questions needs to be recovered, that a Support-Engineer can access the account and can apply the delete-my-account procedure for SE's including anonymize the user identifiable data in the users account. So the step here is the Account recovery step. Therefor I order, critical admin team, to execute following sql query update steps, to recover the users account to a state, a Support-Engineer can hijack the account and applies the [[https://wiki.cacert.org/Arbitrations/Training/Lesson20/DeleteAccountProcSEv2|Delete My Account Procedure for SEs v2]] including a printout to PDF The user account: Name : xxx Email: xxx ID: xxx The proposed 3 sql query update lines, that Software-Assessor Michael Taenzer proposed, and I've tested on a local system where is to be replaced with the user ID of above user: {{{ update `domains` SET `deleted`=0 WHERE `domains`.`memid`=''; update `email` SET `deleted`=0 WHERE `memid`=''; update `users` SET `deleted`=0 WHERE `id`=''; }}} Frankfurt/Main, 2011-01-26 == Discovery III == * Other Delete Account cases that may affected by a SE's action, not ordered by an arbitrator, that may affected by this behavior ? (see also [[Arbitrations/a20100307.1|a20100307.1]]) * Known Arbitration cases with (C of [[Arbitrations/a20100307.1|a20100307.1]])'s interference || Arbitration case || State || Arbitrator || Delete Account Cases || || [[Arbitrations/a20091119.1|a20091119.1]] || {g} closed || BernhardFröhlich || {g} || || [[Arbitrations/a20100108.1|a20100108.1]] || {g} closed || BernhardFröhlich || {g} || || [[Arbitrations/a20100131.1|a20100131.1]] || {y} running || UlrichSchroeter || {g} || || [[Arbitrations/a20090703.2|a20090703.2]] || {g} closed || MarioLipinski || {g} || || [[Arbitrations/a20100128.1|a20100128.1]] || {g} closed || BernhardFröhlich || || || [[Arbitrations/a20100210.2|a20100210.2]] || {g} closed || UlrichSchroeter || || * 3 of the 4 cases have been closed / finished, w/o anonymizing the users data. There still persists user identifiable user data in the related user records. How to proceed ? * This question opens the next question, what is with old delete account cases and users data ? How many cases exists, that hadn't been handled thru SE's manual procedure to anonymize users data ? {{{ # collect total users deleted SELECT count(id) FROM users where deleted !='0000-00-00 00:00:00'; # collect total users deleted and manual SE delete procedure hasn't been executed # or were made mistakes (not to reset flags) SELECT count(id) FROM users where deleted !='0000-00-00 00:00:00' and email not like 'arbitration_a%' and fname not like 'a20%' and (listme=1 or admin=1 or ttpadmin=1 or orgadmin=1 or board=1 or tverify=1 or locadmin=1 or locked=0 or adadmin=1); }}} * continues unter [[#Discovery_IV|Discovery IV]] * 2011-01-26 (Critical Team) exec report * 1 record table domains, 1 record table email, 1 record table users recovered * 2011-01-27 (Support): [s20110126.45] Exec report * 1 Client cert, revoked 2010-01-31 * 1 domain * 1 Server cert, revoked 2010-01-31. * 2011-01-28 (A) current state of (C)'s account * 1 client cert, 1 domain, 1 server cert revoked/removed * users data in account have been anonymized thru [s20110126.45] based on intermediate ruling #2 * users account has been locked and deleted, users request fullfiled. * Ruling to cover: delete account, CCA termination * Probably ruling has to cover old cases too, so therefor I split the ruling into 2 parts 1. Ruling on delete my account request by (C) -> intermediate ruling #3 to finish 1. Ruling on affected mistakenly used delete my account procedures w/ user identifiable data that remains in system == Intermediate Ruling #3 == * Users account has been recovered and processed thru the manual SE delete my account procedure, based on intermediate ruling #2, dated 2011-01-26 * CCA termination calculation based on [[Arbitrations/Training/Lesson20|Arbitration Case - Delete Account Request - Proposal Procedure for Arbitrators - Step 8]] * Last Certs expired or revoked: 2010-01-31, calculated: 2010-04-30 * CCA ends after the date + 3 month or ruling date if later * Ruling date: 2011-01-28 * It cannot be the users fault, that this arbitration case was about nearly 1 year on the arbitrations queue. * As the users account was deleted by a SE at 2010-01-31, the user could not affect the community, and therefor has no side effects regarding CCA R/L/O * So therefor I set the CCA termination date to not the ruling date, but the calculated certs end date. * CCA termination date is set to: 2010-04-30 Frankfurt/Main, 2011-01-28 == Discovery IV == * 3 of the 4 cases have been closed / finished, w/o anonymizing the users data. There still persists user identifiable user data in the related user records. How to proceed ? * This question opens the next question, what is with old delete account cases and users data ? How many cases exists, that hadn't been handled thru SE's manual procedure to anonymize users data ? * This topic to be added to the agenda of next Arbitration team meeting: [[Arbitrations/Meetings/ATAgendaandMinutes-20110201|2011-02-01]] * 2011-01-28 (SA): proposed queries reviewed by at least one (SA) * 2011-01-28 (A): exec req for 2 Adhoc SQL queries to (Critical Admins) {{{ # collect total users deleted SELECT count(id) FROM users where deleted !='0000-00-00 00:00:00'; # colect total users deleted and manual SE delete procedure hasn't been # executed or errors were made (eg. not to reset flags) SELECT count(id) FROM users where deleted !='0000-00-00 00:00:00' and email not like 'arbitration_a%' and fname not like 'a20%' and (listme=1 or admin=1 or ttpadmin=1 or orgadmin=1 or board=1 or tverify=1 or locadmin=1 or locked=0 or adadmin=1); }}} * 2011-01-28 (CA): sends exec report * sql1: collect total users deleted: 1074 * sql2: total deleted w/o manual SE procedure: 1057 * CAcert's [[http://www.cacert.org/index.php?id=10|Privacy Policy]] defines on top 6 {{{ 6. How to update, correct, or delete your information You are able to update, add and remove your information at any time via our web interface, log into the 'My Account' and then click on the 'My Details' section, and then click the relevant link }}} . so a user can expect, if he request an account removal, his personal data will also be deleted. That the Delete My Account request is handled thru Arbitration is not in question. But the procedure to delete the users data on request is. . CAcert's [[http://www.cacert.org/index.php?id=10|Privacy Policy]] defines no data retention practices. So therefor top 10 {{{ 10. Legal mandates CAcert adopts the Australian privacy regulations.Please see http://www.privacy.gov.au/ for further details. }}} . comes into effect as a fallback. Under [[http://www.privacy.gov.au/materials/types/guidelines/view/6478]] the retention practice definition is as follows: {{{ How long does the personal information need to be kept? NPP 4.2 requires organisations to securely destroy or permanently de-identify information that is no longer needed for the permitted purposes for which it may be used or disclosed (... under National Privacy Principle 2). Although the IPPs do not contain a similar obligation, agencies should nevertheless consider retention practices, subject to other applicable record-keeping requirements such as those contained in the Commonwealth Archives Act. }}} . New questions: * Does NPP (4.2) applies here ? (National Privacy Principle 4) * Or does IPP applies here ? (Information Privacy Principle 4) * Does exists other applicable record-keeping requirements ? . A similiar case on Privacy purpose has been handled in the past: [[Arbitrations/a20090913.1]] == (Private Part) == * Link to Arbitration case [[Arbitrations/priv/a20100131.1|a20100131.1 (Private Part)]] <> ==== EOT Private Part ==== == Ruling == The original dispute filing "User wishes Account removal" moved to two seperate cases in discovery phase: I. the users request of Account removal I. PII and problematical sys settings on 1057 of 1074 deleted accounts cases ==== Part I ==== 1. I hereby confirm the discovery I steps and resulting Intermediate ruling #1, dated 2010-11-08, to be identified the user as a PoJAM case, that all (C)'s identifiable data to be anonymized under this arbitration file. This step has been executed 2010-11-08 by (A) immediately. 1. As a former SE executed the delete account request without prior authoritsation by an Arbitrator, the state of PII on users account was in question. The request in discovery phase II result was that there still remains user identifiable data within the system about the user. So therefor the Intermediate ruling #2, dated 2011-01-26 was to recover the deleted account, to process later on the manual delete account procedure for SE's. I hereby confirm intermediate ruling #2. 1. The users account should be anonymized and deleted as requested by the Claimant. * Support has executed the "Delete my Account" procedure for SE's steps thru intermediate ruling execution #3 * CCA termination date set in intermediate ruling #3 dated 2011-01-28 to: 2010-04-30, I hereby confirm. ==== Part II ==== * Hereby I follow the precedent of case [[Arbitrations/a20091118.1|a20091118.1]] to split this case to two cases. * The question PII and problematical sys settings on 1057 of 1074 deleted accounts cases has to be handled in a seperate arbitration case that is caused by complexity within the running case and leaves to off-topic. * The new case should continue with the discovery and deliberations found under the current case. Material and informations found to be transfered to the new case. * New case: [[Arbitrations/a20110221.1|a20110221.1]] Frankfurt/Main, 2011-02-21 == Execution == * 2011-02-21 (A): sending ruling to (CM), (DRO) * 2011-02-21 (A): create new case [[Arbitrations/a20110221.1|a20110221.1]], transfer infos found to [[Arbitrations/a20110221.1|a20110221.1]] dispute filing, deliberations, discovery * 2011-02-21 (A): case closed == Similiar Cases == || [[Arbitrations/a20090703.2|a20090703.2]] || [[Arbitrations/a20090703.2|please remove me from the database (deleted by SE)]] || || [[Arbitrations/a20080702.1|a20080702.1]] || [[Arbitrations/a20080702.1|User requests to delete account with Assurance Points]] || || [[Arbitrations/a20090618.3|a20090618.3]] || [[Arbitrations/a20090618.3|Assurer requests to delete account]] || || [[Arbitrations/a20090618.5|a20090618.5]] || [[Arbitrations/a20090618.5|User requests to delete account with no Assurance Points]] || || [[Arbitrations/a20090826.1|a20090826.1]] || [[Arbitrations/a20090826.1|User wants account deleted, no Assurance Points, no certificates]] || || [[Arbitrations/a20090926.1|a20090926.1]] || [[Arbitrations/a20090926.1|User wants account deleted, no Assurance Points, no certificates]] || see also: [[Arbitrations/Training/Lesson20|Arbitrations Training Lesson 20 - Arbitration Case - Delete Account Request]] ||[[Arbitrations/a20090810.2|a20090810.2]] ||[[Arbitrations/a20090810.2|User requests removal of first (incorrect) account.]] || ||[[Arbitrations/a20080702.1|a20080702.1]] ||[[Arbitrations/a20080702.1|User requests to delete account with Assurance Points]] || ||[[Arbitrations/a20080702.1|a20080702.1]] ||[[Arbitrations/a20090618.3|Assurer requests to delete account]] || ||[[Arbitrations/a20090703.2|a20090703.2]] ||[[Arbitrations/a20090703.2|please remove me from the database (SE special case)]] || ||[[Arbitrations/a20090913.1|a20090913.1]] ||[[Arbitrations/a20090913.1|user want that we remove his name and email from lists archive]] || ---- . CategoryArbitration . CategoryArbCaseAccountDelNonAssurer