MySQL online course

Create MySQL Users and Grant Database Privileges

Learn how to create MySQL users, restrict connections by host, grant least-privilege database access, and verify account permissions.

Why create separate MySQL users?

A MySQL account is an identity used to authenticate a client and authorize actions on a MySQL server. The highly privileged root user is intended for administration, not routine application or personal work.

Using separate accounts limits the damage caused by a stolen password, an application vulnerability, or an accidental command. It also makes auditing clearer because each application, service, developer, or administrative role can have its own identity.

Follow the principle of least privilege: give an account only the permissions required for its task. For example, a reporting account may need SELECT but not DELETE, while an application that writes orders may need SELECT, INSERT, and perhaps UPDATE on specific objects.

Before continuing, review how to access MySQL with the command-line client and the difference between databases and tables in MySQL database terminology.

Understand MySQL account identity

MySQL identifies an account by both a user name and a host component. The usual notation is 'username'@'hostname'.

  • 'testuser'@'localhost' identifies the user testuser connecting in the local connection context.
  • 'testuser'@'app.example.internal' is a different account identity, even though the user names are the same.
  • 'testuser'@'%' uses a wildcard host and may permit connections from many client hosts.

localhost represents a local client connection context. An account created for localhost is not automatically the account used when the client connects from another machine. Similarly named accounts with different host values can have different passwords and different privileges.

Prefer a specific client host or a narrowly controlled host pattern. A host value such as '%' is convenient for testing but broadens the possible connection sources. If remote access is necessary, combine specific host restrictions with network controls and TLS.

Create a MySQL user

Use CREATE USER to create an account and configure password authentication. Run account-management statements from an appropriately authorized administrative account.

CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'replace-with-a-strong-password';

This creates a local account named testuser. It does not automatically grant access to application databases, tables, or other objects.

Choose a strong, unique password. Do not put real passwords in source code, shared command logs, documentation, or shell history. The example contains a placeholder only. When possible, use a password prompt or an approved secret-management system. Authentication plugins and password-management policies vary by MySQL version and deployment, so check the documentation for the installed version when your environment requires a specific plugin or policy.

Account creation is separate from database creation and table creation. Use CREATE DATABASE to create a database and CREATE TABLE to create tables; use CREATE USER to create an account.

Grant privileges to an account

A privilege is permission to perform an action, such as reading, inserting, changing, or deleting data. Use GRANT to assign privileges to an existing account.

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

This gives testuser access to all objects in testdb, but not to every database on the server. It is useful for a controlled learning environment or a dedicated test database. In production, it may be broader than an application needs.

A read-only reporting account demonstrates a narrower grant:

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

You can restrict permissions to one table and selected operations:

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

Common privileges include SELECT for reading, INSERT for adding rows, UPDATE for changing rows, DELETE for removing rows, CREATE for creating objects, ALTER for changing object definitions, DROP for removing objects, INDEX for index operations, and EXECUTE for executing routines. ALL PRIVILEGES grants a broad set of privileges at the selected scope; it should not be the default choice when a smaller set is sufficient.

The account issuing GRANT must have suitable authority, such as the relevant privilege together with GRANT OPTION, or appropriate administrative permissions. An account cannot grant permissions it is not authorized to delegate.

Understand privilege scope

The ON database.object part of a grant determines where the privilege applies. Select the narrowest scope that meets the account's task.

GRANT target — Scope — Meaning — Typical use — Security consideration

*.* — Global — All databases and objects — Server-wide administration — Extremely broad; avoid for applications.

database_name.* — Database — Every object in one database — A dedicated application or test database — Broader than necessary if the account needs only a few tables.

database_name.table_name — Object — One specific table — A service limited to one table — Usually narrower and easier to audit.

Common privilege reference

Privilege — Allows — Typical account type

SELECT — Reading rows — Reporting or read-only accounts.

INSERT — Adding rows — Data-ingestion or application accounts.

UPDATE — Changing existing rows — Application accounts that modify records.

DELETE — Removing rows — Accounts that require controlled deletion.

CREATE — Creating database objects — Schema-management accounts.

ALTER — Changing object definitions — Migration or schema-management accounts.

DROP — Removing database objects — Administrative or development accounts.

EXECUTE — Executing stored routines — Accounts that use approved procedures or functions.

