👋 欢迎来到吴老二技术博客,专注运维 / 数据库 / 云原生
首页/数据库/正文

人大金仓 KingbaseES 权限管理完全指南:从用户创建、权限授予到特殊权限与权限回收

吴
吴老二
2026-11-21数据库12 次浏览0 条评论

文章必须遵循一个真实数据库运维生命周期:

创建身份 → 规划权限 → 授予权限 → 特殊权限配置 → 权限验证 → 权限变更 → 权限回收 → 安全检查。

而且全篇只使用一个业务场景、一个数据库、一套用户和对象。每一步都承接上一步,不要出现前面创建了一套用户,后面又换一套用户的情况。

一、先建立整个人大金仓权限体系的认知

在正式操作之前,必须先让读者明白:数据库权限并不是只有表权限,而是由多个层次共同决定的。

KingbaseES 权限体系

身份与角色

用户、角色、角色成员关系、登录属性

系统级权限

创建数据库、创建角色、超级用户等

数据库级权限

CONNECT、CREATE、TEMPORARY

Schema 级权限

USAGE、CREATE

对象级权限

表、视图、序列、函数、列

特殊权限与安全控制

PUBLIC、WITH GRANT OPTION、默认权限、RLS、对象所有权、角色继承

最终有效访问权限

用户最终能连接什么数据库、访问什么对象、执行什么操作

这里需要强调一个核心认识:

用户最终能够执行什么操作,不是由某一条 GRANT 决定的,而是由身份属性、对象所有权、直接授权、角色继承、PUBLIC 授权以及安全策略等共同决定的。

这也是整篇文章最重要的知识主线。

二、统一实战环境:从零建立权限管理场景

整篇文章都使用下面这套环境。

1. 业务场景

某公司有一个订单管理系统,数据库管理员负责维护数据库,开发人员负责业务开发,业务人员负责查询数据,审计人员负责检查数据。

| 用户/角色 | 职责 | 权限规划 | | --------------- | ------------------ | -------------------------------- | | 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 |

2. 先明确用户、角色、所有者的区别

这是很多初学者容易混淆的地方。

  • 用户:主要用于身份认证和登录。
  • 角色:用于集中管理一组权限,也可以作为用户组。
  • 所有者:创建数据库对象时,通常由执行创建操作的角色成为对象所有者。

KingbaseES 使用统一的角色机制管理用户和权限。角色可以被授予其他角色,角色也可以拥有数据库对象。官方建议将权限优先授予角色,再将角色分配给用户。

KingbaseES产品手册+1

三、第一阶段:创建用户、角色和数据库

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 行记录)

2. 创建业务对象所有者

test=# CREATE USER db_owner WITH PASSWORD 'Owner@123456';
CREATE ROLE

3. 创建开发和只读用户

test=# CREATE USER dev_user WITH PASSWORD 'Dev@123456';
CREATE ROLE
test=# CREATE USER read_user WITH PASSWORD 'Read@123456';
CREATE ROLE

4. 创建业务角色

test=# CREATE ROLE developer_role;
CREATE ROLE
test=# CREATE ROLE readonly_role;
CREATE ROLE

5. 创建审计用户

test=# CREATE USER audit_user WITH PASSWORD 'Audit@123456';
CREATE ROLE

6. 查看用户和角色

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=#

以下是数据库级权限操作。

1. 授予连接权限

privilege_demo=# GRANT CONNECT ON DATABASE privilege_demo TO db_owner, dev_user, read_user;
GRANT

2. 授予临时表权限

privilege_demo=# GRANT TEMPORARY ON DATABASE privilege_demo TO dev_user;
GRANT

3. 授予创建 Schema 的权限

GRANT CREATE ON DATABASE privilege_demo TO db_owner;

4. 查看数据库权限

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 行记录)

5. 回收数据库权限

privilege_demo=# REVOKE TEMPORARY ON DATABASE privilege_demo FROM dev_user;
REVOKE

这里要强调:拥有数据库 CONNECT 权限只是允许连接,不代表可以访问数据库中的业务对象。

五、第三阶段:Schema 和业务对象权限

由 db_owner 登录 privilege_demo。

1. 创建 Schema

CREATE SCHEMA business AUTHORIZATION db_owner;

2. 创建业务表

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

3. 创建序列

