• Arbitrations
• a20130530.1

• Case Number: a20130530.1
• Status: running
• Claimants: CAcert (Support) - formerly: Marcus M
• Respondents: CAcert

• Initial Case Manager: AlexRobertson

• Case Manager: PietStarreveld

• former Case Manager: EvaStöwe

• Arbitrator: EvaStöwe

• former Arbitrator: MartinGummi

• Date of arbitration start: 2013-11-28
• Date of ruling: 201Y-MM-DD
• Case closed: 201Y-MM-DD

• Complaint: AdHoc SQL Query to get info about accounts with DOB in the future.

• Relief:
  • original Relief (dropped at 2016-08-21)

    1. to lock the account of the users that show a dob in the future immediately.

    2. to check the accounts if there is activity on them.

    3. if no activity in the account: delte those accounts.

    4. if no activity "give user chance to fix the dob to the correct value".

  • updated relief (update at 2016-08-21):
    1. check assurances of those accounts
       • if assured:
       • revoke assurance (inform both sides)
       • if assurer appear multiple times: warning about better assurances (information where to find training or rules)
    2. up to 3-times an automated mail to all those accounts:
       • write to them and explain issues, and options to fix or to close account

Before: Arbitrator EvaStöwe (A) - former Arbitrator MartinGummi, Respondent: CAcert (R), Claimant: CAcert (Support) - formerly: Marcus M (C), Case: a20130530.1

• Note: until 2016-08-21 this case was handled with the following role entries:
  • Claimants: Marcus M
  • Respondents: CAcert

  • Case Manager: EvaStöwe

  • Arbitrator: MartinGummi

• At 2016-08-21 the Arbitrator was exchanged because of inactivity and wish to resign.
• Because the former Case Manager was much more experienced in that case, the new arbitration roles were assigned with former CM in role of Arbitrator and a new Case Manger.
• As first action the new Arbitrator reviewed the parties (as DRP requests), as Marcus M had left the support (and software) team. Because of this it had to be re-evaluated if the case was a personal case or a role-based case. The according evaluation was done together with representatives of support and board. (Marcus M had not answered to comparable questions in other cases.)
• The result of this review was that the case was a role based case from direction of support (even as software team would be more appropriate and matching the dispute)
  • membership of that team was mentioned in signature
  • authority for actions of that team was requested
• This was decided even as the dispute start with mentioning the software team, but the dispute itself does not contain much that would be of interest for the software, the software assessors or software contributors.

History Log

• 2013-05-30 (issue.c.o) case s20130530.16

• 2013-05-30 (iCM): added to wiki, request for CM / A
• 2013-11-28 (CM): I take care about this case as CM and select Martin Gummi as A
• 2013-12-03 (A, CM, C, Software): discussed and tested SQL-query at software-team-meeting, 2 Software-Assessors authorized sql4
• 2013-12-09 (A): intermediate ruling and order to criticals to execute sql4
• 2013-12-09 (Criticals): Results have been mailed encrypted to the stated persons
• 2013-12-09 (A, CM, C as Support): life session for first analyzis of results
• 2014-03-30 (CM): asks A about progress in this case
• 2016-06-24 (CM): repeats question about progress in case, mentions possible exchange of arbitrator for this case
• 2016-08-21 (former CM): switches arbitrators because of inactivity and resignation of former A, afterwards roles of CM and A (see note above)

Now: A: Eva Stöwe, CM: PietStarreveld

• 2016-08-21 (A, CM, support, board member): face to face session about who is claimant and nature of case and possible future of case, (Support) updates the requested relief, as a lock of accounts and futher original requested steps were not favored by either former and current A and also neither (Support), nor (board member - and porbably board in total)

Now: claim is understood to be filed for support and not as a personal case and will be continued like this, claimant clarified to be: CAcert (Support)

• 2016-08-22 (A): send former communication to new CM
• 2016-08-22 (A): informs former C and Support that former C is dropped as claimant of this case and that the case is continued with CAcert (Support) as claimant
• 2016-08-26 (A): requests member to confirm destruction of database data obtained in previous role
• 2016-08-28 (CM): asks former A to confirm all emails relevant to this case have been shared with A and CM

