MySQL权限查询：如何筛选具备ALTER、CREATE等权限的用户

Hey folks! Let's walk through how to solve these two common MySQL permission lookup scenarios clearly and effectively.

If you're hunting for users who either hold the ALTER permission (globally) or have all privileges on the MySQL server, you can directly query the mysql.user system table—this is where global-level permissions are stored.

Here's the query to pull those users:

SELECT user, host, 
       CASE WHEN alter_priv = 'Y' THEN 'Has ALTER privilege' END AS alter_status,
       CASE WHEN all_privileges = 'Y' THEN 'Has all privileges' END AS all_priv_status
FROM mysql.user
WHERE alter_priv = 'Y' OR all_privileges = 'Y';

Quick Notes: #

• The user and host columns together uniquely identify a MySQL user (since a single username can be tied to different hosts).
• alter_priv = 'Y' checks if the user has global ALTER access.
• all_privileges = 'Y' flags users granted the ALL PRIVILEGES permission (which includes every possible privilege).
• For a more human-readable breakdown of a specific user's grants, you can run SHOW GRANTS FOR 'username'@'host'; once you have their user/host details.

When dealing with a large user base and needing to filter by specific permissions like CREATE or ALTER, keep in mind that permissions can exist at multiple levels: global, database-specific, table-specific, or even column-specific.

To cover all these levels, you'll need to query multiple system tables: mysql.user (global), mysql.db (database-level), and mysql.tables_priv (table-level).

Here's a comprehensive query to find users with either CREATE or ALTER permission at any level:

-- Get users with global CREATE/ALTER permissions
SELECT DISTINCT user, host, 'Global' AS permission_level
FROM mysql.user
WHERE create_priv = 'Y' OR alter_priv = 'Y'

UNION

-- Get users with database-level CREATE/ALTER permissions
SELECT DISTINCT user, host, CONCAT('Database: ', db) AS permission_level
FROM mysql.db
WHERE create_priv = 'Y' OR alter_priv = 'Y'

UNION

-- Get users with table-level CREATE/ALTER permissions
SELECT DISTINCT user, host, CONCAT('Table: ', db, '.', table_name) AS permission_level
FROM mysql.tables_priv
WHERE priv_type IN ('CREATE', 'ALTER');

Quick Notes: #

• UNION combines results from different tables while removing duplicates (so a user with both global and database-level permissions won't appear multiple times).
• The permission_level column tells you exactly where the permission applies—global scope, a specific database, or a specific table.
• If you only care about global permissions, you can skip the UNION sections for database and table levels.

Just remember to run these queries with a user that has access to the mysql database (like the root user), since system tables are restricted to privileged accounts.

内容的提问来源于stack exchange，提问作者Saleem