Mysql

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 called root that has all the privileges. You need to be logged in
as root to execute 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 the following command to change root password. Type your preferred password in place of newpassword. After hitting the Enter key, it will ask you to enter the current password. If the current password is empty, just hit the Enter key.

mysqladmin -u root -p password 'newpassword'

Logging as a User

Use the following command to log in as root user. For logging in as a different user, type that
username in place of root. After hitting the Enter key, it will ask you to enter the password. After
entering the correct password, you would see the 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 the following SQL command to view the username and the 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 anuj with the password Test@123 at localhost (is the default host most
of the time), log in as root and use the following command.

CREATE USER 'anuj'@'localhost' IDENTIFIED BY 'Test@123';

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 the following
command.

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

To grant all the privileges, use ALL as below.

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

Changing a User Password

To change the password of a user called anuj to anuj123, log in as the root user and use the
following command.

SET PASSWORD FOR 'anuj'@'localhost' = PASSWORD('anuj123');

Deleting a User

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

DROP USER anuj@localhost;

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

Summary
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,
for improved security it’s a good practice to use a dedicated user with only the required privileges.
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.

 

About the author

Anuj kumar

I'm Anuj kumar a web developer with 4+ year experience .I write blogs in my free time. I love to learn new technologies and share with others. I founded PHPGurukul in September 2015. The main aim of this website to is provide php , jquery , mysql , phpoops and other web development tutorials. I am trying best effort to make PHPGurukul useful for every single moment spend on this website. If you think this website is useful to visit please share with your friend and buddies.

Leave a Comment