• 2016-08-28 (A): asks for volunteers on cacert-teams@l.c.o and software@l.c.o mailing lists to develop one or more additional queries

• 2016-08-29 (PietStarreveld): as list member, sends query proposal to A

• 2016-08-29 (A): thanks list member for query and points out that the proposed query lists more data than requested but explains that the additional data is already part of the initial data set and therefore decides the query proposal is not problematic
• 2016-08-29 (A): requests Software Assessors to review and test the proposed query and provide a more suitable query if applicable or required
• 2016-09-16 (A): requests Support to lookup the primary email address of member as member doesn't reply to emails sent to his last known working email address
• 2016-09-16 (A): asks Support to investigate whether the original request to member sent on 2016-08-26 has been handled as part of the resignation and
• 2016-09-16 (Support): confirms that member's primary email address and last known working email address are identical
• 2016-09-16 (A): first reminder of request to member dated 2016-08-26 to confirm destruction of database data obtained in previous role
• 2016-10-13 (A): second and last reminder to member to confirm destruction of database data obtained in previous role, request to reply within two weeks
• 2016-10-13 (A): remind Software Assessors to review proposed query for obtaining info about accounts with DOB in the future
• 2016-11-01 (CM): Request Support to provide primary email address of former Arbitrator of a20130530.1 and a20131128.1
• 2016-11-12 (Support): replies to request for primary email address of former Arbitrator
• 2016-11-25 (CM): thanks Support for providing the requested information
• 2016-11-25 (CM): send second request to former A to confirm all emails relevant to this case have been shared with A and CM
• 2016-11-25 (CM): receive NDR on second request to former A due to recipient exceeding storage allocation
• 2016-11-25 (Software Assessor): confirms having reviewed the query and suggests an update to the filter
• 2016-11-25 (A): requests Software Assessor to provide the required update to the query
• 2016-11-25 (Software Assessor): provides the required update to the query
• 2016-11-26 (CM): thanks Software Assessor for reviewing and updating the query

• 2016-11-26 (A): gives intermediate ruling II - "AdHoc SQL Query to get info about accounts with DOB in the future" - request Critical Team to execute query

• 2016-11-28 (Critical Team): executes requested query and provides results in encrypted form
• 2016-11-29 (CM): thanks Critical Team for executing the query and providing the results encrypted

Link to Arbitration case a20130530.1 (Private Part), Access for (CM) + (A) only

EOT Private Part

Dispute

Original Dispute

Dear arbitrators,
 
while looking on the statistics about the PoJAM users the software team suggests to lock the account of the users that show a dob in the future immediately.

In a second step supportshould check the accounts if there is activity on them.

If there is no activity the account should beanonymised similar to delete account routine v3 but with the difference that the primary email address be ayyyy.mm.dd.x@c.o. while  the existing email address should be kept as second address in theaccount. This is done to identify if the user tries to dispute the original email address.

In the case of activity the user should get the chance to fix the dob to the correct value.

SQL statement to get the data for the clean up.
 
SELECT `users`.`fname`, `users`.`lname`, `users`.`email`, `users`.`dob` 
from `users` WHERE
YEAR(`users`.`dob`)>=2013

Updated Relief at face-to-face session at 2016-08-21

• instead of formerly requested activities:
  1. check assurances of those accounts
     • if assured:
     • revoke assurance (inform both sides)
     • if assurer appear multiple times: warning about better assurances (information where to find training or rules)
  2. up to 3-times an automated mail to all those accounts:
     • write to them and explain issues, and options to fix or to close account

Discovery 1

• CCA 2.3 Obligations

You are obliged
    to provide accurate information as part of Assurance. You give permission for verification of the information using CAcert-approved methods.
    to make no false representations. 

