MySQL: User Management
User management is an important aspect of managing a MySQL Server. This section covers the most common user management features encountered while managing a server.
MySQL AB has posted improved documentation on their website. The section on MySQL user administration can be found at this link.
Creating a New User Account
To create a new user account, first log in as
root. Next, use the following command to create the user.
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'some_password';
This command would give the new user all privileges on all databases and tables. The user could only log in from the host specified by localhost. For the changes to take effect, you must call the
flush privileges; command to make the server reread the user table.
The previous command is not something you would generally do. A more reasonable command line might look like this.
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON db.* TO 'username'@'localhost' IDENTIFIED BY 'password';
This example explicitly identifies the privileges being granted. This is preferable as privileges are limited to only the user and database where access is required. The privileges are only applied to the database 'db' and not all the databases on the sever.
Change a User's Password
If you need to change a users password and nothing else, use the following set of commands.
mysql -u root -p
update user set password=password('new_password') where user='username';
The password function encrypts the password in the database. Remember to execute
flush privileges; so that your changes take effect.