How to manage MySQL user permissions and privileges

MySQL privileges control what a user can do and where they can do it. A user with ALL PRIVILEGES on a database can create tables, drop them, read every row, and modify any data. A user with only SELECT can read but not change anything. Getting this right matters for security, especially on shared hosting or any environment where multiple applications share the same MySQL server. This guide covers how to view, grant, revoke, and audit privileges with practical examples for every common scenario.

If you need to create users first or are not sure which users exist on your server, see How to show and manage MySQL users. For general MySQL database management on Hostney, see the MySQL databases knowledge base.

How MySQL privileges work#

MySQL privileges are assigned at four levels, from broadest to most specific:

1. Global – applies to all databases on the server ( *.* )
2. Database – applies to all tables in a specific database ( wordpress_db.* )
3. Table – applies to a specific table ( wordpress_db.wp_options )
4. Column – applies to specific columns in a table (rarely used in practice)

When a user runs a query, MySQL checks privileges from most specific to least specific. A user denied SELECT globally but granted SELECT on a specific database can still read that database. The most specific grant wins.

For most small business and WordPress hosting scenarios, you work at the database level. Global privileges should be reserved for admin accounts that genuinely need server-wide access.

Viewing current privileges#

Show grants for a specific user

SHOW GRANTS FOR 'wp_user'@'localhost';

Output:

+----------------------------------------------------------------+
| Grants for wp_user@localhost                                   |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `wp_user`@`localhost`                    |
| GRANT ALL PRIVILEGES ON `wordpress_db`.* TO `wp_user`@`localhost` |
+----------------------------------------------------------------+

GRANT USAGE ON *.* appears for every user. It means the user can connect to MySQL but has no global privileges. This line is not a grant of any real access, it just confirms the account exists.

Show grants for the current user

SHOW GRANTS;

List all database-level grants across all users

SELECT grantee, table_schema, privilege_type
FROM information_schema.SCHEMA_PRIVILEGES
ORDER BY grantee, table_schema;

This is useful for auditing who has access to what. On a server with many databases, this query gives you a complete picture without checking each user individually.

List all global-level grants

SELECT grantee, privilege_type
FROM information_schema.USER_PRIVILEGES
WHERE grantee NOT LIKE "'mysql.%'"
ORDER BY grantee;

Any user that shows up here with privileges beyond USAGE has server-wide access. On a properly configured hosting server, only the root account and backup accounts should appear.

Granting privileges#

The GRANT statement assigns privileges to a user. The basic syntax is:

GRANT privilege_list ON scope TO 'username'@'host';

Grant all privileges on a specific database

GRANT ALL PRIVILEGES ON wordpress_db.* TO 'wp_user'@'localhost';

This gives the user full control over every table in wordpress_db , including the ability to create and drop tables. This is the standard grant for a WordPress or WooCommerce database user because WordPress needs CREATE TABLE for plugin activations and schema updates.

Grant read-only access to a database

GRANT SELECT ON analytics_db.* TO 'reporting_user'@'localhost';

The user can run SELECT queries against any table in analytics_db but cannot insert, update, or delete rows. Use this for reporting tools, dashboard integrations, or any application that only needs to read data.

Grant common application privileges without full access

GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_user'@'localhost';

This covers normal CRUD operations without granting structural privileges like CREATE , DROP , ALTER , or INDEX . Use this when the application does not need to modify the database schema after initial setup. This is more restrictive than ALL PRIVILEGES and follows the principle of least privilege.

Grant privileges on a specific table

GRANT SELECT, INSERT ON wordpress_db.wp_posts TO 'limited_user'@'localhost';

Table-level grants are granular but hard to maintain. Every new table requires a separate grant statement. In practice, database-level grants are more manageable for most use cases.

Grant with the ability to grant to others

GRANT ALL PRIVILEGES ON wordpress_db.* TO 'admin_user'@'localhost' WITH GRANT OPTION;

WITH GRANT OPTION lets this user assign their own privileges to other users. Only use this for admin accounts. A user with GRANT OPTION can effectively create new admin-level users on the database, so this should not be handed out casually.

Grant global privileges

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

This gives the user full access to every database on the server. This is appropriate for DBA accounts used for backups, migrations, and server administration. Do not grant global privileges to application users. If a single compromised WordPress plugin can access every database on the server, your blast radius just went from one site to all of them.

Common privilege types explained#

WordPress requires at minimum: SELECT , INSERT , UPDATE , DELETE , CREATE , ALTER , DROP , INDEX . In practice, most people grant ALL PRIVILEGES on the WordPress database because plugin updates and migrations may need additional privileges like CREATE TEMPORARY TABLES or LOCK TABLES . The security trade-off is acceptable because the grant is scoped to a single database.

Revoking privileges#

The REVOKE statement removes privileges. The syntax mirrors GRANT :

REVOKE privilege_list ON scope FROM 'username'@'host';

Revoke a specific privilege

REVOKE DROP ON wordpress_db.* FROM 'wp_user'@'localhost';

This removes only the DROP privilege. The user keeps everything else they were granted. This is useful if you want to prevent an application from accidentally dropping tables while keeping other access intact.

Revoke all privileges on a database

REVOKE ALL PRIVILEGES ON wordpress_db.* FROM 'wp_user'@'localhost';

