Create a MySQL User and Give It Permissions from the Command Line

What do these words mean?

MySQL is a popular database system. A database is a store for information.

A MySQL user is a login. A permission (also called a privilege) is a rule about what that login may do, such as read data or delete it. It works like a school pass: a student may enter the library but not the staff room.

The command line is a screen where you type instructions. You reach it on a server with SSH, a safe way to log in from far away. Ask Hostvento support if SSH is on your plan.

Before you start

You need MySQL admin rights, usually the MySQL root login. On shared hosting you normally cannot do this. Use your control panel's MySQL Databases tool instead.

Steps

Connect to your server with SSH.
Log in to MySQL:
```
mysql -u root -p
```
This opens MySQL as the root user and asks for the password.
Create the user. Pick your own name and a strong password:
```
CREATE USER 'shopuser'@'localhost' IDENTIFIED BY 'StrongPassword123!';
```
This makes a user named shopuser who can log in only from the same computer.
Give permissions on one database:
```
GRANT ALL PRIVILEGES ON shopdb.* TO 'shopuser'@'localhost';
```
This lets the user do everything inside the database shopdb and nothing outside it.
Apply the changes:
```
FLUSH PRIVILEGES;
```
This tells MySQL to reload its permission list.
Leave MySQL:
```
EXIT;
```

Give fewer permissions

Often a user needs less. For a read-only user, use:

GRANT SELECT ON shopdb.* TO 'reportuser'@'localhost';

This lets the user only read data. Other useful words are INSERT, UPDATE and DELETE.

Check the permissions

SHOW GRANTS FOR 'shopuser'@'localhost';

This lists what the user may do.

Take permissions away or remove a user

Warning: deleting a user can stop any site that uses it. Check first.

REVOKE ALL PRIVILEGES ON shopdb.* FROM 'shopuser'@'localhost';
DROP USER 'shopuser'@'localhost';

The first line takes the rights away. The second line deletes the user.

Tip: Give each app its own user, with only the rights it needs. That limits damage if one is hacked.

Quick recap

Use CREATE USER to make a login.
Use GRANT to give permissions, then FLUSH PRIVILEGES.
Give the least permissions needed.
Check with SHOW GRANTS.