search

arkdb / arkproxy

高性能、高可靠的数据库跨云中间件，完全兼容MySQL技术体系，由极数云舟出品开源
GNU Lesser General Public License v2.1
102 stars 30 forks source link

用户不能对数据库进行隔离 #3

Closed mawenbin666 closed 4 years ago

mawenbin666 commented 4 years ago

场景

一个数据库实例下，有多个数据库，一个数据库对应一个项目，每个项目的账号看到的数据库都是不一样的， 这种该如何实现呢？

jesset commented 4 years ago

正常授权到数据库就行，GRANT ALL PRIVILEGES ON dbname1.* TO 'db1_user'@'1.2.3%' ...

mawenbin666 commented 4 years ago

正常授权到数据库就行，GRANT ALL PRIVILEGES ON dbname1.* TO 'db1_user'@'1.2.3%' ...

正常授权的话，如果有多个项目呢，怎么配置多个用户呢，类似于mycat的那种功能

jesset commented 4 years ago

连接 Arkproxy 配置文件里所定义的 proxy_port 业务端口，将不同的库仅授权给各自的用户（和mysql原生的一样）

GRANT ALL PRIVILEGES ON db1.* TO 'user1'@'%' IDENTIFIED BY 'user1_Pass' ;
GRANT ALL PRIVILEGES ON db2.* TO 'user2'@'%' IDENTIFIED BY 'user2_Pass' ;

举例说明：

[root@ ~]# mysql -A -h10.0.0.135 -P 3336 -uuser1 -puser1_Pass
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.31-34 Percona Server (GPL), Release 34, Revision 2e68637

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
+--------------------+
2 rows in set (0.00 sec)

mysql> ^DBye

[root@  ~]# mysql -A -h10.0.0.135 -P 3336 -uuser2 -puser2_Pass
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.31-34 Percona Server (GPL), Release 34, Revision 2e68637

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db2                |
+--------------------+
2 rows in set (0.00 sec)

mysql>
mawenbin666 commented 4 years ago

连接 Arkproxy 配置文件里所定义的 proxy_port 业务端口，将不同的库仅授权给各自的用户（和mysql原生的一样）

GRANT ALL PRIVILEGES ON db1.* TO 'user1'@'%' IDENTIFIED BY 'user1_Pass' ;
GRANT ALL PRIVILEGES ON db2.* TO 'user2'@'%' IDENTIFIED BY 'user2_Pass' ;

举例说明：

[root@ ~]# mysql -A -h10.0.0.135 -P 3336 -uuser1 -puser1_Pass
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.31-34 Percona Server (GPL), Release 34, Revision 2e68637

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
+--------------------+
2 rows in set (0.00 sec)

mysql> ^DBye

[root@  ~]# mysql -A -h10.0.0.135 -P 3336 -uuser2 -puser2_Pass
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.31-34 Percona Server (GPL), Release 34, Revision 2e68637

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db2                |
+--------------------+
2 rows in set (0.00 sec)

mysql>

对应到arkproxy.cnf是怎么配置呢？

proxy_backend_passwd    =   
proxy_backend_user      =   
proxy_port              =   3336

这里多个用户怎么配置呢？

jesset commented 4 years ago

可以参考一下示例配置文件，https://github.com/arkdb/arkproxy/blob/master/arkproxy.cnf.example

配置文件内各参数的含义和使用可以查 https://github.com/arkdb/arkproxy/wiki/配置参数说明

mawenbin666 commented 4 years ago

可以参考一下示例配置文件，https://github.com/arkdb/arkproxy/blob/master/arkproxy.cnf.example

配置文件内各参数的含义和使用可以查 https://github.com/arkdb/arkproxy/wiki/配置参数说明

示例的话是只配置了一个用户，而你上面演示的是两个用户，user1和user2，
所以，想问的是，user1和user2改怎么配置呢？

proxy_backend_passwd    =   arkproxy
proxy_backend_user      =   arkproxy
proxy_port              =   3336
jesset commented 4 years ago 
# 在arkproxy的运行主机上执行如下，user，password，port分别对应 proxy_backend_user ， proxy_backend_passwd，proxy_port

mysql -h 127.0.0.1 -P 3336 -uarkproxy -parkproxy     

# 给user1/user2授权
GRANT ALL PRIVILEGES ON db1.* TO 'user1'@'%' IDENTIFIED BY 'user1_Pass' ;
GRANT ALL PRIVILEGES ON db2.* TO 'user2'@'%' IDENTIFIED BY 'user2_Pass' ;

不知道这样清楚了没有

mawenbin666 commented 4 years ago 
# 在arkproxy的运行主机上执行如下，user，password，port分别对应 proxy_backend_user ， proxy_backend_passwd，proxy_port

mysql -h 127.0.0.1 -P 3336 -uarkproxy -parkproxy     

# 给user1/user2授权
GRANT ALL PRIVILEGES ON db1.* TO 'user1'@'%' IDENTIFIED BY 'user1_Pass' ;
GRANT ALL PRIVILEGES ON db2.* TO 'user2'@'%' IDENTIFIED BY 'user2_Pass' ;

不知道这样清楚了没有

明白了，其实他底层还是通过mysql.user这个表进行管理授权的，而不是arkproxy进行管理。