You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/10/17 11:04:20 UTC
[GitHub] [shardingsphere] wallacezhou opened a new issue, #21615: authority type DATABASE_PERMITTED not apply correctly for some account when there are multi accounts
wallacezhou opened a new issue, #21615:
URL: https://github.com/apache/shardingsphere/issues/21615
## Bug Report
**For English only**, other languages will not accept.
Before report a bug, make sure you have:
- Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues).
- Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview).
Please pay attention on issues you submitted, because we maybe need more details.
If no response anymore and we cannot reproduce it on current information, we will **close it**.
Please answer these questions before submitting your issue. Thanks!
### Which version of ShardingSphere did you use?
v5.2.0
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
### Expected behavior
use authority type DATABASE_PERMITTED in ShardingSphere-Proxy 5.2.0,
config two accounts testacc_a，testacc_b
config account testacc_a to access logic db testdb_a,
account testacc_b to access logic db testdb_b
expected that both two accounts can access ShardingSphere Proxy via mysql ui tools eg. workbench or navicat
### Actual behavior
only account testacc_a access ShardingSphere Proxy via mysql ui tool successfully,
the other account testacc_b failed to access ShardingSphere Proxy via mysql ui tool
### Reason analyze (If you can)
when connect with account testacc_a, the sql only query logic db testdb_a
but when connect with account testacc_b , there would be one or more sql that would query logic db testdb_a,
as testacc_b is configed to access logic db testdb_b, it cannot found db testdb_a
accss testdb_a will cause the unknow database error like below
in ShardingSphere Proxy log, connect use testacc_b, error log like below:
in sharding proxy version 5.1, config use SCHEMA_PRIVILEGES_PERMITTED has no this issue
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
config rules in file server.yaml
rules:
- !AUTHORITY
users:
- testacc_a@:123456
- testacc_b@:123456
provider:
type: DATABASE_PERMITTED
props:
user-database-mappings: testacc_a@=testdb_a,testacc_b@=testdb_b
add config file config-sharding.yaml and config-testdb_a.yaml,
config-testdb_a.yaml：
databaseName: testdb_a
config-testdb_a.yaml:
databaseName: testdb_b
add dataSources for the two files, the dataSources can be the same or different
### Example codes for reproduce this issue (such as a github link).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #21615: authority type DATABASE_PERMITTED not apply correctly for some account when there are multi accounts
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #21615:
URL: https://github.com/apache/shardingsphere/issues/21615#issuecomment-1283348596
### DatabaseBackendHandlerFactory
```java
if (sqlStatement instanceof DALStatement || (sqlStatement instanceof SelectStatement && null == ((SelectStatement) sqlStatement).getFrom())) {
return new UnicastDatabaseBackendHandler(queryContext, connectionSession);
}
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang closed issue #21615: authority type DATABASE_PERMITTED not apply correctly for some account when there are multi accounts
Posted by GitBox <gi...@apache.org>.
RaigorJiang closed issue #21615: authority type DATABASE_PERMITTED not apply correctly for some account when there are multi accounts
URL: https://github.com/apache/shardingsphere/issues/21615
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #21615: authority type DATABASE_PERMITTED not apply correctly for some account when there are multi accounts
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #21615:
URL: https://github.com/apache/shardingsphere/issues/21615#issuecomment-1281011417
hI @wallacezhou
Thanks for the feedback, did your client tool specify database when connecting?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #21615: authority type DATABASE_PERMITTED not apply correctly for some account when there are multi accounts
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #21615:
URL: https://github.com/apache/shardingsphere/issues/21615#issuecomment-1283347823
> @RaigorJiang
>
> connect use mysql 8 client workbench success with proxy account testacc_a: [success_testacc_a_workbench_mysql8.log](https://github.com/apache/shardingsphere/files/9808471/success_testacc_a_workbench_mysql8.log)
>
> connect use mysql 8 client workbench fail with proxy account testacc_b: [fail_testacc_b_workbench_mysql8.log](https://github.com/apache/shardingsphere/files/9808629/fail_testacc_b_workbench_mysql8.log)
>
> connect use mysql 8 client navicat success with proxy account testacc_a: [success_testacc_a_navicat_mysql8.log](https://github.com/apache/shardingsphere/files/9808485/success_testacc_a_navicat_mysql8.log)
>
> connect use mysql 8 client navicat fail with proxy account testacc_b: [fail_testacc_b_navcat_mysql8.log](https://github.com/apache/shardingsphere/files/9808491/fail_testacc_b_navcat_mysql8.log)
>
> different client will have different connect sql, the common issue is proxy account would access the database that does not belong to it
@wallacezhou Thanks you, after checking the log, I think it is a bug for now.
The reason is that `DALStatement` uses `UnicastDatabaseBackendHandler`, which is used to get a database at random. (containing unauthorized databases).
Are you interested in trying to fix this?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] wallacezhou commented on issue #21615: authority type DATABASE_PERMITTED not apply correctly for some account when there are multi accounts
Posted by GitBox <gi...@apache.org>.
wallacezhou commented on issue #21615:
URL: https://github.com/apache/shardingsphere/issues/21615#issuecomment-1288842921
fix the client fails when connect to proxy issue
but when client send extra meta database sql to proxy, there still exist authority errors
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #21615: authority type DATABASE_PERMITTED not apply correctly for some account when there are multi accounts
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #21615:
URL: https://github.com/apache/shardingsphere/issues/21615#issuecomment-1296371465
@wallacezhou Merged, look forward to your continued participation in the community!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] wallacezhou commented on issue #21615: authority type DATABASE_PERMITTED not apply correctly for some account when there are multi accounts
Posted by GitBox <gi...@apache.org>.
wallacezhou commented on issue #21615:
URL: https://github.com/apache/shardingsphere/issues/21615#issuecomment-1285296585
after check the code , class ProxyBackendHandlerFactory method newInstance() will be used to invoke DatabaseBackendHandlerFactory
in ProxyBackendHandlerFactory's newInstance() method, it will call method check() in SQLCheckEngine
the check() call invoke check() mehtod in AuthorityChecker
when the param database name have value but the user has permission for the database,
check will be failed, at last a SQLCheckException is thrown
there are to cases
1.when connect to shardingsphere proxy with a client tool, the connect sql may contain database the connect account has no permission, the database name is used as parameter in mehtod check() of class AuthorityChecker, cause the client tool receive an error and the connection fails
2. connect to shardingsphere proxy with a client tool success,
but there are still cases check() method of AuthorityChecker fails
eg. execute a select query `select * from tablename` in mysql client, there may exist extra sql that query Mysql's meta database performance_schema send to proxy
the select sql check will pass and execute success
but the extra query of fails, as database name performance_schema is used as parameter in check() method of AuthorityChecker, SQLCheckException throws. unlike the connecttion case, the SQLCheckException has no visible impact
proxy get the database name get from the client sql, use it as parameter in AuthorityChecker check() method
though the database name is correct, the check may fail and cause issue
3 ways to fix the issue now I can think
1. consider weather the check() method of SQLCheckEngine shoud throw a SQLCheckException or not,
if no exception throws, the client can connect success
2. add a new check() mehtod that only return a boolean value and do not throw a exception for this case
3. in method newInstance() of ProxyBackendHandlerFactory, revise the logic of variable databaseName used for check
String databaseName = sqlStatementContext.getTablesContext().getDatabaseName().isPresent()
? sqlStatementContext.getTablesContext().getDatabaseName().get()
: connectionSession.getDatabaseName();
SQLCheckEngine.check(sqlStatementContext, Collections.emptyList(),
getRules(databaseName), databaseName, ProxyContext.getInstance().getContextManager().getMetaDataContexts().getMetaData().getDatabases(), connectionSession.getGrantee());
in proxy version 5.1, client can connect proxy success, the reason is the databaseName parameter passed to SQLCheckEngine.check() is null, as connectionSession.getDatabaseName() logic is different, the check logic is skipped and check result is success
this may be not a good choice.
thing need to charify:
mysql client send extra meta database sql to proxy, eg query from performance_schema,
and the extra sql it not visuable to user, these meta databases are not owned by user
weather need to check these extra sql based on user configured authority?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] wallacezhou commented on issue #21615: authority type DATABASE_PERMITTED not apply correctly for some account when there are multi accounts
Posted by GitBox <gi...@apache.org>.
wallacezhou commented on issue #21615:
URL: https://github.com/apache/shardingsphere/issues/21615#issuecomment-1282031239
@RaigorJiang
connect use mysql 8 client workbench success with proxy account testacc_a:
[success_testacc_a_workbench_mysql8.log](https://github.com/apache/shardingsphere/files/9808471/success_testacc_a_workbench_mysql8.log)
connect use mysql 8 client workbench fail with proxy account testacc_b:
[fail_testacc_b_workbench_mysql8.txt](https://github.com/apache/shardingsphere/files/9808483/fail_testacc_b_workbench_mysql8.txt)
connect use mysql 8 client navicat success with proxy account testacc_a:
[success_testacc_a_navicat_mysql8.log](https://github.com/apache/shardingsphere/files/9808485/success_testacc_a_navicat_mysql8.log)
connect use mysql 8 client navicat fail with proxy account testacc_b:
[fail_testacc_b_navcat_mysql8.log](https://github.com/apache/shardingsphere/files/9808491/fail_testacc_b_navcat_mysql8.log)
different client will have different connect sql,
the common issue is proxy account would access the database that does not belong to it
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #21615: authority type DATABASE_PERMITTED not apply correctly for some account when there are multi accounts
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang commented on issue #21615:
URL: https://github.com/apache/shardingsphere/issues/21615#issuecomment-1415150604
> @RaigorJiang 😂 now, shardingSphere-Proxy can grant access permission to all databases simply with `*` , such as `admin@localhost=*`
But what this issue discusses has nothing to do with that.
Can you submit a PR to update the docs? Reference #19399
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] wallacezhou commented on issue #21615: authority type DATABASE_PERMITTED not apply correctly for some account when there are multi accounts
Posted by GitBox <gi...@apache.org>.
wallacezhou commented on issue #21615:
URL: https://github.com/apache/shardingsphere/issues/21615#issuecomment-1281660503
yes, I use mysql 8.0, the client tools work fine when Sharding Proxy is version 5.1
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #21615: authority type DATABASE_PERMITTED not apply correctly for some account when there are multi accounts
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang commented on issue #21615:
URL: https://github.com/apache/shardingsphere/issues/21615#issuecomment-1415070289
@ZZemptypoint What kind of example is needed?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] wallacezhou commented on issue #21615: authority type DATABASE_PERMITTED not apply correctly for some account when there are multi accounts
Posted by GitBox <gi...@apache.org>.
wallacezhou commented on issue #21615:
URL: https://github.com/apache/shardingsphere/issues/21615#issuecomment-1281660502
yes, I use mysql 8.0, the client tools work fine when Sharding Proxy is version
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] wallacezhou commented on issue #21615: authority type DATABASE_PERMITTED not apply correctly for some account when there are multi accounts
Posted by GitBox <gi...@apache.org>.
wallacezhou commented on issue #21615:
URL: https://github.com/apache/shardingsphere/issues/21615#issuecomment-1281704793
@RaigorJiang
checked the code in master branch with revision number 84cf85fa0f7e3c8097c17f5a26a0024cba40f39c
org.apache.shardingsphere.authority.provider.database.builder.DatabasePrivilegeBuilder
the method buildPrivileges() build the privileges, it calls method getUserDatabases()
the logic in bold line:
when the database host is unlimited value: %, then the database is add to the result
if the user is not match, seems that one user can add other user's database when the database host is %
private static Map<ShardingSphereUser, ShardingSpherePrivileges> buildPrivileges(final Collection<ShardingSphereUser> users, final String mappingProp) {
Map<ShardingSphereUser, Collection<String>> userDatabaseMappings = convertDatabases(mappingProp);
Map<ShardingSphereUser, ShardingSpherePrivileges> result = new HashMap<>(users.size(), 1);
users.forEach(each -> result.put(each, new DatabasePermittedPrivileges(new HashSet<>(getUserDatabases(each, userDatabaseMappings)))));
return result;
}
private static Collection<String> getUserDatabases(final ShardingSphereUser shardingSphereUser, final Map<ShardingSphereUser, Collection<String>> userDatabaseMappings) {
Set<String> result = new HashSet<>();
for (Entry<ShardingSphereUser, Collection<String>> entry : userDatabaseMappings.entrySet()) {
boolean isAnyOtherHost = checkAnyOtherHost(entry.getKey().getGrantee(), shardingSphereUser);
**if (isAnyOtherHost || shardingSphereUser == entry.getKey() || shardingSphereUser.equals(entry.getKey())) {**
result.addAll(entry.getValue());
}
}
return result;
}
private static boolean checkAnyOtherHost(final Grantee grantee, final ShardingSphereUser shardingSphereUser) {
return ("%".equalsIgnoreCase(grantee.getHostname())
|| grantee.getHostname().equals(shardingSphereUser.getGrantee().getHostname())) && grantee.getUsername().equals(shardingSphereUser.getGrantee().getUsername());
}
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] ZZemptypoint commented on issue #21615: authority type DATABASE_PERMITTED not apply correctly for some account when there are multi accounts
Posted by "ZZemptypoint (via GitHub)" <gi...@apache.org>.
ZZemptypoint commented on issue #21615:
URL: https://github.com/apache/shardingsphere/issues/21615#issuecomment-1415026722
@RaigorJiang Why not give an example of this new feature in the [documentation ](https://shardingsphere.apache.org/document/5.3.1/cn/user-manual/shardingsphere-proxy/yaml-config/authentication/#database_permitted)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] ZZemptypoint commented on issue #21615: authority type DATABASE_PERMITTED not apply correctly for some account when there are multi accounts
Posted by "ZZemptypoint (via GitHub)" <gi...@apache.org>.
ZZemptypoint commented on issue #21615:
URL: https://github.com/apache/shardingsphere/issues/21615#issuecomment-1415082901
😂 now, shardingSphere-Proxy can grant access permission to all databases simply with `*` , such as `admin@localhost=*`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #21615: authority type DATABASE_PERMITTED not apply correctly for some account when there are multi accounts
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #21615:
URL: https://github.com/apache/shardingsphere/issues/21615#issuecomment-1281909276
Hi @wallacezhou , can you provide the log `Read from client`?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] wallacezhou commented on issue #21615: authority type DATABASE_PERMITTED not apply correctly for some account when there are multi accounts
Posted by GitBox <gi...@apache.org>.
wallacezhou commented on issue #21615:
URL: https://github.com/apache/shardingsphere/issues/21615#issuecomment-1282154678
test with sharding proxy v5.1.1 with the same proxy accountsand databases,
all connection using mysql workbench and navicat are success
check the logs, testacc_b also access testdb_a, but no unkonw database error
proxy v5.1.1, mysql 8 client workbench success with proxy account testacc_a:
[v5.1.1_testacc_a_workbench.log](https://github.com/apache/shardingsphere/files/9809246/v5.1.1_testacc_a_workbench.log)
proxy v5.1.1, mysql 8 client workbench success with proxy account testacc_b:
[v5.1.1_testacc_b_workbench.log](https://github.com/apache/shardingsphere/files/9809254/v5.1.1_testacc_b_workbench.log)
proxy v5.1.1, mysql 8 client navicat success with proxy account testacc_a:
[v5.1.1_testacc_a_navicat.log](https://github.com/apache/shardingsphere/files/9809264/v5.1.1_testacc_a_navicat.log)
proxy v5.1.1, mysql 8 client navicat success with proxy account testacc_b:
[v5.1.1_testacc_b_navicat.log](https://github.com/apache/shardingsphere/files/9809269/v5.1.1_testacc_b_navicat.log)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org