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

History Log

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

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)

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

EOT Private Part


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 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

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

Discovery 1

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. 

   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?

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

   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`;

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).


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



Similiar Cases


Adhoc SQL query: Dispute to get some statisical data (U18)


Ad hoc SQL query requested


Ad hoc SQL query requested


SQL: mail addresses of former assurers without the CATS passed


Event officer request recurrent notification to assurers near the location of the following ATEs


User requests a list of people who have more than 150 points


request list of OA


U18 query


SQL query


Addtl. adhoc interactive sql-query


How many users using sample pwd


PII and problematical sys settings on 1057 of 1074 deleted accounts cases still remains in database

Bug reports

bug #872

PoJAM restricitions to apply to production system (several restrictions) PoJAM 3.3,, 4.1, 4.2

Arbitrations/a20130530.1 (last edited 2016-11-29 07:31:10 by PietStarreveld)