How to Create a New user and Grant Permissions in MySQL

Prerequisites

Create a New User in MySQL

sudo mysql -u root -p
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Grant Permissions to User in MySQL

Types of Permissions

  • ALL PRIVILEGES — this would allow a MySQL user full access to a database or if no database is selected, global access across the system
  • CREATE — allows user to create new tables or databases
  • DROP — allows user to delete tables or databases
  • DELETE — allows user to delete rows from tables
  • INSERT — allows user to insert rows into tables
  • SELECT — allows user to use the SELECT command to read through databases
  • UPDATE — allow user to update table rows
  • GRANT OPTION — allows user to grant or remove other users’ privileges
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';
FLUSH PRIVILEGES;

Grant specific permissions

GRANT type_of_permission ON database_name.table_name TO 'username'@'localhost';
REVOKE type_of_permission ON database_name.table_name FROM 'username'@'localhost';
SHOW GRANTS FOR 'username'@'localhost';

Conclusion

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store