• C asked with orignial dispute to
  • find all members with DoB in the future, with a SQL-query, since their DoB is definitely wrong and conflict with CCA 2.3
  • to lock those accounts
  • order support to check activitiy on those accounts

  • if no activity can be found, to let the account be anonymised similar to delete account routine v3 but with the difference that the primary email address be ayyyy.mm.dd.x@c.o. while the existing email address should be kept as second address in theaccount.

  • give active members a chance to fix their DoB.
• A, CM, C decided to expand the case to all members with a DoB newer than the date their account was created, since the same arguments can be applied to those accounts.

• a20130521.1 already checked that there are about 12 members with a DoB after 2013.

• The software does not allow to create an account with a DoB in the future, any more.
• Instead of the procedure proposed by C the following procedure may be better.
  • Use the SQL-query to finde those members.
  • Inform those members about the problem and ask them to change their DoB.
  • Later: check again, if there remain accounts with this error.
  • Lock only the remaining accounts and initiate CCA-violation-cases against them.

• Skipped init mail since A & CM had direct contact with C at software-meeting, before there was time to write it. C is known to have accepted CCA and know arbitration procedures.

• SQL-query was modified and tested on the testserver by two software assessors.
• Version sql4 was authorized by two software assessors (Benny B and Michael T) at 2013-12-03.
• sql4 authorized by software

   1 SELECT `id`, `email`, `fname`, `lname`
   2 FROM `users`
   3 WHERE DATE(`dob`) >= DATE(`created`)
   4 AND `deleted` = 0

Discussion 1 of sql4

Which personal informations of the members will be disclosed to whom by this query?

• Reasons behind the query.

  • There are known inconsistencies in accounts where the DoB is in the future. (a20130521.1)

  • CAcert should not tolerate such inconsistencies in accounts of members who are using CAcert-certificates, since other people depend on CAcert knowing their members.
  • To be able to get audited CAcert needs to reduce known inconsistencies as much as possible.
• Who will receive the information?
  • Critical team will receive the results
    • Criticals are needed to perform the query, since nobody else has the rights to do so.
    • They will be able to see the results.
    • They are authorized by a board motion to handle such information, if ordered by arbitration, according to SP/SM. They also should have ABC and are bound by SP,SM and PP.
    • There is no other way to collect the information.
  • Arbitrators will receive the results
    • Since A has to authorize the query by a ruling A needs to check if the results.
    • A needs the results primarily to decide how to proceed with the next step. This will depend at least on the amount of the results and may also depend on the results themselves.
    • The CM has to have the same informations as A, since the CM is there to supervise the actions of A (four eyes principle) and to be able to provide all gathered informations to another A if the original one is not able to proceed with the case.
    • A and CM don't have ABC, but they will probably see the personal informations, anyway, if further actions against the members have to be initiated, because of their roles as arbitrators.
    • As arbitrators they are trusted to handle personal informations appearing in cases. They are authorized to do so by board, as stated in DRP.
  • Support may receive results later
    • Support will have to be involved in the next steps to resolve the account inconsistencies that will be discovered by the query. C will then be able (or is needed) to see the results, and maybe more personal informations of the concerned members, anyway.
    • Support team members have ABC and are authorized to handle personal informations of members in this role.
  • May the Claimant see the results?
    • The claimant is a support team member.
    • Even if it would not be ok to present the results of the query to any possible claimant it is ok to do so in this case, at least if C will be involved as support member in the cleanup.
• Which kind of personal informations of members will be discovered by the query?
  • primary- email

    • Is needed to contact the members, so that they can change wrong DoBs, or to look up their accounts by support, if needed later during the process.

  • id of the account
    • Though not explicitly needed for the process, it can help to distinguish accounts by support, if a member happens to have multiple accounts with wrong DoB.
    • While the primary email address is unique for every account, it is quite helpful for support to have a second attribute to identify the correct account.
    • The account-id is not a personal information of the member and of little use beside identifying an account.
  • fname, lname
    • The members in question could be contacted without them, but mails are considered more legit, if they are addressed personally, even if they will be signed.
    • Since at least the DoB is wrong, there may be other inconsistencies in the account. To be able to handle clear fake accounts differently, as compared to accounts that may just have a wrong DoB because of an error of the member, in the next step, the names will be needed.
    • Support could be ordered to provide them, but support does not have the authority to decide on them.
      • In this case the only group of people that would not see the names would be critcals.
      • Criticals are known to get hands on personal informations of members, in special situations, as stated above.
      • But to use support to collect the names would require significant additional work by support, depending on the amount of results.

