* Case Number: a20131210.1 * Status: closed * Claimants: Marcus M * Respondents: CAcert * Inital Case Manager: EvaStöwe * Case Manager: MartinGummi * Arbitrator: EvaStöwe * Date of arbitration start: 2013-12-12 * Date of ruling: 2013-12-13 * Case closed: 2013-12-20 * Complaint: Find out some information about when accounts where created * Relief: TBD Before: Arbitrator Eva Stöwe (A), Respondent: CAcert (R), Claimant: Marcus M (C), Case: a20131210.1 == History Log == . 2013-12-10 (issue.c.o) case [s20131210.105] . 2013-12-11 (iCM): added to wiki, request for CM / A . 2013-12-11 (iCM): notification to C . 2013-12-12 (CM)/(A): Take care of this case . 2013-12-12 (CM): init mail to C . 2013-12-13 (A): ruling . 2013-12-20 (A): close case == Original Dispute, Discovery (Private Part) (optional) == * '''Link to Arbitration case [[Arbitrations/priv/a20131210.1|a20131210.1 (Private Part)]], Access for (CM) + (A) only''' ## ==> INCLUDE SECTION BOT <> ## <== INCLUDE SECTION EOT ==== EOT Private Part ==== == Discovery == * In original dispute, C asks to find out some information about when accounts where created. . Please find out the id number of the first account created in a year. This is needed to give support the chance to see in what year an account was created as the SE console does not give this information back. * Proposed SQL statement by C: {{{#!highlight sql select year(`created`) as `year`, min(`id`) from `users` group by `year`; }}} * The proposed sql-statement was created, tested and approved by two software assessors (Benny B, Michael T) @ sap 2013-12-10 * Need of support to get this informations * Support is often asked by arbitration to provide some data about members. Especially if it is not known if CCA was accepted by a member. For members who did not do some recent actions through which an CCA acceptence could be recorded, this could be guessed by the creation date of an account.The same is true for some other policies like AP or the assurance programs CAcert went through. * Currently there is no other way to decide in this cases than by looking up a lot of dates of activities in accounts, to only estimate the answer. * If support would be able to provide at least the creation year some of those questions could be answered much better while reducing the personal informations that would be revealed to arbitration. * Privacy considerations: * Account ids don't contain any private information about our members. * They cannot be used to look up private informations by other members, providing account ids does not * If the proposed ids were provided, support can guess the creation year for each account * While this is some personal knowledge about the member, at the time support can see the id of the member, support has already access to more critical personal informations of the member. * Support already has access to the first CCA-acceptance date. For new accounts this is the same date as the creation date. This is a much more precise information than what is asked in the dispute. * Support can also see some other dates of activity for all accounts. * Currently support is asked to provide a lot of those informations to arbitration, when the real question to answer would be, if an account was created during the existance of some policies which will then be guessed by arbitration afterwards according to the activities on the account. * If support could provide an estimate about the creation of the account without looking up and sending all those other dates to arbitration, the need for seeing more personal informations about the member would be reduced. * To give support the additional information, in which year an account was created should be no problem compared to what they already know and can guess about an account. * Other - more critical - personal informations would get better protection, doing so. * Who else should see the results? * Arbitration has a similar need for the results as support, even as they see less account ids directly. And they are also trusted to handle such informations. * If one can calculate the year of joining CAcert by looking at the id of a member, this provides some personal information about the member, that is currently not available to other members. * Members can see the ids of their assurers and assurees. * While there is a good reason for support and arbitration to know about the creation year of an account, there is no reason why members (or even non-members) should see this information about each other. * The results should be accessible by support team and arbitration team, only. == Ruling == * Critical team should execute the following sql-query and send the results encrypted to C as a support member, A and CM. {{{#!highlight sql select year(`created`) as `year`, min(`id`) from `users` group by `year`; }}} * Support is also allowed to ask critical team for further executions of this query. Each additional execution has to be documented in this case file as post arbitration action. * Support has to present the results on a page that is visible to support team and arbitration team, only. This page should be linked in this case file. Cologne, 2013-12-13 == Execution == . 2013-12-13 (A): send ruling to C, Critical, Support and CM . 2013-12-13 (A): send execution order to Critical Team . 2013-12-15 (Crit): result . 2013-12-15 (A): send execution order to C to present results according to ruling . 2013-12-15 (C): send link [[Arbitrations/a20131210.1/Internal-Information|Link to results]] (access for support engineers and arbitrators only) == Post Arbitration Actions == === Additional Executions === . 2014-01-01 (Support): asked for additional execution of query for first ID of 2014 . 2014-01-01 (Crit): result of additional execution . 2015-01-09 (Support): asked for additional execution of query for first ID of 2015 . 2015-01-09 (Crit): result of additional execution == Similiar Cases == || [[Arbitrations/a20091118.1|a20091118.1]] || [[Arbitrations/a20091118.1|Assurance(s) while TTP program frozen 3 disputes: TTPfrozen, Arbitrator, Systemchanges]] || || [[Arbitrations/a20100822.1|a20100822.1]] || [[Arbitrations/a20100822.1|SQL query]] || || [[Arbitrations/a20130521.1|a20130521.1]] || [[Arbitrations/a20130521.1|Adhoc SQL query: Dispute to get some statisical data (U18)]] || || [[Arbitrations/a20090424.1|a20090424.1]] || [[Arbitrations/a20090424.1|Ad hoc SQL query requested]] || || [[Arbitrations/a20090427.2|a20090427.2]] || [[Arbitrations/a20090427.2|Ad hoc SQL query requested]] || || [[Arbitrations/a20090518.2 |a20090518.2 ]] || [[Arbitrations/a20090518.2 |SQL: mail addresses of former assurers without the CATS passed]] || || [[Arbitrations/a20090525.1|a20090525.1]] || [[Arbitrations/a20090525.1|Event officer request recurrent notification to assurers near the location of the following ATEs]] || || [[Arbitrations/a20090810.3|a20090810.3]] || [[Arbitrations/a20090810.3|User requests a list of people who have more than 150 points]] || || [[Arbitrations/a20090902.1|a20090902.1]] || [[Arbitrations/a20090902.1|request list of OA]] || || [[Arbitrations/a20091221.1|a20091221.1]] || [[Arbitrations/a20091221.1|U18 query]] || || [[Arbitrations/a20101114.1|a20101114.1]] || [[Arbitrations/a20101114.1|Addtl. adhoc interactive sql-query]] || || [[Arbitrations/a20110413.1|a20110413.1]] || [[Arbitrations/a20110413.1|How many users using sample pwd]] || || [[Arbitrations/a20110221.1|a20110221.1]] || [[Arbitrations/a20110221.1|PII and problematical sys settings on 1057 of 1074 deleted accounts cases still remains in database]] || ---- . CategoryArbitration . CategoryArbCaseSystemTasks