As you manage your dedicated server, you will invariably come across times when you need to reset passwords, add or delete accounts, or generally perform maintenance on your database users. When working with MySQL, there are a couple of simple ways to manage user accounts. The first involves accessing it from the command line, while the second involves using a graphical interface, such as phpMyAdmin.
Today, you will learn how to create users, change passwords, and grant privileges from the MySQL command prompt.
To create a user in the current version of MySQL with the username “porlion” and the password “duct543”, you would type the following from the mysql> prompt:
CREATE USER porlion IDENTIFIED BY PASSWORD 'duct543';
By default, the new user will have no privileges in any database. To grant the user “porlion” privileges in a database named “lucky_db”, type the following:
GRANT SELECT,INSERT,UPDATE,DELETE ON lucky_db.* TO 'porlion'@localhost';
If you need to grant all privileges to the same user, type this:
GRANT ALL ON lucky_db.* TO 'my_user'@localhost IDENTIFIED BY 'my_pass';
To change the password of a current user named “porlion”, type:
SET PASSWORD FOR 'porlion'@localhost = PASSWORD('newpassword');
Replace “newpassword” with the actual new password for the user.
In the next post, we will look at ways to manage MySQL users from within phpMyAdmin.