* Case Number: a20131124.2
  * Status: close
  * Claimants: Marcus M
  * Respondents: CAcert
  * Inital Case Manager: EvaStöwe
  * Case Manager: MartinGummi
  * Arbitrator: EvaStöwe
  * former Arbitrator: MartinGummi
  * former Case Manager: EvaStöwe
  * Date of arbitration start: 2013-12-04
  * Date of ruling: 2014-06-09
  * Case closed: 2014-06-12
  * Complaint: SQL Request for analysing assurance data for wrong entries

  * Relief: TBD

Before: Arbitrator EvaStöwe (A), Respondent: CAcert (R), Claimant: Marcus M (C), Case: a20131124.2


== History Log ==

ASR1-6: assurer 1-6
ASE1-7: assuree 1-7

 . 2013-11-24 (issue.c.o) case [s20131124.68]
 . 2013-11-29 (iCM): added to wiki, request for CM / A
 . 2013-11-29 (iCM): info mail to C
 . 2013-12-03 (CM): I take care of this take 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 sql1
 . 2013-12-09 (C, A, CM): life session to discuss first action in the case
 . 2013-12-14 (A): Intermediate Ruling I, send to criticals
 . 2013-12-15 (Critical Team): send execution notice and results encrypted to A, CM, C
 . 2014-01-12 (A): asked (AS) with a known negative assurance in his account if the account may be accessed by Support to get more information about how the assurance looks like, to clarify why it was not found in the result set. (per chat)
 . 2014-01-12 (A), (CM), (C), (software): checked how negative assurances are displayed on the testserver for SE and members (old and new), also looked into the software, for rounding to 0 of negative assurances for those sides (life session via VoIP)
 . 2014-01-13 (CM): asked (AS) if he sees the mentioned negative assurances with negative values in his account - (AS) does not - (AS) proposed to search for the mails he got as assuree for those assurances (via phone)
 . 2014-01-13 (A): (Support) to look up the assurance with negative value in the account of (AS)
 . 2014-01-13 (Support): no assurances with negative values found in (AS) account, the mentioned assurances are displayed with 0 points
 . 2014-01-15 (CM), (C), (Software): discussed and tested sql-query2 at software-team-meeting, 2 Software-Assessors authorized sql2
 . 2014-01-21 (A): Intermediate Ruling II, send to criticals
 . 2014-01-21 (Critical Team): send execution notice and results encrypted to A, CM, C
 . 2014-02-16 (former A) switched A and CM
 . 2014-02-26 (A): asks C and C of a20140126.1 if the would agree to split of the handling of 8 asssurances and merge the rest with a20140126.1
 . 2014-02-27 (C of a20140126.1): has no issues with this proposal, as long as the result would be documented with the rest of the statistic information gained here 
 . 2014-03-03 (C): agrees to the proposed procedure
 . 2014-03-04 (A): partial ruling (split and merge of case), send to C and C (now C1) of a20140126.1
 . 2014-03-04 (Software): provided, tested and approved SQL-query to get assurance ids for assurances with awarded points <0 or >150
 . 2014-03-04 (A): documents merge with a20140126-1
 . 2014-03-05 (A): intermediate ruling III, send to criticals
 . 2014-03-07 (Critical Team): reports results
 . 2014-03-07 (C): asks if he should/may look up email-addresses for the ids provided by the query (VoIP)
 . 2014-03-07 (A): acknowledges C (VoIP)
 . 2014-03-07 (C): send according data encryptet to A, CM, 2 entries missing
 . 2014-03-08 (C): updates the list, encrypted to A, CM
 . 2014-04-01 (A): conatacts assurer ASR1-6 and assurees ASE1-7 with the idea to change points of the 8 assurances into the range of 0-35 and asks if they oppose this idea
Following this there was some mail contact - mostly simple agreements. It would blow up this log without sense to report them singularly, they are collected in the table below. (Sometimes there were short responses from the side of A, too.) 
Communication that went deeper is reported in this log.

