GBase 8a
运维管理
文章

GBase8a 数据库用户管理

可口
发表于2024-05-30 15:15:271282次浏览2个评论

1.用户管理

1.1.创建新用户

操作场景

创建新的 GBase 8a MPP Cluster 数据库用户账号。

前提条件

执行此操作的用户必须有全局 CREATE USER 权限。

语法格式

使用 CREATE USER 语句创建新的 GBase 8a MPP Cluster 数据库用户帐号，创建语 法如下:

CREATE USER user [IDENTIFIED BY [PASSWORD] [password]]

​

表 1.1-1 参数说明

​

参数名称说明
USER user指定要创建的帐号名称。user 支持如下几种方式的书写: 1.任何主机:user@'%' 2.本机:user@'localhost' 3.网段:user@'192.168.0.0'\4. ip 地址:user@'192.168.10.6' 5.与 user@'%'等价:user \6. user 长度支持 128 字符
[IDENTIFIED BY [PASSWORD] [password]]通过可选择的 IDENTIFIED BY 语句可以给帐号赋予一个密 码。特别注意的是，密码设置为纯文本格式时可以省略 PASSWORD 关键字。password 是帐号密码

示例

说明

  • 对每一个未赋权的帐号，初始时只有登录数据库的权限。

示例 1

创建 admin 用户，密码为admin.

gbase> CREATE USER admin IDENTIFIED BY 'admin';
Query OK, 0 rows affected
gbase> EXIT
Bye
$ gccli -uadmin –padmin
GBase client 9.5.3.22.126635. Copyright (c) 2004-2022, GBase.  All Rights Reserved.
All Rights

示例 2

使用超级用户 root 登录，创建一个用户 user1。

$ gccli -uroot -p
Enter password:
GBase client 9.5.3.22.126635. Copyright (c) 2004-2022, GBase.  All Rights Reserved.
​
gbase> CREATE USER user1;
Query OK, 0 rows affected

1.2.修改用户名

操作场景

重命名已经存在的 GBase 8a MPP Cluster 用户。

前提条件

修改用户名必须具有全局 CREATE USER 权限。

语法格式

使用 RENAME USER 语句修改用户名称，语法格式如下:

RENAME USER old_user TO new_user

说明

修改用户名称后，权限保留不变。

示例

 gbase> RENAME USER user1 to user2;
 Query OK, 0 rows affecte

1.3.删除用户

操作场景

删除已经存在的 GBase 8a MPP Cluster 用户。

前提条件

删除用户信息必须具有全局 CREATE USER 权限。

语法格式

使用 DROP USER 语句删除用户，语法格式如下:

DROP USER user;

说明

DROP USER 不会自动关闭任何打开的用户会话。

当存在使用 此用户打开的会话时，删除此用户并不会影响这些已打开会话的访问权限，直到会话关闭。

示例

删除 admin 用户。

 gbase> DROP USER admin;
Query OK, 0 rows affected

1.4.修改用户密码

语法格式

使用 SET PASSWORD 修改用户密码，语法格式如下:

SET PASSWORD [FOR user] = PASSWORD('newpassword')

​

表 1.4-1 参数说明

​

参数名称说明
FOR user指定要修改密码的帐户名称，如果省略，表示修改当前 登录集群帐户的密码。
PASSWORD('newpassword')指定帐户的新密码。

示例

示例 1

为当前登录集群的帐户设置密码。

$ gccli –uroot
GBase client 9.5.3.22.126635. Copyright (c) 2004-2020, GBase.All Rights Reserved
gbase> SET PASSWORD = PASSWORD('admin');
Query OK, 0 rows affected
gbase> EXIT
Bye

使用新密码重新登录数据库:

$ gccli -uroot -p
Enter password:
GBase client 9.5.3.22.126635. Copyright (c) 2004-2020, GBase. All Rights Reserved.

示例 2

为指定的登录集群的用户设置密码。

$ gccli -uroot --nice_time_format -p
Enter password:
GBase client 9.5.3.22.126635. Copyright (c) 2004-2020, GBase. All Rights Reserved.
gbase> SET PASSWORD FOR admin = PASSWORD('adminnew');
Query OK, 0 rows affected
gbase> EXIT
Bye
$ gccli -uadmin --nice_time_format -p
Enter password:
GBase client 9.5.3.22.126635. Copyright (c) 2004-2020, GBase. Reserved.
All Rights

2.权限管理

GRANT 和 REVOKE 语句使用

操作场景

