* Case Number: a20091221.1
  * Status: closed
  * Claimants: Werner Dworak (SE)
  * Respondents: CAcert
  * Case Manager: Lambert Hofstra
  * Arbitrator: UlrichSchroeter
  * Date of arbitration start: 2009-12-21
  * Date of ruling: 2009-12-23
  * Case closed: 2009-12-23
  * Complaint: Adhoc SQL-query about U18 cases

{{{
please demand a sql query over the CAcert live database to show how many
assured members at present are below 18 years of age. And if possible
ask how many members have been below age of 18 at the time of their
first assurance. Further ask how many assurers are or have been below
age of 18 years.

Yes, I agree to CCA and DRP.
}}}

  * Relief: TBD

Before: Arbitrator UlrichSchroeter (A), Respondent: CAcert (R), Claimant: Werner Dworak (C), Case: a20091221.1


== History Log ==
 . 2009-12-21 (UlrichSchroeter): added to wiki, request for CM / A
 . 2009-12-21 (C): I accept CCA/DRP under this arbitration
 . 2009-12-22 (A), (AlexanderPrinsier): deployment of sql queries
 . 2009-12-22 (A): testing of deployed sql queries against a local testsystem database
 . 2009-12-23 (A): sending sql query exec request to sysadmin team
 . 2009-12-23 (A): rcvd answer with adhoc sql query results from sysadmin team
 . 2009-12-23 (A): finishing ruling

== Discovery ==
 * "please demand a sql query over the CAcert live database to show how many ---( assured )--- members at present are below 18 years of age"
  * q1:
  . Description: Total users
  . Query .....: SELECT * FROM cacert.users;
  * q2:
  . Description: count users U18
  . Query .....: SELECT count(id) FROM cacert.users where dob > '1991-12-21';
 * "Further ask how many assurers are or have been below age of 18 years"
  * q3:
  . Description: count users U18 and Is Assurer
  . Query .....: SELECT count(id) FROM cacert.users where dob > '1991-12-21' and assurer=1;
 * "please demand a sql query over the CAcert live database to show how many '''assured''' members at present are below 18 years of age" (see q1, q2)
  * q4:
  . Query: select count(*) from (SELECT count(users.id) FROM cacert.users right join cacert.notary on cacert.notary.to=cacert.users.id where cacert.users.dob > '1991-12-21' group by cacert.users.id) as subq;
 * "ask how many members have been below age of 18 at the time of ''their first'' assurance"
  * q5:
  . Description: by aphexer, untested, amount of members who were assured when they were <18 years old)
  . Query .....: SELECT count(*) FROM users,notary where users.id = notary.to AND DATEDIFF(notary.when,DATE_ADD(users.dob,interval 18 year)) < 0;
  * q6:
  . Desrciption: (tested query) total count assurances before reaching age of 18 years
  . Query .....: SELECT count(*) FROM cacert.users, cacert.notary where users.id = notary.to AND DATEDIFF(notary.when,DATE_ADD(users.dob,interval 18 year)) < 0;
  * q7:
  . Description: (tested query) total count of users who were assured before they were 18 years or older
  . Query .....: select count(*) from (SELECT count(users.id) FROM cacert.users, cacert.notary where users.id = notary.to AND DATEDIFF(notary.when,DATE_ADD(users.dob,interval 18 year)) < 0 group by users.id) as subq;

 * The count base of users (q1) includes disabled users, locked users, user accounts that probably were not be enabled, so it includes many inactive accounts. Should this count as the base ?
 . the [[http://www.cacert.org/stats.php|Statistics]] script lists (Dec 22nd, 2009) round about 22,000 (22K) individual assured users:
 || Users with 1-49 Points || 5,137 ||
 || Users with 50-99 Points || 3,974 ||
 || Assurer Candidates || 9,880 ||
 || Assurers with test || 3,151 ||
 . The new users statistics for 2009 displays 29,252 and 161,402 total new users (2002-2009) so limiting query1 to the assured users base gives the best realistic results
  * q1b:
  . Description: counts total, unique assured users
  . Query .....: select count(*) from (select count(cacert.notary.to) from cacert.notary group by cacert.notary.to having sum(cacert.notary.points) > 0) as subq;
 * Original questions relates to q4, q7, q3
 * Addtl question q1b results in the baseline of all assured users
 * Query Results includes only statistical data like the [[http://www.cacert.org/stats.php|Statistics]] script. No PII or other security issues are affected by the adhoc queries. No laws and policies to the protection of persons under age or against discrimination are affected.
 * The Adhoc query is the result of the direct policy work on [[PolicyDrafts/PolicyOnJuniorAssurersMembers2| Policy On Junior Members and Assurers (2)]] to get a count base they are work for

A: This is expressly provided for in Policy. The Role of Arbitration in cases such as this is to provide for governance and control. Therefore I issue the following:

== Ruling ==
Dear Sysadmin Team,
please execute following 4 adhoc sql queries
against the CAcert database:

query 1  (reference: q1b)
select count(*) from (select count(cacert.notary.to) from cacert.notary
group by cacert.notary.to having sum(cacert.notary.points) > 0) as subq;

query 2  (reference: q4)
select count(*) from (SELECT count(users.id) FROM cacert.users right join
cacert.notary on cacert.notary.to=cacert.users.id where cacert.users.dob >
'1991-12-21' group by cacert.users.id) as subq;

query 3  (reference: q7)
select count(*) from (SELECT count(users.id) FROM cacert.users,
cacert.notary where users.id = notary.to AND
DATEDIFF(notary.when,DATE_ADD(users.dob,interval 18 year)) < 0 group by
users.id) as subq;

query 4  (reference: q3)
SELECT count(id) FROM cacert.users where dob > '1991-12-21' and assurer=1;

The expected results doesn't include any privacy informations. So therefor, the results can probably be published. But, before publishing the results, Lambert (CM) and I (A) will have to do a review on the results. So please, send us the results by replying to this email.

----

There is no data included in the results, that prevents publishing of this data by any policy, or that
breaches any rules or policys. So the results can be published like other statistical data
under the CAcert main website.

Frankfurt/Main, Dec 23th 2009

== Execution ==
 . 2009-12-23 (A) sent results to (C)
 . 2009-12-23 (C): [[https://lists.cacert.org/wws/arc/cacert-policy/2009-12/msg00069.html|published result on cacert-policy mailing list]]
 . 2009-12-23 (A): case closed

== Similiar Cases ==
|| [[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]] ||

----
 . CategoryArbitration
 . CategoryArbCaseSystemTasks