User Management (MySQL)

Create Users and Grant/Revoke Privileges

After making any changes to user privilages, you need to flush them to ensure they're applied...

 FLUSH PRIVILEGES;

To give full privileges (including the ability to alter user accounts) to a new user coming from any location use;

 GRANT ALL PRIVILEGES on <database>.* to '<user>'@'%' identified by '<password>' WITH GRANT OPTION;

To give no privileges to a new user coming from a specific host;

 GRANT USAGE on <database>.* to '<user>'@'<host>' identified by '<password>';

To give an existing user read-only access;

 GRANT SELECT on <database>.* to '<user>'@'<host>';

To give an existing user web-user, read-only access to local database webdb;

 GRANT SELECT ON webdb.* TO 'web-user'@'localhost';

To remove/revoke a privilege from an existing user web-user;

 REVOKE SELECT ON webdb.* FROM 'web-user'@'localhost';

To remove an existing user use the following...

 DROP USER '<user>'@'<host>';

You need to specify the full user@host entry (or MySQL assumes the wild-card host %)
Existing users sessions are not dropped, but will be unable to re-establish

To display all configured users;

SELECT CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') FROM mysql.user;

Then use the displayed lines to see the detail of each user

SET PASSWORD FOR 'user'@'%' = PASSWORD('newpass');