Not familiar with MySQL? You may want to also check out our blog article, MySQL Basics: How to Create a MySQL Database.
Why Would I Need Users in MySQL?
Creating users is like creating login accounts for your database. If you want to give people access to work on your database, your best course of action is to create an account for them to log in on their own. You can then delete their account if they no longer need access. You can also limit their access by giving their account specific permissions. As the administrator, you can edit their permissions at any time to better fit your needs.
Create MySQL User
To create a user in MySQL, you need to first log in to the MySQL server as the root user. Note that if your MySQL databases are on a server, you will first need to connect to the server via the command line. Follow your web host’s instructions to establish the connection. Once you have reached the location of your MySQL, you can login to root by inputting:
mysql --user=root mysql
This will log you in as the root user. This gives you the permissions you need to create and manage users.
To create users, you can use the following formula:
CREATE USER ‘user’@‘localhost’ IDENTIFIED BY ‘password’;
Replace user with the name you want to identify the new user. They will use this to log in.
Replace localhost with the host. The user will only be able to log in from the hostname listed. However, if you replace localhost with a ‘%’ (commonly referred to as a wildcard), this user will be able to log in from any host.
Replace password with a password for the new user.
You can also create a user without a password by using the formula:
CREATE USER ‘user’@‘localhost’;
However, this is not recommended since opens up vulnerabilities in your MySQL server.
Setting User Permissions
Setting up permissions per user is helpful, especially when you have multiple people working on your databases. You can determine what user accesses what, which keeps the database secure and the lessens the risk of one user causing widespread damage.
The key to adding permissions is using the GRANT statements. GRANT statements essentially grant the user certain permissions. Here’s an example of a GRANT statement:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON thisDatabase.* TO ‘user’@‘localhost’;
This specific example gives user at the localhost permission to access the database called thisDatabase. If you use this example to set permissions, be sure to replace thisDatabase with the name of your database and the ‘user’@’localhost’ with the user and its corresponding hostname. Keep in mind that users will only be able to access the database from the hostname provided in the GRANT statement. If you want them to be able to gain access from anywhere, the hostname can be replaced by a ‘%’ wildcard. If you wanted to limit them to a domain, you would change localhost to hostname.example.com.
You can also use a GRANT statement to give users full privileges over your MySQL server. To do this, the GRANT statement would look like:
GRANT ALL PRIVILEGES ON *.* TO ‘user’@‘localhost’ WITH GRANT OPTION;
This would create a “super” user that would be able to do everything you can now. Only create these for people that absolutely need it. These users will be able to make changes that could affect the entirety of your MySQL server.
Seeing Permissions for Existing Users
There is a way for you to view existing permissions for a user, too. If you know the user’s username, you can check the privileges it has been given. This is a handy tool to keep in mind if you ever need to review someone’s permissions. The formula is simple:
SHOW GRANTS FOR ‘user’@‘localhost’;
Revoking Permissions of Existing Users
If you were to view someone’s permissions and decide they needed to be changed, you could use a REVOKE statement to remove their permissions, then use a new GRANT statement to give them new permissions.
REVOKE SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON thisDatabase FROM ‘user’@‘localhost’;
A REVOKE statement looks very similar to a GRANT statement and many of the same ideas apply. As with the GRANT statement, replace thisDatabase with your database name, and the user and localhost with the actual username and hostname. If this example was executed, it would remove user’s access to thisDatabase.
Users and Permissions in Review
User creation opens new avenues of database management. Paired with permissions, you can limit and direct the users in MySQL. Using permissions wisely can protect your database against accidental errors that affect the entire system and prevent widespread damage if an account was hacked. While it may be daunting to write your own GRANT statements, you can also undo them by revoking their permissions.
If you liked this blog article, you can subscribe to our blog to be notified of new articles. Stay tuned for the next installment in MySQL Basics.
