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

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

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

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

a20130521.1

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

a20090424.1

Ad hoc SQL query requested

a20090427.2

Ad hoc SQL query requested

a20090518.2

SQL: mail addresses of former assurers without the CATS passed

a20090525.1

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

a20090810.3

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

a20090902.1

request list of OA

a20091221.1

U18 query

a20100822.1

SQL query

a20101114.1

Addtl. adhoc interactive sql-query

a20110413.1

How many users using sample pwd

a20110221.1

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