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/12/17 16:53:30 UTC
[GitHub] [shardingsphere] vikiuvb opened a new issue, #22944: Disable DistSQL privileges for a user
vikiuvb opened a new issue, #22944:
URL: https://github.com/apache/shardingsphere/issues/22944
Let's assume we have a web application connecting to the ShardingSphere proxy. The application uses a separate user, 'appuser' to connect to the database. Since the application only performs CRUD operations on the table, we grant only the `INSERT, UPDATE, DELETE and SELECT` privileges to the 'appuser'.
In this case, how do we prevent the 'appuser' from executing the DistSQL commands?
I looked at the documentation and the code, and I don't seem to find any way to achieve 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.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #22944: Disable DistSQL privileges for a user
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #22944:
URL: https://github.com/apache/shardingsphere/issues/22944#issuecomment-1356724703
Fine-grained authorization management is an enterprise-level feature, and it is not easy to implement in the open source community.
As far as I know, DBPlusEngine of SphereEX already provides such a capability. Do you accept commercial services?
https://www.sphere-ex.com/
--
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
Re: [I] Disable DistSQL privileges for a user [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu closed issue #22944: Disable DistSQL privileges for a user
URL: https://github.com/apache/shardingsphere/issues/22944
--
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] vikiuvb commented on issue #22944: Disable DistSQL privileges for a user
Posted by GitBox <gi...@apache.org>.
vikiuvb commented on issue #22944:
URL: https://github.com/apache/shardingsphere/issues/22944#issuecomment-1356859061
@RaigorJiang I believe the ShardingSphere already has support to inject custom AuthorityRegistry. All we need is a DistSQL type in PrivilegeType.java and AuthorityChecker.java that would allow adopters to customize the permissions.
Do you think it makes sense to add the following code ?
**PrivilegeType.java**
```
public enum PrivilegeType {
SELECT,
INSERT,
UPDATE,
DELETE,
CREATE,
ALTER,
DROP,
GRANT,
INDEX,
REFERENCES,
LOCK_TABLES,
CREATE_VIEW,
SHOW_VIEW,
EXECUTE,
EVENT,
TRIGGER,
SUPER,
SHOW_DB,
RELOAD,
SHUTDOWN,
PROCESS,
FILE,
CREATE_TMP,
REPL_SLAVE,
REPL_CLIENT,
CREATE_PROC,
ALTER_PROC,
CREATE_USER,
CREATE_TABLESPACE,
CREATE_ROLE,
DROP_ROLE,
TRUNCATE,
USAGE,
CONNECT,
TEMPORARY,
CREATE_DATABASE,
INHERIT,
CAN_LOGIN,
CREATE_FUNCTION,
CREATE_TABLE,
BACKUP_DATABASE,
CREATE_DEFAULT,
BACKUP_LOG,
CREATE_RULE,
CREATE_SEQUENCE,
CREATE_TYPE,
CREATE_SESSION,
ALTER_SESSION,
CREATE_SYNONYM,
ADMINISTER_BULK_OPERATIONS,
ALTER_ANY_AVAILABILITY_GROUP,
ALTER_ANY_CONNECTION,
ALTER_ANY_CREDENTIAL,
ALTER_ANY_DATABASE,
ALTER_ANY_ENDPOINT,
ALTER_ANY_EVENT_NOTIFICATION,
ALTER_ANY_EVENT_SESSION,
ALTER_ANY_LINKED_SERVER,
ALTER_ANY_LOGIN,
ALTER_ANY_SERVER_AUDIT,
ALTER_ANY_SERVER_ROLE,
ALTER_RESOURCES,
ALTER_SERVER_STATE,
ALTER_SETTINGS,
ALTER_TRACE,
AUTHENTICATE_SERVER,
CONNECT_ANY_DATABASE,
CONNECT_SQL,
CONTROL_SERVER,
CREATE_ANY_DATABASE,
CREATE_AVAILABILITY_GROUP,
CREATE_DDL_EVENT_NOTIFICATION,
CREATE_ENDPOINT,
CREATE_SERVER_ROLE,
CREATE_TRACE_EVENT_NOTIFICATION,
EXTERNAL_ACCESS_ASSEMBLY,
IMPERSONATE_ANY_LOGIN,
SELECT_ALL_USER_SECURABLES,
UNSAFE_ASSEMBLY,
VIEW_ANY_DATABASE,
VIEW_ANY_DEFINITION,
VIEW_SERVER_STATE,
DISTSQL
}
```
**AuthorityChecker.java**
```
public final class AuthorityChecker implements SQLChecker<AuthorityRule> {
@Override
public boolean check(final String databaseName, final Grantee grantee, final AuthorityRule rule) {
return null == grantee || rule.findPrivileges(grantee).map(optional -> optional.hasPrivileges(databaseName)).orElse(false);
}
@Override
public void check(final SQLStatementContext<?> sqlStatementContext, final List<Object> params, final Grantee grantee,
final String currentDatabase, final Map<String, ShardingSphereDatabase> databases, final AuthorityRule rule) {
if (null == grantee) {
return;
}
Optional<ShardingSpherePrivileges> privileges = rule.findPrivileges(grantee);
ShardingSpherePreconditions.checkState(privileges.isPresent(), () -> new SQLCheckException(String.format("Access denied for user '%s'@'%s'", grantee.getUsername(), grantee.getHostname())));
ShardingSpherePreconditions.checkState(null == currentDatabase || privileges.filter(optional -> optional.hasPrivileges(currentDatabase)).isPresent(),
() -> new SQLCheckException(String.format("Unknown database '%s'", currentDatabase)));
PrivilegeType privilegeType = getPrivilege(sqlStatementContext.getSqlStatement());
boolean hasPrivileges = privileges.get().hasPrivileges(Collections.singletonList(privilegeType));
ShardingSpherePreconditions.checkState(hasPrivileges, () -> new SQLCheckException(String.format("Access denied for operation %s.", null == privilegeType ? "" : privilegeType.name())));
}
@Override
public boolean check(final Grantee grantee, final AuthorityRule rule) {
return rule.findUser(grantee).isPresent();
}
@Override
public boolean check(final Grantee grantee, final BiPredicate<Object, Object> validator, final Object cipher, final AuthorityRule rule) {
Optional<ShardingSphereUser> user = rule.findUser(grantee);
return user.filter(each -> validator.test(each, cipher)).isPresent();
}
private PrivilegeType getPrivilege(final SQLStatement sqlStatement) {
if (sqlStatement instanceof MySQLShowDatabasesStatement) {
return PrivilegeType.SHOW_DB;
}
if (sqlStatement instanceof DMLStatement) {
return getDMLPrivilege(sqlStatement);
}
if (sqlStatement instanceof DDLStatement) {
return getDDLPrivilege(sqlStatement);
}
// TODO add more Privilege and SQL statement mapping
return null;
}
private PrivilegeType getDMLPrivilege(final SQLStatement sqlStatement) {
if (sqlStatement instanceof SelectStatement) {
return PrivilegeType.SELECT;
}
if (sqlStatement instanceof InsertStatement) {
return PrivilegeType.INSERT;
}
if (sqlStatement instanceof UpdateStatement) {
return PrivilegeType.UPDATE;
}
if (sqlStatement instanceof DeleteStatement) {
return PrivilegeType.DELETE;
}
return null;
}
private PrivilegeType getDDLPrivilege(final SQLStatement sqlStatement) {
if (sqlStatement instanceof AlterDatabaseStatement) {
return PrivilegeType.ALTER_ANY_DATABASE;
}
if (sqlStatement instanceof AlterTableStatement) {
return PrivilegeType.ALTER;
}
if (sqlStatement instanceof CreateDatabaseStatement) {
return PrivilegeType.CREATE_DATABASE;
}
if (sqlStatement instanceof CreateTableStatement) {
return PrivilegeType.CREATE_TABLE;
}
if (sqlStatement instanceof CreateFunctionStatement) {
return PrivilegeType.CREATE_FUNCTION;
}
if (sqlStatement instanceof DropTableStatement || sqlStatement instanceof DropDatabaseStatement) {
return PrivilegeType.DROP;
}
if (sqlStatement instanceof TruncateStatement) {
return PrivilegeType.TRUNCATE;
}
if (sqlStatement instanceof DistSQLStatement) {
return PrivilegeType.DISTSQL;
}
return null;
}
@Override
public int getOrder() {
return AuthorityOrder.ORDER;
}
@Override
public Class<AuthorityRule> getTypeClass() {
return AuthorityRule.class;
}
}
```
--
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 #22944: Disable DistSQL privileges for a user
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #22944:
URL: https://github.com/apache/shardingsphere/issues/22944#issuecomment-1357269508
@vikiuvb
In fact, DistSQL also includes a variety of operation types, such as RDL, RAL, etc., or divided into SHARDING, SHADOW, etc. according to features.
If you just need to limit DistSQL, I think you can do it in a fork branch, but doing it in the master branch should not be enough.
--
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