List, Add and Remove Users from MySQL Console

Learn to list, add, update and delete MySQL users from the console. Also, learn to assign or unassign the granted authorities.

1. List All Users

We can use the following query to see the list of all users in the database server:

prompt> SELECT user FROM mysql.user;  

We can use the following command if we have already logged into the database server.

prompt> SELECT user FROM user;  

To show the current user, use the following query.

prompt> SELECT user();  
 //or
prompt> SELECT current_user();  

2. Create a New User

To create a new user on the server, “CREATE USER” command is used. Please note that if we create the user with this command, no privileges are assigned to the user by default.

prompt> CREATE USER 'demouser'@'localhost' IDENTIFIED BY 'password';

3. Delete a User

To delete a user, use “DROP USER” command.

prompt> DROP USER 'superdemouser'@'localhost';

4. Rename a User

To rename a user, use “RENAME USER” command.

prompt> RENAME USER 'demouser'@'localhost' TO 'superdemouser'@'localhost';

5. Change Password

To change a user’s password, use the “SET PASSWORD” command.

prompt> SET PASSWORD FOR 'demouser'@'localhost' = PASSWORD('newpassword');

6. Grant Privileges

For granting privileges to a user, use “GRANT” command.

prompt> GRANT SELECT ON test.* TO 'demouser'@'localhost';
prompt> GRANT SELECT, UPDATE ON test.* TO 'demouser'@'localhost';
prompt> GRANT SELECT, DELETE ON test.* TO 'demouser'@'localhost';

7. Revoke Privileges

For revoking access to a user, use “REVOKE” command with table name.

prompt> REVOKE INSERT ON test.EMPLOYEE TO 'demouser'@'localhost';

8. Granting Table- and Column- specific Privileges

For granting privileges on a particular tablee to a user, use “GRANT” command with table name.

prompt> GRANT INSERT ON test.EMPLOYEE TO 'demouser'@'localhost';
prompt> GRANT INSERT, UPDATE ON test.EMPLOYEE TO 'demouser'@'localhost';

For granting privileges on a particular column(s) of table to a user, use “GRANT” command with table name and column name.

prompt> GRANT INSERT (FIRSTNAME), SELECT (FIRSTNAME) ON test.EMPLOYEE TO 'demouser'@'localhost';

Happy Learning !!