|| assurer || answer || AP granted || Comment || assuree || answer ||
||  ASR1 || NDR 2014-04-02 || -1 || user stays over 100 AP || ASE1 || ok 2014-04-12 (in person) ||
||  ASR1 || NDR 2014-04-02 || -1 || user stays over 100 AP || ASE2 || ||
||  ASR2 || ok 2014-04-02 || -20 || user stays over 100 AP || ASE1 || ok 2014-04-12 (in person) ||
||  ASR3 || ok 2014-04-14 || -123 || user stays over 100 AP || ASE3 || ok 2014-04-03 ||
||  ASR4 || ok 2014-04-02 || 999 || user stays over 100 AP || ASE4 || ||
||  ASR3 || ok 2014-04-14 || 358 || user will fall below 100 AP || ASE5 || ? 2014-04-02 ||
||  ASR5 || || 355 || user stays over 100 AP || ASE6 || ||
||  ASR6 || ok 2014-04-09 || 350 || user stays over 100 AP || ASE7 || ok 2014-04-02 ||

 . 2014-04-02 (mail-system): bounce for ASR1
 . 2014-04-06 (A): asks Support for @cacert.org - address of ASR1 (as the awailability of such was suspected).
 . 2014-04-06 (Support): provides according address
 . 2014-04-06 (A): corrects due date for answer for assurer and assurees who had not respondet until then
 . 2014-04-06 (A): same information to ASR1 included with request to fix primary email address
 . 2014-04-06 (Mail-system): bounce for ASR1 primary address (@cacert.org does not bounce)
 . 2014-04-16 (A): asks two most active software assessors (SA1, SA2) to provide queries to get those assurances set to 0 / 35 awarded points
 . 2014-04-18 (A): asks ASE5 in german if there are objections as previous answer from AS3 only consisted of "in germany?" and there was no answer to previous reply from A
 . 2014-05-07 (A): asks two other software assessors (SA3, SA4) to provide the mentioned queries.
 . 2014-05-20 (A): reminds second set of software assessors about the request
 . 2014-05-22 (SA3): propoasl for SQL-query
 . 2014-05-23 (A): CARS about ACK from SA4 in person - asks for a test of query because it changes the DB
 . 2014-05-25 (SA3): proposed details for the testing
 . 2014-05-25 (A): comments about testing
 . 2014-05-29 (SA1): tested query on testserver, all queries ok, but only 2 match A's request and so only ok for those two unless the request of A is changed
 . 2014-05-29 (A): only had intention to execute the two that match request as the others are not needed
 . 2014-05-30 (SA3): comments that ruling should have been more precise
 . 2014-05-30 (A): it was no ruling and if it would not have been precise enough the case file whould have provided the needed information, also the request was originally send to two software assesor who were familiar with the former queries and which kind of query would be relevant in this case
 . 2014-06-09 (A): checked with C that his email may be displayed to the affected members in the ruling (privacy, via chat-tool)
 . 2014-06-09 (A): ruling send to critical team, C, CM and affected members (ASR1-6 and ASE1-7) in BCC (for privacy reasons)
 . 2014-06-09 (A): send mail source to CM to verify the BCC-party
 . 2014-06-10 (critical team): executed queries, check was zero
 . 2014-06-12 (A): informed affected members about change, asks assurer to note it on the CAP forms, thanks them
 . 2014-06-10 (A): informs board and internal auditor about result of this case
 . 2014-06-10 (A): thanks and informs C, CM, critical team, SAs
 . 2014-06-12 (CM): close case

The case was split at 2014-03-04. 

Most of the case was merged with [[Arbitrations/a20140126.1|a20140126.1]], while the handling of 8 assurances wich had awarded points outside every sensible range is handled in this case.

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

 * '''Link to Arbitration case [[Arbitrations/priv/a20131124.2|a20131124.2 (Private Part)]], Access for (CM) + (A) only'''  

## ==> INCLUDE SECTION BOT
<<Include(Arbitrations/priv/a20131124.2)>>  
## <== INCLUDE SECTION EOT

==== EOT Private Part ====
== original Dispute ==
{{{
> Hi guys,
>
> a user informed me that he as a one assurance in his account which shows
>
> 100 Assurance Points and 250 Experience Points.
>
> By looking at the data more closely I come to the conclusion that the 
> assurer must have made a mistake and entered 350 instead of 35 points. 
> In the system these 350 points where split into 100 Assurance Points and
>
> 250 Experience Points.
>
> This assurance was made in 2007 when to my knowledge the super assurance
>
> programme was active.
>
> In the current software there is no chance to enter less than 0 and more
>
> than 35 Assurances points.
>
> My request is that arbitration and software should check how many 
> accounts have assurance with more than 35 or less then 0 points.
> in a second step the outcome should be analysed and to see if there is a
>
> need for a clean up and how this clean up should take place.
> My intention is not to remove the super assurances but to clean up cases
>
> as described above.
>
> Software should provide the needed SQL statements.
}}}