privilege_demo=# CREATE SEQUENCE business.t_user_id_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE

4. 插入测试数据

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

5. 创建视图

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

6. 创建函数

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,现在正式使用它们管理权限。

1. 给开发角色授予 Schema 权限

privilege_demo=# GRANT USAGE ON SCHEMA business TO developer_role;
GRANT

2. 给开发角色授予表权限

privilege_demo=# GRANT SELECT, INSERT, UPDATE, DELETE
privilege_demo-# ON ALL TABLES IN SCHEMA business
privilege_demo-# TO developer_role;
GRANT

3. 给开发角色授予序列权限

privilege_demo=# GRANT USAGE, SELECT, UPDATE
privilege_demo-# ON ALL SEQUENCES IN SCHEMA business
privilege_demo-# TO developer_role;
GRANT

4. 给只读角色授予查询权限

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

5. 将角色授予用户

privilege_demo=# GRANT developer_role TO dev_user;
GRANT ROLE
privilege_demo=#
privilege_demo=# GRANT readonly_role TO read_user;
GRANT ROLE

这样以后如果新增开发人员,只需要把 developer_role 授予新用户,不必重复执行所有对象授权。

6. 查看角色成员关系

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 行记录)

7. 验证角色权限

分别使用两个用户登录:

-- 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 权限而失败。

8. 角色切换 SET ROLE

角色权限还涉及一个重要概念:当前会话中哪些角色正在生效。

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

需要注意,角色成员关系、角色继承属性、角色授权选项及默认角色设置会影响实际生效权限,不能把所有角色关系简单理解为无条件继承。

七、第五阶段:对象权限的完整设置

这一阶段把各种数据库对象都纳入权限管理。

1. 表权限

| 权限 | 作用 | | ---------- | ------------ | | 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 表。

2. 列级权限

这里需要特别说明,KingbaseES 的列级授权不是简单地把表级权限拆分。

官方文档列出的列级权限主要是:

  • INSERT
  • UPDATE
  • REFERENCES

例如,只允许开发人员修改用户名,不允许通过这项授权修改手机号:

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 权限不会限制其更新该列。

3. 序列权限

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

4. 视图权限

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

视图的底层对象权限还需要结合视图的安全行为、定义者权限等判断。

5. 函数和过程权限

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

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 不同版本对特殊系统权限的继承和使用有专门规定,不能仅依赖普通角色继承来判断。

2. ANY 系统权限

这是金仓非常值得单独介绍的一类特殊权限。

普通对象授权:

privilege_demo=# GRANT SELECT
privilege_demo-# ON business.t_user
privilege_demo-# TO dev_user;
GRANT

只允许访问指定对象。

ANY 权限则可以让用户针对某类对象执行跨对象操作,例如对多个用户拥有的表进行查询。

但 ANY 权限需要按照对应版本的权限插件、支持的权限类型及授权语法配置,不能直接套用普通表权限的 GRANT 写法。

文章应专门介绍:

  • ANY 权限的作用。
  • ANY 权限与普通对象授权的区别。
  • ANY 权限的启用条件。
  • ANY 权限的授权和回收。
  • 如何查询 ANY 权限。
  • ANY 权限可能带来的越权风险。

不建议直接给普通开发人员配置 SELECT ANY、UPDATE ANY、DELETE ANY 等全局能力。 官方开发指南也明确提醒,不应随意授予普通用户额外的全局权限。

3. SYSBACKUP 备份权限

SYSBACKUP 是金仓专门提供的备份管理特权。

它用于授权相应用户执行物理备份操作,而不是让备份人员获得全部数据库对象的管理权限。

这个章节需要介绍:

  • SYSBACKUP 的作用。
  • SYSBACKUP 的启用条件。
  • 对应备份插件的配置。
  • 如何授予备份权限。
  • 如何查看备份权限。
  • 如何撤销备份权限。

这里必须以实际版本的官方安全指南和备份插件文档为准,不能将 SYSBACKUP 简单写成普通的 GRANT SELECT 权限。

KingbaseES产品手册+1

4. 三权分立:SSO、SAO

这是金仓安全管理体系中很有代表性的功能。

三权分立管理模型

系统管理员

数据库管理

SSO

安全管理

SAO

审计管理

