本贴将介绍非虚拟集群情况下新建用户并对用户赋予数据库权限操作说明，及虚拟集群情况下赋权限的方法。
操作场景
GRANT语句允许系统管理员处理用户权限的赋予。
前提条件
要使用GRANT，用户必须拥有GRANT OPTION权限，可以授予用户权限。
语句说明
1）创建用户并赋权限语句
CREATE USER user IDENTIFIED BY 'user';
GRANT ALL ON *.* TO user;
GRANT SELECT ON *.* TO user;
GRANT ALL ON *.*.* TO user;
2）查询权限语句
select * from gbase.user\G
show grants for user;
示例说明
示例1及示例2为非虚拟集群情况下新建用户并对用户赋予数据库权限，示例3为虚拟集群情况下新建用户并对用户赋予数据库权限。
示例1：
先利用root用户建库建表。
create database test;
use test;
CREATE TABLE sales(
sales_employee VARCHAR(50) NOT NULL,
fiscal_year INT NOT NULL,
sale DECIMAL(14,2) NOT NULL);
# 赋予新用户所有权限
CREATE USER user1 IDENTIFIED BY 'user1';
GRANT ALL ON *.* TO user1;
# 查看用户权限
gbase> show grants for user1;
+-----------------------------------------------------------------------------------------------------------------+
| Grants for user1@% |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.*.* TO 'user1'@'%' IDENTIFIED BY PASSWORD '*34D3B87A652E7F0D1D371C3DBF28E291705468C4' |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
# 利用新建用户user1对表进行insert、select及load操作
[gbase@node35 gcinstall]$ gccli -uuser1 -puser1
gbase> use test;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> INSERT INTO sales(sales_employee,fiscal_year,sale)
-> VALUES('Bob',2016,100),
-> ('Bob',2017,150),
-> ('Bob',2018,200),
-> ('Alice',2016,150),
-> ('Alice',2017,100),
-> ('Alice',2018,200),
-> ('John',2016,200),
-> ('John',2017,150),
-> ('John',2018,250);
Query OK, 9 rows affected (Elapsed: 00:00:00.01)
Records: 9 Duplicates: 0 Warnings: 0
gbase> select * from sales;
+----------------+-------------+--------+
| sales_employee | fiscal_year | sale |
+----------------+-------------+--------+
| Bob | 2016 | 100.00 |
| Bob | 2017 | 150.00 |
| Bob | 2018 | 200.00 |
| Alice | 2016 | 150.00 |
| Alice | 2017 | 100.00 |
| Alice | 2018 | 200.00 |
| John | 2016 | 200.00 |
| John | 2017 | 150.00 |
| John | 2018 | 250.00 |
+----------------+-------------+--------+
9 rows in set (Elapsed: 00:00:00.01)
gbase> load data infile 'ftp://gbase:gbase@××××××××××//home/gbase/window/wf_bak/sales.dat' into table test.sales data_format 3 fields terminated by '|';
Query OK, 0 rows affected (Elapsed: 00:00:28.44)
Task 11 finished, Loaded 20000000 records, Skipped 0 records
示例2：
# 创建新建用户users但是不赋予任何权限
[gbase@node35 gcinstall]$ gccli -uroot
gbase> CREATE USER user2 IDENTIFIED BY 'user2';
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
# 利用新用户user2进行select操作
[gbase@node35 gcinstall]$ gccli -uuser2 -puser2
gbase> select * from test.sales;
ERROR 1142 (42000): SELECT command denied to user 'user2'@'localhost' for table 'sales'
# 赋予user2新用户select权限
[gbase@node35 gcinstall]$ gccli -uroot
gbase> GRANT SELECT ON *.* TO user2;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
gbase> show grants for user2;
+---------------------------------------------------------------------------------------------------------------+
| Grants for user2@% |
+---------------------------------------------------------------------------------------------------------------+
| GRANT SELECT ON *.*.* TO 'user2'@'%' IDENTIFIED BY PASSWORD '*12A20BE57AF67CBF230D55FD33FBAF5230CFDBC4' |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
# 利用新用户user2进行select操作
[gbase@node35 gcinstall]$ gccli -uuser2 -puser2
gbase> select * from test.sales;
+----------------+-------------+--------+
| sales_employee | fiscal_year | sale |
+----------------+-------------+--------+
| Bob | 2016 | 100.00 |
| Bob | 2017 | 150.00 |
| Bob | 2018 | 200.00 |
| Alice | 2016 | 150.00 |
| Alice | 2017 | 100.00 |
| Alice | 2018 | 200.00 |
| John | 2016 | 200.00 |
| John | 2017 | 150.00 |
| John | 2018 | 250.00 |
+----------------+-------------+--------+
9 rows in set (Elapsed: 00:00:00.00)
# 查看所有用户权限
gbase> select * from gbase.user\G
*************************** 1. row ***************************
Host: %
User: root
Password:
Default_VC:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Unmask_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
max_cpus: 0
max_memories: 0
max_tmp_space: 0
resource_group: 0
task_priority: 2
user_limit_storage_size:
user_storage_size: 0
UID: 1
plugin: gbase_native_password
auth_string:
*************************** 2. row ***************************
Host: %
User: gbase
Password: *9C0ADBD7F08FA9D49D82760B104110C55B943B8D
Default_VC:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Unmask_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
max_cpus: 0
max_memories: 0
max_tmp_space: 0
resource_group: 0
task_priority: 2
user_limit_storage_size:
user_storage_size: 0
UID: 2
plugin: gbase_native_password
auth_string:
*************************** 3. row ***************************
Host: %
User: user1
Password: *34D3B87A652E7F0D1D371C3DBF28E291705468C4
Default_VC:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Unmask_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
max_cpus: 0
max_memories: 0
max_tmp_space: 0
resource_group: 0
task_priority: 2
user_limit_storage_size:
user_storage_size: 0
UID: 101
plugin: gbase_native_password
auth_string:
*************************** 4. row ***************************
Host: %
User: user2
Password: *12A20BE57AF67CBF230D55FD33FBAF5230CFDBC4
Default_VC:
Select_priv: Y
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Unmask_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
max_cpus: 0
max_memories: 0
max_tmp_space: 0
resource_group: 0
task_priority: 2
user_limit_storage_size:
user_storage_size: 0
UID: 103
plugin: gbase_native_password
auth_string:
4 rows in set (Elapsed: 00:00:00.00)
示例3：
# 虚拟集群情况下创建新用户及权限管理
[gbase@node13 vc]$ gcadmin
CLUSTER STATE: ACTIVE
==============================================================
| GBASE COORDINATOR CLUSTER INFORMATION |
==============================================================
| NodeName | IpAddress | gcware | gcluster | DataState |
--------------------------------------------------------------
| coordinator1 | *********** | OPEN | OPEN | 0 |
--------------------------------------------------------------
| coordinator2 | *********** | OPEN | OPEN | 0 |
--------------------------------------------------------------
| coordinator3 | *********** | OPEN | OPEN | 0 |
--------------------------------------------------------------
| coordinator4 | *********** | OPEN | OPEN | 0 |
--------------------------------------------------------------
=============================================
| GBASE VIRTUAL CLUSTER INFORMATION |
=============================================
| VcName | DistributionId | comment |
---------------------------------------------
| vc1 | 1 | vc1 |
---------------------------------------------
| vc2 | 2 | vc2 |
---------------------------------------------
2 virtual cluster: vc1, vc2
4 coordinator node
0 free data node
[gbase@node13 vc]$ gccli
gbase> create database vc1.test;
Query OK, 1 row affected (Elapsed: 00:00:00.02)
gbase> create user gtest identified by 'gtest';
Query OK, 0 rows affected (Elapsed: 00:00:00.02)
gbase> GRANT ALL ON *.* TO gtest;
ERROR 1818 (HY000): No VC selected.
gbase> GRANT ALL ON *.*.* TO gtest;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
gbase> show grants for gtest;
+-----------------------------------------------------------------------------------------------------------------+
| Grants for gtest@% |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.*.* TO 'gtest'@'%' IDENTIFIED BY PASSWORD '*0A1FE54786E3B2A086A421F7713BE2ACFEC4456C' |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
# 虚拟集群情况下权限管理使用' GRANT ALL ON *.* TO gtest;'会报错；需要使用'GRANT ALL ON *.*.* TO gtest;'，两种集群情况权限管理需注意。