== Discovery ==
 * In his role as support engineer, C got to know about an assurance with 100 Assurance Points and 250 Experience Points.
 * This assurance was made in 2007 when, to the knowledge of C, the super assurance programme was active.
 * In the current software there is no chance to enter less than 0 and more than 35 points.

 * C requests in the orignial dispute
  * that arbitration and software should check how many accounts have assurances with more than 35 or less then 0 points
  * in a second step the outcome should be analysed, to see if there is a need for a cleanup and 
  * how this cleanup should take place.

 * The init mail was skipped, 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.

 * There is another arbitration case, about detecting assurances with more than 150 points with an ad hoc SQL-query: [[Arbitrations/a20100822.1| a20100822.1]]. 
 * original CM (today A) of a20100822.1 is A in this case a20131124.2 and knows the results of a20100822.1.
 * a20100822.1 does not explicitly cover assurances over 150. It was searched for assurances below 0 and something was found. The checks were done about 3 years ago, the results could be out-date (there may be much more results than before). 
 * a20100822.1 also does not cover any cleanup.
 * Through a20100822.1 it is known, that there are non-trivial numbers of assurances a20131124.2 addresses.
 * The assurance that got the attention of C was not detected in that case.
 * To be able to decide on a cleanup it is needed to get a more detailed look into the assurances, that cannot be done with only getting numbers for the assurances.
 * Since the grouped check of a20100822.1 was not enough to detect the offending assurance, this also askes for a more detailed query.

 * During a VoIP meeting at 2013-12-09 A, C, CM came to the conclusion, that the first step of the claim was covered enough through a20100822.1 that it could be skipped in this case and it should be directly started with a query to look up the assurances in question grouped by the assurers.

 * A SQL-Query for the detection of those assurances was designed and tested on the testserver by two software assessors.
 * The SQL-query sql1 was authorized by two software assessors (Benny B and Michael T) at 2013-12-03.

 * SQL Query authorized by Benny B and Michael T @ software meeting from 2013-12-03
 {{{#!highlight sql
SELECT `u`.`id`, `u`.`email`, `u`.`fname`, `u`.`lname`, COUNT(`n`.`points`) AS `assurances`, `n`.`points`, `n`.`method`
FROM `users` AS `u`
INNER JOIN `notary` AS `n` ON `n`.`from` = `u`.`id`
WHERE (`n`.`points` > '35'
OR `n`.`points` < '0')
AND `n`.`deleted` = 0
GROUP BY `u`.`id`, `u`.`email`, `u`.`fname`, `u`.`lname`, `n`.`points`, `n`.`method`
ORDER BY `u`.`id`, `n`.`points` DESC
}}}

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

 * 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 statet in the DRP.   
  * Support may receive results later on
    * 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 discoverd by the query?
  * primary-email 
     * Is needed to be able to look up the accounts of the assurers by support, to get more precise informations, that may be needed to decide if they were legit. (For example if they were super assurers, or allowed to assure in other programs.)
  * 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 assurers could be identified without the names, but they will be needed in some cases, to be able to know if they should have the right for some special flags. So the chances are hight, that they will be needed later on, anyway.
    * Support could be ordered to provide 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.
     * To use support to collect the names would require significant additional work by support, depending on the amount of results.
 * Even while this is a lot of personal information, it should only be information about members that are either known anyway, because they took part as assurer in special programs, or members who entered wrong or unreasonable (negative) numbers of assurance points.
  * The privacy of the first group will not be violated greatly through the query, because they waived some of their privacy by entering the program and they should be aware that their activities could be monitored later on, for example by arbitration.
  * The later group entered at least dubious informations and has to undergo that we need to look at some personal informations to be able to decide if and how to fix it.

=== Discovery II (after itermediate ruling I) ===
The results categorized per assurance method and points:
	