三类职责分离,避免数据库管理、安全策略和审计权限集中于同一身份。

需要分别介绍:

  • 系统管理员:负责数据库日常管理。
  • SSO:负责安全策略及相关管理。
  • SAO:负责审计管理及相关管理。

这里不能只写一条 CREATE USER 就结束,还需要解释三权分立的启用条件、预定义管理用户、权限边界和相互制约关系。

Kingbase帮助中心+1

5. PUBLIC 公共权限

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 是默认包含所有角色的公共授权对象,检查权限时不能忽略它。

6. WITH GRANT OPTION 转授权

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 理解级联回收。

7. 默认权限

给未来创建的表自动授权:

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

这里一定要讲清楚:默认权限只影响未来对象,不会自动改变已经创建的表。

8. 行级安全策略 RLS

这是权限控制从对象级进入数据行级的功能。

例如两个开发人员都能查询订单表,但每个人只能查询自己负责的订单。

文章可以介绍:

  • ENABLE ROW LEVEL SECURITY。
  • CREATE POLICY。
  • USING。
  • WITH CHECK。
  • ALTER TABLE FORCE ROW LEVEL SECURITY。
  • DROP POLICY。

需要明确它与普通 SELECT 授权的区别,并注明版本支持情况。

9. 角色启用和禁用

金仓还提供角色启用、禁用管理能力,适合讲解权限临时控制。

这一部分应结合对应版本的角色管理功能及相关插件说明,区分角色成员关系的撤销、角色禁用和用户 NOLOGIN,三者不是同一个操作。

九、第七阶段:权限查询与最终有效权限检查

这部分必须独立成章。

因为实际运维中,最常见的问题不是不会授权,而是:

明明执行了 REVOKE,为什么用户还是能访问数据?

要回答这个问题,就必须从多个维度检查。

1. 查看所有用户和角色

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 行记录)

2. 查看数据库对象授权记录

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

3. 查看指定用户的对象权限

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 行记录)

4. 查看角色成员关系

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 行记录)

5. 查看实际表权限

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 行记录)

6. 查看实际 Schema 权限

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 行记录)

7. 检查当前会话身份

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 等策略限制 |

十、第八阶段:完整权限回收实战

这一阶段继续使用前面创建的用户和对象。

假设现在发生以下情况:

  • dev_user 离职,需要取消开发权限。
  • read_user 不再需要查看业务数据。
  • developer_role 不再使用。
  • db_operator 不再需要创建数据库。
  • PUBLIC 不再允许访问用户表。

1. 回收开发角色的对象权限

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

2. 回收只读角色权限

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

3. 回收函数执行权限

privilege_demo=# REVOKE EXECUTE
privilege_demo-# ON FUNCTION business.get_user_count()
privilege_demo-# FROM readonly_role;
警告:  不能撤销未授予的权限
REVOKE

4. 回收角色成员关系

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

5. 回收系统权限

privilege_demo=# ALTER USER db_operator
privilege_demo-# NOCREATEDB;
ALTER ROLE
privilege_demo=#
privilege_demo=# ALTER USER db_operator
privilege_demo-# NOCREATEROLE;
ALTER ROLE

6. 回收默认权限

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

7. 回收 PUBLIC 权限

privilege_demo=# REVOKE ALL PRIVILEGES
privilege_demo-# ON business.t_user
privilege_demo-# FROM PUBLIC;
REVOKE

8. 禁止用户登录

privilege_demo=# ALTER USER dev_user NOLOGIN;
ALTER ROLE
privilege_demo=#
privilege_demo=# ALTER USER read_user NOLOGIN;
ALTER ROLE

9. 验证权限回收结果

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 行记录)

这里有一个必须说明的细节:

上面的回收操作是针对明确列出的授权来源,并不能保证用户所有访问途径都被清除。还需要检查:

  • 用户是否直接获得过权限。
  • 是否通过其他角色获得权限。
  • 是否通过 PUBLIC 获得权限。
  • 是否拥有对象所有权。
  • 是否存在其他用户通过转授权形成的权限链。
  • 是否存在其他系统特权。

如果用户已经离职,且需要彻底清理账号,还要处理现有连接、其拥有的对象和依赖关系。不能直接把 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

评论需 登录 后发表(注册并验证邮箱)。
还没有评论,来抢沙发~