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