ALL PRIVILEGES — A broad set of privileges at the selected scope — Dedicated test accounts or carefully controlled administration.

Verify the account and its access

First test authentication with the MySQL client. The -p option causes an interactive password prompt, which is preferable to placing a password directly in the command line.

mysql -u testuser -p

After connecting, inspect the databases visible to this account:

SHOW DATABASES;

Select the intended database and inspect its tables:

USE testdb;
SHOW TABLES;

Then perform a permitted action, such as a carefully chosen SELECT. To confirm a restriction, test an operation the account should not have; it should fail with an access-denied error. System schemas may be visible or only partially accessible depending on the MySQL version and the account's privileges, so visibility alone is not a complete permissions test.

An authorized administrator can inspect the grants for the exact account identity:

SHOW GRANTS FOR 'testuser'@'localhost';

Check — Command or action — Expected result

Account can authenticate — mysql -u testuser -p — The password prompt accepts the credentials and a session opens.

Assigned grants are correct — SHOW GRANTS FOR 'testuser'@'localhost'; — The output shows the intended scopes and privileges.

Permitted database is visible — SHOW DATABASES; — The expected database is visible when the account has suitable access.

Allowed operation succeeds — Run the intended SELECT, INSERT, or other operation — The required action completes.

Disallowed operation is denied — Attempt an operation outside the grant — MySQL rejects the action.

Change or remove unnecessary privileges

Review grants periodically. For example, this statement removes write-related privileges from an account while leaving any other granted privileges unchanged:

REVOKE INSERT, UPDATE, DELETE ON testdb.*
FROM 'testuser'@'localhost';

Rotate credentials according to your operational policy and remove accounts that are no longer needed. Avoid sharing accounts when individual auditing and accountability matter. Dedicated, minimally privileged application accounts are easier to monitor than a shared administrative account.

SQL account-management statements such as CREATE USER, GRANT, and REVOKE take effect without restarting MySQL. You normally do not need to run FLUSH PRIVILEGES after using these statements.

Version-aware account syntax

Older examples sometimes combine password setup with a GRANT statement. That behavior is deprecated, unsupported, or version-dependent in modern MySQL environments. Use the clearer current workflow:

  1. Create the account with CREATE USER.
  2. Assign permissions separately with GRANT.
  3. Verify the exact account identity with SHOW GRANTS.

Check the documentation for the installed MySQL version when authentication plugins, password expiration, account locking, or other account policies are involved.

Troubleshoot common problems

Access denied when logging in

  • Confirm the user name, password, and host portion of the account.
  • Check whether the account was created only for localhost while the connection originates from another machine.
  • Have an authorized administrator inspect the exact account with SHOW GRANTS and confirm the client host.
  • Consider whether the server's authentication plugin or policy is compatible with the client.

Login succeeds, but the expected database is missing

  • No privilege may have been granted on that database.
  • The grant may belong to a same-named account with a different host value.
  • The account may have object privileges that do not make the database appear in the current SHOW DATABASES result.
  • Run SHOW GRANTS for the exact identity, grant the minimum required scope, reconnect, and test again.

GRANT fails with a permission error

  • The current account may lack authority to grant the requested privilege.
  • The request may exceed the privileges held by the granting account.
  • The current account may lack GRANT OPTION where it is required.
  • Use a suitably authorized administrative account or reduce the requested scope.

A legacy GRANT statement behaves differently

Create the account first with CREATE USER, then issue a separate GRANT. Consult the documentation for the installed version instead of relying on older GRANT ... IDENTIFIED BY examples.

The account has more access than intended

  • Audit the account with SHOW GRANTS.
  • Look for broad scopes such as *.*, ALL PRIVILEGES, or a wildcard host such as '%'.
  • Revoke unnecessary permissions and replace broad database, object, or host scopes with narrower ones.

Recommended workflow

  1. Choose a dedicated account name for the application, service, developer, or role.
  2. Choose the most specific allowed client host, preferably avoiding unrestricted remote access.
  3. Create the account with CREATE USER.
  4. Grant only the required privileges at the narrowest practical scope.
  5. Connect with mysql -u username -p.
  6. Run SHOW DATABASES, select the intended database, and test permitted and denied operations.
  7. Use TLS and network controls for necessary remote connections.
  8. Rotate credentials, review grants, and remove unused accounts.