View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0000872Main CAcert Websitemiscpublic2010-10-05 03:472013-05-30 00:24
ReporterINOPIAE Assigned ToINOPIAE  
PrioritynormalSeveritymajorReproducibilityhave not tried
Status needs workResolutionopen 
Summary0000872: PoJAM restricitions to apply to production system (several restrictions) PoJAM 3.3,, 4.1, 4.2
DescriptionWhile testing on the with the dates of a minor person I entered a person with the dob 1995-01-01.
When I assured that minor, there is no way to mark that I saw the signature of the parent as given by PoJAM 3.3
After I added 60 points to that person, I am able to create a class3 signature for that account, which should be according to PoJAM 3.3 "The system therefore will block all "reliance" products as defined by policy"
I assume that this is the practice on the live system too.
Additional InformationPoJAM 3.3 Therefore, a change should be put into the system:

    If the member is under 18 years of age,
    the system shall require the Assurer to confirm
    that consent was established during the Assurance,
    or otherwise as considered by the Assurer,
    before additional higher reliance products are available.

PoJAM 4.1 Assurer status may only be granted if the user is at least 14 years old. Other preliminaries for the Assurer status set up by other policies are untouched. Combining AP and this policy, a Junior Assurer is a CAcert member with 100 Assurance Points, has passed the CAcert Assurer Challenge, and is between the ages of 14 and 18 years.

PoJAM 4.2 A Junior Assurer can issue a maximum of 10 Assurance Points to an assuree, independent of how many Experience Points the Junior already has. The Experience Points awarded to the Junior Assurer are untouched.
TagsNo tags attached.
Reviewed by
Test Instructions

Relationships

related to 0000976 closedUli60 Main CAcert Website List of update request for webdb database structure upgrade with tables / fields 
has duplicate 0000937 closed test.cacert.org Very young people 
related to 0001054 needs review & testingTed Main CAcert Website Review the code regarding the new point calculation in ./includes/general.php 
related to 0000941 needs workUli60 Main CAcert Website Policy Repository Migration 

Activities

Uli60

2011-05-21 23:37

updater   ~0001975

2011-05-08 16:55
abheiden
0000937: Very young people
I have generated a user account which is born on May, 7th 2011. Dates in the future are not possible but a one day old child can have his own CAcert account.

INOPIAE

2012-08-28 21:04

updater   ~0003167

Last edited: 2012-08-28 21:05

 Other PoJAM areas are also affected and needs to be solved.
see test in 1054 https://bugs.cacert.org/view.php?id=1054#c=3166
u14.1054@acme.com DOB 1.1.2000
assured with 35 points
=> no assurer => ok
added 70 points via batch => no assurer => OK
added CATS => shows assuer with 10 points => false
added 1 assurance added => assurance possible => false

u18.1054@acme.com DOB 1.1.1996
assured with 35 points => no assurer => ok
added 70 points via batch => no assurer => OK
added CATS
added 1 assurance =>ok
added 5 assurances via batch
All assurance show 10 points => ok
account has now 6 assurances
wot.id 15 show you can grant up to 15 points => should show 10 points
added new assurance with 20 points
Points reduced to 10 points according to PoJAM

INOPIAE

2013-05-14 14:43

updater   ~0004000

Text for Confirmation checkbox:
_("I confirm that parental consent was established and documented on CAP form during the Assurance according to %s."), "<a href=\"https://svn.cacert.org/CAcert/Policies/PolicyOnJuniorAssurersMembers.html\" target=\"_blank\">Policy On Junior Assurers / Members (PoJAM)</a>")

Messsage displayed if user did not tick the PoJAM confirmation:
You need to confirm that the parental consent was established and documented during the assurance of this member.

The confirmation will be stored in the user agreement table

INOPIAE

2013-05-19 12:16

updater   ~0004010

Last edited: 2013-05-19 15:41

 To find information about junior members.