Intermediate Ruling 1

Critical team are ordered to execute the SQL query and to pass the results encrypted to Marcus as (C) with a copy to myself as (A) and Eva as (CM)

2013-12-09, Lübeck, Germany

Discovery 2

• A, CM and C (in his role as support) had a life-session at 2013-12-09 to make a first analysis of the results. C was allowed to do a spot check on 10 of the accounts, for this.
• There were 161 accounts in the results.
• At first glance:
  • 6 are accounts previously annonymized through former arbitration cases
  • 49 look like accounts that were intended for organizations by the users - and probably don't contain any real personal informations
  • 27 accounts seam to contain completely fake data
  • 79 don't show obvious indications of false data in fname and lname
• The relief was updated to:
  1. check assurances of those accounts
     • if assured:
     • revoke assurance (inform both sides)
     • if assurer appear multiple times: warning about better assurances (information where to find training or rules)
  2. up to 3-times an automated mail to all those accounts:
     • write to them and explain issues, and options to fix or to close account
• The request for revokation of assurances for obviously incorrect data is sensible, as it is not possible to rely on such assurances.
• It also makes sense to inform the affected members both about the situation as well as about reasons for revokation of the assurances.
• If both is done, it makes sense to first revoke any such assurance (with explanation) and then to address all affected members about correcting their data and the reason for having correct data and possible alternatives (like closing down the account, trying to get an organisation account, using personal data for certificates on addresses / emails of organisations)
  • The reason is that as long as there are assurances a member cannot change the names or date of birth
• The following query was provided by a member and reviewed by a Software Assessor (with a minor update to match the database structure better):

   1 SELECT `users`.`id`, `users`.`email`, `users`.`fname`, `users`.`lname`,
   2 count(*) as "assurances"
   3 FROM `notary`
   4 INNER JOIN `users`
   5    ON  (`notary`.`to` = `users`.`id`
   6         AND DATE(`users`.`dob`) >= DATE(`users`.`created`)
   7         AND `users`.`deleted` = 0)
   8 WHERE ((`notary`.`points` > 0 or `notary`.`awarded` > 0)
   9        AND `notary`.`deleted` = 0)
  10 GROUP BY `users`.`id`, `users`.`email`, `users`.`fname`, `users`.`lname`;

• This query is meant to be used
  • to identify accounts with
    • DoB in the future,
    • which are not deleted and
    • have at least one un-deleted assurance with more than 0 AP
  • It also should provide the number of such assurances
• Beside of the counter the query provides a subset of the data of the query spl4 which already was executed in the context of this case. As the data is already known in the context of this cases, no additional considerations about data protection have to be done before execution of that query.

Intermedate Ruling II

I hereby rule as Intermediate Ruling II:

Critical team should execute the following query. The result should be send encrypted to the Arbitrator (and the CM if a public key is available).

Query:

SELECT `users`.`id`, `users`.`email`, `users`.`fname`, `users`.`lname`,
count(*) as "assurances"
FROM `notary`
INNER JOIN `users`
   ON  (`notary`.`to` = `users`.`id`
        AND DATE(`users`.`dob`) >= DATE(`users`.`created`)
        AND `users`.`deleted` = 0)
WHERE ((`notary`.`points` > 0 or `notary`.`awarded` > 0)
       AND `notary`.`deleted` = 0)
GROUP BY `users`.`id`, `users`.`email`, `users`.`fname`, `users`.`lname`;

Eva Stöwe - 2016-11-26

Ruling

Execution

Similiar Cases

Bug reports

• CategoryArbitration

• CategoryArbCaseSystemTasks