嘿，我来帮你梳理下怎么查询单个用户能访问的数据库、表和列权限信息，不同数据库的方法略有不同，我把主流的几种都整理出来了：

首先得确认用户的完整身份（MySQL用户是user@host形式的，用户名+主机地址），如果不确定主机地址，可以先查：

SELECT user, host FROM mysql.user WHERE user = '你的用户名';

• 查看数据库级权限
  用SHOW GRANTS命令最直观，直接输出用户的所有权限：

SHOW GRANTS FOR 'username'@'host地址';

如果想要结构化的结果，可以查系统视图：

SELECT privilege_type, is_grantable
FROM information_schema.user_privileges
WHERE grantee = "'username'@'host地址'";

• 查看表级权限
  查询用户能访问的所有表及对应权限：

SELECT table_schema, table_name, privilege_type
FROM information_schema.tables_priv
WHERE grantee = "'username'@'host地址'";

• 查看列级权限
  如果用户有针对特定列的精细化权限，用这个查询：

SELECT table_schema, table_name, column_name, privilege_type
FROM information_schema.columns_priv
WHERE grantee = "'username'@'host地址'";

PostgreSQL的系统视图很全面，先确认用户存在：

SELECT usename FROM pg_user WHERE usename = '你的用户名';

• 查看数据库级权限
  查询用户能访问的数据库及具体权限：

SELECT datname, privilege_type
FROM pg_database d
JOIN pg_roles r ON r.rolname = 'username'
WHERE has_database_privilege(r.rolname, d.datname, 'CONNECT')
OR has_database_privilege(r.rolname, d.datname, 'CREATE')
OR has_database_privilege(r.rolname, d.datname, 'TEMPORARY');

• 查看表级权限

SELECT table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'username';

• 查看列级权限

SELECT table_schema, table_name, column_name, privilege_type
FROM information_schema.column_privileges
WHERE grantee = 'username';

SQL Server通过系统目录视图来查询权限，先获取用户的principal_id再关联查询：

• 查看数据库级权限

DECLARE @username NVARCHAR(128) = '你的用户名';
DECLARE @principal_id INT = (SELECT principal_id FROM sys.database_principals WHERE name = @username);

SELECT 
    db.name AS database_name,
    dp.permission_name,
    dp.state_desc AS permission_state
FROM sys.database_permissions dp
JOIN sys.databases db ON dp.major_id = db.database_id
WHERE dp.grantee_principal_id = @principal_id;

• 查看表级权限

DECLARE @username NVARCHAR(128) = '你的用户名';
DECLARE @principal_id INT = (SELECT principal_id FROM sys.database_principals WHERE name = @username);

SELECT 
    SCHEMA_NAME(o.schema_id) AS schema_name,
    o.name AS table_name,
    dp.permission_name,
    dp.state_desc AS permission_state
FROM sys.database_permissions dp
JOIN sys.objects o ON dp.major_id = o.object_id
WHERE dp.grantee_principal_id = @principal_id
AND o.type = 'U'; -- U代表用户自定义表

• 查看列级权限

DECLARE @username NVARCHAR(128) = '你的用户名';
DECLARE @principal_id INT = (SELECT principal_id FROM sys.database_principals WHERE name = @username);

SELECT 
    SCHEMA_NAME(o.schema_id) AS schema_name,
    o.name AS table_name,
    c.name AS column_name,
    dp.permission_name,
    dp.state_desc AS permission_state
FROM sys.database_permissions dp
JOIN sys.objects o ON dp.major_id = o.object_id
JOIN sys.columns c ON dp.major_id = c.object_id AND dp.minor_id = c.column_id
WHERE dp.grantee_principal_id = @principal_id;

小提醒 #

执行这些查询的账号需要有足够的权限，比如MySQL需要SELECT权限在mysql库，PostgreSQL需要USAGE权限在目标schema，SQL Server需要VIEW DEFINITION或CONTROL权限。另外，有些权限是继承自角色的（比如PostgreSQL的PUBLIC角色），查询时别忘了考虑这些继承权限。

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