# How to create users in MySQL

To create a new user in [MySQL](https://www.ionos.com/digitalguide/server/know-how/what-is-mysql/), you need sufficient administrator rights or superuser privileges that allow you to create user accounts and manage authorizations in addition to access to the respective [database](https://www.ionos.com/digitalguide/hosting/technical-matters/databases/). You also need to know what type of access the new user requires, whether that’s read rights, write rights, or even administrative rights.

## How to use MySQL’s `CREATE USER` command

When installing the [database management system](https://www.ionos.com/digitalguide/hosting/technical-matters/database-management-systems-dbms/), MySQL automatically generates a **root account**. This account grants you comprehensive control over your databases, tables and users, allowing for efficient administration. If you need help with the installation process, our [MySQL tutorial](https://www.ionos.com/digitalguide/server/know-how/learn-mysql-in-simple-steps/) has all the essential information.

With your root account, you can create additional user accounts or new MySQL users and assign them authorizations. On Ubuntu systems with MySQL 5.7 or newer versions, the MySQL root user is configured by default to authenticate itself with the `auth_socket` plugin rather than a password. This means that if the name of the system user invoking the MySQL client differs from the name of the MySQL user specified in the command, you’ll need to prefix the command with `sudo` to gain access to your root account:

```bash
$ sudo mysql
```

To create a new user in MySQL, use the `CREATE USER` command. This allows you to create a user with a specific username and password:

```bash
mysql> CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';
```

Replace `username` with a username of your choice. Under `host`, enter the name of the host where the new user can connect from. If the user should only be able to access the database from your local [Ubuntu server](https://www.ionos.com/digitalguide/server/configuration/ubuntu-server/), you can enter `localhost`.

When choosing the **authentication plugin**, you have several options. The `auth_socket` plugin offers high security by requiring users to enter a password for database access. However, it restricts remote connections, potentially requiring more effort for external programs to interact with MySQL. Alternatively, you can omit the `WITH authentication_plugin` part of the command to authenticate users using the MySQL standard plugin `caching_sha2_password`. This how the command would look like:

```bash
mysql> CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password';
```

Once you’ve created a new user, you need to assign authorizations to them.

## How to assign rights to users in MySQL

The creation and management of user rights are essential for maintaining data security in MySQL. The general command for assigning user rights is:

```bash
mysql> GRANT PRIVILEGE ON database.table TO 'username'@'host';
```

The value `PRIVILEGE` determines which actions the user can perform in the specified database and table. You can replace this value with the following commands, among others:

- `CREATE`: Allows users to create a database or table
- `SELECT`: Allows users to retrieve data
- `INSERT`: Allows users to add new entries to tables
- `UPDATE`: Allows users to modify existing entries in tables
- `DELETE`: Allows users to delete table entries
- `DROP`: Allows users to drop entire database tables

You can also grant new users several privileges at once. When doing so, you need to separate the priveleges with a comma:

```bash
mysql> GRANT SELECT, INSERT, UPDATE ON database.table TO 'username'@'host';
```

**Authorizations for all databases or tables** can also be granted in a single command by entering `*` instead of the individual database and table names. For example, the following command gives a user the authorization to query data (`SELECT`), to add new entries (`INSERT`) and to change existing entries (`UPDATE`) in all databases and tables.

```bash
mysql> GRANT SELECT, INSERT, UPDATE ON *.* TO 'username'@'host';
```

Once you’ve executed the `CREATE USER` or `GRANT` commands in MySQL, you can use the `FLUSH PRIVILEGES` command to update the database. This reloads the authorization tables, ensuring that the new authorizations are put into effect:

```bash
mysql> FLUSH PRIVILEGES;
```

However, it’s important to only grant users the authorizations they need. If you give a user full control, this can pose a **high security risk**.

## How to revoke user rights from users in MySQL

The `REVOKE` command is used to remove user rights in MySQL. The syntax is similar to that of the `GRANT` command. However, with this command, you need to use `FROM` instead of `TO`:

```bash
mysql> REVOKE type_of_permission ON database_name.table_name FROM 'username'@'host';
```

To display the current authorizations that a user has, you can use the `SHOW GRANTS` command:

```bash
mysql> SHOW GRANTS FOR 'username'@'host';
```

You can use the `DROP` command to delete a user:

```bash
mysql> DROP USER 'username'@'localhost';
```

You should be extremely careful when deleting users, especially users with administrative privileges. Ensure you only remove users you really want to delete in order to avoid unintended data loss.

Once you’re done creating new MySQL users and granting them rights, you can exit the MySQL client:

```bash
mysql> exit
```


This is a markdown version of: [https://www.ionos.com/digitalguide/websites/web-development/mysql-create-user/](https://www.ionos.com/digitalguide/websites/web-development/mysql-create-user/) for AI/LLM consumption.