* Case Number: a20110226.1
  * Status: Closed
  * Claimants: Thomas N
  * Respondents: CAcert
  * Initial Case Manager: UlrichSchroeter
  * Case Manager: BernhardFröhlich
  * Arbitrator: UlrichSchroeter
  * Date of arbitration start: 2011-03-10
  * Date of ruling: 2011-03-13
  * Case closed: 2011-03-14
  * Complaint: Auto dispute request doesn't work (Account cleanup)

  * Relief: TBD

Before: Arbitrator UlrichSchroeter (A), Respondent: CAcert (R), Claimant: Thomas N (C), Case: a20110226.1


== History Log ==
 . 2011-02-26 (issue.c.o) case [s20110226.6]
 . 2011-02-26 (iCM): added to wiki, request for CM / A
 . 2011-03-02 (C): requests progress report
 . 2011-03-04 (iCM): progress report request response to (C)
 . 2011-03-09 (CM): I'll take care about this case as (CM)
 . 2011-03-09 (A): I'll take care about this case as (A)
 . 2011-03-09 (A): sending init mailing with CCA/DRP acceptance request to (C)
 . 2011-03-10 (C): accepts CCA/DRP under this arbitration
 . 2011-03-10 (A): please check if (C)'s account can be opened under Admin console
 . 2011-03-11 (Support): [s20110310.24] trying open account under Admin console, results in: Account not found.
 . 2011-03-11 (A): request to (Critical Admins): to execute Account Discovery query and Deleted Account state query over (C)'s users email
 . 2011-03-12 (Critical Admin): sends report
 . 2011-03-12 (A): request to (Critical Admins): (request #2) to execute sql query about state of (C)'s email address under table 'email'
 . 2011-03-12 (Critical Admin): sends report to request #2
 . 2011-03-12 (A): exec request to (Support) to check (C)'s account by ID
 . 2011-03-13 (Support): [s20110312.27] exec report
 . 2011-03-13 (A): request to (Critical Admins): (request #3) to execute sql query about state of (C)'s email address under table 'email' with addtl. fields as output
 . 2011-03-13 (Critical Admin): sends report to request #3

== Original Dispute, Discovery (Private Part) ==

 * Link to Arbitration case [[Arbitrations/priv/a20110226.1|a20110226.1 (Private Part)]]

<<Include(Arbitrations/priv/a20110226.1)>>  

==== EOT Private Part ====

== Discovery ==
 * Account Discovery and Recovery Procedures can be found under case [[Arbitrations/a20100131.1|a20100131.1]]

 * For discovery of the status of the account, the following SQL query can be used (see [[Arbitrations/a20090703.2|a20090703.2]], [[Arbitrations/a20100131.1|a20100131.1]]):
{{{
SELECT id, fname, mname, lname, suffix, dob FROM `users` WHERE email = '<email>';
SELECT n.* FROM `users` u LEFT JOIN `notary` n ON n.`from`=u.`id` OR n.`to`=u.`id` WHERE u.`email` = '<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` = '<email>'  GROUP BY d.`id`;
SELECT COUNT(ec.id) FROM `users` u LEFT JOIN emailcerts ec ON ec.memid=u.id WHERE u.`email` = '<email>';
}}}

 * sql query to verify delete status of account (see also [[Arbitrations/a20100131.1|a20100131.1]])
  {{{
SELECT id, deleted  FROM `users` WHERE email = '<email>';
}}}

 * SQL query to recover a deleted account (see also [[Arbitrations/a20100131.1|a20100131.1]])
  {{{
update `domains` SET `deleted`=0 WHERE `domains`.`memid`='<ID>';
update `email` SET `deleted`=0 WHERE `memid`='<ID>';
update `users` SET `deleted`=0 WHERE `id`='<ID>';
}}}

 * Support-Engineers cannot find nor access the account
 * Assurers who searches for the given email address receives a warning message (red warning letters)
  {{{
You are about to assure a person that isn't currently verified. If you continue and they do not verify their account within 48 hours the account could automatically be removed by the system.
}}}

 * Tests on a local testserver to reproduce this scenario results:
  * table email, field hash filled with text (not empty)

 * SQL query for verification, that hash field is filled ((Critical Admins) request #3)
  {{{
SELECT id, memid, deleted, hash, attempts  FROM `email` WHERE email = '<email>';
}}}
  * result: field is filled on related email record

 * Account is blocked by "hash" value in email table
  * Proposed Solution steps:
   1. sql update query to critical team
    1. lock users record so user cannot login to this account but account can be accessed by SE
    1. remove "hash" value in table email
   1. exec request to Support to exec Delete Account Procedure for SE's v2 over this account

 * Proposed Update SQL query to block-off users account that SE's can access the related users account to execute the Delete Account procedure for SE's v2
  {{{
# Step 1: lock affected user account that user cannot login to this account
update users set locked=1 where email='<email>';

# Step 2: unblock (remove) hash value in table email, field hash of effected users email record
update email set hash='' where email='<email>';
}}}

== Ruling ==
 * Discovery process evaluated that the affected users account is blocked by a filled table email, field hash with a value, that prohibits access to the users record by Support-Engineers, nor by the user itself, but showed up to Assurers, who searches for the Assuree's email address with a warning, that this account has not been confirmed yet
 * To block off the deadlock the hash field needs to be cleared.
 * As the old users record should be cleaned up (delete old account), I hereby order the following execution steps to be handled
  a. by Critical Sysadmins team, to execute the Update SQL queries given. This Update SQL query includes two steps:
   1. lock affected user account that user cannot login to this account any more
   1. unblock (remove) hash value in table ''email'', field ''hash'' of effected users email record, so Support-Engineers can access the affected account to execute the Delete Account procedure
  a. by Support-Engineer, to execute the [[Arbitrations/Training/Lesson20/DeleteAccountProcSEv2|Delete my Account procedure for SE's v2]] on the affected users account
 * CCA termination date is not to set, because user still continues to be a member by his 2nd account.

Frankfurt/Main, 2011-03-13

== Execution ==
 * 2011-03-13 (A): sending ruling to (C), (Critical Sysadmins t/l)
 * 2011-03-13 (A): sending Update SQL query request to (Critical Sysadmins t/l), (Critical Sysadmins)
 * 2011-03-13 (Critical Sysadmin): exec report, user record locked, hash cleared of affected user account
 * 2011-03-13 (A): sending exec request to (Support) (current state of affected account is ''locked'')
 * 2011-03-13 (Support): [s20110313.24] access to account failed !
 * 2011-03-14 (A): sending advanced Update SQL query request to (Critical Sysadmins t/l), (Critical Sysadmins)
 * 2011-03-14 (Critical Sysadmin): exec report, advanced SQL update script executed
 * 2011-03-14 (A): sending exec request to (Support) (try #2) (current state of affected account is ''locked'')
 * 2011-03-14 (Support): [s20110314.35] executed this ticket following the DeleteAccountProcSEv2
 * 2011-03-14 (A): Exec report to (C). Case closed.

== Similiar Cases ==
|| [[Arbitrations/a20100215.2|a20100215.2]] || [[Arbitrations/a20100215.2|Delete (2nd) Account Request ]] ||
|| [[Arbitrations/a20100506.1|a20100506.1]] || [[Arbitrations/a20100506.1|I would like to either have you remove my registration record or replace the first name in "my details" of my record]] (dismissed) ||
|| [[Arbitrations/a20101204.1|a20101204.1]] || [[Arbitrations/a20101204.1|disputing email address, accounts cleanup thru arbitration]] ||

see also: [[Arbitrations/Training/Lesson20|Arbitrations Training Lesson 20 - Arbitration Case - Delete Account Request]]

----
 . CategoryArbitration
 . CategoryArbCaseAccountCleanup
 . CategoryArbCaseEmailDispute