How to Create a MySQL User and Assign Permissions

Working with MySQL rarely involves just the root user. Each application, developer, or service needs its own account with specific access rights. This is a fundamental security principle: minimal privileges for performing specific tasks.

Let's explore how to create MySQL users, assign them permissions, and manage database access.

Connecting to MySQL

Before creating users, you need to log into the MySQL console with an account that has sufficient privileges (typically root):

mysql -u root -p

After entering the password, you'll enter the MySQL interactive console. All subsequent commands are executed within it.

Creating a User

Basic syntax for user creation:

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

Breaking down the components:

• username — the user's name
• host — the host from which connections are allowed
• password — the user's password

User Creation Examples

User for local connection:

CREATE USER 'webapp'@'localhost' IDENTIFIED BY 'SecurePass123!';

This user can only connect from the same server where MySQL is installed.

User for remote connection from specific IP:

CREATE USER 'admin'@'192.168.1.100' IDENTIFIED BY 'AnotherPass456!';

Access is only allowed from IP address 192.168.1.100.

User with access from any host:

CREATE USER 'remote_user'@'%' IDENTIFIED BY 'RemotePass789!';

The % symbol means "any host". Use this cautiously — open access creates security risks.

User for connections from specific subnet:

CREATE USER 'network_user'@'192.168.1.%' IDENTIFIED BY 'NetworkPass321!';

Access for any address from the 192.168.1.0/24 range.

Granting Permissions — The GRANT Command

After creating a user, they have no permissions. You can assign them using the GRANT command.

Basic syntax:

GRANT privileges ON database.table TO 'user'@'host';

Privilege Levels

MySQL supports a detailed permission system. Here are the main privileges:

Read permissions:

SELECT — data retrieval

Write permissions:

• INSERT — adding records
• UPDATE — modifying data
• DELETE — removing records

Structure permissions:

• CREATE — creating tables and databases
• ALTER — modifying table structure
• DROP — deleting tables and databases
• INDEX — managing indexes

Administrative permissions:

• CREATE USER — creating users
• GRANT OPTION — transferring your permissions to others
• RELOAD — reloading privileges
• SHUTDOWN — stopping the server
• PROCESS — viewing processes

Special privilege:

ALL PRIVILEGES — all available permissions (except GRANT OPTION)

Practical Permission Assignment Examples

Full access to specific database:

GRANT ALL PRIVILEGES ON shop_db.* TO 'webapp'@'localhost';

User webapp receives all permissions for the shop_db database and all its tables (the * symbol).

Read-only access to database:

GRANT SELECT ON analytics_db.* TO 'analyst'@'192.168.1.50';

Useful for analysts or reporting systems that don't need to modify data.

Read and write permissions without structure modification:

GRANT SELECT, INSERT, UPDATE, DELETE ON blog_db.* TO 'cms_user'@'localhost';

Typical permission set for applications: work with data but don't touch table structure.

Access to specific table:

GRANT SELECT, UPDATE ON shop_db.orders TO 'order_manager'@'localhost';

Even more precise control — permissions only for the orders table in the shop_db database.

Access to all databases on server:

GRANT ALL PRIVILEGES ON *.* TO 'superuser'@'localhost';

The *.* construction means "all databases, all tables". This is almost equivalent to root access.

Permission to transfer your privileges:

GRANT SELECT, INSERT ON project_db.* TO 'team_lead'@'localhost' WITH GRANT OPTION;

The WITH GRANT OPTION allows the user to transfer their permissions to other users.

Applying Changes

After assigning permissions in older MySQL versions, you needed to execute:

FLUSH PRIVILEGES;

This command forces MySQL to re-read the privilege tables. In modern versions (MySQL 5.7+), GRANT and REVOKE commands automatically apply changes, but executing FLUSH PRIVILEGES is still safe and often recommended for assurance.

Checking User Permissions

To see what permissions are assigned to a specific user:

SHOW GRANTS FOR 'username'@'host';

Example:

SHOW GRANTS FOR 'webapp'@'localhost';

The output will show all GRANT commands that were executed for this user.

Changing User Password

In MySQL 5.7 and newer:

ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

In older versions:

SET PASSWORD FOR 'username'@'host' = PASSWORD('new_password');

Example:

ALTER USER 'webapp'@'localhost' IDENTIFIED BY 'NewSecurePass999!';

Revoking Permissions — The REVOKE Command

If you need to remove specific privileges from a user, use REVOKE:

REVOKE privilege ON database.table FROM 'user'@'host';

Examples:

Remove delete permission:

REVOKE DELETE ON shop_db.* FROM 'webapp'@'localhost';

Revoke all permissions on database:

