How to create a new user and giving specific permissions in MySQL.

by

1.) Create new user.


mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

2.) Now, we will identify what are the different permissions in MySQL.

  • ALL PRIVILEGES – this would allow a MySQL user full access to a designated database (or if no database is selected, global access across the system.
  • CREATE –  allows them to create new tables or databases.
  • DROP –  allows them to them to delete tables or databases.
  • INSERT –  allows them to insert rows into tables.
  • SELECT –  allows them to use the SELECT command to read through databases.
  • UPDATE –  allow them to update table rows.
  • GRANT OPTION –  allows them to grant or remove other users’ privileges.

3.) Then, you can assign one of above permissions to created user.


mysql>  GRANT type_of_permission ON database_name.* TO 'newuser'@'localhost';

As a example,


mysql>  GRANT  SELECT ON database_name.* TO 'newuser'@'localhost';

Finish!

Now we will see how to remove added permission from above user.


mysql>  REVOKE  SELECT ON database_name.*  FROM  'newuser'@'localhost';

You can delete the create user.


mysql>  DROP USER 'newuser'@'localhost';

Cheers! Happy coding.

Leave a Comment