VMware ESXi and vSphere Cluster Management

Create MySQL Users and Grant Database Privileges

Learn to create MySQL users, grant least-privilege access by database or table, verify permissions, and troubleshoot account and connection issues.

Why use separate MySQL users?

A MySQL account is an identity that connects to the server and performs database work. Routine administration, application connections, reporting, and schema changes should use separate accounts instead of the highly privileged root user.

Using root for an application or daily work creates unnecessary risk. A software defect, leaked credential, or accidental statement could affect every database on the server. Separate accounts limit the damage and make activity easier to audit.

The principle of least privilege means giving an account only the permissions and object scope required for its job.

• Authentication answers: “Who is trying to connect?”
• Authorization answers: “What may this authenticated account do?”

CREATE USER configures an account and its authentication details. GRANT controls authorization by assigning privileges.

Understand MySQL account identity

MySQL identifies an account with both a user name and a host part. The notation is 'user'@'host'.

'app_user'@'localhost'
'report_user'@'localhost'
'app_user'@'10.20.30.15'

These are separate account definitions. For example, 'app_user'@'localhost' and 'app_user'@'10.20.30.15' can have different passwords and different privileges.

Common host values and patterns

• localhost limits the account to connections originating on the machine running MySQL. Local client connections commonly use the local socket or local connection mechanisms.
• A specific host, such as '10.20.30.15', restricts connections to that source address.
• A carefully chosen network pattern can allow a defined group of hosts, depending on MySQL host-pattern rules.
• % is a broad wildcard. Avoid it unless access from broadly varying network locations is genuinely required and other controls, such as firewall rules and TLS, are in place.

The host value is only one part of remote connectivity. A remote connection also depends on MySQL network binding, firewall rules, routing, and the organization’s TLS policy.

Create a MySQL user

Run account-management statements from an authorized administrative connection. The basic statement is:

CREATE USER 'app_user'@'localhost'
  IDENTIFIED BY 'replace-with-a-strong-secret';

CREATE USER creates the account and configures password authentication. It does not grant access to databases, tables, or data.

Use a strong, unique secret generated and stored through an approved secret-management process. The example text is a placeholder, not a production password. Do not put real passwords in shell history, command lines, source code, or version control. Exact authentication clauses can vary between MySQL releases and configured authentication plugins, so check the syntax supported by the server when using an authentication method other than the default.

Grant privileges

The general form of a grant is:

GRANT privilege_list
ON object_scope
TO 'user'@'host';

Each part has a specific purpose:

• Privilege list: the actions allowed, such as SELECT or INSERT.
• Object scope: the databases, tables, or other objects affected.
• Target account: the exact MySQL account receiving the privileges.

The administrator issuing GRANT must have sufficient privileges and grant authority. Newly assigned privileges take effect without restarting MySQL.

Common privileges

ALL PRIVILEGES is limited by the scope in the statement, but it can still be unnecessarily broad. Database-scoped ALL PRIVILEGES is different from global ALL PRIVILEGES. Prefer a smaller privilege list when the account’s task does not require everything.

Choose the privilege scope

A database and a schema are commonly used as names for the logical container holding tables and other objects. Narrower scope supports least-privilege design.

Example: one database for an application

CREATE USER 'app_user'@'localhost'
  IDENTIFIED BY 'replace-with-a-strong-secret';

GRANT ALL PRIVILEGES
ON testdb.*
TO 'app_user'@'localhost';

This account can work with objects in testdb, but the grant does not provide the same access to unrelated databases. In production, replace ALL PRIVILEGES with only the operations the application needs.

Example: read-only reporting

CREATE USER 'report_user'@'localhost'
  IDENTIFIED BY 'replace-with-a-strong-secret';

GRANT SELECT
ON testdb.*
TO 'report_user'@'localhost';

The reporting account can read data in testdb, but it cannot insert, update, delete, or change schema objects through this grant.

Example: one table

GRANT SELECT, INSERT
ON testdb.orders
TO 'app_user'@'localhost';

This grant permits reading and adding rows in testdb.orders. It does not imply permissions on other tables.

Design accounts by responsibility

Verify the new account

Connect with the nonadministrative account using the MySQL command-line client. The -p option requests the password interactively, which is safer than putting the password directly in the command.

mysql -u app_user -p

After connecting, inspect visible databases and select the intended database:

SHOW DATABASES;
USE testdb;
SHOW TABLES;

Visibility from SHOW DATABASES depends on the account’s effective privileges. A user may see only databases for which it has relevant access, so the output is not necessarily a complete list of databases on the server.

Test an operation that the account is supposed to perform:

SELECT * FROM orders LIMIT 5;

Do not test only with an administrative account. The purpose of verification is to observe what the intended account can actually do.

Inspect grants as an administrator

SHOW GRANTS FOR 'app_user'@'localhost';

Use the exact user and host components. The result displays the grant statements associated with that account. Review it for accidental global privileges, overly broad database scope, and grants that are no longer needed.

Troubleshoot common problems

Access denied during login

Common causes include an incorrect password, a host mismatch, a different matching account definition, or an unreachable server socket or network interface.

• Confirm the user name and the origin of the connection.
• As an administrator, inspect the exact account definition and its grants.
• Use the correct host-specific account or create a deliberately restricted account for the required source.
• For remote access, check network reachability, MySQL bind settings, firewall rules, and TLS requirements.

Login succeeds but the database is unavailable

The account may have been created without a grant, or the grant may target the wrong database, table, user, or host. It may also have only SELECT while attempting a write or schema change.

SHOW GRANTS FOR 'app_user'@'localhost';

Grant the missing privilege at the smallest suitable scope, then reconnect and test an explicitly permitted operation.

GRANT fails for the administrator

The current account may lack the requested privilege or grant authority, or the statement may contain invalid account or object syntax. Inspect the current administrator’s grants, connect with an authorized administrative account, and validate each database, table, user, and host identifier.

The account can access too much

Review SHOW GRANTS output. Broad grants such as *.*, unnecessary ALL PRIVILEGES, broad host patterns, or additional grants for the same account can produce excessive access.

• Revoke unnecessary privileges.
• Replace global grants with database- or table-level grants.
• Use explicit host restrictions where possible.
• Create separate accounts for distinct roles.

A credential was exposed

Rotate the password promptly. Remove the secret from exposed command lines, scripts, repositories, and configuration where possible, and move credentials to an approved secret-management or protected configuration mechanism. Password rotation should be part of normal account operations, not only an emergency response.

Recommended workflow

1. Define the account’s job: application, reporting, migration, or administration.
2. Choose a specific host component that matches the intended connection source.
3. Create the account with CREATE USER.
4. Grant only the necessary privileges at database or table scope.
5. Connect using the mysql client and a password prompt.
6. Run SHOW DATABASES, USE, and a permitted query.
7. Run SHOW GRANTS as an administrator and check for excess access.
8. Protect and rotate the account secret through approved operational processes.

For a focused walkthrough, return to creating a MySQL user and apply the same account, scope, and verification principles.