GRANT 和 REVOKE 语句允许系统管理员处理用户权限的赋予与收回。

前提条件

要使用 GRANT 或 REVOKE，用户必须拥有 GRANT OPTION 权限，可以授予或收 回用户权限。

语法格式

GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user IDENTIFIED BY [[PASSWORD] [password]]
[WITH with_option ...] object_type:
TABLE | FUNCTION | PROCEDURE priv_level:
* | *.* | database_name.* |database_name.table_name | table_name | database_name.routine_nameREVOKE
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
FROM user
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user

说明

对于 GRANT 和 REVOKE 语句，priv_level 可以授予不同级别的权限:

  • 全局级(Global level) :全局权限应用到给定服务器的所有数据库上。这些权限存储在gbase.user表中。只可以通过 GRANT ALL ON ..*和 REVOKE ALL ON ..*授予和收回全局权限;

  • 数据库级(Database level) :数据库权限应用于给定数据库的所有对象 上。这些权限存储在gbase.db和gbase.host表中。只可以通过 GRANT ALL ON vc_name.db_name...和 REVOKE ALL ON vcname.db_name.授予和收 回数据库权限;

  • 表级(Table level) :表权限应用于给定表的所有列。这些权限存储在gbase.tables_priv表 中 。 只可以通过 GRANT ALL ON vcname.db_name.tbl_name 和 REVOKE ALL ON vcname.db_name.tbl_name 授予和收回表权限;

    列级(column level) :列权限应用于表中的指定列。这些权限存储在gbase.tables_priv表中。只可以通过 GRANT SELECT,INSERT,UPDATE (column) ON vcname.db_name.tb1_name 和 REVOKE SELECT(column) ON vcname.db_name.tb1_name 授予和收回列权限。

示例

示例 1:为用户 admin 赋予 t 表中 a 列的 select 权限。

gbase> CREATE TABLE t(a int,b varchar(40));
Query OK, 0 rows affected
gbase> INSERT INTO t VALUES (1,'test'),(2,'share');
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0
gbase> GRANT SELECT(a) ON test.t TO admin;
Query OK, 0 rows affected
gbase> SELECT * FROM gbase.tables_priv;
+------+------+-------+----------+-------------------+---------------------+----------+-------------+
| Host |  Db  |  User |Table_name|      Grantor      |      Timestamp      |Table_priv| Column_priv |
+------+------+-------+----------+-------------------+---------------------+----------+-------------+
| %    | test | admin |     t    |root@192.168.10.115| 2013-10-18 14:52:33 |          |   Select    |
+------+------+-------+----------+-------------------+---------------------+----------+-------------+

示例 2:收回 t 表中列 a 的 SELECT 权限。

gbase> REVOKE SELECT(a) ON test.t FROM admin;
Query OK, 0 rows affected
gbase> SELECT * FROM gbase.tables_priv;
Empty set

注意:

对用户赋予某指定权限后，若该对象被删除，而用户的权限未被回收，则新建同名对象后，用户即拥有该对象的权限。

权限级别

数据库权限分为:

  • 数据库对象操作类权限;

  • 数据操作类权限;

  • 存储过程和自定义函数执行权限;

  • 数据查看类权限;

  • 数据库(包含用户管理)管理;

    下表是 GRANT 和 REVOKE 中的权限级别，语法格式中 priv_type 指定为下列任一 种。

​

表 2-1 参数说明

​

权限意义
ALL [PRIVILEGES]设置除 GRANT OPTION 之外的所有简单权限
ALTER允许使用 ALTER TABLE
ALTER ROUTINE更改或取消已存储的子程序
CREATE允许使用 CREATE TABLE
CREATE ROUTINE创建已存储的子程序
CREATE TEMPORARY TABLE允许使用 CREATE TEMPORARY TABLE
CREATE USER允许使用 CREATE USER, DROP USER, RENAME USER 和 REVOKE ALL PRIVILEGES
CREATE VIEW允许使用 CREATE VIEW
DELETE允许使用 DELETE
DROP允许使用 DROP TABLE
EXECUTE允许用户运行已存储的子程序
FILE允许使用 SELECT...FROM TABLE_NAME INTO OUTFILE、LOAD DATA 等
GRANT OPTION允许授予权限
INDEX允许使用 CREATE INDEX 和 DROP INDEX
INSERT允许使用 INSERT
PROCESS允许使用 SHOW FULL PROCESSLISTS
RELOAD允许使用 FLUSH
SELECT允许使用 SELECT
SHOW DATABASESSHOW DATABASES 显示所有数据库
SHOW VIEW允许使用 SHOW CREATE VIEW
SHUTDOWN允许使用 gbaseadmin shutdown
SUPER允许使用 KILL 和 SET GLOBAL 语句
UPDATE允许使用 UPDATE
USAGE仅仅用于连接登录数据库，主要用来设置 with option 部分
EVENT创建、修改、删除 EVENT 的权限

3.示例说明

前提条件

创建新用户时，执行此操作的用户必须有全局 CREATE USER 权限（建议使用dba用户），对用户赋权时，对应库名要存在。

 

 

库存在

库不存在

 

 

库名

创建用户

数据库

赋权库

新建库

用户验证权限

新用户授权-流程图

示例说明：

  • 使用dba用户登录数据库，并创建新库分别为：test_dmc、test_etl、test_crm;

  • 创建新用户test_dmc,密码为‘test_dmc’;

  • 将test_dmc、test_etl、test_crm，赋权给test_dmc用户;

  • test_dmc用户权限设定，对test_dmc库所有权限，对test_etl库只能select权限，对test_crm只有create table、select、insert权限;

  • 查看test_dmc用户权限，并时进行验证;

  • 收回权限test_dmc，并删除test_dmc用户;

步骤1：dba用户登录

使用gbase用户登录数据库;

[gbase@mpp953 ~]$ gccli -uroot -pgbase
​
GBase client 9.5.3.22.126635. Copyright (c) 2004-2022, GBase.  All Rights Reserved.

步骤2：创建新库

**创建新库分别为，test_dmc、test_etl、test_crm;**gbase> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| gbase              |
| gctmpdb            |
| gclusterdb         |
| testdb             |
+--------------------+
6 rows in set (Elapsed: 00:00:00.00)

对用户赋权限时，对应库名要存在，反之，使用CREATE DATABASE *databasename新建库.

gbase> CREATE DATABASE test_dmc;
Query OK, 1 row affected (Elapsed: 00:00:00.02)
​
gbase> CREATE DATABASE test_etl;
Query OK, 1 row affected (Elapsed: 00:00:00.00)
​
gbase> CREATE DATABASE test_crm;
Query OK, 1 row affected (Elapsed: 00:00:00.01)
​
gbase> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| gbase              |
| gctmpdb            |
| gclusterdb         |
| test_crm           |
| test_dmc           |
| test_etl           |
| testdb             |
+--------------------+
9 rows in set (Elapsed: 00:00:00.00)

步骤3：创建新用户

创建新用户test_dmc,密码为‘test_dmc’;

gbase> CREATE USER test_dmc IDENTIFIED BY 'test_dmc';
Query OK, 0 rows affected (Elapsed: 00:00:00.05)
​
​

步骤4：用户赋权

将test_dmc、test_etl、test_crm，赋权给test_dmc用户;

  • 将test_dmc库，test_dmc用户，具有test_dmc库所有权限;

  • 对test_etl库，test_dmc用户，具有select权限;

  • 对test_crm库，test_dmc用户，具有create table、select、insert权限;

-- 使用 all 参数，将test_dmc库所有权限，赋权给test_dmc用户;
​
gbase> GRANT ALL PRIVILEGES ON test_dmc.* TO test_dmc;    
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
​
gbase> SHOW GRANTS FOR test_dmc;
+-----------------------------------------------------------------------------------------------+
| Grants for test_dmc@%                                                                         |
+-----------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.*.* TO 'test_dmc'@'%' IDENTIFIED BY PASSWORD '*4D13C9D8FFE2F7628D9DE333C27B56DCCE7F9502' |
| GRANT ALL PRIVILEGES ON "vc00001"."test_dmc".* TO 'test_dmc'@'%'                              |
+-----------------------------------------------------------------------------------------------+
2 rows in set (Elapsed: 00:00:00.00)--text-color: #202124;-- 使用 select 参数，将test_dmc库所有权限，赋权给test_dmc用户;
​
gbase> GRANT SELECT ON test_etl.* TO test_dmc;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
​
gbase> SHOW GRANTS FOR test_dmc;
+------------------------------------------------------------------------------------------------+
| Grants for test_dmc@%                                                                          |
+------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.*.* TO 'test_dmc'@'%' IDENTIFIED BY PASSWORD '*4D13C9D8FFE2F7628D9DE333C27B56DCCE7F9502' |
| GRANT ALL PRIVILEGES ON "vc00001"."test_dmc".* TO 'test_dmc'@'%'                               |
| GRANT SELECT ON "vc00001"."test_etl".* TO 'test_dmc'@'%'                                       |
+------------------------------------------------------------------------------------------------+
3 rows in set (Elapsed: 00:00:00.00)

 

