How To Create User Accounts Using MySQL CREATE USER Statement

MySQL CREATE USER Statement

The CREATE USER statement in MySQL is used to create new user accounts in the MySQL database system. After creating a user, you can assign privileges to that user using the GRANT statement, allowing them to interact with databases and tables.

Syntax:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

username: The name of the user to be created.
host: The host from which the user can connect to the MySQL server. It can be an IP address, domain name, or a wildcard (%) to allow connections from any host.
password: The password associated with the user. This should be a secure password.

Example 1: Create a User with a Password

To create a user named new_user who can connect from any host and has the password password123:

CREATE USER 'new_user'@'%' IDENTIFIED BY 'password123';

This creates the user new_user that can log in from any host (%), and their password is set to password123.

Example 2: Create a User with a Specific Host

To create a user named new_user who can connect only from the local machine (localhost):

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password123';

This will allow new_user to connect only from localhost.

Example 3: Create a User with No Password (Not Recommended)

To create a user without a password (not recommended for production environments):

CREATE USER 'new_user'@'localhost' IDENTIFIED BY '';

This creates the user new_user who can connect localhost but does not have a password. Note: This is insecure and should be avoided.

Example 4: Create a User with SSL Authentication

To create a user who requires an SSL connection for authentication:

CREATE USER 'secure_user'@'%' IDENTIFIED BY 'securepass'
SSL REQUIRE;

This will force the user to connect over an encrypted SSL connection.

Assigning Privileges to the User

Once the user is created, you can grant privileges using the GRANT statement. For example, to grant the user full privileges on a database:

GRANT ALL PRIVILEGES ON database_name.* TO 'new_user'@'%';

This will allow the new_user to have full access (SELECT, INSERT, UPDATE, DELETE, etc.) to all tables in the database_name.

Apply Changes

After creating a user and assigning privileges, use the FLUSH PRIVILEGES command to make the changes take effect immediately:

FLUSH PRIVILEGES;

View Created Users

To see the list of all users in MySQL:

SELECT user, host FROM mysql.user;

This will display a list of usernames and their associated hostnames.

Conclusion

The CREATE USER statement is a fundamental command for adding users to a MySQL server. By defining specific permissions with GRANT, you can control the level of access each user has to the system. It's essential to properly secure user accounts and avoid weak or no passwords in production systems.

Souy Soeng