SQL Academy · 课时

角色与权限

Q: 「角色与权限」这节课中我会学到什么？

使用 GRANT 和 REVOKE 控制访问权限 你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

使用 GRANT 和 REVOKE 控制访问权限

第 1 / 4 课13 个步骤

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

什么是角色和权限

在 SQL 中，角色是可以分配给用户的命名权限组。权限控制用户或角色可以对表、视图和函数等数据库对象执行哪些操作。

您无需分别为每个用户授予权限，而是可以创建一个具有所需权限的角色，然后一次性将该角色分配给多个用户。这会让大规模管理访问控制变得容易得多。

创建角色

使用 CREATE ROLE 在 PostgreSQL 中定义新角色。根据配置方式，角色可以代表单个用户，也可以代表一组用户。

角色默认创建时不具备任何权限——您必须明确授予它们访问对象的权限。

CREATE ROLE readonly_user;
CREATE ROLE app_writer;
CREATE ROLE db_admin;

授予表权限

GRANT 语句授予角色或用户对数据库对象执行特定操作的权限。常见的表级权限包括 SELECT、INSERT、UPDATE 和 DELETE。

您可以授予单个权限，也可以用逗号分隔后一次性授予多个权限。

-- Grant SELECT only (read-only role)
GRANT SELECT ON employees TO readonly_user;

-- Grant multiple privileges
GRANT SELECT, INSERT, UPDATE ON orders TO app_writer;

授予所有权限

如果角色需要对某个表拥有完整访问权限，您可以使用 GRANT ALL PRIVILEGES，而不必逐一列出每项权限。这样会授予指定对象上的所有适用权限。

请谨慎使用 ALL PRIVILEGES——只应将其授予确实需要完全控制该对象的角色。

-- Grant full access to db_admin on a table
GRANT ALL PRIVILEGES ON employees TO db_admin;

-- Or the shorthand form
GRANT ALL ON orders TO db_admin;

撤销权限

REVOKE 语句会从角色或用户中移除之前授予的权限。当需求发生变化或角色不再需要某些权限时，可以通过它收紧访问控制。

执行 REVOKE 后，受影响的角色会立即失去在指定对象上的相应权限。

-- Remove UPDATE access from app_writer
REVOKE UPDATE ON orders FROM app_writer;

-- Remove all privileges from a role
REVOKE ALL PRIVILEGES ON employees FROM readonly_user;

将角色分配给用户

在 PostgreSQL 中，用户本身也是角色——具有 LOGIN 属性的角色可以连接到数据库。您可以使用 GRANT role TO user 将组角色分配给登录角色。

分配完成后，用户会继承该角色拥有的所有权限，因此可以轻松地一次性管理多个用户的权限。

-- Create a login user
CREATE ROLE alice WITH LOGIN PASSWORD 'secret123';

-- Assign the readonly role to alice
GRANT readonly_user TO alice;

-- Alice can now SELECT on tables granted to readonly_user

从用户中撤销角色

要移除分配给用户的角色，请使用 REVOKE role FROM user。此后，用户将不再继承该角色带来的权限。

当员工职责发生变化或离开组织时，这一功能非常有用——您可以撤销其角色，而无需修改角色自身的权限定义。

-- Remove the readonly role from alice
REVOKE readonly_user FROM alice;

-- Alice no longer has SELECT on tables via that role

schema 级权限

角色要访问 schema 中的任何对象，必须先拥有该 schema 上的 USAGE 权限。没有这项权限，即使角色在某个特定表上拥有 SELECT 权限，也无法读取数据，因为它无法解析 schema 路径。

请始终在授予对象级权限的同时，授予 schema 上的 USAGE 权限。

-- Allow readonly_user to see inside the public schema
GRANT USAGE ON SCHEMA public TO readonly_user;

-- Then grant table-level privilege
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

未来对象的默认权限

在 schema 中创建新表时，现有角色不会自动获得访问权限。使用 ALTER DEFAULT PRIVILEGES，可以确保由特定角色创建的未来对象会自动允许另一个角色访问。

这样可以避免一个常见问题：新创建的表对应用程序角色不可见，直到有人想起手动运行 GRANT。

-- Future tables created by the current user will be SELECTable by readonly_user
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly_user;

-- Future tables will allow INSERT/UPDATE/DELETE for app_writer
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_writer;

WITH GRANT OPTION

默认情况下，获得权限的角色不能将该权限转授给其他角色。添加 WITH GRANT OPTION 后，接收者也可以将该权限授予其他角色。

请谨慎使用此选项——这意味着该角色会成为访问控制中值得信任的授权节点，过度使用可能会使审计更加复杂。

-- app_writer can now grant SELECT on orders to other roles
GRANT SELECT ON orders TO app_writer WITH GRANT OPTION;

-- app_writer can then do:
-- GRANT SELECT ON orders TO reporting_role;

查看已授予的权限

PostgreSQL 将权限信息存储在系统目录视图中。您可以查询 information_schema.role_table_grants，查看哪些角色被授予了哪些表上的哪些权限。

在 psql 中，反斜杠命令 \dp tablename 也会以紧凑格式显示某个表的访问权限列表。

-- List all table-level grants in the current database
SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'public'
ORDER BY grantee, table_name;

快速检查

检验您对 SQL 中角色和权限的理解。

在交互式课程中回答

回顾：角色和权限

在本课中，您学习了 SQL 如何通过角色和权限管理访问控制：

CREATE ROLE 定义新角色或用户账户
GRANT 将对象上的权限（SELECT、INSERT、UPDATE、DELETE、ALL）分配给角色
REVOKE 移除这些权限
GRANT role TO user 将组角色分配给登录用户，使其继承该角色的权限
角色必须先拥有 schema 上的 USAGE，才能访问其中的对象
ALTER DEFAULT PRIVILEGES 确保未来的对象会自动可访问
WITH GRANT OPTION 允许接收者进一步转授该权限

保持权限最小化并采用基于角色的权限管理，是安全数据库设计的核心原则——只授予必要的权限，并在不再需要时及时撤销。

免费开始

用 AI 导师学习 SQL — 免费

在浏览器中编写并运行真实代码，获得全天候 AI 导师的即时帮助，并在网页或应用中继续学习。

课程: 46
课程: 183

免费开始学习查看完整课程

常见问题解答

「角色与权限」课时是免费的吗？

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

「角色与权限」这节课中我会学到什么？

使用 GRANT 和 REVOKE 控制访问权限你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 SQL Academy 需要有经验吗？

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

「角色与权限」课时需要多长时间？

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

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

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

此课程中的所有课时

← 返回 SQL Academy