Create a new user and grant permissions in MySQL
Last updated on: 2020-06-03
Authored by: John Abercrombie
The article describes the MySQL® database permissions. It also describes how to create a new user and grant or revoke permissions.
Using MySQL commands
Following are helpful suggestions for MySQL commands.
You do not have to type MySQL commands in capital letters. Both of the following commands work equally well:
CREATE USER ‘username’@’localhost’ IDENTIFIED BY ‘password’; create user ‘username’@’localhost’ identified by ‘password’;
However, using capital letters helps you keep the command syntax separate from the data or variable elements of the command.
Also, you can change the
localhost element, which allows the user to access the database from the computer
you are currently on. If you want to allow the user to access the database from anywhere, use a
localhost. If you want the user to log into the database from a specific IP address, insert that
IP address instead. For example
Therefore, you could create a user with any of the following commands:
CREATE USER ‘username’@’localhost’ IDENTIFIED BY ‘password’; CREATE USER ‘username’@’%’ IDENTIFIED BY ‘password’; CREATE USER ‘username’@’22.111.000.34’ IDENTIFIED BY ‘password’;
Note: If you want the user to be able to access the database from two specific locations, and only those
locations, you should create the user twice. For example, suppose you want your user to be able to log in to
the database from the computer you are currently on as well as a specific IP address. First, create the user
by using the
localhost location in the command. Then, create the user again by using the specific IP address.
Common permissions include the following:
ALL PRIVILEGES: Allows the user full access to a designated database or allows global access across the system if you don’t designate a database. This permission includes all of the following permissions except GRANT.
CREATE: Allows the user to create new tables or databases.
DROP: Allows the user to delete tables or databases.
DELETE: Allows the user to delete rows from tables.
INSERT: Allows the user to insert rows into tables.
SELECT: Grants the user read-only privileges to the designated databases.
UPDATE: Allows the user to update table rows.
LOCK TABLES: Allows the user to lock tables.
SHOW DATABASES: Allows the user to list all databases.
GRANT OPTION: Allows the user to grant or remove other users’ privileges. You must grant this permission explicitly.
Log in to MySQL as the root user by using the following command:
mysql -u root -p
The system prompts you for the root password for MySQL, which is different than your root password for the server itself.
Note: If you have sudo privileges on the server, you can likely find the MySQL root password in the ~/.my.cnf file, as the root user.
Create a new user
Now that you are logged into MySQL as root, you can create your user. Choose a username that you like.
Enter the following command, replacing
password with your chosen username and password:
CREATE USER ‘username’@’localhost’ IDENTIFIED BY ‘password’;
If successful, the system displays Query OK.
Use the following format to grant user privileges in MySQL:
GRANT permission1, permission2, permission3 ON databasename.tablename TO ‘newuser’@’localhost’;
If you just want to give the user access to all the tables on a database, use
databasename.tablename. Similarly, if you want the user to have a particular set of privileges on
all tables of all databases, use
Examples of granting privileges:
GRANT ALL PRIVILEGES ON databasename.* TO ‘newuser’@’%’; GRANT ALL PRIVILEGES, GRANT OPTION ON *.* TO ‘newuser’@’22.111.000.34’; GRANT SELECT, SHOW DATABASES, LOCK TABLES ON databasename.* TO ‘newuser’@’localhost’;
When you need to revoke a user’s privileges, use the following format:
REMOVE permission1, permission2, permission3 ON databasename.* FROM ‘newuser’@’localhost’;
To check what privileges a user has, use the following command:
SHOW GRANTS newuser;
After you create a user, grant privileges, or revoke privileges, run the following command:
This command reloads the tables with the new users and privileges included. Think of it as saving your changes.
After you have finished making changes, use the following command to exit MySQL safely: