南大通用GBase 8a用户角色role、用户组的管理

GBase 8a支持用户组，或者叫用户角色。用户组内的用户，自动拥有组的权限，可以更高效的批量管理用户权限。

创建用户组

gbase> create role testdb_admin;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

给用户组授权

gbase> grant all on testdb.* to testdb_admin;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

添加用户到用户组

gbase> create user test1 identified by 'test1';
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

gbase> grant testdb_admin to test1;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

测试用户权限

可以正常登录，并享有相应的权限。

[gbase@gbase_rh7_003 ~]$ gccli -utest1 -ptest1

GBase client 9.5.3.18.123926. Copyright (c) 2004-2020, GBase.  All Rights Reserved.

gbase> show grants;
+--------------------------------------------------------------------------------------------------------+
| Grants for test1@%                                                                                     |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.*.* TO 'test1'@'%' IDENTIFIED BY PASSWORD '*06C0BF5B64ECE2F648B5F048A71903906BA08E5C' |
| GRANT "testdb_admin" TO "test1"                                                                        |
+--------------------------------------------------------------------------------------------------------+
2 rows in set (Elapsed: 00:00:00.00)

gbase> use testdb;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> create table test1(id int);
Query OK, 0 rows affected (Elapsed: 00:00:00.14)

gbase> drop table test1;
Query OK, 0 rows affected (Elapsed: 00:00:00.05)

删除用户组

gbase> drop role testdb_admin;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

测试用户组删除后的用户权限

已经不再享有用户组的。

[gbase@gbase_rh7_003 ~]$ gccli -utest1 -ptest1

GBase client 9.5.3.18.123926. Copyright (c) 2004-2020, GBase.  All Rights Reserved.

gbase> show grants;
+--------------------------------------------------------------------------------------------------------+
| Grants for test1@%                                                                                     |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.*.* TO 'test1'@'%' IDENTIFIED BY PASSWORD '*06C0BF5B64ECE2F648B5F048A71903906BA08E5C' |
+--------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)


gbase> use testdb;
ERROR 1044 (42000): Access denied for user 'test1'@'%' to database 'testdb'

查看用户组ROLE

查看现有版本元数据，只发现了如下方案可以判断ROLE。如果密码为273A88CB37BFAB343D2A9DB9FAC9E15FE959B38A，则为ROLE角色，否则为普通用户。

gbase> select user from user where password='*273A88CB37BFAB343D2A9DB9FAC9E15FE959B38A';
+----------------------------------------------------------------------------------------------------------------------------------+
| user                                                                                                                             |
+----------------------------------------------------------------------------------------------------------------------------------+
| testrole                                                                                                                         |
| testrole2                                                                                                                        |
| testrole3                                                                                                                        |
+----------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (Elapsed: 00:00:00.00)

ROLE角色是不能修改密码的

gbase> set password for testrole=password('1234');
ERROR 1758 (HY000): gcluster dal error: testrole is role.
gbase>

而没有密码，角色/role也就无法登录了。