Create a new user and grant permissions in MySQL
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.
Capitalization
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.
localhost parameter
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 %
symbol
instead of localhost
. If you want the user to log into the database from a specific IP address, insert that
IP address instead. For example username’@’22.111.000.34
.
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.
Permissions
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
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 username
and password
with your chosen username and password:
CREATE USER ‘username’@’localhost’ IDENTIFIED BY ‘password’;
If successful, the system displays Query OK.
Grant permission
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.*
instead
of 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’;
Revoke permissions
When you need to revoke a user’s privileges, use the following format:
REMOVE permission1, permission2, permission3 ON databasename.* FROM ‘newuser’@’localhost’;
Check permissions
To check what privileges a user has, use the following command:
SHOW GRANTS newuser;
Apply permissions
After you create a user, grant privileges, or revoke privileges, run the following command:
FLUSH PRIVILEGES;
This command reloads the tables with the new users and privileges included. Think of it as saving your changes.
Log out
After you have finished making changes, use the following command to exit MySQL safely:
EXIT;
Updated 11 months ago