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:

1st:
---
SELECT
        `n`.`id`,
        `n`.`from`,
        `n`.`awarded`,
        `n`.`points`,
        `n`.`method`,
        `n`.`deleted`
FROM    `notary` AS `n`
WHERE   `n`.`from` = `n`.`to`
AND     `n`.`method` LIKE 'Administrative%'
AND     ( `n`.`awarded` > 2 OR `n`.`points` > 2 );
---

2nd:
---
SELECT
        `n`.`id`,
        `n`.`from`,
        `n`.`to`,
        `n`.`awarded`,
        `n`.`points`,
        `n`.`method`,
        `n`.`deleted`
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,
BenBE.

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


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
Benedikt

Discovery

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:

Motivation

Global Privacy considerations

sql1c

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.

sql3a

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.

sql3a1

   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:

sql3b

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:

Ruling

Execution

Similiar Cases

a20131207.1

Request for Analysis of Data Consistency

a20131124.2

SQL Request for analysing assurance data for wrong entries

a20131128.1

SQL Query - Request for analysing

a20131210.1

Find out some information about when accounts where created

a20091118.1

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

a20100822.1

SQL query

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

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