|| || 36 || 38 || 39 || 40 || 41 || 42 || 44 || 45 || 46 || 48 || 50 || 52 || 55 || 60 || 61 || 65 || 66 || 70 || 75 || 77 || 80 || 85 || 90 || 95 || 99 || 100 || 105 || 110 || 115 || 118 || 120 || 125 || 130 || 131 || 133 || 135 || 140 || 145 || 150 || Sum||
||                          ||  || 2 ||  || 1 ||  || 1 || 4 ||  || 1 || 1 || 10 ||  || 1 || 3 ||  || 2 ||  || 9 || 1 ||  || 33 || 2 || 16 || 4 ||  || 6 || 1 || 3 || 32 ||  || 5 || 1 || 7 ||  ||  || 2 || 6 ||  || 279 || 433||
|| Administrative Increase  ||  || 1 ||  || 2 ||  || 2 || 2 ||  || 5 || 3 || 5 ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  || 1 ||  ||  || 2 ||  ||  ||  ||  ||  ||  ||  ||  ||  || 1 || 24||
|| CT Magazine - Germany    ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  || 1 ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  || 1||
|| Face to Face Meeting     || 2 || 3 || 1 || 80 || 3 || 3 || 5 || 9 || 2 || 16 || 369 || 1 || 32 || 594 || 1 || 121 || 1 || 108 || 49 || 1 || 73 || 93 || 162 || 50 || 1 || 305 || 30 || 20 || 197 || 1 || 888 || 13 || 178 || 3 || 1 || 13 || 30 || 1 || 1205 || 4665||
|| Thawte Points Transfer   ||  ||  ||  || 1 ||  ||  ||  ||  ||  ||  || 7 ||  ||  || 3 ||  ||  ||  ||  ||  ||  ||  ||  || 6 ||  ||  || 1 ||  || 2 || 1 ||  ||  ||  ||  ||  ||  ||  ||  ||  || 30 || 51||
|| Trusted Third Parties    ||  ||  ||  ||  ||  ||  ||  ||  || 1 || 1 ||  ||  ||  || 2 ||  ||  ||  ||  ||  ||  ||  ||  || 1 ||  ||  || 1 || 1 ||  || 16 ||  || 2 || 1 ||  ||  ||  ||  ||  ||  || 108 || 134||
|| Unknown                  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  || 29 ||  ||  || 9 ||  || 4 ||  ||  ||  ||  || 4 ||  || 61 ||  ||  || 11 ||  ||  || 5 ||  ||  ||  ||  ||  ||  ||  || 2 ||  || 311 || 436||
|| Sum || 2 || 6 || 1 || 84 || 3 || 6 || 11 || 9 || 9 || 21 || 421 || 1 || 33 || 611 || 1 || 127 || 1 || 117 || 50 || 1 || 110 || 95 || 246 || 54 || 1 || 325 || 32 || 25 || 253 || 1 || 895 || 15 || 185 || 3 || 1 || 15 || 38 || 1 || 1934 || 5744||


==== considerations on consistency of results ====

 * The assurance that triggerd C to file the original dispute was not identified in the results set.

 * There were no assurances with negative assurance points found.
 * A member (AS) is known who has got assurances with negative assurance points
  * AS authorized a look into his account to look at those assurances
  * AS also looked into his account
  * Neither support nor AS saw an assurance with less than 0 points (old and new view)
  * AS confirmed that he does not know about any special change of those assurances, especially no arbitration or support activity.

===== research with the testserver =====
 * An assurance on the testserver was changed to negative values (since it is not possible issue assurances with negative points anymore)
  * This assurance was found on the old assurance overview for the member (wot10.php)
  * It was also found on the old assurance overview for support

 * A look into the software repository showed that the new display always shows the maximum of "points" and "awarded" which will always be at least 0. Nothing like this was found for the old view.

 * If the software on the testserver does not have any patches that did affect the old view compared to the life system, the assurances with negative points should be displayed as such on the old views.
  * none were found, so
   * either it can be explained with another patch-status of the software
   * or there is no negative assurance in said account, anymore
    * there is no reason known why the assurances changed to 0

