User Management (MySQL)

Create Users and Grant/Revoke Privileges

To create a new user you need to specify the username, the location/IP address it will be connecting from (use % for any location), and their password

 CREATE USER '<user>'@'localhost' identified by '<password>';

You can then give permissions to that user

 GRANT ALL ON <dayabase>.* TO '<user>'@'localhost';

Depending on the version of MySQL running you can do this in one command (note that the WITH GRANT OPTION allows the user to create more users and/or grant privileges to users).

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

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

 FLUSH PRIVILEGES;

Delete Users

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

Display Users

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

Change User Password

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