Creating a New User and Setting Permissions in MySQL

Getting Started with MySQL

MySQL is like a helpful organizer for data, and it's free for everyone to use. It usually works with other tools like Linux, Apache, MySQL, and PHP (LAMP stack). Right now, it's the most popular free database out there.

This guide will show you how to make a new friend in MySQL (we call them a user) and give them special powers to do different things. Let's get started!

What You Need Before We Start

Before we begin, you'll need to have something called a MySQL database. Imagine it like a safe place for information. This guide thinks you have this safe place on a special computer called a virtual private server, running a cool system called Ubuntu 20.04. But don't worry, the ideas we'll talk about work the same no matter where your database is.

If you don't have a MySQL database yet and want to set one up, we have a guide on How To Install MySQL. It doesn't matter if your computer works with a different system – creating a new MySQL friend and giving them special powers will be pretty similar. Ready to go? Let's do it!

Note: As we go through this guide, you might notice some parts of the commands that need your attention. We'll point them out with a special highlight like this. It's kind of like adding your own emojis to a message – just keep an eye out for these notes, and you'll know when to put your own touch on things!

Creating a New Friend in MySQL

When you install MySQL, it creates a special user called "root." This user can do pretty much anything in the database – it's like the boss! But because it's so powerful, we should only use it for important stuff.

This step shows you how to use the root user to make a new friend (a user) and give them some powers.

In Ubuntu systems running MySQL 5.7 and later, the root user doesn't use a password by default – it's a bit like having your house key always ready. But to make changes, you'll need to use a special command called sudo. This makes sure you have the right permissions to do what you want with MySQL. Ready to make some new friends? Let's go!

sudo mysql

Note: If your root MySQL user has a password, you'll need to use a different command to access the MySQL world. This command lets you use MySQL like a regular user, and you'll only get special privileges in the database if you enter the right password. It's like requiring a special key to unlock something - once you have it, you're in!

mysql -u root -p

Once you're in the MySQL world, you can create a new friend using a special command called CREATE USER. It's like putting together a puzzle – just follow the steps!

CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';

After typing CREATE USER, you write down the name you want for your new friend. Then, you add an @ sign and the name of the place they'll be connecting from. If you're only letting them connect from your Ubuntu server, you can just write "localhost." Putting single quotes around both the name and the place isn't always required, but it can help avoid mistakes.

You can pick how your user logs in to MySQL. The auth_socket plugin is easy because users don't need a password to get in. But it can also make it tricky for programs outside your computer to talk to MySQL because it blocks remote connections.

Instead of specifying the authentication plugin, you can skip that part to let the user log in with MySQL's default security system, called caching_sha2_password. This is a good choice if you want to use a password for logging in because it's very secure. To create a user that uses caching_sha2_password, run this command. Just replace "sammy" with the username you want and pick a strong password that you'll remember.

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

Note: If you're using PHP for things like phpMyAdmin and facing issues with caching_sha2_password, no worries! You can create a user that uses the older, but still safe, plugin called mysql_native_password. This might help to avoid any problems when using PHP with your database.

CREATE USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

If you're not certain about which plugin to choose, don't worry! You can start by creating a user that uses the caching_sha2_plugin and if needed, you can adjust it later using the command: ALTER.

ALTER USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Once you've made your new user, you can give them the right permissions.

Giving User Permissions

Here's the basic way to give a user permission to do things:

GRANT PRIVILEGE ON database.table TO 'username'@'host';

In this example, the PRIVILEGE value determines what the user can do with the specified database and table. You can give the user multiple permissions at once by separating them with commas. If you want to give the user permissions for all databases or tables, you can use asterisks (*) instead of specific names. In SQL, asterisks mean "all" databases or tables.

Here's an example command: it gives a user the ability to do various things like creating, altering and dropping databases and tables. They can also add, update and delete data from any table and perform other actions like querying data and creating foreign keys. But keep in mind, it's a good idea to only give users the specific abilities they actually need. So, feel free to adjust what your user can do based on what tasks they should be able to perform.

You can see the complete list of things users can do in the official MySQL documentation. To give these abilities to your user, just use the GRANT statement. Instead of "sammy," use the name of your own MySQL user. Easy, right?

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sammy'@'localhost' WITH GRANT OPTION;

Keep in mind, this statement includes something called WITH GRANT OPTION. This means your MySQL user can share any permissions it has with other users on the system.

Caution: Be careful with granting the ALL PRIVILEGES privilege to your MySQL user. It gives them extensive powers, almost like being the boss (root user). Here's how some users might do it:

GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;

Think twice before giving such extensive powers. If someone can access this MySQL user, they'll have complete control over all the databases on the server.

Lots of guides recommend using the FLUSH PRIVILEGES command right after you make a NEW USER or give them special powers. This helps to make sure that the changes take effect right away.

FLUSH PRIVILEGES;

But here's the thing: if you change the special settings using a statement like GRANT, the database will automatically update itself. So, you don't really need to use the FLUSH PRIVILEGES command in our case. But hey, running it won't harm anything either.

If you ever need to take away a permission, the process is almost the same as giving it:

REVOKE type_of_permission ON database_name.table_name FROM 'username'@'host';

Note: When you're taking away permissions, remember to use "FROM" in the command, not "TO" like you did when giving permissions.

To check what a user can currently do, run the SHOW GRANTS command:

SHOW GRANTS FOR 'username'@'host';

Similar to deleting databases with DROP, you can use DROP to remove a user:

DROP USER 'username'@'localhost';

Once you've made your MySQL user and given them special settings, you can leave the MySQL world by exiting the MySQL client:

exit

In the future, when you want to sign in with your new MySQL user, just type a command like this:

mysql -u sammy -p

With the -p flag, MySQL will ask you to type in your password when you want to log in with your MySQL user account. This is to make sure it's really you trying to access the account.

Conclusion:

After completing this tutorial, you now know how to make new users and give them different powers in a MySQL database. You can keep exploring and trying out different things with your MySQL users, or you might want to learn more about some advanced MySQL settings.

Excited to manage your MySQL database?

Begin by making new users and deciding what they can do! Try out different settings to improve your database skills. Let's get started!