How to Create a New User and Grant Privileges in MySQL

In this article, we will discuss how to grant different levels of permissions to third parties and provide access to created tables in MySQL. Setting up a user account is the first step because you cannot perform any action without it.

How to Create a Regular User in MySQL

To add a user to MySQL, you must have privileges to create users. The query syntax is as follows:

CREATE USER [IF NOT EXISTS]
  user [auth_option] [, user [auth_option]] …
  DEFAULT ROLE role [, role ] …
  [REQUIRE {NONE | tls_option [[AND] tls_option] …}]
  [WITH resource_option [resource_option] …]
  [password_option | lock_option] …
  [COMMENT 'comment_string' | ATTRIBUTE 'json_object']

The [square brackets] indicate optional parameters.

Field description:

• If [IF NOT EXISTS] is specified, the query will not be interrupted if an error occurs.
• User: the name of the account. It consists of a user name and hostname (‘user_name’@’host_name’). The ‘host_name’ part is optional (specifies the hostname from which the connection can be established; % means “all possible hosts”). If we want to create a remote connection user in MySQL, we can specify a domain name, IP address, or subnet address. An account name consisting only of a username can be written as ‘user_name’@’%’. Such an account will be accessible from any host. It is also possible to create an anonymous user: ‘’@’localhost.
• [auth_option] is an optional value that specifies how the account is authenticated (password, lock properties, etc.)
• DEFAULT ROLE specifies the role of the user.
• If REQUIRE {NONE | tls_option [[AND] tls_option] …} is specified, it checks the X.509 certificate in addition to the ordinary authentication.
• WITH allows you to set a limit on the use of server resources. For example, limit the number of requests, updates, or connections to the server.
• password_option is for password management parameters: validity period, reuse restriction, and failed-login attempts tracking.
• COMMENT…| ATTRIBUTE allows adding user comments or attributes in JSON format (cannot be used simultaneously).

Granting Privileges

Creating users and granting them privileges to the database in MySQL is the first thing you should do. To grant privileges or assign a role (a named set of privileges), use the GRANT operator. But you cannot do both at the same time.

To grant privileges, you must have privileges yourself.

The syntax for defining an account’s role:

GRANT role [, role] …
  TO user_or_role [, user_or_role] …
  [WITH ADMIN OPTION]

Field description:

• role: the role we are granting.
• user_or_role: account’s name or role.
• If WITH ADMIN OPTION is specified, it is possible to grant the role to someone else from the account with this role. Example:

GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost';

The syntax for adding privileges:

GRANT
  priv_type
  ON [object_type] priv_level
  TO user_or_role

Field description:

• The priv_type is the privilege type. There are many of them: to create tables, update, populate, or delete them; to create roles, users, and indexes; to administer backups and accounts without passwords, and much more.
• object_type specifies the next object’s type (TABLE, FUNCTION, or PROCEDURE).
• priv_level specifies the privilege level.

Privileges can be:

• Global: these privileges apply to all databases on the server. The syntax:

ON *.*

GRANT SELECT, INSERT ON *.* TO 'user_name'@'localhost';

• For databases: apply to all objects in the specified database and create or delete tables. Example:

GRANT SELECT, INSERT ON mydb.* TO 'user_name'@'localhost';

• For tables: apply to all columns in a table, like to insert or update records. Example:

GRANT SELECT, INSERT ON mydb.mytable TO 'user_name'@'localhost';

• For columns: apply to certain columns (insert, view, update data). In the example, the privilege is followed by the column name in parentheses:

GRANT SELECT (col1), INSERT (col1, col2) ON mydb.mytbl TO 'user_name'@'localhost';

There are also subroutine and proxy user privileges.

If you grant all privileges to the account, you can create a “superuser”:

GRANT ALL ON *.* TO 'user_name'@'localhost';

To revoke the roles or privileges of an account, use REVOKE.

REVOKE INSERT ON *.* FROM 'user_name'@'localhost';

We revoke the user’s privilege to insert data into tables at the global level.

To remove a user, use DROP:

DROP USER 'user_name'@'localhost';

In conclusion, creating a new user and assigning privileges in MySQL is crucial for managing your database. With these tips, you can easily create a new user account and assign privileges to it and ensure that your data is secure and accessible only to authorized users.