Before: Arbitrator Eva Stöwe (A), Respondent: CAcert (R), Claimant: Benny B (C1 former C) Marcus M (C2) Benedikt H (C3), Case: a20140126.1

History Log

Original Dispute, Discovery (Private Part)

EOT Private Part

original Dispute

This case was split from a20131207.1 afterwards it was merged with one part of Arbitrations/a20131124.2. The according parts from the original disputes are:

Dear Arbitrator,

as you asked for this case to continue so eagerly AND as there has been
recent work that needs to tended by Arbitration, I'm quite glad to
enable you to continue getting new things done in this case.

While testing bug 1042[1] "new points calculation" the Software Team
observed some strange behaviour with administrative increase entries on
the test server database.

The Software Team is pretty sure that this behaviour is generated
through entries entered via the Test Management System.

To clarify that this behaviour is not present on the productive database
the Software Team wants to get the following SQL statements executed on
the productive system:

FROM    `notary` AS `n`
WHERE   `n`.`from` = `n`.`to`
AND     `n`.`method` LIKE 'Administrative%'
AND     ( `n`.`awarded` > 2 OR `n`.`points` > 2 );

FROM    `notary` AS `n`
WHERE   `n`.`from` !=  `n`.`to`
AND     `n`.`method` LIKE 'Administrative%';

Regarding the first statement the reason is to see if there are any
administrative increases in the database where more than 2 points were
allocated to the columns points or awarded. There should be no such
records available as the administrative increase by default should be at
most 2 points. Records that violate this assumption of the Software
should be fixed by correcting their method, as they are not according to
the method "Administrive Increase" our software does. Based on the
situations these records appear special handling for their actual
"method" might be necessary.

The second statement is to see if there are any administrative increases
in the database where the person issuing points (`from`) is not the same
as receiving them (`to`). There should be no such records available as
the administrative increase as present in the software is always set to
make `from` equals `to`.

Special handling as with the records in the first query may be required.

This request should be added to the arbitration case a20140126.1 [2].
Please deliver the results to the an ABCed Software Team member or an
Software Assessor.

It would be great if the results could be received soon. The execution
is not blocking the work on bug 1042 but might lead to confusion for our
users if those information are not properly cleaned up when the patch
for [1] is installed.

Kind regards,

Dear Arbitration,

Audit planned to validate the sanity of CAcert's database as a
pre-requisite for a external CA certification.

The database sanity check should be done by a tool running at least two
times: initial and after the clean up of the database.

The tool should provide the following information:
- How many assurances are counted with the incorrect amount of points
- How many assurances have more than 35 points granted
- How many assurances will be affected by a new points calculation
- How many accounts have flags (orgadmin, ttpadmin, codesign) set but
are no assurers
- How many accounts have outdated flags (board, tverify, 1024bit, etc.)
- Count of non-unique entries per column
- Count of self-assurances are not notary-entries
- Count of domain names in "bad domain table" and in database
- Count of non-conform entries in database (script code, UTF-16 Chars,
discontinued values, etc.)
- Count of foreign keys in database
- Count of empty tables

For the clean up, the tool should provide necessary information where
manual clean up or case to case decision is needed. When personal data
need to be seen, support should handle the case - or be ruled by

To follow our principles of openness, I recommend to publish the
information not holding any private data.

if you have any further questions, I will be glad to help.

Best Regards


This case was split from 20131207.1 with a ruling from 2014-01-26. It was than merged with a split of part of a20131124.2 with a ruling from 2014-03-04.

At 2015-10-07 the Arbitrator also added a new dispute which is in a lot of parts related to this case. By this C3 and his dispute became part of this case.

According Discovery from a20131207.1

The according discovery of a20131207.1 was:


Global Privacy considerations


Software team could not provide a query to answer that question, since the relevant information is not stored in the database.

C should be asked to drop or to help to update the question. Else he could provide a query as a member of the software team, that would answer the question. Currently there is no other way to proceed here.

C stated via Skype at 2013-12-13 that he wants to provide a matching query.


Since there were no real policies active at the start von CAcert, but there were some otherwise defined assurance programs, the question has to be opend up to look for all assurance programs, not only for policies.

Currently (2013-12-13) there does not exist a list with precise dates when which assurance program was active and it may be hard to fix explicite dates for the correct start and end of some of them.

According to a20091118.1 it took some time to get the information about the end of a program published and the software fixed so that according entries would be impossible.

What could be done and could help to answer the question is to find the dates of first and last entries for assurances of all kinds known to the database.

Even if it is hard to tell when a program started and stopped exactly, it may be possible to check if all entries for a program fall into a period where the program was clearly running.


   1 select method, count(*), min(`when`), max(`when`) from notary group by method;

The query was created, tested and approved by 2 software assessors (Michael T and C) at sap 2013-12-10.

Even as only some assurers were able to initiate assurances of some kinds, the queries do not reveal any private informations of the involved assurer. The defining dates tell more about the respective programs than the involved assurers.

There is no privacy issue preventing the execution of this query.

Results of sql3a1:


Software team could not provide a query to answer that question, since the relevant information would be stored in a text-field and it is currently not known, what entries would be allowed. Answer from software team was: Not impossible but a lot of work.

C should be asked to drop or update the question, so that it could be answered somehow. Else he could provide a query as a member of the software team.

A discussion via Skype at 2013-12-13 with C gave:



Similiar Cases


Request for Analysis of Data Consistency


SQL Request for analysing assurance data for wrong entries


SQL Query - Request for analysing


Find out some information about when accounts where created


Assurance(s) while TTP program frozen 3 disputes: TTPfrozen, Arbitrator, Systemchanges


SQL query


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


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

Arbitrations/a20140126.1 (last edited 2017-01-09 21:19:56 by EvaStöwe)