SQL Academy · 课时

列级权限

Q: 「列级权限」这节课中我会学到什么？

隐藏敏感列 你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

隐藏敏感列

列级权限 是 CoddyKit 上的免费 SQL Academy 课时。这是第 3 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 SQL Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Academy 课程共包含 4 节课。

列级权限为何重要

并非每位用户都应查看表中的每一列。薪资列、密码哈希或信用卡号可能与用户名或电子邮件等完全公开的数据位于同一张表中。

列级权限让您可以授予对特定列的访问权限，而不是授予整个表的访问权限，从而将敏感数据对没有业务理由查看它们的用户隐藏起来。

整张表上的 GRANT

默认情况下，GRANT SELECT ON table 允许某个角色读取所有列。这对于公开数据没有问题，但当表中同时包含敏感列和非敏感列时，就会带来问题。

下面的查询向 analyst 角色授予对 employees 表的完整读取权限，包括薪资和 SSN。

GRANT SELECT ON employees TO analyst;

列级 GRANT 语法

PostgreSQL（以及标准 SQL）允许您在 GRANT 语句中列出特定的列名。语法如下：

GRANT privilege (col1, col2) ON table TO role;

下面的示例授予 analyst 角色读取 id、name 和 department 的权限，但不包括 salary 或 ssn。

GRANT SELECT (id, name, department) ON employees TO analyst;

验证列级权限

您可以通过查询 information_schema.column_privileges 视图，在 PostgreSQL 中检查列级权限。该视图会列出哪个被授权者对哪个列拥有哪种权限。

SELECT grantee, table_name, column_name, privilege_type
FROM information_schema.column_privileges
WHERE table_name = 'employees'
ORDER BY grantee, column_name;

缺少正确列的权限时会发生什么

如果某个角色尝试读取未获授权访问的列，数据库会返回权限被拒绝错误。只有引用获准访问的列才会成功。

假设已向 analyst 授予 id、name 和 department 的权限，下面的第一个查询会失败，第二个查询会成功。

-- This will fail for analyst (no permission on salary):
-- SELECT id, name, salary FROM employees;

-- This succeeds:
SELECT id, name, department FROM employees;

列级 UPDATE 权限

列级限制同样适用于 UPDATE。您可以允许某个角色只更新特定列，例如允许服务台角色更新用户的 status，但不能更改其 email 或 password_hash。

GRANT UPDATE (status) ON users TO helpdesk;

-- helpdesk can now run:
UPDATE users SET status = 'suspended' WHERE id = 42;

使用视图隐藏列

另一种常见方法是创建一个只公开安全列的视图，然后向该视图授予访问权限，而不是向基础表授予权限。这种方法适用于所有数据库，而不仅仅是支持列级 GRANT 的数据库。

CREATE VIEW public_employees AS
SELECT id, name, department, hire_date
FROM employees;

GRANT SELECT ON public_employees TO analyst;

撤销列级访问权限

与 GRANT 一样，您可以使用带有列列表的 REVOKE 来移除对特定列的访问权限。如果某个角色拥有宽泛的表级访问权限，您可能需要先完全撤销该权限，再授予受限的列级访问权限。

-- Remove all SELECT on the table first
REVOKE SELECT ON employees FROM analyst;

-- Then grant only safe columns
GRANT SELECT (id, name, department) ON employees TO analyst;

列级权限与行级安全的结合

列级权限和行级安全（RLS）相辅相成。RLS 控制用户可以查看哪些行；列级权限控制这些行中的哪些列可见。

二者结合后，可形成强大的二维访问控制：限制行集合，并隐藏每一行中的敏感字段。

-- RLS policy: employees can see only their own row
CREATE POLICY own_row ON employees
  FOR SELECT
  USING (user_id = current_user_id());

-- Column grant: hide salary even for own row
GRANT SELECT (id, name, department) ON employees TO employee_role;

使用 SECURITY DEFINER 函数

当您需要超越简单列列表的细粒度逻辑时，SECURITY DEFINER 函数可以充当桥梁。该函数以其所有者的权限运行（所有者拥有完整的列访问权限），并且只返回它选择公开的内容，无论调用者是谁。

CREATE OR REPLACE FUNCTION get_employee_summary(emp_id INT)
RETURNS TABLE(id INT, name TEXT, department TEXT)
SECURITY DEFINER
LANGUAGE sql AS
$$
  SELECT id, name, department
  FROM employees
  WHERE id = emp_id;
$$;

GRANT EXECUTE ON FUNCTION get_employee_summary(INT) TO analyst;

实用设计：分层列安全

一个稳健的生产环境模式包含三层：

表所有权 — 只有应用服务账户拥有基础表。
视图或列级 GRANT — 读取角色只能访问非敏感列。
审计列 — 通过触发器记录哪个用户在什么时间访问了敏感数据。

这样，即使某个角色在某一层意外获得了过多权限，其他层仍会保护数据。

-- Layer 1: revoke public access
REVOKE ALL ON employees FROM PUBLIC;

-- Layer 2: expose safe columns via view
CREATE VIEW employee_public AS
SELECT id, name, department, hire_date FROM employees;

GRANT SELECT ON employee_public TO reporting_role;

-- Layer 3: audit trigger logs sensitive field reads (pseudocode)
-- CREATE TRIGGER audit_salary AFTER SELECT ON employees ...

快速检查

哪条 SQL 语句可以正确授予 hr_viewer 角色读取 employees 表中仅 name 和 department 列的权限？

回顾：列级权限

列级权限让您可以限制对单个字段的访问，而不是限制整个表的访问，从而将薪资、社会保障号码和密码哈希值等敏感数据对未获授权的角色隐藏起来。

要点：

使用 GRANT SELECT (col1, col2) ON table TO role 限制可读取的列。
使用带有列列表的 REVOKE 移除对特定列的访问权限。
视图是一种可移植的替代方案，适用于所有数据库。
将列级权限与 RLS 结合，实现二维访问控制。
SECURITY DEFINER 函数通过额外逻辑提供程序化的列级筛选。

只要持续一致地应用，列级安全就是在数据层落实最小权限原则的最简单、最有效的方法之一。

常见问题解答

「列级权限」课时是免费的吗？

是的 — 「列级权限」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Academy 课程的其余内容，请升级到 CoddyKit PRO。 SQL Academy 课程共包含 4 节课。

「列级权限」这节课中我会学到什么？

隐藏敏感列你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 SQL Academy 需要有经验吗？

无需任何先前经验。CoddyKit 上的 SQL Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 3 节课，共 4 节。

「列级权限」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 SQL Academy 课中编写并运行代码吗？

能。每节 SQL Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 SQL Academy