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
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,
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’;
To delete an user, we use the statement DROP USER.
mysql> DROP USER ‘Bob’@’localhost’;