===== general ideas around assurances with negative values =====
 * Beside of this, there are an issue with assurances with negative points
  * the old (wot10) and the new (wot15) view show differnt sums for the assurance points of a member (if it is below 100).
   * if at least wot10 calculates to below 100 it is not clear how many assurance points the member actually has, this affects
    * what certificates the member can issue
    * if he can be an assurer
   * if there would be a negative entry, such an entry could negate a lot of "real" positive assurances and block someone from getting up to 50 or 100 points altogether, if it would be high enough
    * since it is not possible to enter such assurances anymore, this cannot happen anymore
    * but accounts could be affected by this through old assurances
  * If there really are assurances with negative assurance points in the system it would be a good idea to clarify the reason behind them and clean them up together with the corresponding assurers and assurees.
  * Since this is only slightly related to the case that was the reason for the original dispute, a split of the case may be a good idea, too. 

===== updated sql-query with awarded? =====
 * There are two entries stored about how many points the assurer issued with an assurance: "points" and "awarded".
  * the result-set of sql-query with "points" did not include assurances outside 0-150 points which "should be there". 
  * Maybe they can be found with an updated version of the query
  * If nature of the query stays the same with only a change to (or inclusion/substruction of) "awarded", privacy issues have to be answered according to the original query.

 * A SQL-Query for the detection of those assurances based on "awarded" was designed and tested on the testserver by two software assessors.
 * The SQL-query sql2 was authorized by two software assessors (Benny B and Dirk A) at 2014-01-15.

 * SQL Query sql2 authorized by Benny B and Dirk A @ software meeting from 2014-01-15
{{{#!highlight sql
SELECT `u`.`id`, `u`.`email`, `u`.`fname`, `u`.`lname`, COUNT(`n`.`awarded`) AS `assurances`, `n`.`awarded`, `n`.`method`
FROM `users` AS `u`
INNER JOIN `notary` AS `n` ON `n`.`from` = `u`.`id`
WHERE (`n`.`awarded` > '35'
OR `n`.`awarded` < '0')
AND `n`.`deleted` = 0
GROUP BY `u`.`id`, `u`.`email`, `u`.`fname`, `u`.`lname`, `n`.`awarded`, `n`.`method`
ORDER BY `n`.`awarded` DESC
}}}

== Discovery III (after itermediate ruling II) ==
The results categorized per assurance method and points:

||  || -123 || -20 || -1 || 36 || 37 || 38 || 39 || 40 || 41 || 42 || 44 || 45 || 46 || 50 || 52 || 55 || 60 || 61 || 64 || 65 || 66 || 70 || 75 || 80 || 85 || 90 || 96 || 98 || 100 || 110 || 115 || 118 || 120 || 125 || 126 || 130 || 131 || 135 || 140 || 150 || 350 || 355 || 358 || 999 || Sum ||
||                          ||  ||  ||  ||  ||  || 2 ||  || 1 ||  || 1 || 3 ||  ||  || 6 ||  ||  || 1 ||  ||  ||  ||  || 2 ||  || 33 ||  || 14 ||  ||  || 6 || 1 || 2 ||  ||  ||  ||  ||  ||  ||  ||  || 211 ||  ||  ||  ||  || 283||
|| Administrative Increase  ||  ||  ||  ||  ||  ||  ||  ||  ||  || 1 ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  || 1||
|| Face to Face Meeting     || 1 || 1 || 2 || 9 || 1 ||  || 2 || 16 || 1 || 9 ||  || 2 ||  || 22 || 1 || 1 || 4 || 1 ||  || 9 || 1 || 10 || 3 || 34 || 1 || 2 ||  ||  || 38 || 9 || 5 || 1 || 19 || 2 || 1 || 25 || 3 || 4 || 5 || 165 || 1 || 1 || 1 || 1 || 414||
|| Temporary Increase       ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  || 2 ||  || 1 || 8 ||  ||  || 1 ||  || 1 ||  ||  ||  ||  ||  ||  ||  || 1 || 1 || 2 ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  || 1 ||  ||  ||  ||  || 18||
|| Trusted Third Parties    ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  || 1 ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  ||  || 2 ||  ||  ||  ||  ||  ||  ||  ||  || 9 ||  ||  ||  ||  || 12||
|| Sum || 1 || 1 || 2 || 9 || 1 || 2 || 2 || 17 || 1 || 11 || 5 || 2 || 2 || 36 || 1 || 1 || 6 || 1 || 1 || 9 || 1 || 12 || 3 || 67 || 1 || 16 || 1 || 1 || 46 || 10 || 9 || 1 || 19 || 2 || 1 || 25 || 3 || 4 || 5 || 386 || 1 || 1 || 1 || 1 || 728||

