用户的只读状态
在KingbaseES数据库中,除了对用户授予特定的对象的select权限方法外,还可以将事务设置成只读状态。在该状态下,用户不能执行数据库操作种类的操作,极大的限制了用户除了select权限以外操作,从而表现为只读用户的特征。当用户处于只读事务状态,其本身对于数据库对象的权限没有改变。当改变用户的只读状态后,即可恢复用户的各种权限。
KingbaseES提供系统存储过程alteruserreadonly来设置用户的只读状态。
alteruserreadonly储存过程
语法:
PROCEDURE alteruserreadonly(database VARCHAR, username VARCHAR, isReadOnly BOOLEAN)
参数说明:
database 数据库名称,为空时表示全部数据库。
Username 用户名。
isReadOnly 是否为只读。
示例:
test=# \c test system
You are now connected to database "test" as userName "system".
test=# create user u1 with password '12345678ab';
CREATE ROLE
test=# create user u2 with password '12345678ab';
CREATE ROLE
test=# \du
角色列表
角色名称 | 属性 | 成员属于
----------+--------------------------------------------+----------
kcluster | 无法登录 | {}
sao | 没有继承 | {}
sso | 没有继承 | {}
system | 超级用户, 建立角色, 建立 DB, 复制, 绕过RLS | {}
u1 | | {}
u2 | | {}
test=# \c - u1
You are now connected to database "test" as userName "u1".
test=> create table t1(id int);
CREATE TABLE
test=> insert into t1 values (1);
INSERT 0 1
test=> \c test system
You are now connected to database "test" as userName "system".
test=# call alteruserreadonly ('','u1',true);
CALL
test=# \c - u2
You are now connected to database "test" as userName "u2".
test=> create table a(id int);
CREATE TABLE
test=> grant select on a to u1;
GRANT
test=> \c - u1
You are now connected to database "test" as userName "u1".
test=> create table t1(id int);
ERROR: ReadOnlyUser cannot execute CREATE TABLE.
test=> show default_transaction_read_only ;
default_transaction_read_only
-------------------------------
on
(1 行记录)
test=> show transaction_read_only ;
transaction_read_only
-----------------------
on
(1 行记录)
test=> show read_only_user ;
read_only_user
----------------
on
(1 行记录)
test=> select * from a;
id
----
(0 行记录)
test=> select * from t1;
id
----
1
(1 行记录)
test=> insert into t1 values (2);
ERROR: ReadOnlyUser cannot execute INSERT.
test=> insert into A values (1);
ERROR: ReadOnlyUser cannot execute INSERT.
test=> \c test system
You are now connected to database "test" as userName "system".
test=# call alteruserreadonly ('','u1',false);
CALL
test=# drop table t1,a;
DROP TABLE
test=# drop user u1,u2;
DROP ROLE