概述
GBase 8s的用户管理,与其它关系型数据库稍有不同,主要表现在:
- GBase 8s支持使用操作系统的用户,访问数据库。操作系统的用户在授权后,可以直接访问GBase 8s数据库,其它的关系型数据库,通常使用数据库的内置用户访问数据库中的数据,不支持使用操作系统用户直接访问数据库中数据。
- GBase 8s支持创建和使用数据库内部用户,访问数据库中的数据。这一特性在数据库安装后默认是关闭的,需要数据库管理员修改参数,打开这一特性。
- GBase 8s使用库级权限和表级权限管理用户对数据的访问。
- 库级权限为:CONNECT,RESOURCE和DBA。只有获取库级权限,才可以连接到数据库。
- 表级权限为:SELECT,INSERT,UPDATE,DELETE,INDEX,ALTER,REFERENCE,ALL。
- GBase 8s的内部有一个内置的public用户,内部用户对数据库的访问权限为public用户标准权限加上内部用户的专有权限。public用户默认对表有查询,新增,更新,删除,索引权限。数据库管理员可以根据需要,修改public的标准权限。
用户管理
GBase 8s的用户分为:
- 操作系统用户
GBase 8s可以使用操作系统的一个用户,在授权后访问数据库。
- 内部用户
使用数据库内部一个用户进行访问。
内部用户需要使用一个操作系统用户做为代理。
可以创建一个默认用户,简化用户用户的创建。
GBase 8s的用户体系图:
操作系统用户
创建操作系统用户
操作系统的用户创建和删除,可详见操作系统的管理员手册,下面信息以CentOS 7为例,说明了操作系统用户的创建和删除语法。
语法
useradd [options] <user_name>
示例
useradd -u 1001 -g 1001 -d /home/user_01 -m -s /bin/bash user_01
在CentOS 7中,可以简写为
useradd user_o_01
说明:创建后的操作系统用户,不能立即访问GBase 8s中的数据,需要在授权后,才可以连接数据库,并访问数据库中的数据。用户的授权,请参考下面的权限管理。
删除操作系统用户
语法
userdel [options] <user_name>
示例
userdel user_o_01
内部用户
GBase 8s的内部用户默认为不允许使用。如果需要使用GBase 8s的内部用户,需要修改相应参数配置,并使之生效。
配置参数
配置onconfig中的USERMAPPING参数,允许使用内部用户访问GBase 8s。
当该参数为OFF时,不允许使用内部用户访问GBase 8s。
####################################################################
# USERMAPPING - Control access to GBase for users without operating
# system accounts.
####################################################################
# OFF - users without operating system accounts cannot use GBase
# BASIC - users without operating system accounts can use GBase but
# not as privileged users
# ADMIN - users without operating system accounts can use GBase as
# privileged users
####################################################################
USERMAPPING ADMIN
配置操作系统代理用户。
GBase 8s代理用户的配置文件,一般保存在/etc/gbasedbt/allowed.surrogates中。
在该配置文件中,可以配置代理的用户和组。多个用户和组信息,可以用逗号分隔。用户配置信息以users关键字和冒号开头,组配置信息以groups关键字和冒号开头。
代理用户配置示例如下:
[gbasedbt@train ~]$ cat /etc/gbasedbt/allowed.surrogates
#Added by gbasedbt installer - USERS:daemon
#USERS:daemon
users:user_agent,gbasedbt
groups:user_agent,gbasedbt
[gbasedbt@train ~]$
修改后的配置信息不会立即生效,需要数据库管理员更新配置信息。可以通过重启数据库更新信息,也可以通过onmode命令更新该信息。
[gbasedbt@train ~]$ onmode -cache surrogates
Your evaluation license will expire on 2022-06-09 12:00:00
[gbasedbt@train ~]$
注:一定要提前创建好代理用户,且在修改该配置参数后,一定要更新数据库的代理用户缓存信息。
如果在修改配置信息后,未更新配置信息,有可能出现类似下面的错误。
[gbasedbt@train gbasedbt]$ dbaccess - -
Your evaluation license will expire on 2022-06-09 12:00:00
> create user user01 with password '111111' properties uid 1000 group(1000) home '/home/user_agent';
26728: The uid 1000 is not in the /etc/gbasedbt/allowed.surrogates file or in the cache.
Error in line 1
Near character position 96
>
经过上面的配置后,GBase 8s可以创建和使用数据库的内部用户,访问数据库中的数据了。
创建默认内部用户
可以创建一个默认用户,做为其它内部用户的模板。
语法
create default user with properties user <user_name> [properties];
示例
create default user with properties user user_agent home '/home/user_agent';
创建默认用户后,GBase 8s会创建一个名为public的内部用户。
创建内部用户(以默认用户属性为模板)
语法
create user <user_name> with password <password>;
示例
create user user_i_01 with password '111111';
创建内部用户(独立属性)
语法
creaate user <user_name> with password <password> <properties>;
示例
create user user_i_99 with password '111111' properties uid 1002 group(1002) home '/home/user_agent';
权限管理
权限分类说明
GBase 8s的权限分为数据库权限和表权限。
- 数据库权限
权限名称 | 说明 |
---|---|
CONNECT | 级别最低的一种数据库级别的用户权限。拥有该权限的用户可以执行SELETE ,UPDATE,INSERT,DELETE语句,针对数据表执行存储过程,创建数据表的视图,创建临时表。 |
RESOURCE | 拥有该权限的用户除了拥有CONNECT全部权限外,还可以创建新的表,并可以对自己创建的表执行ALTER和DROP操作,创建索引。 |
DBA | 数据库的建立者和拥有者被自动授予这种权限。拥有DBA权限的用户除拥有RESOURCE全部权限外,还可以对其他用户授予或解除CONNECT,RESOURCE,DBA权限,可以对所有数据库对象进行操作。 |
- 表权限
权限名称 | 表 | 列 | 说明 |
---|---|---|---|
INSERT | x | 插入表数据 | |
UPDATE | x | x | 更新表数据 |
DELETE | x | 删除表数据 | |
SELECT | x | x | 查询表数据 |
REFERENCES | x | x | 引用表的列 |
ALTER | x | 添加或删除列,修改列数据类型,添加或删除约束,修改表的锁定模式等 | |
INDEX | x | 建立索引 | |
ALL | x | 全部权限 |
为用户授予权限
语法
database <db_name>;
grant <connect | resource | dba > to <user_name>;
grant <insert | update [column_name1, column_name2, ...] | delete | select [column_name1, column_name2, ...] | references [column_name1, column_name2, ...] | alter | index | all> on <table_name | view_name> to <user_name>;
revoke <connect | resource | dba > from <user_name>;
revoke <insert | update [column_name1, column_name2, ...] | delete | select [column_name1, column_name2, ...] | references [column_name1, column_name2, ...] | alter | index | all> on <table_name | view_name> from <user_name>;
示例
database mydb;
grant connect to user_o_01;
grant resource to user_o_01;
grant insert on t_user to user_02;
revoke connect from user_o_01;
revoke resource from user_01;
revoke insert on t_user from user_02;
revoke delete on t_user from public;
演示
环境准备
使用gbasedbt创建一个数据库,并在数据库中创建两张表。
使用下面的语句创建数据库和表。
create database mydb with log;
create table t_dept(f_deptid int, f_deptname varchar(20));
create table t_user(f_userid int, f_username varchar(20));
执行过程如下。
[gbasedbt@devsvr ~]$ dbaccess - -
Your evaluation license will expire on 2022-09-06 00:00:00
> create database mydb with log;
Database created.
> create table t_dept(f_deptid int, f_deptname varchar(20));
Table created.
> create table t_user(f_userid int, f_username varchar(20));
Table created.
> info tables;
Table name
t_dept t_user
>
查看表权限信息
使用下面的语句,可以查看当前数据库中public用户对表的访问权限。
database <db_name>;
select * from systabauth where tabid >= 100;
> select a.grantor, a.grantee, b.tabname, a.tabauth from systabauth a inner join systables b on a.tabid = b.tabid where a.tabid >= 100;
grantor gbasedbt
grantee public
tabname t_dept
tabauth su-idx---
grantor gbasedbt
grantee public
tabname t_user
tabauth su-idx---
2 row(s) retrieved.
>
tabauth权限说明:
s(选择)、u(更新)、*(列级别特权)、i(插入)、d(删除)、x(索引)、a(改变)和 r(引用)
操作系统用户权限演示
创建操作系统用户
创建两个操作系统用户 user_o_01 和 user_o_02。
[root@devsvr ~]# useradd user_o_01
[root@devsvr ~]# passwd user_o_01
Changing password for user user_o_01.
New password:
BAD PASSWORD: The password is a palindrome
Retype new password:
passwd: all authentication tokens updated successfully.
[root@devsvr ~]# useradd user_o_02
[root@devsvr ~]# passwd user_o_02
Changing password for user user_o_02.
New password:
BAD PASSWORD: The password is a palindrome
Retype new password:
passwd: all authentication tokens updated successfully.
[root@devsvr ~]#
为操作系统用户授权
在mydb数据库下,为用户授权
> grant connect to user_o_01;
Permission granted.
> grant resource to user_o_02;
Permission granted.
>
使用新操作系统用户(connect权限)连接到数据库,验证connect权限。
> connect to 'mydb@gbaseserver' user 'user_o_01';
ENTER PASSWORD:
Disconnected.
Connected.
> info tables;
Table name
t_dept t_user
> insert into t_dept values(1,'dev');
1 row(s) inserted.
> select * from t_dept;
f_deptid f_deptname
1 dev
1 row(s) retrieved.
> update t_dept set f_deptname = 'test' where f_deptid = 1;
1 row(s) updated.
> select * from t_dept;
f_deptid f_deptname
1 test
1 row(s) retrieved.
> delete from t_dept where f_deptid = 1;
1 row(s) deleted.
> select * from t_dept;
f_deptid f_deptname
No rows found.
> create table t_log(f_logid int, f_msg varchar(20));
388: No resource permission.
Error in line 1
Near character position 49
> create index idx_dept on t_dept(f_deptid);
388: No resource permission.
Error in line 1
Near character position 32
>
新操作系统用户user_o_01,被授予connect权限后,可以对数据库中的表进行INSERT/UPDATE/DELETE/SELECT,但不能创建新的表和索引,创建新表和索引需要resource权限。
使用新操作系统用户(resource权限)连接到数据库,验证resource权限。
> connect to 'mydb@gbaseserver' user 'user_o_02';
ENTER PASSWORD:
Disconnected.
Connected.
> insert into t_dept values(1,'dev');
1 row(s) inserted.
> select * from t_dept;
f_deptid f_deptname
1 dev
1 row(s) retrieved.
> update t_dept set f_deptname = 'test' where f_deptid = 1;
1 row(s) updated.
> select * from t_dept;
f_deptid f_deptname
1 test
1 row(s) retrieved.
> delete from t_dept where f_deptid = 1;
1 row(s) deleted.
> select * from t_dept;
f_deptid f_deptname
No rows found.
> create table t_log(f_logid int, f_msg varchar(20));
Table created.
> create index idx_dept on t_dept(f_deptid);
Index created.
> drop table t_log;
Table dropped.
> drop index idx_dept;
Index dropped.
>
新操作系统用户user_o_02,被授予resource权限后,可以对数据库中的表进行INSERT/UPDATE/DELETE/SELECT,创建新的表和索引,删除表和索引。
控制public的表级权限。
使用gbasedbt修改public用户的权限
[gbasedbt@devsvr ~]$ dbaccess - -
Your evaluation license will expire on 2022-09-06 00:00:00
> database mydb;
Database selected.
> select a.grantor, a.grantee, b.tabname, a.tabauth from systabauth a inner join systables b on a.tabid = b.tabid where a.tabid >= 100;
grantor gbasedbt
grantee public
tabname t_dept
tabauth su-idx---
grantor gbasedbt
grantee public
tabname t_user
tabauth su-idx---
2 row(s) retrieved.
> revoke insert,update,delete,index on t_dept from public;
Permission revoked.
> revoke update,delete,index on t_user from public;
Permission revoked.
> select a.grantor, a.grantee, b.tabname, a.tabauth from systabauth a inner join systables b on a.tabid = b.tabid where a.tabid >= 100;
grantor gbasedbt
grantee public
tabname t_dept
tabauth s--------
grantor gbasedbt
grantee public
tabname t_user
tabauth s--i-----
2 row(s) retrieved.
>
修改public后,操作系统用户再次连接到数据库后,对t_dept表只能进行查询操作,无法进行DML操作。对t_user表,可以进行查询和插入操作,但不能进行更新和删除操作。
> connect to 'mydb@gbaseserver' user 'user_o_01';
ENTER PASSWORD:
Disconnected.
Connected.
> info tables;
Table name
t_dept t_user
> select * from t_dept;
f_deptid f_deptname
No rows found.
> insert into t_dept values(1, 'dev');
275: The Insert privilege is required for this operation.
Error in line 1
Near character position 20
> select * from t_user;
f_userid f_username
No rows found.
> insert into t_user values(1,'gbasedbt');
1 row(s) inserted.
> select * from t_user;
f_userid f_username
1 gbasedbt
1 row(s) retrieved.
> update t_user set f_username = 'gbase8s' where f_userid = 1;
273: No UPDATE permission for t_user.
Error in line 1
Near character position 15
> delete from t_user where f_userid = 1;
274: No DELETE permission for t_user.
Error in line 1
Near character position 20
>
控制操作系统用户的表级权限
使用gbasedbt为操作系统用户授权
[gbasedbt@devsvr ~]$ dbaccess - -
Your evaluation license will expire on 2022-09-06 00:00:00
> database mydb;
Database selected.
> select a.grantor, a.grantee, b.tabname, a.tabauth from systabauth a inner join systables b on a.tabid = b.tabid where a.tabid >= 100;
grantor gbasedbt
grantee public
tabname t_dept
tabauth s--------
grantor gbasedbt
grantee public
tabname t_user
tabauth s--i-----
2 row(s) retrieved.
> grant insert, update on t_dept to user_o_01;
Permission granted.
> grant delete,index on t_dept to user_o_02;
Permission granted.
> grant update,delete on t_user to user_o_02;
Permission granted.
> select a.grantor, a.grantee, b.tabname, a.tabauth from systabauth a inner join systables b on a.tabid = b.tabid where a.tabid >= 100;
grantor gbasedbt
grantee public
tabname t_dept
tabauth s--------
grantor gbasedbt
grantee public
tabname t_user
tabauth s--i-----
grantor gbasedbt
grantee user_o_01
tabname t_dept
tabauth -u-i-----
grantor gbasedbt
grantee user_o_02
tabname t_dept
tabauth ----dx---
grantor gbasedbt
grantee user_o_02
tabname t_user
tabauth -u--d----
5 row(s) retrieved.
>
在为用户user_o_01授予t_dept表的INSERT和UPDATE权限后,该用户可以向表中插入数据和更新数据,但不能删除数据。
> connect to 'mydb@gbaseserver' user 'user_o_01';
ENTER PASSWORD:
Disconnected.
Connected.
> info tables;
Table name
t_dept t_user
> select * from t_dept;
f_deptid f_deptname
No rows found.
> insert into t_dept values(1, 'dev');
1 row(s) inserted.
> update t_dept set f_deptname = 'test' where f_deptid = 1;
1 row(s) updated.
> select * from t_dept;
f_deptid f_deptname
1 test
1 row(s) retrieved.
> delete from t_dept where f_deptid = 1;
274: No DELETE permission for t_dept.
Error in line 1
Near character position 20
>
在操作系统用户授权后,可以对t_dept表进行查询和删除操作,可以创建索引和删除索引,但不能进行插入和更新操作。
> connect to 'mydb@gbaseserver' user 'user_o_02';
ENTER PASSWORD:
Disconnected.
Connected.
> info tables;
Table name
t_dept t_user
> select * from t_dept;
f_deptid f_deptname
1 test
1 row(s) retrieved.
> update t_dept set f_deptname = 'dev' where f_deptid = 1;
273: No UPDATE permission for t_dept.
Error in line 1
Near character position 15
> insert into t_dept values(2, 'dev');
275: The Insert privilege is required for this operation.
Error in line 1
Near character position 20
> select * from t_dept;
f_deptid f_deptname
1 test
1 row(s) retrieved.
> delete from t_dept where f_deptid = 1;
1 row(s) deleted.
> select * from t_dept;
f_deptid f_deptname
No rows found.
> create index idx_dept on t_dept(f_deptid);
Index created.
> drop index idx_dept;
Index dropped.
>
对于用户user_o_02,在授权后,可以执行public用户没有的权限。
虽然user_o_02是resource用户,由于取消了public用户的index权限,所以在不单独为用户
> connect to 'mydb@gbaseserver' user 'user_o_02';
ENTER PASSWORD:
Disconnected.
Connected.
> info tables;
Table name
t_dept t_user
> select a.grantor, a.grantee, b.tabname, a.tabauth from systabauth a inner join systables b on a.tabid = b.tabid where a.tabid >= 100;
grantor gbasedbt
grantee public
tabname t_dept
tabauth s--------
grantor gbasedbt
grantee public
tabname t_user
tabauth s--i-----
grantor gbasedbt
grantee user_o_01
tabname t_dept
tabauth -u-i-----
grantor gbasedbt
grantee user_o_02
tabname t_dept
tabauth ----dx---
grantor gbasedbt
grantee user_o_02
tabname t_user
tabauth -u--d----
5 row(s) retrieved.
> select * from t_user;
f_userid f_username
1 gbasedbt
1 row(s) retrieved.
> insert into t_user values(2, 'gbase8s');
1 row(s) inserted.
> update t_user set f_username = 'gbase' where f_userid = 2;
1 row(s) updated.
> select * from t_user;
f_userid f_username
1 gbasedbt
2 gbase
2 row(s) retrieved.
> delete from t_user where f_userid = 2;
1 row(s) deleted.
> select * from t_user;
f_userid f_username
1 gbasedbt
1 row(s) retrieved.
> create index idx_user on t_user(f_userid);
315: No create index permission.
Error in line 1
Near character position 32
>
数据库内部用户权限演示
配置参数,允许使用数据库内部用户
参数USERMAPPING控制是否允许使用数据库内部用户。
当该参数为OFF时,不允许使用内部用户访问GBase 8s。
####################################################################
# USERMAPPING - Control access to GBase for users without operating
# system accounts.
####################################################################
# OFF - users without operating system accounts cannot use GBase
# BASIC - users without operating system accounts can use GBase but
# not as privileged users
# ADMIN - users without operating system accounts can use GBase as
# privileged users
####################################################################
USERMAPPING OFF
可以使用onstat -c查看配置文件中参数内容。可以使用onmode -wf命令,设置参数的值。
[gbasedbt@devsvr ~]$ onstat -c | grep USERMAPPING
Your evaluation license will expire on 2022-09-06 00:00:00
# USERMAPPING - Control access to GBase for users without operating
USERMAPPING OFF
[gbasedbt@devsvr ~]$ onmode -wf USERMAPPING=ADMIN
Your evaluation license will expire on 2022-09-06 00:00:00
Value of USERMAPPING has been changed to ADMIN.
[gbasedbt@devsvr ~]$ onstat -c | grep USERMAPPING
Your evaluation license will expire on 2022-09-06 00:00:00
# USERMAPPING - Control access to GBase for users without operating
USERMAPPING ADMIN
[gbasedbt@devsvr ~]$
配置代理
在root用户下,创建一个用户:user_agent,做为GBase 8s内部用户的代理用户。
[root@devsvr ~]# useradd user_agent
[root@devsvr ~]# passwd user_agent
Changing password for user user_agent.
New password:
BAD PASSWORD: The password is a palindrome
Retype new password:
passwd: all authentication tokens updated successfully.
[root@devsvr ~]#
在root用户下,在/etc/gbasedbt目录下,创建一个名称为allowed.surrogates的文件,并配置代理的用户和组。
[root@devsvr ~]# ls /etc/gbasedbt
ls: cannot access /etc/gbasedbt: No such file or directory
[root@devsvr ~]# mkdir /etc/gbasedbt
[root@devsvr ~]# chown root:gbasedbt /etc/gbasedbt
[root@devsvr ~]# touch /etc/gbasedbt/allowed.surrogates
[root@devsvr ~]# echo "users:user_agent,gbasedbt" > /etc/gbasedbt/allowed.surrogates
[root@devsvr ~]# echo "groups:user_agent,gbasedbt" >> /etc/gbasedbt/allowed.surrogates
使代理的配置生效。
在gbasedbt用户下,执行onmode命令,使配置生效。
[gbasedbt@devsvr ~]$ onmode -cache surrogates
Your evaluation license will expire on 2022-09-06 00:00:00
[gbasedbt@devsvr ~]$
创建默认用户
[gbasedbt@devsvr ~]$ dbaccess - -
Your evaluation license will expire on 2022-09-06 00:00:00
> database sysuser;
Database selected.
> info tables;
Table name
sysauth sysdbsecadmauth sysintauthusers syssecpara
syssurrogategroups syssurrogates systcxattributes systcxusers
systrustedcontext sysusercursess sysuserext sysuserlimits
sysusermap sysuseros
> select * from sysusermap;
username surrogate_id
No rows found.
> create default user with properties user user_agent;
User created.
> select * from sysusermap;
username surrogate_id
public 1
1 row(s) retrieved.
>
创建内部用户
> create user user_i_01 with password '111111';
User created.
> create user user_i_02 with password '111111';
User created.
> select * from sysusermap;
username surrogate_id
public 1
user_i_01 1
user_i_02 1
3 row(s) retrieved.
>
为用户授权
为用户授予connect/resource权限。
> database mydb;
Database closed.
Database selected.
> grant connect to user_i_01;
Permission granted.
> grant resource to user_i_02;
Permission granted.
>
查看当前权限信息
当前数据库的表的权限信息,保存在systabauth表中。
> select a.grantor, a.grantee, b.tabname, a.tabauth from systabauth a inner join systables b on a.tabid = b.tabid where a.tabid >= 100;
grantor gbasedbt
grantee public
tabname t_dept
tabauth s--------
grantor gbasedbt
grantee public
tabname t_user
tabauth s--i-----
grantor gbasedbt
grantee user_o_01
tabname t_dept
tabauth -u-i-----
grantor gbasedbt
grantee user_o_02
tabname t_dept
tabauth ----dx---
grantor gbasedbt
grantee user_o_02
tabname t_user
tabauth -u--d----
5 row(s) retrieved.
>
通过上面的查询结果,可以看出,目前mydb数据库中,只有t_dept和t_user两张表,public用户对t_dept表有查询权限,对t_user表有查询和插入权限。
对于操作系统用户user_o_01,授予了对t_dept表的插入和更新权限,对于操作系统用户user_o_02,授予了对t_dept表的删除和创建索引权限,对t_user表,授予了更新和删除权限。
暂时没有对数据库内部用户user_i_01和user_i_02的授权信息。
验证数据库内部用户权限(从public用户继承)
由于暂时未对数据库内部用户user_i_01和user_i_02进行表权限的授权,user_i_01被授予了connect权限,user_i_02被授予了resource权限,所以两个用户对t_dept表只有查询权限,对t_user表,有查询和插入的权限。
user_i_02可以新建和删除表。新建表可以进行查询,插入,更新,删除操作。
user_i_01用户操作演示:
> connect to 'mydb@gbaseserver' user 'user_i_01';
ENTER PASSWORD:
Disconnected.
Connected.
> info tables;
Table name
t_dept t_user
> select * from t_dept;
f_deptid f_deptname
No rows found.
> insert into t_dept values(1, 'gbasedbt');
275: The Insert privilege is required for this operation.
Error in line 1
Near character position 20
> select * from t_user;
f_userid f_username
1 gbasedbt
1 row(s) retrieved.
> insert into t_user values(2, 'gbase8s');
1 row(s) inserted.
> select * from t_user;
f_userid f_username
1 gbasedbt
2 gbase8s
2 row(s) retrieved.
> update t_user set f_username = 'gbase' where f_userid = 2;
273: No UPDATE permission for t_user.
Error in line 1
Near character position 15
> delete from t_user where f_userid = 2;
274: No DELETE permission for t_user.
Error in line 1
Near character position 20
> create table t_log(f_logid int, f_msg varchar(20));
388: No resource permission.
Error in line 1
Near character position 49
>
user_i_02用户操作演示:
> connect to 'mydb@gbaseserver' user 'user_i_02';
ENTER PASSWORD:
Disconnected.
Connected.
> info tables;
Table name
t_dept t_user
> select * from t_dept;
f_deptid f_deptname
No rows found.
> insert into t_dept values(1, 'gbasedbt');
275: The Insert privilege is required for this operation.
Error in line 1
Near character position 20
> create table t_log(f_logid int, f_msg varchar(20));
Table created.
> insert into t_log(1, 'this is a demo record');
201: A syntax error has occurred.
Error in line 1
Near character position 19
> insert into t_log values(1, 'this is a demo record');
1 row(s) inserted.
> select * from t_log;
f_logid f_msg
1 this is a demo recor
1 row(s) retrieved.
> update t_log set f_msg = 'hello world' where f_logid = 1;
1 row(s) updated.
> select * from t_log;
f_logid f_msg
1 hello world
1 row(s) retrieved.
> delete from t_log where f_logid = 1;
1 row(s) deleted.
> drop table t_log;
Table dropped.
> select * from t_user;
f_userid f_username
1 gbasedbt
2 gbase8s
2 row(s) retrieved.
> insert into t_user values(3, 'gbase');
1 row(s) inserted.
> select * from t_user;
f_userid f_username
1 gbasedbt
2 gbase8s
3 gbase
3 row(s) retrieved.
> update t_user set f_username = 'gbase_user' where f_userid = 3;
273: No UPDATE permission for t_user.
Error in line 1
Near character position 15
> delete from t_user where f_userid = 3;
274: No DELETE permission for t_user.
Error in line 1
Near character position 20
> create index idx_user on t_user(f_userid);
315: No create index permission.
Error in line 1
Near character position 32
>
验证数据库内部用户权限(管理员单独授权)
在上例是,新建的内部用户,对t_dept表只有查询权限,对t_user表只有查询和插入权限。下面演示如何让user_i_01对t_dept表有插入和删除权限,user_i_01对t_dept表有更新和创建索引权限。
[gbasedbt@devsvr ~]$ dbaccess - -
Your evaluation license will expire on 2022-09-06 00:00:00
> database mydb;
Database selected.
> grant insert,delete on t_dept to user_i_01;
Permission granted.
> grant update,index on t_dept to user_i_02;
Permission granted.
> connect to 'mydb@gbaseserver' user 'user_i_01';
ENTER PASSWORD:
Disconnected.
Connected.
> select * from t_dept;
f_deptid f_deptname
No rows found.
> insert into t_dept values(1, 'dev');
1 row(s) inserted.
> select * from t_dept;
f_deptid f_deptname
1 dev
1 row(s) retrieved.
> update t_dept set f_deptname = 'test' where f_deptid = 1;
273: No UPDATE permission for t_dept.
Error in line 1
Near character position 15
> delete from t_dept where f_deptid = 1;
1 row(s) deleted.
> connect to 'mydb@gbaseserver' user 'user_i_02';
ENTER PASSWORD:
Disconnected.
Connected.
> select * from t_dept;
f_deptid f_deptname
No rows found.
> insert into t_dept values(1, 'dev');
275: The Insert privilege is required for this operation.
Error in line 1
Near character position 20
> connect to 'mydb@gbaseserver' user 'user_i_01';
ENTER PASSWORD:
Disconnected.
Connected.
> insert into t_dept values(1, 'dev');
1 row(s) inserted.
> insert into t_dept values(2, 'test');
1 row(s) inserted.
> connect to 'mydb@gbaseserver' user 'user_i_02';
ENTER PASSWORD:
Disconnected.
Connected.
> select * from t_dept;
f_deptid f_deptname
1 dev
2 test
2 row(s) retrieved.
> update t_dept set f_deptname = 'market' where f_deptid = 2;
1 row(s) updated.
> select * from t_dept;
f_deptid f_deptname
1 dev
2 market
2 row(s) retrieved.
> delete from t_dept where f_deptid = 2;
274: No DELETE permission for t_dept.
Error in line 1
Near character position 20
> create index idx_dept on t_dept(f_deptid);
Index created.
> drop index idx_dept;
Index dropped.
>