Request the following results from critical
1. Select count(1), YEAR(`users`.`dob`) as year from `users` where YEAR(`users`.`dob`)>=1995 Group by YEAR(`users`.`dob`)
2. SELECT count( `temp`.`no` ) , sum( `temp`.`assurances` ) FROM (
SELECT 1 AS no, count( 1 ) AS assurances, sum( `notary`.`points` ) AS points
FROM `users`, `notary` WHERE YEAR(`users`.`dob`)>=1995 and `users`.`id`=`notary`.`to` GROUP BY `users`.`id`
) AS `temp` WHERE `temp`.`points` <50
3. SELECT count( `temp`.`no` ) , sum( `temp`.`assurances` ) FROM (
SELECT 1 AS no, count( 1 ) AS assurances, sum( `notary`.`points` ) AS points
FROM `users`, `notary` WHERE YEAR(`users`.`dob`)>=1995 and `users`.`id`=`notary`.`to` GROUP BY `users`.`id`
) AS `temp` WHERE `temp`.`points` >=50 and `temp`.`points` <100
4. SELECT count( `temp`.`no` ) , sum( `temp`.`assurances` ) FROM (
SELECT 1 AS no, count( 1 ) AS assurances, sum( `notary`.`points` ) AS points
FROM `users`, `notary` WHERE YEAR(`users`.`dob`)>=1995 and `users`.`id`=`notary`.`to` GROUP BY `users`.`id`
) AS `temp` WHERE `temp`.`points` >=100
(corrected SQL-Statements 2-4)

Uli60

2013-05-19 12:45

updater   ~0004011

https://bugs.cacert.org/view.php?id=872#c4010
1. questions 2-4 results in mysql error
   ERROR 1111 (HY000): Invalid use of group function
2. file as dispute

BenBE

2013-05-21 21:14

updater   ~0004015

The queries for 2 through 4 can be combined in one:

SELECT
    count( `temp`.`no` ) AS AffectedUsers,
    sum( `temp`.`assurances` ) AS AffectedAssurances,
    if(points = 0, "No points", if(points < 50, "1 < x < 50", if(points < 100, "50 <= x < 100", "100 <= x"))) AS ReceivedPoints
FROM (
    SELECT 1 AS no, count( 1 ) AS assurances, sum( `notary`.`points` ) AS points
    FROM `users`, `notary`
    WHERE YEAR(`users`.`dob`)>=1995 and `users`.`id`=`notary`.`to`
    GROUP BY `users`.`id`
    ) AS `temp`
GROUP BY ReceivedPoints

INOPIAE

2013-05-30 00:22

updater   ~0004034

I attached the result of the query authorised by arbitraion https://wiki.cacert.org/Arbitrations/a20130521.1

INOPIAE

2013-05-30 00:24

updater  

a20130521.1.typescript (2,818 bytes)

Issue History

Date Modified Username Field Change
2010-10-05 03:47 INOPIAE New Issue
2010-10-05 03:48 INOPIAE Category organisational section => misc
2011-05-21 23:37 Uli60 Note Added: 0001975
2011-05-21 23:37 Uli60 Relationship added duplicate of 0000937
2011-05-21 23:38 Uli60 Relationship replaced has duplicate 0000937
2011-08-31 11:40 Uli60 Relationship added related to 0000976
2012-08-28 20:57 Uli60 Relationship added related to 0001054
2012-08-28 21:04 INOPIAE Note Added: 0003167
2012-08-28 21:04 INOPIAE Description Updated
2012-08-28 21:05 INOPIAE Note Edited: 0003167
2012-08-28 21:10 Uli60 Summary PoJAM 3.3 restricition not applied => PoJAM restricitions to apply to production system (several restrictions) PoJAM 3.3,, 4.1, 4.2
2012-08-28 21:10 Uli60 Additional Information Updated
2013-01-10 10:49 Werner Dworak Relationship added related to 0000941
2013-05-01 08:45 INOPIAE Description Updated
2013-05-14 14:43 INOPIAE Note Added: 0004000
2013-05-14 14:43 INOPIAE Assigned To => INOPIAE
2013-05-14 14:43 INOPIAE Status new => needs work
2013-05-19 12:16 INOPIAE Note Added: 0004010
2013-05-19 12:45 Uli60 Note Added: 0004011
2013-05-19 15:41 INOPIAE Note Edited: 0004010
2013-05-21 21:14 BenBE Note Added: 0004015
2013-05-30 00:22 INOPIAE Note Added: 0004034
2013-05-30 00:24 INOPIAE File Added: a20130521.1.typescript