The user loses all access to wordpress_db but their account still exists and they can still connect to MySQL. They just cannot do anything with that database.

Revoke all privileges everywhere

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'old_user'@'localhost';

This strips the user down to USAGE only. They can connect but have no access to any database. This is useful when you want to remove access without deleting the account, for example if you need to preserve audit trail entries tied to the username.

Verify after revoking

Always verify that the revoke worked:

SHOW GRANTS FOR 'wp_user'@'localhost';

If only GRANT USAGE ON *.* remains, the user has no real access.

FLUSH PRIVILEGES: when you need it and when you do not#

FLUSH PRIVILEGES reloads the grant tables from disk. There is a common misconception that you need to run it after every GRANT or REVOKE statement. You do not.

You do NOT need FLUSH PRIVILEGES after:

• GRANT  statements
• REVOKE  statements
• CREATE USER
• DROP USER
• ALTER USER
• SET PASSWORD

All of these statements automatically update the in-memory privilege cache. Running FLUSH PRIVILEGES after them is harmless but unnecessary.

You DO need FLUSH PRIVILEGES after:

• Directly modifying the  mysql.user ,  mysql.db , or other grant tables with  INSERT ,  UPDATE , or  DELETE  statements

If you are managing users through GRANT / REVOKE (which you should be), you will rarely if ever need FLUSH PRIVILEGES . It is a leftover from older workflows where DBAs edited grant tables directly.

Dropping users#

When a user is no longer needed, remove them entirely:

DROP USER 'old_user'@'localhost';

This removes the account and all associated privileges. The user cannot connect after this.

Drop if exists

DROP USER IF EXISTS 'old_user'@'localhost';

Prevents errors in cleanup scripts if the user was already removed.

Check before dropping

Before dropping a user, check what they have access to:

SHOW GRANTS FOR 'old_user'@'localhost';

If the user has GRANT OPTION , other users may have received privileges from them. Dropping the user does not automatically revoke privileges that were granted to others using that user’s GRANT OPTION . Those downstream grants persist. Audit them before removing the account.

Practical scenarios#

WordPress setup with least privilege

For a new WordPress installation:

CREATE DATABASE wordpress_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'wp_user'@'localhost' IDENTIFIED BY 'strong_password_here';
GRANT ALL PRIVILEGES ON wordpress_db.* TO 'wp_user'@'localhost';

This gives WordPress full control over its own database and nothing else. The user cannot see or touch other databases on the server. For a step-by-step database creation guide, see How to create a database in MySQL.

Read-only user for reporting

CREATE USER 'report_user'@'localhost' IDENTIFIED BY 'strong_password_here';
GRANT SELECT ON production_db.* TO 'report_user'@'localhost';

Reporting dashboards and analytics tools should never have write access to production data. If the reporting tool is compromised or has a SQL injection vulnerability, the worst it can do is read data, not modify or delete it.

Backup user

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

This is the minimum privilege set for mysqldump to create consistent backups. SELECT reads the data, LOCK TABLES ensures consistency during the dump, and SHOW VIEW , EVENT , and TRIGGER allow dumping those objects. The user cannot modify any data.

For mysqlpump or xtrabackup , different privileges may be needed. Check the tool’s documentation for the exact requirements.

Removing access for a decommissioned application

-- Check what the user has
SHOW GRANTS FOR 'old_app'@'localhost';

-- Revoke everything
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'old_app'@'localhost';

-- Verify
SHOW GRANTS FOR 'old_app'@'localhost';

-- Drop the user entirely
DROP USER 'old_app'@'localhost';

Do not skip the verification step. If you revoke privileges but do not drop the user, the account still exists and could be re-granted access later by mistake.

Security best practices#

Never grant global privileges to application users. WordPress does not need access to mysql.* or any other database on the server. Scope every application user to its own database with database-level grants.

Use separate users for separate applications. If you run WordPress and a custom app on the same server, each should have its own MySQL user with access only to its own database. If one application is compromised, the other remains isolated.

Avoid using root for application connections. The root account should be used for server administration only, never in an application’s config file. Create a dedicated user for each application with the minimum privileges it needs.

Audit privileges regularly. Run the information_schema.SCHEMA_PRIVILEGES query periodically to check who has access to what. Stale users with active privileges are a common finding on servers that have been running for a few years.

On Hostney, database users are managed through the control panel under Hosting > MySQL Databases. When you create a database user through the panel, permissions are set automatically with appropriate access for WordPress and other applications. The control panel enforces per-account database isolation, so users on one hosting account cannot access databases belonging to another account regardless of MySQL privilege configuration.

Quick reference#

-- View privileges
SHOW GRANTS FOR 'user'@'host';

-- Grant all on a database
GRANT ALL PRIVILEGES ON db_name.* TO 'user'@'host';

-- Grant specific privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON db_name.* TO 'user'@'host';

-- Grant read-only
GRANT SELECT ON db_name.* TO 'user'@'host';

-- Revoke specific privilege
REVOKE DROP ON db_name.* FROM 'user'@'host';

-- Revoke everything
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host';

-- Drop user
DROP USER 'user'@'host';

-- Audit all database-level grants
SELECT grantee, table_schema, privilege_type
FROM information_schema.SCHEMA_PRIVILEGES
ORDER BY grantee, table_schema;