User Account Management In MySQL

You need to have a user account to execute queries on data. When you install mysql you will prompted to enter root user details or some times you might have installed mysql with root user and no password. It is recommended to have a password for root user after completion of installation and it is best practice to use different set of mysql users to serve different purposes instead of root user to mitigate security vulnerabilities.

To execute any of the following instructions to create and edit user you have to connect with mysql server using mysql client.

mysql -u root -p

Assuming your mysql server is on localhost else specify server with option -h

Create User

To create user we use the statement CREAT USER.  Find the definitive syntax here

mysql>  CREATE USER ‘Bob’@’localhost’ IDENTIFIED BY ‘password‘;

In mysql every user is associated with host. Here it is localhost specified in the statement above. It meas user Bob can only login from host localhost(same machine)

After your create user, he will be assigned the default permissions. To let him access specific database or table you have to grant him the permissions. To do so, we use the statement GRANT

To grant all permissions on all database,

mysql> GRANT ALL PRIVILEGES ON *.* TO ‘Bob’@’localhost’;

To grant all permissions on specific database,

mysql> GRANT ALL PRIVILEGES ON db_name.* TO ‘Bob’@’localhost’;

Once you are done with creating user. Reload privileges using

mysql> flush privileges;

This command will reload users and their privileges.

Change User Password

User can be altered using the statement ALTER USER. To change the password of an user, use the following statement

mysql> ALTER USER ‘Bob’@’localhost’ IDENTIFIED BY ‘password’;

You can change your own password by following statment,

mysql> ALTER USER USER() IDENTIFIED BY ‘password’;

You can also change the password of an user using following two statements as well,

SET PASSWORD
GRANT

Change password using SET PASSWORD

mysql> SET PASSWORD FOR ‘Bob’@’localhost’ =’password’;

Note: Before MySQL 5.7.6 you have to give hash to the statement SET PASSWORD using password function.

Change password using GRANT

mysql> GRANT ALL PRIVILEGES ON  *.* TO ‘Bob’@’localhost’ IDENTIFIED BY ‘new_password’;

List All Users

Following query will list all available users

mysql> select User, Host from mysql.user;

Change host associated with user account

You can change host of any user by updating the Host column of table mysql.user for that user. With out having permissions to update this table you can’t change the host of any user

mysql> update mysql.user set Host=’host’ where Host=’old_host’;

Delete User

To delete an user, we use the statement DROP USER.

mysql> DROP USER ‘Bob’@’localhost’; 

 

Leave a Reply

Your email address will not be published. Required fields are marked *