Add or remove Moodle users via MySQL

Here are some quick queries to remove or add Moodle users and administrators via MySQL. Use at your own risk! In almost all cases, it’ll be better to use a web service; however, this is for those times when you need to test on a sample or development instance. If you have multiple instances, definitely query information_schema to generate the needed SQL for the statement(s) you need below.

	/**
	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';

view raw

moodle_users.sql

hosted with ❤ by GitHub