Create and remove Moodle Users via MySQL Commands

moodle_users.sql

/** 
Create user.
Although this is the insert query, it is almost always better to use a web service rather than doing this manually. This way enforces Moodle standards and ensures quality data. Use at your own risk.

Webservice example: https://stackoverflow.com/questions/35881584/using-moodle-create-users-and-enroll-them-in-courses-via-sql
Code typically used: https://github.com/moodle/moodle/blob/a409707794cc6d74063787d27adb42154426c803/user/lib.php#L42        

Note: The password is set using MD5, however Moodle dutifully transforms this to a much more secure password on initial login.
See: https://github.com/moodle/moodle/blob/06e3b6d8bab42b8e56d169d006f31f4a15684830/auth/manual/auth.php#L100
See: https://github.com/moodle/moodle/blob/a4f914b54dbafcaf1cc2bf1cce8bde30cc69db57/lib/moodlelib.php#L4575
**/
INSERT INTO mdl_user 
            (auth, 
             confirmed, 
             mnethostid, 
             email, 
             username, 
             password, 
             lastname, 
             firstname) 
VALUES     ('manual', 
            1, 
            1, 
            'manual@example.com', 
            'manualuser', 
            MD5('password'), 
            'lastname', 
            'firstname'); 
            
/**
Force user to change password.

Based on:
Settings from: https://github.com/moodle/moodle/blob/06e3b6d8bab42b8e56d169d006f31f4a15684830/auth/manual/auth.php#L82
Code typically used: https://github.com/moodle/moodle/blob/a4f914b54dbafcaf1cc2bf1cce8bde30cc69db57/lib/moodlelib.php#L1874
**/
INSERT INTO mdl_user_preferences 
            ( 
                        userid, 
                        name, 
                        value 
            ) 
SELECT id, 
       'auth_forcepasswordchange', 
       1 
FROM   mdl_user 
WHERE  username = 'manualuser' 
on duplicate KEY 
UPDATE value = 1;

/** 
Make user an administrator.

Based on: https://github.com/moodle/moodle/blob/06e3b6d8bab42b8e56d169d006f31f4a15684830/admin/roles/admins.php#L85
Read more about MySQL sets: https://dev.mysql.com/doc/refman/5.7/en/set.html
Add/Remove from Set: https://web.archive.org/web/20100412075034/http://www.futhark.ch/mysql/109.html

Note: You will need to purge caches after setting the admin. :: $php admin/cli/purge_caches.php
**/
UPDATE mdl_config c 
       CROSS JOIN mdl_user u 
SET    c.value = Concat_ws(',', IF(value = '', NULL, value), u.id) 
WHERE  u.username = 'manualuser' 
       AND u.deleted = 0 
       AND Find_in_set(u.id, c.value) = 0 
       AND c.name = 'siteadmins'; 
       
/** 
Remove a user as an admin.

Based on: https://github.com/moodle/moodle/blob/06e3b6d8bab42b8e56d169d006f31f4a15684830/admin/roles/admins.php#L85
Read more about MySQL sets: https://dev.mysql.com/doc/refman/5.7/en/set.html
Add/Remove from Set: https://web.archive.org/web/20100412075034/http://www.futhark.ch/mysql/109.html
**/
UPDATE mdl_config c 
       CROSS JOIN mdl_user u 
SET    c.value = Trim(BOTH ',' FROM REPLACE(Concat(',', value, ','), 
                                           Concat(',', u.id, ','), ',') 
                 ) 
WHERE  u.username = 'manualuser' 
       AND Find_in_set(u.id, c.value) <> 0 
       AND c.name = 'siteadmins'; 

/**
Flag a user as deleted.

Based on: https://github.com/moodle/moodle/blob/a4f914b54dbafcaf1cc2bf1cce8bde30cc69db57/lib/moodlelib.php#L4004
For full cleanup, run the following script from the command line: moodle/admin/cli/fix_deleted_users.php ( https://github.com/moodle/moodle/blob/5f54a8760f2821c12bd90beaa37b9fa937f101e6/admin/cli/fix_deleted_users.php )
**/
UPDATE mdl_user u 
SET    u.deleted = 1, 
       auth = 'nologin' 
WHERE  u.username = 'manualuser';