
DML 的风险不在写错,在锁得太多
导语 增删改查的语法半小时就能学会,但运维事故很少是“语法写错”造成的,更多是“一条没走索引的 UPDATE 在高峰期锁了半张表”“批量插入没包事务导致刷盘几百次”“DELETE
文章必须遵循一个真实数据库运维生命周期:
创建身份 → 规划权限 → 授予权限 → 特殊权限配置 → 权限验证 → 权限变更 → 权限回收 → 安全检查。
而且全篇只使用一个业务场景、一个数据库、一套用户和对象。每一步都承接上一步,不要出现前面创建了一套用户,后面又换一套用户的情况。
在正式操作之前,必须先让读者明白:数据库权限并不是只有表权限,而是由多个层次共同决定的。
身份与角色
用户、角色、角色成员关系、登录属性
系统级权限
创建数据库、创建角色、超级用户等
数据库级权限
CONNECT、CREATE、TEMPORARY
Schema 级权限
USAGE、CREATE
对象级权限
表、视图、序列、函数、列
特殊权限与安全控制
PUBLIC、WITH GRANT OPTION、默认权限、RLS、对象所有权、角色继承
最终有效访问权限
用户最终能连接什么数据库、访问什么对象、执行什么操作
这里需要强调一个核心认识:
用户最终能够执行什么操作,不是由某一条 GRANT 决定的,而是由身份属性、对象所有权、直接授权、角色继承、PUBLIC 授权以及安全策略等共同决定的。
这也是整篇文章最重要的知识主线。
整篇文章都使用下面这套环境。
某公司有一个订单管理系统,数据库管理员负责维护数据库,开发人员负责业务开发,业务人员负责查询数据,审计人员负责检查数据。
| 用户/角色 | 职责 | 权限规划 | | --------------- | ------------------ | -------------------------------- | | system | 数据库初始管理账号 | 初始管理 | | db_owner | 业务对象所有者 | 创建和管理业务对象 | | dev_user | 开发人员 | 业务数据增删改查 | | read_user | 查询人员 | 只读 | | audit_user | 审计人员 | 查看授权及审计相关信息,按需配置 | | developer_role | 开发角色 | 集中管理开发权限 | | readonly_role | 只读角色 | 集中管理查询权限 |
数据库及对象:
| 类型 | 名称 | | -------- | ---------------- | | 数据库 | privilege_demo | | Schema | business | | 用户表 | t_user | | 订单表 | t_order | | 订单视图 | v_order | | 用户序列 | t_user_id_seq | | 查询函数 | get_user_count |
这是很多初学者容易混淆的地方。
KingbaseES 使用统一的角色机制管理用户和权限。角色可以被授予其他角色,角色也可以拥有数据库对象。官方建议将权限优先授予角色,再将角色分配给用户。
KingbaseES产品手册+1
使用具有相应权限的管理员账号执行:
test=# CREATE DATABASE privilege_demo;
CREATE DATABASE
test=# \l
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | ICU 排序 | 存取权限
----------------+----------+----------+-------------+-------------+----------+-------------------
app_order | ops_user | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | |
kingbase | system | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | |
privilege_demo | system | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | |
security | system | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | |
template0 | system | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | =c/system +
| | | | | | system=CTc/system
template1 | system | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | =c/system +
| | | | | | system=CTc/system
test | system | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | |
(7 行记录)
test=# CREATE USER db_owner WITH PASSWORD 'Owner@123456';
CREATE ROLE
test=# CREATE USER dev_user WITH PASSWORD 'Dev@123456';
CREATE ROLE
test=# CREATE USER read_user WITH PASSWORD 'Read@123456';
CREATE ROLE
test=# CREATE ROLE developer_role;
CREATE ROLE
test=# CREATE ROLE readonly_role;
CREATE ROLE
test=# CREATE USER audit_user WITH PASSWORD 'Audit@123456';
CREATE ROLE
test=# SELECT
test-# rolname,
test-# rolsuper,
test-# rolinherit,
test-# rolcreaterole,
test-# rolcreatedb,
test-# rolcanlogin
test-# FROM sys_catalog.sys_roles
test-# ORDER BY rolname;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin
----------------------------+----------+------------+---------------+-------------+-------------
app_user | f | t | f | f | t
audit_user | f | t | f | f | t
db_owner | f | t | f | f | t
dev_user | f | t | f | f | t
developer_role | f | t | f | f | f
kcluster | f | t | f | f | f
ops_user | f | t | f | f | t
pg_execute_server_program | f | t | f | f | f
pg_monitor | f | t | f | f | f
pg_read_all_settings | f | t | f | f | f
pg_read_all_stats | f | t | f | f | f
pg_read_server_files | f | t | f | f | f
pg_signal_backend | f | t | f | f | f
pg_stat_scan_tables | f | t | f | f | f
pg_write_server_files | f | t | f | f | f
read_user | f | t | f | f | t
readonly_role | f | t | f | f | f
sao | f | f | t | f | t
sao_oper | f | f | f | f | f
sao_public | f | f | f | f | f
sso | f | f | t | f | t
sso_oper | f | f | f | f | f
sso_public | f | f | f | f | f
sys_execute_server_program | f | t | f | f | f
sys_monitor | f | t | f | f | f
sys_read_all_settings | f | t | f | f | f
sys_read_all_stats | f | t | f | f | f
sys_read_server_files | f | t | f | f | f
sys_signal_backend | f | t | f | f | f
sys_stat_scan_tables | f | t | f | f | f
sys_write_server_files | f | t | f | f | f
system | t | t | t | t | t
(32 行记录)
角色本身可以不具备 LOGIN 属性,专门用于权限集合;用户通常需要 LOGIN 属性才能登录。
连接数据库:
test=# \c privilege_demo
您现在以用户名"system"连接到数据库"privilege_demo"。
privilege_demo=#
以下是数据库级权限操作。
privilege_demo=# GRANT CONNECT ON DATABASE privilege_demo TO db_owner, dev_user, read_user;
GRANT
privilege_demo=# GRANT TEMPORARY ON DATABASE privilege_demo TO dev_user;
GRANT
GRANT CREATE ON DATABASE privilege_demo TO db_owner;
privilege_demo=# SELECT
privilege_demo-# datname,
privilege_demo-# has_database_privilege(
privilege_demo(# 'dev_user', oid, 'CONNECT'
privilege_demo(# ) AS connect_privilege,
privilege_demo-# has_database_privilege(
privilege_demo(# 'dev_user', oid, 'CREATE'
privilege_demo(# ) AS create_privilege,
privilege_demo-# has_database_privilege(
privilege_demo(# 'dev_user', oid, 'TEMPORARY'
privilege_demo(# ) AS temporary_privilege
privilege_demo-# FROM sys_catalog.sys_database
privilege_demo-# WHERE datname = 'privilege_demo';
datname | connect_privilege | create_privilege | temporary_privilege
----------------+-------------------+------------------+---------------------
privilege_demo | t | f | t
(1 行记录)
privilege_demo=# REVOKE TEMPORARY ON DATABASE privilege_demo FROM dev_user;
REVOKE
这里要强调:拥有数据库 CONNECT 权限只是允许连接,不代表可以访问数据库中的业务对象。
由 db_owner 登录 privilege_demo。
CREATE SCHEMA business AUTHORIZATION db_owner;
privilege_demo=# CREATE TABLE business.t_user (
privilege_demo(# id BIGINT PRIMARY KEY,
privilege_demo(# username VARCHAR(50),
privilege_demo(# phone VARCHAR(20),
privilege_demo(# create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
privilege_demo(# );
CREATE TABLE
privilege_demo=#
privilege_demo=# CREATE TABLE business.t_order (
privilege_demo(# id BIGINT PRIMARY KEY,
privilege_demo(# user_id BIGINT,
privilege_demo(# order_no VARCHAR(50),
privilege_demo(# amount NUMERIC(10,2),
privilege_demo(# create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
privilege_demo(# );
CREATE TABLE
privilege_demo=# CREATE SEQUENCE business.t_user_id_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE
privilege_demo=# INSERT INTO business.t_user
privilege_demo-# (id, username, phone)
privilege_demo-# VALUES
privilege_demo-# (1, '张三', '13800000001'),
privilege_demo-# (2, '李四', '13800000002');
INSERT 0 2
privilege_demo=#
privilege_demo=# INSERT INTO business.t_order
privilege_demo-# (id, user_id, order_no, amount)
privilege_demo-# VALUES
privilege_demo-# (1, 1, 'ORD001', 100),
privilege_demo-# (2, 2, 'ORD002', 200);
INSERT 0 2
privilege_demo=# CREATE VIEW business.v_order AS
privilege_demo-# SELECT
privilege_demo-# o.id,
privilege_demo-# o.order_no,
privilege_demo-# u.username,
privilege_demo-# o.amount
privilege_demo-# FROM business.t_order o
privilege_demo-# JOIN business.t_user u
privilege_demo-# ON o.user_id = u.id;
CREATE VIEW
privilege_demo=# CREATE FUNCTION business.get_user_count()
privilege_demo-# RETURNS BIGINT
privilege_demo-# LANGUAGE SQL
privilege_demo-# AS $$
privilege_demo$# SELECT COUNT(*) FROM business.t_user;
privilege_demo$# $$;
CREATE FUNCTION
到这里,业务对象已经全部创建完成。
这里需要特别提醒:后续如果换成 db_owner 以外的用户创建对象,对象所有者也会发生变化。权限管理必须始终关注对象的实际 OWNER。
这是整篇文章最重要的部分之一。
前面已经创建了 developer_role 和 readonly_role,现在正式使用它们管理权限。
privilege_demo=# GRANT USAGE ON SCHEMA business TO developer_role;
GRANT
privilege_demo=# GRANT SELECT, INSERT, UPDATE, DELETE
privilege_demo-# ON ALL TABLES IN SCHEMA business
privilege_demo-# TO developer_role;
GRANT
privilege_demo=# GRANT USAGE, SELECT, UPDATE
privilege_demo-# ON ALL SEQUENCES IN SCHEMA business
privilege_demo-# TO developer_role;
GRANT
privilege_demo=# GRANT USAGE
privilege_demo-# ON SCHEMA business
privilege_demo-# TO readonly_role;
GRANT
privilege_demo=#
privilege_demo=# GRANT SELECT
privilege_demo-# ON ALL TABLES IN SCHEMA business
privilege_demo-# TO readonly_role;
GRANT
privilege_demo=# GRANT developer_role TO dev_user;
GRANT ROLE
privilege_demo=#
privilege_demo=# GRANT readonly_role TO read_user;
GRANT ROLE
这样以后如果新增开发人员,只需要把 developer_role 授予新用户,不必重复执行所有对象授权。
privilege_demo=# SELECT
privilege_demo-# r.rolname AS role_name,
privilege_demo-# m.rolname AS member_name
privilege_demo-# FROM sys_catalog.sys_auth_members am
privilege_demo-# JOIN sys_catalog.sys_roles r
privilege_demo-# ON am.roleid = r.oid
privilege_demo-# JOIN sys_catalog.sys_roles m
privilege_demo-# ON am.member = m.oid;
role_name | member_name
-----------------------+-------------
pg_stat_scan_tables | pg_monitor
pg_read_all_stats | pg_monitor
pg_read_all_settings | pg_monitor
sys_stat_scan_tables | sys_monitor
sys_read_all_stats | sys_monitor
sys_read_all_settings | sys_monitor
developer_role | dev_user
readonly_role | read_user
(8 行记录)
分别使用两个用户登录:
-- dev_user
privilege_demo=# SELECT * FROM business.t_user;
id | username | phone | create_time
----+----------+-------------+----------------------------
1 | 张三 | 13800000001 | 2026-11-19 16:59:57.373600
2 | 李四 | 13800000002 | 2026-11-19 16:59:57.373600
(2 行记录)
privilege_demo=# INSERT INTO business.t_user
privilege_demo-# (id, username)
privilege_demo-# VALUES
privilege_demo-# (3, '王五');
INSERT 0 1
-- read_user
privilege_demo=# SELECT * FROM business.t_user;
id | username | phone | create_time
----+----------+-------------+----------------------------
1 | 张三 | 13800000001 | 2026-11-19 16:59:57.373600
2 | 李四 | 13800000002 | 2026-11-19 16:59:57.373600
3 | 王五 | | 2026-11-19 17:03:07.523516
(3 行记录)
再尝试使用 read_user 插入数据,应该因为没有 INSERT 权限而失败。
角色权限还涉及一个重要概念:当前会话中哪些角色正在生效。
privilege_demo=# SET ROLE readonly_role;
SET
查看当前角色:
privilege_demo=> SELECT
privilege_demo-> SESSION_USER,
privilege_demo-> CURRENT_USER;
session_user | current_user
--------------+---------------
system | readonly_role
(1 行记录)
恢复原始角色:
privilege_demo=> RESET ROLE;
RESET
需要注意,角色成员关系、角色继承属性、角色授权选项及默认角色设置会影响实际生效权限,不能把所有角色关系简单理解为无条件继承。
这一阶段把各种数据库对象都纳入权限管理。
| 权限 | 作用 | | ---------- | ------------ | | SELECT | 查询数据 | | INSERT | 插入数据 | | UPDATE | 更新数据 | | DELETE | 删除数据 | | TRUNCATE | 清空表数据 | | REFERENCES | 创建外键引用 | | TRIGGER | 创建触发器 |
授予:
privilege_demo=# GRANT SELECT, INSERT, UPDATE, DELETE
privilege_demo-# ON business.t_user
privilege_demo-# TO developer_role;
GRANT
单独授予清空表权限:
privilege_demo=# GRANT TRUNCATE
privilege_demo-# ON business.t_user
privilege_demo-# TO developer_role;
GRANT
单独授予创建触发器权限:
privilege_demo=# GRANT TRIGGER
privilege_demo-# ON business.t_user
privilege_demo-# TO developer_role;
GRANT
注意:表级权限不等于对象所有权。授予 TRUNCATE、TRIGGER 等权限也不代表用户可以随意 ALTER 或 DROP 表。
这里需要特别说明,KingbaseES 的列级授权不是简单地把表级权限拆分。
官方文档列出的列级权限主要是:
例如,只允许开发人员修改用户名,不允许通过这项授权修改手机号:
privilege_demo=# GRANT UPDATE (username)
privilege_demo-# ON business.t_user
privilege_demo-# TO developer_role;
GRANT
只允许指定列插入:
privilege_demo=# GRANT INSERT (id, username)
privilege_demo-# ON business.t_user
privilege_demo-# TO developer_role;
GRANT
查看列级权限:
privilege_demo=# SELECT
privilege_demo-# grantee,
privilege_demo-# table_schema,
privilege_demo-# table_name,
privilege_demo-# column_name,
privilege_demo-# privilege_type,
privilege_demo-# is_grantable
privilege_demo-# FROM information_schema.column_privileges
privilege_demo-# WHERE table_schema = 'business';
grantee | table_schema | table_name | column_name | privilege_type | is_grantable
----------------+--------------+------------+-------------+----------------+--------------
system | business | v_order | id | UPDATE | YES
readonly_role | business | v_order | id | SELECT | NO
developer_role | business | v_order | username | INSERT | NO
system | business | t_order | id | UPDATE | YES
system | business | t_order | amount | INSERT | YES
developer_role | business | t_order | create_time | INSERT | NO
system | business | t_user | phone | SELECT | YES
system | business | t_user | username | REFERENCES | YES
developer_role | business | t_user | id | INSERT | NO
system | business | v_order | order_no | REFERENCES | YES
system | business | t_order | user_id | REFERENCES | YES
developer_role | business | t_order | id | INSERT | NO
readonly_role | business | t_order | user_id | SELECT | NO
system | business | t_user | id | UPDATE | YES
readonly_role | business | t_order | amount | SELECT | NO
developer_role | business | v_order | order_no | SELECT | NO
developer_role | business | t_user | id | UPDATE | NO
system | business | t_user | id | SELECT | YES
readonly_role | business | v_order | username | SELECT | NO
system | business | v_order | amount | SELECT | YES
developer_role | business | v_order | id | UPDATE | NO
system | business | t_order | id | REFERENCES | YES
system | business | t_user | id | REFERENCES | YES
developer_role | business | t_order | user_id | UPDATE | NO
readonly_role | business | t_user | id | SELECT | NO
system | business | t_user | create_time | UPDATE | YES
developer_role | business | t_user | phone | UPDATE | NO
system | business | t_user | create_time | INSERT | YES
developer_role | business | t_user | create_time | INSERT | NO
system | business | t_user | phone | UPDATE | YES
system | business | t_order | amount | UPDATE | YES
system | business | v_order | id | INSERT | YES
system | business | t_order | user_id | UPDATE | YES
developer_role | business | t_order | amount | UPDATE | NO
developer_role | business | v_order | username | UPDATE | NO
developer_role | business | t_user | id | SELECT | NO
developer_role | business | t_user | username | INSERT | NO
readonly_role | business | v_order | amount | SELECT | NO
system | business | v_order | id | REFERENCES | YES
readonly_role | business | t_order | id | SELECT | NO
system | business | v_order | amount | INSERT | YES
system | business | t_order | id | INSERT | YES
developer_role | business | t_order | amount | SELECT | NO
system | business | t_user | create_time | SELECT | YES
system | business | t_order | user_id | SELECT | YES
developer_role | business | t_user | create_time | SELECT | NO
system | business | t_order | create_time | SELECT | YES
system | business | t_user | id | INSERT | YES
system | business | v_order | username | UPDATE | YES
system | business | t_user | username | SELECT | YES
system | business | t_order | create_time | REFERENCES | YES
readonly_role | business | v_order | order_no | SELECT | NO
system | business | t_user | phone | INSERT | YES
system | business | v_order | username | INSERT | YES
developer_role | business | t_order | amount | INSERT | NO
system | business | t_order | order_no | INSERT | YES
developer_role | business | t_user | username | SELECT | NO
developer_role | business | v_order | order_no | UPDATE | NO
system | business | t_user | create_time | REFERENCES | YES
developer_role | business | t_order | create_time | UPDATE | NO
developer_role | business | t_user | create_time | UPDATE | NO
system | business | v_order | order_no | UPDATE | YES
readonly_role | business | t_order | order_no | SELECT | NO
readonly_role | business | t_user | username | SELECT | NO
system | business | t_user | phone | REFERENCES | YES
system | business | t_order | id | SELECT | YES
developer_role | business | t_user | phone | SELECT | NO
system | business | t_order | amount | REFERENCES | YES
developer_role | business | t_order | order_no | INSERT | NO
system | business | v_order | username | SELECT | YES
system | business | t_order | create_time | INSERT | YES
developer_role | business | t_order | order_no | SELECT | NO
developer_role | business | v_order | amount | SELECT | NO
developer_role | business | t_user | phone | INSERT | NO
system | business | t_user | username | UPDATE | YES
system | business | t_order | order_no | REFERENCES | YES
system | business | t_order | amount | SELECT | YES
developer_role | business | v_order | order_no | INSERT | NO
developer_role | business | t_order | user_id | SELECT | NO
developer_role | business | v_order | amount | INSERT | NO
developer_role | business | t_order | user_id | INSERT | NO
developer_role | business | v_order | amount | UPDATE | NO
developer_role | business | t_order | create_time | SELECT | NO
system | business | t_user | username | INSERT | YES
readonly_role | business | t_user | phone | SELECT | NO
developer_role | business | t_order | id | SELECT | NO
system | business | v_order | amount | UPDATE | YES
readonly_role | business | t_user | create_time | SELECT | NO
system | business | t_order | order_no | UPDATE | YES
system | business | v_order | order_no | SELECT | YES
developer_role | business | t_order | order_no | UPDATE | NO
system | business | v_order | amount | REFERENCES | YES
system | business | v_order | id | SELECT | YES
system | business | t_order | create_time | UPDATE | YES
system | business | t_order | user_id | INSERT | YES
readonly_role | business | t_order | create_time | SELECT | NO
system | business | t_order | order_no | SELECT | YES
developer_role | business | v_order | id | INSERT | NO
developer_role | business | t_user | username | UPDATE | NO
system | business | v_order | username | REFERENCES | YES
developer_role | business | t_order | id | UPDATE | NO
developer_role | business | v_order | username | SELECT | NO
system | business | v_order | order_no | INSERT | YES
developer_role | business | v_order | id | SELECT | NO
(104 行记录)
回收:
privilege_demo=# REVOKE UPDATE (username)
privilege_demo-# ON business.t_user
privilege_demo-# FROM developer_role;
REVOKE
注意:如果用户同时拥有整张表的 UPDATE 权限,回收单列 UPDATE 权限不会限制其更新该列。
privilege_demo=# GRANT USAGE, SELECT, UPDATE
privilege_demo-# ON SEQUENCE business.t_user_id_seq
privilege_demo-# TO developer_role;
GRANT
三种权限的区别:
| 权限 | 作用 | | ------ | ----------------------------------- | | USAGE | 使用 nextval 等序列操作 | | SELECT | 查询 currval 等序列值 | | UPDATE | 执行 nextval、setval 等序列修改操作 |
回收:
privilege_demo=# REVOKE ALL PRIVILEGES
privilege_demo-# ON SEQUENCE business.t_user_id_seq
privilege_demo-# FROM developer_role;
REVOKE
privilege_demo=# GRANT SELECT
privilege_demo-# ON business.v_order
privilege_demo-# TO readonly_role;
GRANT
查看:
privilege_demo=# SELECT *
privilege_demo-# FROM information_schema.table_privileges
privilege_demo-# WHERE table_schema = 'business'
privilege_demo-# AND table_name = 'v_order';
grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
---------+----------------+----------------+--------------+------------+----------------+--------------+----------------
system | readonly_role | privilege_demo | business | v_order | SELECT | NO | YES
system | developer_role | privilege_demo | business | v_order | DELETE | NO | NO
system | developer_role | privilege_demo | business | v_order | UPDATE | NO | NO
system | developer_role | privilege_demo | business | v_order | SELECT | NO | YES
system | developer_role | privilege_demo | business | v_order | INSERT | NO | NO
system | system | privilege_demo | business | v_order | TRIGGER | YES | NO
system | system | privilege_demo | business | v_order | REFERENCES | YES | NO
system | system | privilege_demo | business | v_order | TRUNCATE | YES | NO
system | system | privilege_demo | business | v_order | DELETE | YES | NO
system | system | privilege_demo | business | v_order | UPDATE | YES | NO
system | system | privilege_demo | business | v_order | SELECT | YES | YES
system | system | privilege_demo | business | v_order | INSERT | YES | NO
(12 行记录)
回收:
privilege_demo=# REVOKE SELECT
privilege_demo-# ON business.v_order
privilege_demo-# FROM readonly_role;
REVOKE
视图的底层对象权限还需要结合视图的安全行为、定义者权限等判断。
privilege_demo=# GRANT EXECUTE
privilege_demo-# ON FUNCTION business.get_user_count()
privilege_demo-# TO readonly_role;
GRANT
查看:
privilege_demo=# SELECT *
privilege_demo-# FROM information_schema.routine_privileges
privilege_demo-# WHERE routine_schema = 'business';
grantor | grantee | specific_catalog | specific_schema | specific_name | routine_catalog | routine_schema | routine_name | privilege_
type | is_grantable
---------+---------------+------------------+-----------------+----------------------+-----------------+----------------+----------------+-----------
-----+--------------
system | PUBLIC | privilege_demo | business | get_user_count_24602 | privilege_demo | business | get_user_count | EXECUTE
| NO
system | system | privilege_demo | business | get_user_count_24602 | privilege_demo | business | get_user_count | EXECUTE
| YES
system | readonly_role | privilege_demo | business | get_user_count_24602 | privilege_demo | business | get_user_count | EXECUTE
| NO
(3 行记录)
回收:
privilege_demo=# REVOKE EXECUTE
privilege_demo-# ON FUNCTION business.get_user_count()
privilege_demo-# FROM readonly_role;
REVOKE
这里还应介绍函数和过程的定义者权限与调用者权限区别,因为它们可能影响调用用户对底层对象的访问能力。
这一部分是文章区别于普通 SQL 命令手册的关键。
KingbaseES 官方权限体系除了普通对象权限,还包括系统特权、ANY 权限、SYSBACKUP、三权分立、转授权、PUBLIC、角色启用禁用等内容。
KingbaseES产品手册+1
系统特权主要控制用户能够执行哪些数据库管理操作。
| 特权 | 作用 | | ----------- | ------------------ | | SUPERUSER | 超级用户权限 | | CREATEDB | 创建数据库 | | CREATEROLE | 创建、管理角色 | | LOGIN | 允许登录 | | REPLICATION | 复制相关权限 | | SSO | 安全管理员相关特权 | | SAO | 审计管理员相关特权 |
例如创建一个允许创建数据库的用户:
privilege_demo=# CREATE USER db_operator
privilege_demo-# WITH PASSWORD 'Operator@123456'
privilege_demo-# CREATEDB;
CREATE ROLE
授予角色创建权限:
privilege_demo=# ALTER USER db_operator
privilege_demo-# CREATEROLE;
ALTER ROLE
回收:
privilege_demo=# ALTER USER db_operator
privilege_demo-# NOCREATEDB;
ALTER ROLE
privilege_demo=# ALTER USER db_operator
privilege_demo-# NOCREATEROLE;
ALTER ROLE
禁止登录:
privilege_demo=# ALTER USER db_operator
privilege_demo-# NOLOGIN;
ALTER ROLE
需要特别注意:
SUPERUSER、CREATEDB、CREATEROLE 等角色属性与普通对象权限不是一回事。KingbaseES 不同版本对特殊系统权限的继承和使用有专门规定,不能仅依赖普通角色继承来判断。
这是金仓非常值得单独介绍的一类特殊权限。
普通对象授权:
privilege_demo=# GRANT SELECT
privilege_demo-# ON business.t_user
privilege_demo-# TO dev_user;
GRANT
只允许访问指定对象。
ANY 权限则可以让用户针对某类对象执行跨对象操作,例如对多个用户拥有的表进行查询。
但 ANY 权限需要按照对应版本的权限插件、支持的权限类型及授权语法配置,不能直接套用普通表权限的 GRANT 写法。
文章应专门介绍:
不建议直接给普通开发人员配置 SELECT ANY、UPDATE ANY、DELETE ANY 等全局能力。 官方开发指南也明确提醒,不应随意授予普通用户额外的全局权限。
SYSBACKUP 是金仓专门提供的备份管理特权。
它用于授权相应用户执行物理备份操作,而不是让备份人员获得全部数据库对象的管理权限。
这个章节需要介绍:
这里必须以实际版本的官方安全指南和备份插件文档为准,不能将 SYSBACKUP 简单写成普通的 GRANT SELECT 权限。
KingbaseES产品手册+1
这是金仓安全管理体系中很有代表性的功能。
系统管理员
数据库管理
SSO
安全管理
SAO
审计管理
三类职责分离,避免数据库管理、安全策略和审计权限集中于同一身份。
需要分别介绍:
这里不能只写一条 CREATE USER 就结束,还需要解释三权分立的启用条件、预定义管理用户、权限边界和相互制约关系。
Kingbase帮助中心+1
privilege_demo=# GRANT SELECT
privilege_demo-# ON business.t_user
privilege_demo-# TO PUBLIC;
GRANT
这意味着所有符合其他访问条件的用户都可能获得查询能力。
查看:
privilege_demo=# SELECT *
privilege_demo-# FROM information_schema.table_privileges
privilege_demo-# WHERE grantee = 'PUBLIC';
grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
---------+---------+----------------+--------------------+---------------------------------------+----------------+--------------+----------------
system | PUBLIC | privilege_demo | business | t_user | SELECT | NO | YES
system | PUBLIC | privilege_demo | pg_catalog | _dbroleset | SELECT | NO | YES
system | PUBLIC | privilege_demo | pg_catalog | _typ | SELECT | NO | YES
system | PUBLIC | privilege_demo | pg_catalog | _ext | SELECT | NO | YES
system | PUBLIC | privilege_demo | pg_catalog | pg_aggregate | SELECT | NO | YES
system | PUBLIC | privilege_demo | pg_catalog | pg_am | SELECT | NO | YES
system | PUBLIC | privilege_demo | pg_catalog | pg_amop | SELECT | NO | YES
system | PUBLIC | privilege_demo | pg_catalog | pg_depend | SELECT | NO | YES
system | PUBLIC | privilege_demo | pg_catalog | pg_amproc | SELECT | NO | YES
system | PUBLIC | privilege_demo | pg_catalog | pg_attrdef | SELECT | NO | YES
system | PUBLIC | privilege_demo | pg_catalog | pg_attribute | SELECT | NO | YES
回收:
privilege_demo=# REVOKE SELECT
privilege_demo-# ON business.t_user
privilege_demo-# FROM PUBLIC;
警告: 不能撤销未授予的权限
REVOKE
PUBLIC 是默认包含所有角色的公共授权对象,检查权限时不能忽略它。
privilege_demo=# GRANT SELECT
privilege_demo-# ON business.t_user
privilege_demo-# TO dev_user
privilege_demo-# WITH GRANT OPTION;
GRANT
开发人员可以继续授权:
privilege_demo=# GRANT SELECT
privilege_demo-# ON business.t_user
privilege_demo-# TO read_user;
GRANT
回收转授权:
privilege_demo=# REVOKE GRANT OPTION FOR SELECT
privilege_demo-# ON business.t_user
privilege_demo-# FROM dev_user;
REVOKE
如果需要撤销由此产生的下游授权,还需要结合 CASCADE 和 RESTRICT 理解级联回收。
给未来创建的表自动授权:
privilege_demo=# ALTER DEFAULT PRIVILEGES
privilege_demo-# FOR ROLE db_owner
privilege_demo-# IN SCHEMA business
privilege_demo-# GRANT SELECT
privilege_demo-# ON TABLES TO readonly_role;
ALTER DEFAULT PRIVILEGES
回收未来默认授权:
privilege_demo=# ALTER DEFAULT PRIVILEGES
privilege_demo-# FOR ROLE db_owner
privilege_demo-# IN SCHEMA business
privilege_demo-# REVOKE SELECT
privilege_demo-# ON TABLES FROM readonly_role;
ALTER DEFAULT PRIVILEGES
这里一定要讲清楚:默认权限只影响未来对象,不会自动改变已经创建的表。
这是权限控制从对象级进入数据行级的功能。
例如两个开发人员都能查询订单表,但每个人只能查询自己负责的订单。
文章可以介绍:
需要明确它与普通 SELECT 授权的区别,并注明版本支持情况。
金仓还提供角色启用、禁用管理能力,适合讲解权限临时控制。
这一部分应结合对应版本的角色管理功能及相关插件说明,区分角色成员关系的撤销、角色禁用和用户 NOLOGIN,三者不是同一个操作。
这部分必须独立成章。
因为实际运维中,最常见的问题不是不会授权,而是:
明明执行了 REVOKE,为什么用户还是能访问数据?
要回答这个问题,就必须从多个维度检查。
privilege_demo=# SELECT
privilege_demo-# rolname,
privilege_demo-# rolsuper,
privilege_demo-# rolinherit,
privilege_demo-# rolcreaterole,
privilege_demo-# rolcreatedb,
privilege_demo-# rolcanlogin
privilege_demo-# FROM sys_catalog.sys_roles
privilege_demo-# ORDER BY rolname;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin
----------------------------+----------+------------+---------------+-------------+-------------
app_user | f | t | f | f | t
audit_user | f | t | f | f | t
db_operator | f | t | f | f | f
db_owner | f | t | f | f | t
dev_user | f | t | f | f | t
developer_role | f | t | f | f | f
kcluster | f | t | f | f | f
ops_user | f | t | f | f | t
pg_execute_server_program | f | t | f | f | f
pg_monitor | f | t | f | f | f
pg_read_all_settings | f | t | f | f | f
pg_read_all_stats | f | t | f | f | f
pg_read_server_files | f | t | f | f | f
pg_signal_backend | f | t | f | f | f
pg_stat_scan_tables | f | t | f | f | f
pg_write_server_files | f | t | f | f | f
read_user | f | t | f | f | t
readonly_role | f | t | f | f | f
sao | f | f | t | f | t
sao_oper | f | f | f | f | f
sao_public | f | f | f | f | f
sso | f | f | t | f | t
sso_oper | f | f | f | f | f
sso_public | f | f | f | f | f
sys_execute_server_program | f | t | f | f | f
sys_monitor | f | t | f | f | f
sys_read_all_settings | f | t | f | f | f
sys_read_all_stats | f | t | f | f | f
sys_read_server_files | f | t | f | f | f
sys_signal_backend | f | t | f | f | f
sys_stat_scan_tables | f | t | f | f | f
sys_write_server_files | f | t | f | f | f
system | t | t | t | t | t
(33 行记录)
privilege_demo=# SELECT
privilege_demo-# grantor,
privilege_demo-# grantee,
privilege_demo-# table_schema,
privilege_demo-# table_name,
privilege_demo-# privilege_type,
privilege_demo-# is_grantable
privilege_demo-# FROM information_schema.table_privileges
privilege_demo-# ORDER BY table_schema, table_name, grantee;
grantor | grantee | table_schema | table_name | privilege_type | is_grantable
---------+----------------+--------------------+---------------------------------------+----------------+--------------
system | sso | anon | all_policy | REFERENCES | NO
system | sso | anon | all_policy | INSERT | NO
system | sso | anon | all_policy | SELECT | NO
system | sso | anon | all_policy | TRIGGER | NO
system | sso | anon | all_policy | UPDATE | NO
system | sso | anon | all_policy | DELETE | NO
privilege_demo=# SELECT
privilege_demo-# grantor,
privilege_demo-# grantee,
privilege_demo-# table_schema,
privilege_demo-# table_name,
privilege_demo-# privilege_type,
privilege_demo-# is_grantable
privilege_demo-# FROM information_schema.table_privileges
privilege_demo-# WHERE grantee = 'dev_user';
grantor | grantee | table_schema | table_name | privilege_type | is_grantable
---------+----------+--------------+------------+----------------+--------------
system | dev_user | business | t_user | SELECT | NO
(1 行记录)
privilege_demo=# SELECT
privilege_demo-# r.rolname AS role_name,
privilege_demo-# m.rolname AS member_name
privilege_demo-# FROM sys_catalog.sys_auth_members am
privilege_demo-# JOIN sys_catalog.sys_roles r
privilege_demo-# ON am.roleid = r.oid
privilege_demo-# JOIN sys_catalog.sys_roles m
privilege_demo-# ON am.member = m.oid;
role_name | member_name
-----------------------+-------------
pg_stat_scan_tables | pg_monitor
pg_read_all_stats | pg_monitor
pg_read_all_settings | pg_monitor
sys_stat_scan_tables | sys_monitor
sys_read_all_stats | sys_monitor
sys_read_all_settings | sys_monitor
developer_role | dev_user
readonly_role | read_user
(8 行记录)
privilege_demo=# SELECT
privilege_demo-# has_table_privilege(
privilege_demo(# 'dev_user',
privilege_demo(# 'business.t_user',
privilege_demo(# 'SELECT'
privilege_demo(# ) AS can_select,
privilege_demo-#
privilege_demo-# has_table_privilege(
privilege_demo(# 'dev_user',
privilege_demo(# 'business.t_user',
privilege_demo(# 'INSERT'
privilege_demo(# ) AS can_insert,
privilege_demo-#
privilege_demo-# has_table_privilege(
privilege_demo(# 'dev_user',
privilege_demo(# 'business.t_user',
privilege_demo(# 'UPDATE'
privilege_demo(# ) AS can_update,
privilege_demo-#
privilege_demo-# has_table_privilege(
privilege_demo(# 'dev_user',
privilege_demo(# 'business.t_user',
privilege_demo(# 'DELETE'
privilege_demo(# ) AS can_delete;
can_select | can_insert | can_update | can_delete
------------+------------+------------+------------
t | t | t | t
(1 行记录)
privilege_demo=# SELECT
privilege_demo-# has_schema_privilege(
privilege_demo(# 'dev_user',
privilege_demo(# 'business',
privilege_demo(# 'USAGE'
privilege_demo(# ) AS can_usage,
privilege_demo-#
privilege_demo-# has_schema_privilege(
privilege_demo(# 'dev_user',
privilege_demo(# 'business',
privilege_demo(# 'CREATE'
privilege_demo(# ) AS can_create;
can_usage | can_create
-----------+------------
t | f
(1 行记录)
privilege_demo=# SELECT
privilege_demo-# SESSION_USER,
privilege_demo-# CURRENT_USER;
session_user | current_user
--------------+--------------
system | system
(1 行记录)
这两个值在使用 SET ROLE、定义者权限函数等场景下可能不同。
建议读者形成一个完整的排查思路:
| 检查维度 | 检查内容 | | ----------- | ------------------------------ | | 用户属性 | 是否允许登录、是否为超级用户 | | 系统权限 | 是否有 CREATEDB、CREATEROLE 等 | | 数据库权限 | 是否拥有 CONNECT | | Schema 权限 | 是否拥有 USAGE、CREATE | | 对象权限 | 是否拥有 SELECT、INSERT 等 | | 角色权限 | 是否通过其他角色获得权限 | | PUBLIC | 是否通过公共授权获得权限 | | 转授权 | 是否存在其他用户转授的权限 | | 所有权 | 是否是对象所有者 | | 安全策略 | 是否受到 RLS 等策略限制 |
这一阶段继续使用前面创建的用户和对象。
假设现在发生以下情况:
privilege_demo=# REVOKE ALL PRIVILEGES
privilege_demo-# ON ALL TABLES IN SCHEMA business
privilege_demo-# FROM developer_role;
REVOKE
privilege_demo=#
privilege_demo=# REVOKE ALL PRIVILEGES
privilege_demo-# ON ALL SEQUENCES IN SCHEMA business
privilege_demo-# FROM developer_role;
REVOKE
privilege_demo=#
privilege_demo=# REVOKE USAGE, CREATE
privilege_demo-# ON SCHEMA business
privilege_demo-# FROM developer_role;
警告: 不能撤销未授予的权限
REVOKE
privilege_demo=# REVOKE ALL PRIVILEGES
privilege_demo-# ON ALL TABLES IN SCHEMA business
privilege_demo-# FROM readonly_role;
REVOKE
privilege_demo=#
privilege_demo=# REVOKE USAGE
privilege_demo-# ON SCHEMA business
privilege_demo-# FROM readonly_role;
REVOKE
privilege_demo=# REVOKE EXECUTE
privilege_demo-# ON FUNCTION business.get_user_count()
privilege_demo-# FROM readonly_role;
警告: 不能撤销未授予的权限
REVOKE
privilege_demo=# REVOKE developer_role
privilege_demo-# FROM dev_user;
REVOKE ROLE
privilege_demo=#
privilege_demo=# REVOKE readonly_role
privilege_demo-# FROM read_user;
REVOKE ROLE
privilege_demo=# ALTER USER db_operator
privilege_demo-# NOCREATEDB;
ALTER ROLE
privilege_demo=#
privilege_demo=# ALTER USER db_operator
privilege_demo-# NOCREATEROLE;
ALTER ROLE
privilege_demo=# ALTER DEFAULT PRIVILEGES
privilege_demo-# FOR ROLE db_owner
privilege_demo-# IN SCHEMA business
privilege_demo-# REVOKE ALL PRIVILEGES
privilege_demo-# ON TABLES FROM developer_role;
ALTER DEFAULT PRIVILEGES
privilege_demo=#
privilege_demo=# ALTER DEFAULT PRIVILEGES
privilege_demo-# FOR ROLE db_owner
privilege_demo-# IN SCHEMA business
privilege_demo-# REVOKE ALL PRIVILEGES
privilege_demo-# ON TABLES FROM readonly_role;
ALTER DEFAULT PRIVILEGES
privilege_demo=# REVOKE ALL PRIVILEGES
privilege_demo-# ON business.t_user
privilege_demo-# FROM PUBLIC;
REVOKE
privilege_demo=# ALTER USER dev_user NOLOGIN;
ALTER ROLE
privilege_demo=#
privilege_demo=# ALTER USER read_user NOLOGIN;
ALTER ROLE
privilege_demo=# SELECT
privilege_demo-# has_table_privilege(
privilege_demo(# 'dev_user',
privilege_demo(# 'business.t_user',
privilege_demo(# 'SELECT'
privilege_demo(# ) AS dev_select,
privilege_demo-#
privilege_demo-# has_table_privilege(
privilege_demo(# 'read_user',
privilege_demo(# 'business.t_user',
privilege_demo(# 'SELECT'
privilege_demo(# ) AS read_select;
dev_select | read_select
------------+-------------
t | t
(1 行记录)
这里有一个必须说明的细节:
上面的回收操作是针对明确列出的授权来源,并不能保证用户所有访问途径都被清除。还需要检查:
如果用户已经离职,且需要彻底清理账号,还要处理现有连接、其拥有的对象和依赖关系。不能直接把 NOLOGIN 当作完整的权限清理。
这也是很多权限教程容易遗漏的部分。
假设 dev_user 不仅获得了业务表权限,还自己创建了一些表。
此时直接删除用户,可能会遇到:
需要先处理对象所有权。
在支持相应语法的 KingbaseES 版本中,可以使用:
REASSIGN OWNED BY dev_user TO db_owner;
将该用户拥有的对象转移给 db_owner。
然后检查并清理该用户在当前数据库中的剩余授权及对象:
DROP OWNED BY dev_user;
最后删除用户:
DROP USER dev_user;
注意:
REASSIGN OWNED 和 DROP OWNED 的作用范围及版本支持需要提前确认。DROP OWNED 可能删除对象,执行前必须检查对象清单并做好备份,不能直接当作普通权限回收命令使用。
评论 0