MySQL - How to grant access to users?

Grant access to users in MySQL

To create a new user:

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

To grant full privileges on a specific table:

GRANT ALL PRIVILEGES ON database.table TO 'user'@'localhost';

To grant full privileges on all tables in the database:

GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost';

To grant full privileges on all databases and tables:

GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost';

It will create a superuser therefore contains a security risk. So you can try granting these specific privileges instead:

CREATE – allows the user to create databases and tables
DROP - allows the user to drop databases and tables
DELETE - allows the user to delete rows from specific MySQL table
INSERT - allows the user to insert rows into specific MySQL table
SELECT – allows the user to read the database
UPDATE - allows the user to update table rows

Example:

GRANT SELECT, INSERT, DELETE, UPDATE ON database.* TO 'user'@'localhost';

After that, flush privileges to take effect:

FLUSH PRIVILEGES;

Need a good GUI Tool for MySQL? TablePlus is a modern, native tool with an elegant UI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more. 