-- 对test_crm只有create、select、insert权限;
​
gbase> GRANT create,select,insert  ON test_crm.* TO test_dmc;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
​
gbase>  SHOW GRANTS FOR test_dmc;
+------------------------------------------------------------------------------------------------+
| Grants for test_dmc@%                                                                          |
+------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.*.* TO 'test_dmc'@'%' IDENTIFIED BY PASSWORD '*4D13C9D8FFE2F7628D9DE333C27B56DCCE7F9502' |
| GRANT ALL PRIVILEGES ON "vc00001"."test_dmc".* TO 'test_dmc'@'%'                               |
| GRANT SELECT ON "vc00001"."test_etl".* TO 'test_dmc'@'%'                                       |
| GRANT SELECT, INSERT, CREATE ON "vc00001"."test_crm".* TO 'test_dmc'@'%'                       |
+------------------------------------------------------------------------------------------------+
4 rows in set (Elapsed: 00:00:00.00)

步骤5：验证用户权限

查看test_dmc用户权限，并时进行验证;

gbase>  SHOW GRANTS FOR test_dmc;
+-----------------------------------------------------------------------------------------------+| Grants for test_dmc@%                                                                            |
+------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.*.* TO 'test_dmc'@'%' IDENTIFIED BY PASSWORD '*4D13C9D8FFE2F7628D9DE333C27B56DCCE7F9502' |
| GRANT ALL PRIVILEGES ON "vc00001"."test_dmc".* TO 'test_dmc'@'%'                               |
| GRANT SELECT ON "vc00001"."test_etl".* TO 'test_dmc'@'%'                                       |
| GRANT SELECT, INSERT, CREATE ON "vc00001"."test_crm".* TO 'test_dmc'@'%'                       |
+------------------------------------------------------------------------------------------------+
4 rows in set (Elapsed: 00:00:00.00)

  • 使用test_dmc用户登录，密码为'test_dmc';

[gbase@mpp953 ~]$ gccli -utest_dmc -ptest_dmc
​
GBase client 9.5.3.22.126635. Copyright (c) 2004-2022, GBase.  All Rights Reserved.
​
gbase> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| test_crm           |
| test_dmc           |
| test_etl           |
+--------------------+
5 rows in set (Elapsed: 00:00:00.17)
​
gbase> use test_crm;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
​
gbase> use test_etl;
Query OK, 0 rows affected (Elapsed: 00:00:00.04)
​
gbase> use test_dmc;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

  • 将test_dmc库所有权限，赋权给test_dmc用户;

gbase> use test_dmc;
Query OK, 0 rows affected (Elapsed: 00:00:00.19)
​
gbase> create table t1 (vid int);
Query OK, 0 rows affected (Elapsed: 00:00:00.03)
​
gbase> insert into t1 values (1);
Query OK, 1 row affected (Elapsed: 00:00:00.08)
​
gbase> insert into t1 values (2);
Query OK, 1 row affected (Elapsed: 00:00:00.18)
​
gbase> insert into t1 values (3);
Query OK, 1 row affected (Elapsed: 00:00:00.06)
​
gbase> select * from t1;
+------+
| vid  |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (Elapsed: 00:00:00.00)
​
gbase> delete from t1 where vid = 1;
Query OK, 1 row affected (Elapsed: 00:00:00.04)
​
gbase> select * from t1;
+------+
| vid  |
+------+
|    2 |
|    3 |
+------+
2 rows in set (Elapsed: 00:00:00.01)
​
gbase> show tables;
+--------------------+
| Tables_in_test_dmc |
+--------------------+
| t1                 |
+--------------------+
1 row in set (Elapsed: 00:00:00.11)
​
​
gbase> drop table t1;
Query OK, 0 rows affected (Elapsed: 00:00:00.03)
​
gbase>  DELIMITER //
gbase> CREATE PROCEDURE dodeclare (p1 INT)
    -> BEGIN
    -> DECLARE intX INT;
    -> SET intX = 0;
    -> REPEAT SET intX = intX + 1; UNTIL intX > p1 END REPEAT; SELECT intX;
    -> END //
