If you used our website builder to create your website, you’re probably really shocked that this article exists in the first place. Our sitebuilder makes it so easy to create a website that you never have to worry about MySQL or any other database management system unless you want to. So, this guide is for our WordPress hosting users.
There are tons of reasons why so many people choose WordPress, and MySQL is one of them. MySQL is an amazing web hosting tool for WordPress users. Once you get the hang of it, you’ll have more control over your website than you ever thought possible. There are tons of useful tricks you can do with your website when you understand how to use MySQL, but in this guide, we’ll teach you how to use MySQL create user and to grant user permissions.
Let’s get started, shall we?
Table of Contents
The first step to using MySQL create user
Before you use MySQL create user, you’ll need root access. Your MySQL root account is different from your system root account, so what you do with one won’t affect the other.
Now that you have this basic tip, here are the easy steps to follow if you want to use MySQL create user and grant user privileges:
How to use MySQL create user
When you start using MySQL, you’ll be given some initial login credentials: a username and a password. You’ll need these credentials to get root access. So, you can have total control over your databases. But sometimes you’ll need to give privileges to someone else. For instance, you might want to allow specific users to access the database without having complete control over the database. This is why you need to know how to create users and grant permissions.
To do this, you’ll use MySQL create user – specifically, you’ll be using MySQL to create a non-root user. This is a complete beginner’s guide to using MySQL create user and grant permissions, so just to be thorough, we’ll teach you how to create a user who has full control over your database.
It’s not advised that you give anyone complete control over your database, but you should still know how to do this.
How to use MySQL create user with full control
1. Access Command Line and enter MySQL server:
mysql
2. The script will return this result, which verifies that you’re accessing a MySQL server:
mysql>
3. Execute this command:
CREATE USER 'brand_new_user'@'localhost' IDENTIFIED BY 'password';
NOTE: ‘brand_new_user’ is the username we’ve made up for our new user, and the “IDENTIFIED BY ‘password’” section is where we’ve put the password. When you’re creating a user, you’ll use your own username and password in the quotation marks.
4. To grant all database privileges, execute this command:
GRANT ALL PRIVILEGES ON database_name. * TO 'brand_new_user'@'localhost';
5. To make the changes take effect immediately, flush these privileges by typing in this command:
FLUSH PRIVILEGES;
Once you’ve done that, your new user account will have the same access to the database as the root user. (Again, this is not advised.)
How to grant specific privileges for a MySQL user
To grant specific privileges to a specific user, you’ll need to specify which database and which table you want your user to be able to access. You’ll need to separate the database and table with a period (.) and no spaces.
Here’s what this will usually look like:
GRANT PERMISSION_TYPE ON database_name.* TO ‘brand_new_user@localhost’;
Of course, you’ll replace PERMISSION_TYPE with the specific permission you want to grant to the user, database_name with the name of the database, and brand_new_user with the username of the person you want to have the permission.
Here’s a list of some of the most common permissions you should know how to grant:
A list of permissions in MySQL
- CREATE — lets users create a database or table
- SELECT — lets users retrieve data
- INSERT — lets users add new entries in tables
- UPDATE — allows users to modify existing entries in tables
- DELETE — lets users erase table entries
- DROP — lets users delete entire database tables
- ALTER – lets users modify table structure
- EXECUTE – lets users execute stored routines
- FILE – lets users access files on the server
- GRANT OPTION – Lets users grant or remove the privileges of other users
How to grant multiple permissions to the same user in MySQL
To grant multiple privileges, simply separate them with a comma. For example, to give brand_new_user permission to DELETE and DROP, use this command:
DELETE, DROP ON database_name. * TO 'brand_new_user'@'localhost';
How to revoke permissions
Sometimes, you’ll find yourself needing to take back permissions you’ve previously granted. For instance, if you know you’re going to fire an employee, you don’t want them having root access to your database. Here’s how you revoke permissions:
REVOKE PERMISSION_TYPE ON database_name.table_name FROM ‘brand_new_user’@’localhost’;
How to drop a user
To drop a user, run this command:
DROP USER ‘brand_new_user’@’localhost’;
How to see a user’s permissions
If you want to see a list of all the permissions you’ve granted to a particular user, run this command:
SHOW GRANTS FOR ‘brand_new_user’@'localhost';
Wrapping up
Now that you’ve finished this guide, you know how to use MySQL to create and drop users. In addition, you’ll be able to grant and revoke permissions! Feel free to keep exploring MySQL and experimenting with how you use it. You can check out this guide on how to export files with MySQL command.
