How to create users in MySQL
To create a new user in 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. 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, 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 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:
$ sudo mysql
bashTo create a new user in MySQL, use the CREATE USER
command. This allows you to create a user with a specific username and password:
mysql> CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';
bashReplace 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, 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:
mysql> CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password';
bashOnce you’ve created a new user, you need to assign authorizations to them.
- Simple registration
- Premium TLDs at great prices
- 24/7 personal consultant included
- Free privacy protection for eligible domains
![](https://www.ionos.com/digitalguide/fileadmin/DigitalGuide/IAS/CTAs/SEO_Digital_Guide_Mobile_nur-Nlement_900x750.png)
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:
mysql> GRANT PRIVILEGE ON database.table TO 'username'@'host';
bashThe 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:
mysql> GRANT SELECT, INSERT, UPDATE ON database.table TO 'username'@'host';
bashAuthorizations 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.
mysql> GRANT SELECT, INSERT, UPDATE ON *.* TO 'username'@'host';
bashOnce 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:
mysql> FLUSH PRIVILEGES;
bashHowever, 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
:
mysql> REVOKE type_of_permission ON database_name.table_name FROM 'username'@'host';
bashTo display the current authorizations that a user has, you can use the SHOW GRANTS
command:
mysql> SHOW GRANTS FOR 'username'@'host';
bashYou can use the DROP
command to delete a user:
mysql> DROP USER 'username'@'localhost';
bashYou 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:
mysql> exit
bash