Abbey Workshop | ||||||||||
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.
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';
flush privileges;
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';
flush privileges;
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.
If you need to change a users password and nothing else, use the following set of commands.
mysql -u root -p
use mysql;
update user set password=password('new_password') where user='username';
flush privileges;
The password function encrypts the password in the database. Remember to execute flush privileges; so that your changes take effect.
Copyright © Abbey Workshop 2006