There is another arbitration case ([[Arbitrations/a20140126.1|a20140126.1]] that touches comparable issues and questions about assurances. As they have a ddifferent focus, they require the same kind of researche. 

It is sensible to merge those cases at least for a while.

Both claimants agree to the process.

However there are 8 assurances that are clearly out of the sensible or allowed range regarding awarded points. Those are the ones with more awarded points than 150 (as the one that triggered this case) and those below 0 (which triggerd the second sql-query). 

As there currently is no policy how to bring such assurances "in line" and it could take some time since a general process may be defined, they should be handled seperatley and independently in this case. 

To not mix the handling of the 8 assurances with the rest, a merge in the other case should be done. This also decides the question if the (original) claimant of a20140126.1 should gain access to the personal data collected in this case. Which is currently answered with "no".

== Discovery IV (after partial ruling - split and merge) ==
As the last sql-queries did not provide information on single assurances, and the assurances can only be handled when we know which are the right ones, another sql-query has to be executed. Support would not have enough information to look them up with only knowing the assurer.

The following sql-query was created, tested and approved by software team:
{{{#!highlight sql
SELECT `id` FROM `notary` WHERE `awarded` < 0 OR `awarded` > 150;
}}}
It provides all assurancs with awarded points below 0 or over 150.

== Discovery V (after intermediate ruling III) ==

The assurer and assurees of the assurances with below 0 or above 150 awarded points were identified. They were asked if they would mind if the awarded points were changed were changed up to 0 or down to 35 points. (See table in the history log.) Non of them protested. The answers were mostly agreement. The only member who would be really affected by the change only answered once with something that could not be interpreted one way or the other by A or CM: "In germany ?" The member did not response to further questions in German or English.

Summary:
 * Awarded points below 0 and above 150 never did match any policy or known strategy, the known maximum was 150.
 * AP 4.3 currently only allows for upt to 50 points from one assurer to one assuree while currently there are only assurance processes installed that allow for up to 35 points from one assurer to one assuree.
 * AP does not directly forbid negative assurances but it states for example in 2.3 about 0 as being the minimum number of assurance points someone can have. Also 4.3 is speaking of zero assurance points in another context.
 * The 8 assurances in question never did match any policy and especially do not match the current policy. To meet our policies they have either to be removed or changed to match the allowed bounds.
 * The dispute asks for a cleanup of those assurances as they are not in line with current AP processes.
 * Non of the affected members protested, as they were asked to do if they did not want the change.
 * Even as we are not sure that we have the consent of all affected members (one was not reachable, one answered incomprehensible) the alternative would (revoke the assurances) would be an even greater encroachment.
 * The assurer of the assuree which's answer we could not interpret and who would drop below 100 by doing so, stated that he never intended to award a many points, that it was a typo and that he only wanted to award 35 points. So a reduction down to 35 points matchs his intention. The alternative would be to revoke the assurance completely.
 * In general the answering assurer of the negative awarded points stated that they only wanted to test if it was possible to enter negative points and that they did not have doubts about the identity of the assuree while two of the answering assurer of the awarded points stated that it was an error and they only intended to award 35 points.

It looks like setting the negative assurances to 0 and the assurances above 150 awarded points down to 35 would be in line with the intention and confidence level of the assurer.

As the dispute requests to clean up those assurances so that they match AP this should be done.

Software team was asked to produce according sql-queries and came up with the following two queries:
{{{#!highlight sql
UPDATE cacert.notary SET awarded=0 WHERE awarded < 0;
UPDATE cacert.notary SET awarded=35 WHERE awarded > 150;
}}}

This queries were tested on the testserver and got the ok from 3 software assessors.

== Ruling ==

=== Intermediate Ruling I ===

Critical team should execute the following sql-query an send the results encrypted to C as a support member, A and CM.

{{{#!highlight sql
SELECT `u`.`id`, `u`.`email`, `u`.`fname`, `u`.`lname`, COUNT(`n`.`points`) AS `assurances`, `n`.`points`, `n`.`method`
FROM `users` AS `u`
INNER JOIN `notary` AS `n` ON `n`.`from` = `u`.`id`
WHERE (`n`.`points` > '35'
OR `n`.`points` < '0')
AND `n`.`deleted` = 0
GROUP BY `u`.`id`, `u`.`email`, `u`.`fname`, `u`.`lname`, `n`.`points`, `n`.`method`
ORDER BY `u`.`id`, `n`.`points` DESC
}}}

Kiel, 2013-12-14

=== Intermediate Ruling II ===

Critical team should execute the following sql-query an send the results encrypted to C as a support member, A and CM.

{{{#!highlight sql
SELECT `u`.`id`, `u`.`email`, `u`.`fname`, `u`.`lname`, COUNT(`n`.`points`) AS `assurances`, `n`.`points`, `n`.`method`
FROM `users` AS `u`
INNER JOIN `notary` AS `n` ON `n`.`from` = `u`.`id`
WHERE (`n`.`points` > '35'
OR `n`.`points` < '0')
AND `n`.`deleted` = 0
GROUP BY `u`.`id`, `u`.`email`, `u`.`fname`, `u`.`lname`, `n`.`points`, `n`.`method`
ORDER BY `u`.`id`, `n`.`points` DESC
}}}

Lübeck, 2013-12-21

=== Partial Ruling (Split and Merge) ===
The case should be split in two.

There were 8 assurances found in the database, with awareded points outside of all allowed or sensible bounds. They either have negative awarded points or more than 150 awarded points. Those assurances should be handled in this case.

The rest of this case should be merged with a20140126.1, since both cases are related at least in the nature of the queries and other investigations that are needed to handle them.

Cologne, 2014-03-04

=== Intermediate Ruling III (find assurer and assurees for 8 assurances) ===

I hereby come to the following intermediate ruling:

Critical team should execute the following sql-query:
{{{#!highlight sql
SELECT `id` FROM `notary` WHERE `awarded` < 0 OR `awarded` > 150;
}}}

Hamburg, 2014-03-05

=== Ruling ===

as the Arbitrator of a20131124.1 I hereby come to the following ruling:

Critical team should execute the following two SQL-Queries:

{{{#!highlight sql
UPDATE cacert.notary SET awarded=0 WHERE awarded < 0;
UPDATE cacert.notary SET awarded=35 WHERE awarded > 150;
}}}

You probably have to tell "the DB frontend, that executing unsafe
queries without keys or a LIMIT clause is intended". As this is what
software team provided for execution in this case.

Critical team should afterwards check the result with the following query:

{{{#!highlight sql
SELECT `id` FROM `notary` WHERE `awarded` < 0 OR `awarded` > 150;
}}}

If this check gives results other than 0, they should be reported
encrypted to A and CM of this case.

After all queries are executed the affected members should be informed
by A and CM of this case that their assurances were changed.

The assurer of the assurances should be asked to note the change on
their CAP forms, if still available.

The ordered change does not affect the right to ask for a revoke of
those assurances for either side and should not be understood as a
change in confidence level from either the assurer or CAcert. It is
solely an organisational change to match the AP.

Cologne, 2014-06-09

== Execution ==
 . 2013-12-14 (A): Intermediate Ruling I, send to criticals
 . 2013-12-15 (Critical Team): send execution notice and results encrypted to A, CM, C
 .
 . 2014-01-21 (A): Intermediate Ruling II, send to criticals
 . 2014-01-21 (Critical Team): send execution notice and results encrypted to A, CM, C
 .
 . 2014-03-04 (A): Partial Ruling (Split and Merge) - send to C and C (now C1) of a20140126.1
 . 2014-03-05 (A): documents the merge of most of this case with a20140126.1
 .
 . 2014-03-05 (A): internediate ruling III, send to criticals
 . 2014-03-07 (Critical Team): reports results
 .
 . 2014-06-09 (A): ruling send to critical team, C, CM and affected members (ASR1-6 and ASE1-7) in BCC (for privacy reasons)
 . 2014-06-10 (Critical Team): executed queries, check was zero
 . 2014-06-12 (A): informed affected members about change, asks assurer to note it on the CAP forms, thanks them
 . 2014-06-10 (A): informs board and internal auditor about result of this case
 . 2014-06-10 (A): thanks and informs C, CM, critical team, SAs

== Similiar Cases ==
|| [[Arbitrations/a20140126.1|a20140126.1]] || [[Arbitrations/a20140126.1|Request for Analysis of Data Consistency ]] ||
|| [[Arbitrations/a20131207.1|a20131207.1]] || [[Arbitrations/a20131207.1|Request for Analysis of Data Consistency ]] ||
|| [[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