User Management (MySQL)

From vwiki
Jump to navigation Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

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