# MySQL User Permissions

> Learn how MySQL user permissions work and how to grant privileges like CREATE, SELECT, and ALL PRIVILEGES to an account using the GRANT command on databases.

Author: [Flavio Copes](https://flaviocopes.com/about/) | Published: 2020-01-16 | Topics: [Database](https://flaviocopes.com/tags/database/) | Canonical: https://flaviocopes.com/mysql-user-permissions/

> Let's see how to grant permissions (called privileges) to a user of the MySQL database

By default when you create a new MySQL user using the syntax

```sql
CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>';
```

the user cannot do much. We can say that it can't to anything, actually.

It can't read data from any existing database, let alone modifying the data. And it can't even create a new database.

To make a user do anything, you have to **grant privileges** to it.

You can do so using the `GRANT` command.

We can use `GRANT <permission>`, using the following permission keywords:

- `CREATE`
- `DROP`
- `DELETE`
- `INSERT`
- `SELECT`
- `UPDATE`
- `ALL PRIVILEGES`

## Give privilege to create new databases to a user

```sql
GRANT CREATE ON *.* TO '<username>'@'localhost';
```

## Give privileges to a user to create new tables in a specific database

```sql
GRANT CREATE ON <database>.* TO '<username>'@'localhost';
```

## Give privilege to read (query) a specific database to a user

```sql
GRANT SELECT ON <database>.* TO '<username>'@'localhost';
```

## Give privilege to read a specific database _table_ to a user

```sql
GRANT SELECT ON <database>.<table> TO '<username>'@'localhost';
```

## Give privilege to insert, update and delete rows in a specific database to a user

```sql
GRANT INSERT, UPDATE, DELETE ON <database>.* TO '<username>'@'localhost';
```

## Give privilege to delete tables in a specific database to a user

```sql
GRANT DROP ON <database>.* TO '<username>'@'localhost';
```

## Give privilege to delete databases to a user

```sql
GRANT DROP ON *.* TO '<username>'@'localhost';
```

## Give all privilege on a specific database to a user

```sql
GRANT ALL PRIVILEGES ON <database>.* TO '<username>'@'localhost';
```

## Give all privileges to a user

```sql
GRANT ALL PRIVILEGES ON *.* TO '<username>'@'localhost';
```

## Revoke a privilege

Example to revoke the `DROP` privilege on `<database>`:

```sql
REVOKE DROP ON <database>.* TO '<username>'@'localhost';
```

To revoke all privileges, run:

```sql
REVOKE ALL PRIVILEGES ON *.* TO '<username>'@'localhost';
```

---

You can visualize the privileges of a single user by running:

```sql
SHOW GRANTS FOR '<username>'@'localhost';
```