SQL Server中如何查询用户有权限访问的所有列数据？

核心思路是借助数据库内置的系统元数据视图，先筛选出目标用户对指定表拥有SELECT权限的列，再拼接成可执行的SELECT语句。以下是主流数据库的具体实现：

1. PostgreSQL #

通过information_schema和权限检查函数has_column_privilege获取列列表：

-- 替换占位符：目标用户名、表所在模式、目标表名
SELECT string_agg(column_name, ', ') AS select_columns
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'your_table'
  AND has_column_privilege('target_user', table_schema || '.' || table_name || '.' || column_name, 'select');

执行后会得到有权限的列名拼接字符串，比如col1, col2, col3，直接拼成SELECT col1, col2, col3 FROM public.your_table;即可执行。

如果要自动生成并执行，可写一个PL/pgSQL函数：

CREATE OR REPLACE FUNCTION get_selectable_columns(p_user text, p_schema text, p_table text)
RETURNS void AS $$
DECLARE
  cols text;
BEGIN
  SELECT string_agg(column_name, ', ') INTO cols
  FROM information_schema.columns
  WHERE table_schema = p_schema
    AND table_name = p_table
    AND has_column_privilege(p_user, p_schema || '.' || p_table || '.' || column_name, 'select');
  
  EXECUTE format('SELECT %s FROM %I.%I', cols, p_schema, p_table);
END;
$$ LANGUAGE plpgsql;

-- 调用示例
SELECT get_selectable_columns('target_user', 'public', 'your_table');

2. MySQL #

利用information_schema.COLUMN_PRIVILEGES视图查询列级权限：

-- 替换占位符：用户名（格式为'user@host'）、数据库名、目标表名
SELECT GROUP_CONCAT(column_name SEPARATOR ', ') AS select_columns
FROM information_schema.COLUMN_PRIVILEGES
WHERE GRANTEE = 'target_user@localhost'
  AND TABLE_SCHEMA = 'your_db'
  AND TABLE_NAME = 'your_table'
  AND PRIVILEGE_TYPE = 'Select';

拿到列名后拼接成SELECT col1, col2 FROM your_db.your_table;执行即可。也可以用存储过程动态执行：

DELIMITER //
CREATE PROCEDURE select_allowed_columns(IN p_user VARCHAR(100), IN p_db VARCHAR(100), IN p_table VARCHAR(100))
BEGIN
  DECLARE cols TEXT;
  SELECT GROUP_CONCAT(column_name SEPARATOR ', ') INTO cols
  FROM information_schema.COLUMN_PRIVILEGES
  WHERE GRANTEE = p_user
    AND TABLE_SCHEMA = p_db
    AND TABLE_NAME = p_table
    AND PRIVILEGE_TYPE = 'Select';
  
  SET @sql = CONCAT('SELECT ', cols, ' FROM ', p_db, '.', p_table);
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

-- 调用示例
CALL select_allowed_columns('target_user@localhost', 'your_db', 'your_table');

3. SQL Server #

通过sys.database_permissions和sys.columns等系统视图关联查询：

-- 替换占位符：目标用户名、模式名、目标表名
SELECT STRING_AGG(c.name, ', ') AS select_columns
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.database_permissions dp ON c.object_id = dp.major_id AND c.column_id = dp.minor_id
JOIN sys.database_principals dpri ON dp.grantee_principal_id = dpri.principal_id
WHERE s.name = 'dbo'
  AND t.name = 'your_table'
  AND dpri.name = 'target_user'
  AND dp.permission_name = 'SELECT'
  AND dp.state IN ('G', 'W'); -- G=直接授予，W=授予并允许转授

拼接列名后生成SELECT语句执行，也可以用动态SQL一键执行：

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(c.name, ', ')
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.database_permissions dp ON c.object_id = dp.major_id AND c.column_id = dp.minor_id
JOIN sys.database_principals dpri ON dp.grantee_principal_id = dpri.principal_id
WHERE s.name = 'dbo'
  AND t.name = 'your_table'
  AND dpri.name = 'target_user'
  AND dp.permission_name = 'SELECT'
  AND dp.state IN ('G', 'W');

SET @sql = N'SELECT ' + @cols + N' FROM dbo.your_table';
EXEC sp_executesql @sql;

注意事项 #

• 执行这些查询的用户需要拥有系统元数据视图的SELECT权限（比如information_schema或sys系列视图）。
• 如果用户对整个表有SELECT权限，系统视图会返回所有列，效果等同于SELECT *且不会触发权限错误。
• 不同数据库的系统视图结构存在差异，需根据实际使用的数据库调整语句。

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