the output of the following query has been requested by C in order to notify "old assurers" (active during 2008) that have not passed the CATS yet about that change. 

want a query as follows (taken from Ted his script 7th of April):
select u.email, fname, lname, sum(n.points) from users u, notary n
 where n.to=u.id
   /* No passed test */
   and not exists(select 1 from cats_passed cp where cp.user_id=u.id)
   /* Only Assurers who did an Assurance after 31.12.2007 */
   and exists(select 1 from notary n2 where n2.from=u.id and year(n2.`when`)>2007)
   /* Only Assurers who did at least 2 Assurances */
   and (select count(*) from notary n3 where n3.from=u.id) > 1
 group by email, fname, lname
 having sum(points)>99;

Purpose to send notification to CAcert accounts with more as 99 Trust
Points, not has passed the CATS challenge and were active in 2008 and
later with the message that the should do the CAcert Challenge to get
Assurer status.

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

This is intended to provide compliance with rules and occurs meeting an urgent business need. Therefore I request the Systems Administrators to execute the appropriate Queries and send mails to the all CAcert accounts with more than 99 Trust Points that have not passed the Assurer Challenge to the effect requested. Once these mails have been sent out a short report should be forwarded to be attached to this case file containing the number of accounts affected and the timeframe the emails were actually sent. I also remind all involved that the data handled here is considered sensitive and that all policies, especially the Security Policy, should be followed. The data may not be passed on to any other person or persons and shall only be used for this specific purpose. All extracts of data need to be deleted after their use.

Conclusion

The following was received from P.Gühring:

assurer information sent to 1597 recipients.

Therefore the case is closed.

Arbitrations/a20090518.2 (last edited 2009-12-09 03:37:22 by UlrichSchroeter)