Query OK, 0 rows affected (Elapsed: 00:00:00.19)
​
gbase>  DELIMITER ;
gbase> CALL dodeclare(1000);
+------+
| intX |
+------+
| 1001 |
+------+
1 row in set (Elapsed: 00:00:00.00)
​
gbase> DROP PROCEDURE IF EXISTS dodeclare;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

  • 对test_etl库只能select权限;

gbase> use test_etl;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
​
gbase> select * from t2;
+------+
| vid  |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+
6 rows in set (Elapsed: 00:00:00.01)
​
gbase> create table t4 (vid int);
ERROR 1142 (42000): CREATE command denied to user 'test_dmc'@'localhost' for table 't4'
​
gbase> insert into t2 values (7);
ERROR 1142 (42000): INSERT command denied to user 'test_dmc'@'localhost' for table 't2'
​
gbase> update t2 set vid = 100 where vid = 1;
ERROR 1142 (42000): UPDATE command denied to user 'test_dmc'@'localhost' for table 't2'
​
gbase> drop table t2;
ERROR 1142 (42000): DROP command denied to user 'test_dmc'@'localhost' for table 't2'

  • 对test_crm只有create table、select、insert权限;

gbase> use test_crm;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
​
gbase> create table t3 (vid int);
Query OK, 0 rows affected (Elapsed: 00:00:00.04)
​
gbase> select * from t3;
+------+
| vid  |
+------+
|    1 |
+------+
1 row in set (Elapsed: 00:00:00.15)
​
gbase> delete from t3;
ERROR 1142 (42000): DELETE command denied to user 'test_dmc'@'localhost' for table 't3'
gbase> drop table t3;
ERROR 1142 (42000): DROP command denied to user 'test_dmc'@'localhost' for table 't3'

步骤6：收回权限

收回权限test_dmc，并删除test_dmc用户;

[gbase@mpp953 ~]$ gccli -uroot -pgbase
​
GBase client 9.5.3.22.126635. Copyright (c) 2004-2022, GBase.  All Rights Reserved.
​
gbase> REVOKE ALL  ON test_dmc.* FROM test_dmc;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
​
gbase> REVOKE SELECT  ON test_etl.* FROM test_dmc;
Query OK, 0 rows affected (Elapsed: 00:00:00.05)
​
gbase> REVOKE create,select,insert  ON test_crm.* FROM test_dmc;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
​
 flush privileges;
​
gbase> SHOW GRANTS FOR test_dmc;
+------------------------------------------------------------------------------------------------+
| Grants for test_dmc@%                                                                          |
+------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.*.* TO 'test_dmc'@'%' IDENTIFIED BY PASSWORD *4D13C9D8FFE2F7628D9DE333C27B56DCCE7F9502' |
+------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
​
gbase>  DROP USER test_dmc;
Query OK, 0 rows affected (Elapsed: 00:00:00.02)

grant option 授权的用户可以将权限授权给其他用户

max_queries_per_hour count 每小时可以执行count次查询

max_updates_per_hour count 每小时可以执行count次更新

max_connections_per_hour count 每小时可以建立count次连接

max_user_connections count 单个用户可以同时具有count个连接

撤销权限的巨坑

撤销语句要和授权语句完全一致

假如你给用户 pig@% 授权的语句如下：

gbase> GRANT SELECT ON test.user TO 'pig'@'%';

那么使用如下的语句，根本无法撤销上述的查询权限（即不能撤销该用户对 test 数据库中 user 表的 SELECT 操作权限）：

gbase> REVOKE SELECT ON *.* FROM 'pig'@'%';

而且使用下面的语句，还会报错：

gbase> REVOKE SELECT ON test.* FROM 'pig'@'%';
ERROR 1141 (42000): There is no such grant defined for user 'pig' on host '%'

同样地，如果你使用下面的语句给用户 pig@% 授权：

GRANT SELECT ON *.* TO 'pig'@'%';

那么使用下面的语句，根本无法撤销该用户对 test 数据库中 user 表的 SELECT 权限：

REVOKE SELECT ON test.user FROM 'pig'@'%';

权限 USAGE 是无法撤销的

创建用户的时候，默认会赋予 USAGE 权限：

GRANT USAGE ON *.* TO 'user_name'@'host';

但是 USAGE 权限无法撤销。


 

评论

登录后才可以发表评论
崔哥发表于 8个月前
醉拍春衫惜旧香。天将离恨恼疏狂。年年陌上生秋草，日日楼中到夕阳。云渺渺，水茫茫。征人归路许多长。相思本是无凭语，莫向花笺费泪行。
用户头像
levvel发表于 6个月前
人生贵相知，何用金与钱

热门帖子