[Building Sakai] SAK-16024: 2.6.0 conversion script: site.add.course backfill issue

[Anthony Whyte]

John Leasia reports an issue with the 2.6.0 conversion scripts with  
respect to the new permission site.add.course.  Currently, the  
conversion script adds site.add.course to the user account template  
realms with a .auth role (good) but also "backfills" and adds the  
permission to other templates since the backfill SQL filters on all  
realms with a .auth role.  The account template realms are all that  
should be updated in John's view since these "templates are used for  
*all* accounts associated with the template (all registered accounts,  
all sample accounts, etc.).  Each user doesn't have their own realm  
that determines whether they can add a site or add a course site -  
they all get their permission from the same template.  So, there isn't  
a need to set the permission in anything other than the template - no  
backfilling necessary."  Backfilling for site.add.course is  
promiscuous at present, adding site.add.course to all template realms  
with a .auth role -- a conversion that may not square with an  
institution's permission strategy, as is the case with Michigan.

I suggest we do the following:

1. remove or comment out the sakai.add.course "backfill" portion of  
the trunk 2.6.0 conversion scripts
2. merge to 2.6.x maintenance branch
3. Add a warning note in the 2.6.0 release documentation regarding  
this issue and point to the updated conversion scripts in 2.6.x

Cheers,

Anthony

References:

http://jira.sakaiproject.org/browse/SAK-12324
http://jira.sakaiproject.org/browse/SAK-12631
http://jira.sakaiproject.org/browse/SAK-16024
https://source.sakaiproject.org/svn/reference/tags/sakai-2.6.0/docs/conversion/


sakai_2_6_0_mysql_conversion.sql excerpt:

-- --- SAK-16024 site.add.course is a newly added permission

INSERT INTO SAKAI_REALM_FUNCTION VALUES (DEFAULT, 'site.add.course');
INSERT INTO SAKAI_REALM_RL_FN VALUES((select REALM_KEY from  
SAKAI_REALM where REALM_ID = '!user.template.maintain'), (select  
ROLE_KEY from SAKAI_REALM_ROLE where ROLE_NAME = '.auth'), (select  
FUNCTION_KEY from SAKAI_REALM_FUNCTION where FUNCTION_NAME =  
'site.add.course'));
INSERT INTO SAKAI_REALM_RL_FN VALUES((select REALM_KEY from  
SAKAI_REALM where REALM_ID = '!user.template.registered'), (select  
ROLE_KEY from SAKAI_REALM_ROLE where ROLE_NAME = '.auth'), (select  
FUNCTION_KEY from SAKAI_REALM_FUNCTION where FUNCTION_NAME =  
'site.add.course'));
INSERT INTO SAKAI_REALM_RL_FN VALUES((select REALM_KEY from  
SAKAI_REALM where REALM_ID = '!user.template.sample'), (select  
ROLE_KEY from SAKAI_REALM_ROLE where ROLE_NAME = '.auth'), (select  
FUNCTION_KEY from SAKAI_REALM_FUNCTION where FUNCTION_NAME =  
'site.add.course'));

--  
--------------------------------------------------------------------------------------------------------------------------------------
-- backfill new site.add.course permissions into existing realms
--  
--------------------------------------------------------------------------------------------------------------------------------------

-- for each realm that has a role matching something in this table, we  
will add to that role the function from this table
CREATE TABLE PERMISSIONS_SRC_TEMP (ROLE_NAME VARCHAR(99),  
FUNCTION_NAME VARCHAR(99));

INSERT INTO PERMISSIONS_SRC_TEMP values ('.auth','site.add.course');

-- lookup the role and function numbers
CREATE TABLE PERMISSIONS_TEMP (ROLE_KEY INTEGER, FUNCTION_KEY INTEGER);
INSERT INTO PERMISSIONS_TEMP (ROLE_KEY, FUNCTION_KEY)
SELECT SRR.ROLE_KEY, SRF.FUNCTION_KEY
from PERMISSIONS_SRC_TEMP TMPSRC
JOIN SAKAI_REALM_ROLE SRR ON (TMPSRC.ROLE_NAME = SRR.ROLE_NAME)
JOIN SAKAI_REALM_FUNCTION SRF ON (TMPSRC.FUNCTION_NAME =  
SRF.FUNCTION_NAME);

-- insert the new functions into the roles of any existing realm that  
has the role (don't convert the "!site.helper")
INSERT INTO SAKAI_REALM_RL_FN (REALM_KEY, ROLE_KEY, FUNCTION_KEY)
SELECT
     SRRFD.REALM_KEY, SRRFD.ROLE_KEY, TMP.FUNCTION_KEY
FROM
     (SELECT DISTINCT SRRF.REALM_KEY, SRRF.ROLE_KEY FROM  
SAKAI_REALM_RL_FN SRRF) SRRFD
     JOIN PERMISSIONS_TEMP TMP ON (SRRFD.ROLE_KEY = TMP.ROLE_KEY)
     JOIN SAKAI_REALM SR ON (SRRFD.REALM_KEY = SR.REALM_KEY)
     WHERE SR.REALM_ID != '!site.helper'
     AND NOT EXISTS (
         SELECT 1
             FROM SAKAI_REALM_RL_FN SRRFI
             WHERE SRRFI.REALM_KEY=SRRFD.REALM_KEY AND  
SRRFI.ROLE_KEY=SRRFD.ROLE_KEY AND SRRFI.FUNCTION_KEY=TMP.FUNCTION_KEY
     );

-- clean up the temp tables
DROP TABLE PERMISSIONS_TEMP;
DROP TABLE PERMISSIONS_SRC_TEMP;
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20090723/45d3f740/attachment.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 2417 bytes
Desc: not available
Url : http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20090723/45d3f740/attachment.bin