CREATE USER - MySQL

Overview

The CREATE USER command creates a new user in the MySQL database. It is primarily used for managing database access privileges for specific individuals or applications.

Syntax

CREATE USER username [IDENTIFIED BY 'password'] [OPTIONS]

Options/Flags

  • IDENTIFIED BY 'password': Sets the password for the new user.
  • DEFAULT ROLE role_name: Assigns the specified role as the default role for the user.
  • DEFAULT AUTHENTICATION_PLUGIN plugin_name: Specifies the authentication plugin to use for the user.
  • PASSWORD EXPIRE: Specifies that the user’s password must be changed at the next login.
  • ACCOUNT LOCK: Locks the user’s account, preventing logins.
  • REQUIRE X509: Requires the user to connect using an X.509 certificate.
  • GRANT [privileges] ON [database].[table] or GRANT ALL PRIVILEGES ON [database] or GRANT ALL PRIVILEGES: Grants specific or all database privileges to the user.

Examples

Create a user with a password:

CREATE USER my_user IDENTIFIED BY 'my_password';

Create a user with a default role:

CREATE USER my_user DEFAULT ROLE admin;

Create a user and grant database-level privileges:

CREATE USER my_user IDENTIFIED BY 'my_password';
GRANT SELECT, INSERT, UPDATE ON my_database.* to my_user;

Common Issues

  • Invalid password: Ensure the password meets the complexity requirements set by your MySQL configuration.
  • Duplicate user: The username must be unique within the database.
  • Insufficient privileges: The user executing the CREATE USER command must have the CREATE USER privilege.

Integration

  • Use ALTER USER to modify an existing user’s properties.
  • Combine with GRANT and REVOKE commands to manage user privileges.
  • Integrate with USER() and CURRENT_USER() functions to get information about the current user.