配置参数，允许使用数据库内部用户
参数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.
> 「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
