Roles and permissions

Hi Brad,

We automatically assign our teachers a system role called 'colleague', that allows them view-only access to their colleague's moodle courses without the need for them to be enrolled in all courses. We have nearly 300 staff and fairly frequent comings and goings that meant we had to automate it. Rough steps are:

1. Create a hidden course in moodle. eg. shortname = 'staff'
2. Create your system role. eg. shortname = 'colleague'
3. Assign the people that require the system role to the ldap group ('staff') as students via your normal scripts. Let the standard ldap course sync run which will enrol the people in the course you just created. NB: we assign them as students only so that they cannot see themselves enrolled in the hidden course.
4. Call a file that contains the following sql statement via a scheduled task (cron) after the ldap sync has taken place.

insert into mdl_role_assignments (roleid, contextid, userid, hidden, modifierid, enrol, sortorder)
select (select id from mdl_role where shortname = 'colleague') as roleid,
1 as contextid, userid, 1 as hidden, 2 as modifierid, 'manual' as enrol, 0 as sortorder
from mdl_role_assignments
where contextid = (select id from mdl_context where contextlevel = 50 and instanceid = (select id from mdl_course where shortname = 'staff'))
and userid not in (select userid from mdl_role_assignments where roleid = (select id from mdl_role where shortname = 'colleague')
and contextid = 1
and hidden = 1
and enrol = 'manual'
and sortorder = 0)

What the above statement is really saying is put people in the 'colleague' system role if:

• they are not yet a member of the 'colleague' system role (...userid not in (etc...)) AND
• they are enrolled in the 'staff' course

Removing the system role assignment automatically is a bit trickier, but the same concept applies. Only have system role assignments for members of your hidden course:

DROP TABLE IF EXISTS mdl_temp;
CREATE TABLE mdl_temp (id INT);

insert mdl_temp
(select id
from mdl_role_assignments
where roleid = (select id from mdl_role where shortname = 'colleague')
and contextid = 1
and hidden = 1
and enrol = 'manual'
and sortorder = 0
and userid not in
        (select userid from mdl_role_assignments where contextid =
                (select id from mdl_context where contextlevel = 50 and instanceid =
                        (select id from mdl_course where shortname = 'staff')))
and userid not in (select id from mdl_user where auth = 'manual'));

delete ra.*
from mdl_role_assignments ra, mdl_temp t
where ra.id = t.id;

DROP TABLE IF EXISTS mdl_temp;

The above method has been working for about 2 years without any issues.

And yes, you are running potentially dangerous statements on the role assignments table - understand it, test it on a non-production instance, then test it again

Hope this helps.

Michael