Articles in MySQL Category

Managing MySQL Users

In MySQL, you can create user accounts with different privileges. Privileges can vary from accessing several databases to accessing only one column in a table.

Root User

By default, MySQL has a super user that has all the privileges called root. You need to log in as root for executing many MySQL administrative tasks including managing users.

Changing Root Password

If you didn’t specifically set the root password when installing MySQL, most of the times it would be empty. If the root password is empty, make sure to reset it with a proper password for better security.

In command-line, you can use following command to change root password. Type your preferred password in place of newpassword. After hitting Enter key, it will ask to enter current password. If current password is empty, just hit Enter key.

mysqladmin -u root -p password 'newpassword'

Logging as a User

Use following command to log in as root user. For logging as a different user, type that username in place of root. After hitting Enter key, it will ask to enter the password. After entering correct password, you would see MySQL prompt (mysql>) where you can enter MySQL commands.

mysql -u root -p

Viewing Existing Users

MySQL user details are stored in a table called user of a default database called mysql. In this user table, usernames are stored in a column called user and corresponding host names are stored in a column called host.

Based on these facts, you can use following SQL command to view username and host of existing users. You need to log in as root first.

SELECT user, host FROM mysql.user;

Creating a New User

For creating a user called robin with the password robin123 at localhost (is the default host most of the time), log in as root and use following command.

CREATE USER 'robin'@'localhost' IDENTIFIED BY 'robin123';

Granting Privileges to a User

MySQL has a series of privileges. For a general PHP application, you only need a user with SELECT, INSERT, UPDATE and DELETE privileges for the database you chose. You can grant these privileges to a user called ronbin for a database called my_database using following command.

GRANT SELECT, INSERT, UPDATE, DELETE ON my_database.* TO 'robin'@'localhost';

To grant all the privileges, use ALL as below.

GRANT ALL ON my_database.* TO 'robin'@'localhost';

Changing a User Password

To change the password of a user called robin to robin456, login as root user and use following command.

SET PASSWORD FOR 'robin'@'localhost' = PASSWORD('robin456');

Deleting a User

Log in as root and use following command to delete user robin.

DROP USER [email protected];

Be careful when you delete a user since applications that used the credentials of deleted user may malfunction.


PHP needs the host name, username and password of a privileged MySQL user to connect to a MySQL database. When it comes to production level PHP applications, instead of using root user, it’s a good practice to use a dedicated user with only the required privileges for improved security.

You will only need to deal with MySQL users when you manage your own web server or when you develop web applications locally. If you are on a shared web hosting environment, most of the time, you will be provided privileged MySQL user accounts or a GUI tool to manage MySQL users.

Where to Head from Here...
Share with Your Peers...

We Value Your Feedback...

We love to hear what you think about this article. Please provide your opinion, suggestions and improvements using following form. Note that submitted feedback is not displayed but we will get back to you if it needs a reply.