REVOKE ALL PRIVILEGES ON blog_db.* FROM 'cms_user'@'localhost';

Remove ability to transfer permissions:

REVOKE GRANT OPTION ON project_db.* FROM 'team_lead'@'localhost';

After revoking permissions, it's also recommended to execute FLUSH PRIVILEGES;.

Deleting a User

When a user is no longer needed, you can delete them:

DROP USER 'username'@'host';

Example:

DROP USER 'old_app'@'localhost';

⚠️ Important: when deleting a user, all their permissions are automatically revoked. If you try to delete a non-existent user, MySQL will throw an error.

Common Usage Scenarios

User for Web Application

CREATE USER 'wordpress'@'localhost' IDENTIFIED BY 'wp_strong_pass_2024';
GRANT SELECT, INSERT, UPDATE, DELETE ON wordpress_db.* TO 'wordpress'@'localhost';

FLUSH PRIVILEGES;

The application gets permissions for basic data operations but cannot modify database structure.

User for Backup Operations

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'backup_secure_pwd';
GRANT SELECT, LOCK TABLES, SHOW VIEW ON *.* TO 'backup_user'@'localhost';

FLUSH PRIVILEGES;

This user can read all databases and lock tables to create consistent backups.

Administrator with Limited Permissions

CREATE USER 'db_admin'@'192.168.1.%' IDENTIFIED BY 'admin_pass_123';
GRANT ALL PRIVILEGES ON app_*.* TO 'db_admin'@'192.168.1.%';

FLUSH PRIVILEGES;

The administrator gets full access to all databases whose names begin with app_.

Read-Only User for Logs

CREATE USER 'log_reader'@'%' IDENTIFIED BY 'logs_read_only';
GRANT SELECT ON logs_db.access_logs TO 'log_reader'@'%';
GRANT SELECT ON logs_db.error_logs TO 'log_reader'@'%';

FLUSH PRIVILEGES;

A monitoring system gets access only to log tables and cannot modify anything.

Working with Existing Users

Viewing All Users

SELECT User, Host FROM mysql.user;

This command displays a list of all users in the system and the hosts from which they can connect.

Renaming a User

RENAME USER 'old_name'@'localhost' TO 'new_name'@'localhost';

All user permissions are preserved during renaming.

Checking Current User

If you're already connected to MySQL and want to know which user you're working as:

SELECT USER();

Security When Working with Users

• Use strong passwords. Minimum 12 characters, combination of letters, numbers, and special characters. MySQL doesn't check password complexity — that's your responsibility.
• Restrict access by hosts. Don't use '%' unnecessarily. If the application works locally — specify localhost. If remotely — a specific IP.
• Follow the principle of least privilege. Don't grant ALL PRIVILEGES if SELECT, INSERT, UPDATE is sufficient. Don't grant permissions on all databases (*.*) if only one is needed.
• Regularly review the user list. Remove unused accounts. Old credentials are a potential security breach.
• Don't use root for applications. Root should only be used for administration, never for connecting web applications or scripts.
• Enable SSL for remote connections. If users connect over the network, configure encryption:

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

Common Errors and Solutions

Error: ERROR 1396 (HY000): Operation CREATE USER failed for 'user'@'host'

Cause: user already exists.

Solution: check existing users with SELECT User, Host FROM mysql.user; and either delete the old user or use a different name.

Error: ERROR 1045 (28000): Access denied for user 'webapp'@'localhost'

Cause: incorrect password or user doesn't have permission to connect from this host.

Solution: verify the password. Make sure the user was created for the host from which the connection is being made (localhost and 127.0.0.1 are different hosts for MySQL).

Error: application cannot work with database after user creation.

Cause: forgot to assign permissions or execute FLUSH PRIVILEGES.

Solution: check permissions with SHOW GRANTS FOR 'user'@'host'; and assign missing privileges.

Error: ERROR 1044 (42000): Access denied for user 'user'@'host' to database 'db_name'

Cause: user doesn't have permissions for the specified database.

Solution: assign permissions with the GRANT command.

Difference Between localhost and 127.0.0.1

For MySQL, these are different things:

• 'user'@'localhost' — connection via Unix socket (faster, more secure)
• 'user'@'127.0.0.1' — connection via TCP/IP to local address

If you create a user for localhost but the application tries to connect to 127.0.0.1, MySQL will deny access. In such cases, create two users or use %.

Conclusion

Managing users and permissions in MySQL is a fundamental skill for any administrator. Proper access configuration protects data from unauthorized use and minimizes the consequences of potential compromises.

Core principles: give users only the permissions they actually need, restrict access by hosts, use strong passwords, and regularly review the list of active accounts. These simple rules will ensure basic security for your database.