How to create and grant access to new user in MySQL?
To create a new user in MySQL:
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
or
CREATE USER 'username'@'%' IDENTIFIED BY 'password';
Remember to use your own username and password.
The difference between 'localhost' and '%' is that the account 'username'@'localhost' can only used when connecting from the local host, the 'username'@'%' can be used while connecting from any host.
To create a user without password:
CREATE USER 'username'@'localhost';
To grant full privileges to new user:
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;
or
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;
Instead of granting superuser privileges, you can choose specific privileges to grant to user such as SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS,…
To learn more about granting privileges, visit this post.
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.
Not on Mac? Download TablePlus for Windows.
On Linux? Download TablePlus for Linux
Need a quick edit on the go? Download TablePlus for iOS.
