KingbaseES 创建只读（read_only）用户-CSDN博客

本文链接：https://blog.csdn.net/lyu1026/article/details/124464734

本文介绍了在KingbaseES数据库中进行用户创建、权限设置的过程，包括设置默认事务为只读、分配查看权限以及连接数据库的权限。通过实例展示了用户在只读模式下能执行的操作（如查看表和数据），以及不能执行的操作（如创建表和执行DML操作）。这为数据库的安全管理和访问控制提供了参考。

摘要由CSDN通过智能技术生成

数据库版本：

prod=> select version();
                                                       version                                                        
----------------------------------------------------------------------------------------------------------------------
 KingbaseES V008R006C005B0023 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
(1 row)

创建用户及指定密码：

test=# CREATE USER u_r WITH ENCRYPTED PASSWORD '123456';
CREATE ROLE

设置用户默认事务只读：

test=# alter user u_r set default_transaction_read_only=on;
ALTER ROLE

test=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 sao       | No inheritance                                             | {}
 sso       | No inheritance                                             | {}
 system    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 tom       |                                                            | {}
 u_r       |                                                            | {}

赋予用户权限，查看public模式下所有表：

test=# GRANT USAGE ON SCHEMA public to u_r;
GRANT
test=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO u_r;
ALTER DEFAULT PRIVILEGES

赋予用户连接数据库权限：

test=# GRANT CONNECT ON DATABASE prod to u_r;
GRANT

切换到指定数据库：

test=# \c prod
You are now connected to database "prod" as user "system".
prod=# \d
               List of relations
 Schema |        Name         | Type  | Owner  
--------+---------------------+-------+--------
 public | sys_stat_statements | view  | system
 public | t1                  | table | system
 public | t2                  | table | system
 public | t3                  | table | system
 public | t4                  | table | system
 public | tmp_t0              | table | system
(6 rows)

赋予用户表、序列查看权限：

prod=# GRANT USAGE ON SCHEMA public to u_r;
GRANT
prod=# GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO u_r;
GRANT
prod=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO u_r;
GRANT

用户连接数据库测试：

prod=# \c prod u_r
You are now connected to database "prod" as user "u_r".

# 无法创建数据库对象(read_only)
prod=> create table t1 (id int);
ERROR:  cannot execute CREATE TABLE in a read-only transaction
prod=> \d
               List of relations
 Schema |        Name         | Type  | Owner  
--------+---------------------+-------+--------
 public | sys_stat_statements | view  | system
 public | t1                  | table | system
 public | t2                  | table | system
 public | t3                  | table | system
 public | t4                  | table | system
 public | tmp_t0              | table | system
(6 rows)

prod=> select * from t1 limit 3; 
 id | name 
----+------
 10 | abc
 20 | ABC
(2 rows)

# 无法执行DML操作(read_only)
prod=> delete from t1 where id=10;
ERROR:  cannot execute DELETE in a read-only transaction