How to add a mysql user and grant privileges
Once you've created a mysql database you'll want to add users and give them rights over specific databases or tables.
Once you've created a mysql database you'll want to add users and give them rights over specific databases or tables.
From the command line, log into mysql as root.
mysql -u root -p
You can create a brand new user by typing:
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
newuser will be the name of the user you are creating. localhost can remain localhost or be changed to any host from which the user will be accessing mysql. You can use % to allow all hosts. password will be the password the user will use to login.
Now type this to grant privileges to a database:
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
"." represents the database.table and newuser and localhost should be the user and host you just created. To grant this user all privileges to a single database type:
GRANT ALL PRIVILEGES ON database_name . * TO 'newuser'@'localhost';
where database_name is the name of the database.
Notice that database_name.*
includes all tables in the database. You can also target individual tables but using database_name.table_name
.
Sometimes you will need to flush privileges for this to take effect:
flush privileges
At some point, you may need to inspect existing privileges (or double check your work). To do so, you can use the following:
SHOW GRANTS FOR 'newuser'@'localhost';
If you found this useful, visit our guide on how to create a Mysql root password root to add a little more security to your project.
Nothing says good morning quite like a breakfast sandwich. From the doughiness of the muffin to the eggs' fluffiness to the cheese's gooeyness, breakfast sandwiches are a great start to your morning.