You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by pa...@apache.org on 2021/04/15 11:20:35 UTC
[shardingsphere] branch master updated: Load users and privileges
from SQLServer (#9987)
This is an automated email from the ASF dual-hosted git repository.
panjuan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new 3639ec1 Load users and privileges from SQLServer (#9987)
3639ec1 is described below
commit 3639ec1819dc61dd44c9ff3cf300b64c00ad5e6b
Author: totalo <48...@qq.com>
AuthorDate: Thu Apr 15 19:19:51 2021 +0800
Load users and privileges from SQLServer (#9987)
* feat:add SQLServerPrivilegeLoader
* merge master
* feat:add SQLServerPrivilegeLoader
* checkstyle
* add more
* merge master
* merge master
* merge master
* feat:add sql server privilegeLoader
* feat:checkstyle
* merge master
* Update ShardingSpherePrivileges.java
---
.../authority/model/PrivilegeType.java | 40 ++-
.../authority/checker/AuthorityChecker.java | 2 +-
.../builder/dialect/SQLServerPrivilegeHandler.java | 312 +++++++++++++++++++++
...rovider.natived.builder.StoragePrivilegeHandler | 1 +
.../dialect/SQLServerPrivilegeLoaderTest.java | 135 +++++++++
5 files changed, 488 insertions(+), 2 deletions(-)
diff --git a/shardingsphere-features/shardingsphere-authority/shardingsphere-authority-api/src/main/java/org/apache/shardingsphere/authority/model/PrivilegeType.java b/shardingsphere-features/shardingsphere-authority/shardingsphere-authority-api/src/main/java/org/apache/shardingsphere/authority/model/PrivilegeType.java
index b68c847..6473cfc 100644
--- a/shardingsphere-features/shardingsphere-authority/shardingsphere-authority-api/src/main/java/org/apache/shardingsphere/authority/model/PrivilegeType.java
+++ b/shardingsphere-features/shardingsphere-authority/shardingsphere-authority-api/src/main/java/org/apache/shardingsphere/authority/model/PrivilegeType.java
@@ -66,10 +66,48 @@ public enum PrivilegeType {
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,
- CREATE_TABLE
+ 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
}
diff --git a/shardingsphere-features/shardingsphere-authority/shardingsphere-authority-common/src/main/java/org/apache/shardingsphere/authority/checker/AuthorityChecker.java b/shardingsphere-features/shardingsphere-authority/shardingsphere-authority-common/src/main/java/org/apache/shardingsphere/authority/checker/AuthorityChecker.java
index a70366f..e267349 100644
--- a/shardingsphere-features/shardingsphere-authority/shardingsphere-authority-common/src/main/java/org/apache/shardingsphere/authority/checker/AuthorityChecker.java
+++ b/shardingsphere-features/shardingsphere-authority/shardingsphere-authority-common/src/main/java/org/apache/shardingsphere/authority/checker/AuthorityChecker.java
@@ -18,8 +18,8 @@
package org.apache.shardingsphere.authority.checker;
import org.apache.shardingsphere.authority.constant.AuthorityOrder;
-import org.apache.shardingsphere.authority.model.ShardingSpherePrivileges;
import org.apache.shardingsphere.authority.model.PrivilegeType;
+import org.apache.shardingsphere.authority.model.ShardingSpherePrivileges;
import org.apache.shardingsphere.authority.rule.AuthorityRule;
import org.apache.shardingsphere.infra.executor.check.SQLCheckResult;
import org.apache.shardingsphere.infra.executor.check.SQLChecker;
diff --git a/shardingsphere-features/shardingsphere-authority/shardingsphere-authority-common/src/main/java/org/apache/shardingsphere/authority/provider/natived/builder/dialect/SQLServerPrivilegeHandler.java b/shardingsphere-features/shardingsphere-authority/shardingsphere-authority-common/src/main/java/org/apache/shardingsphere/authority/provider/natived/builder/dialect/SQLServerPrivilegeHandler.java
new file mode 100644
index 0000000..c305a68
--- /dev/null
+++ b/shardingsphere-features/shardingsphere-authority/shardingsphere-authority-common/src/main/java/org/apache/shardingsphere/authority/provider/natived/builder/dialect/SQLServerPrivilegeHandler.java
@@ -0,0 +1,312 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.authority.provider.natived.builder.dialect;
+
+import org.apache.shardingsphere.authority.model.PrivilegeType;
+import org.apache.shardingsphere.authority.provider.natived.builder.StoragePrivilegeHandler;
+import org.apache.shardingsphere.authority.provider.natived.model.privilege.NativePrivileges;
+import org.apache.shardingsphere.authority.provider.natived.model.privilege.database.SchemaPrivileges;
+import org.apache.shardingsphere.authority.provider.natived.model.privilege.database.TablePrivileges;
+import org.apache.shardingsphere.infra.metadata.user.Grantee;
+import org.apache.shardingsphere.infra.metadata.user.ShardingSphereUser;
+
+import javax.sql.DataSource;
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.Collection;
+import java.util.Collections;
+import java.util.LinkedHashMap;
+import java.util.LinkedList;
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+import java.util.Optional;
+import java.util.stream.Collectors;
+
+/**
+ * SQLServer privilege loader.
+ */
+public final class SQLServerPrivilegeHandler implements StoragePrivilegeHandler {
+
+ private static final String GLOBAL_PRIVILEGE_SQL =
+ "SELECT pr.name AS GRANTEE, pe.state_desc AS STATE, pe.permission_name AS PRIVILEGE_TYPE"
+ + "FROM sys.server_principals AS pr JOIN sys.server_permissions AS pe"
+ + "ON pe.grantee_principal_id = pr.principal_id WHERE pr.name IN (%s) GROUP BY pr.name, pe.state_desc, pe.permission_name";
+
+ private static final String SCHEMA_PRIVILEGE_SQL =
+ "SELECT pr.name AS GRANTEE, pe.state_desc AS STATE, pe.permission_name AS PRIVILEGE_TYPE, o.name AS DB"
+ + "FROM sys.database_principals AS pr JOIN sys.database_permissions AS pe"
+ + "ON pe.grantee_principal_id = pr.principal_id JOIN sys.objects AS o"
+ + "ON pe.major_id = o.object_id WHERE pr.name IN (%s) GROUP BY pr.name, pe.state_desc, pe.permission_name, o.name";
+
+ private static final String TABLE_PRIVILEGE_SQL =
+ "SELECT GRANTOR, GRANTEE, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, IS_GRANTABLE from INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE GRANTEE IN (%s)";
+
+ @Override
+ public Collection<ShardingSphereUser> diff(final Collection<ShardingSphereUser> users, final DataSource dataSource) throws SQLException {
+ return Collections.emptyList();
+ }
+
+ @Override
+ public void create(final Collection<ShardingSphereUser> users, final DataSource dataSource) throws SQLException {
+ }
+
+ @Override
+ public void grantAll(final Collection<ShardingSphereUser> users, final DataSource dataSource) throws SQLException {
+ }
+
+ @Override
+ public Map<ShardingSphereUser, NativePrivileges> load(final Collection<ShardingSphereUser> users, final DataSource dataSource) throws SQLException {
+ Map<ShardingSphereUser, NativePrivileges> result = new LinkedHashMap<>();
+ users.forEach(user -> result.put(user, new NativePrivileges()));
+ fillGlobalPrivileges(result, dataSource, users);
+ fillSchemaPrivileges(result, dataSource, users);
+ fillTablePrivileges(result, dataSource, users);
+ return result;
+ }
+
+ private void fillGlobalPrivileges(final Map<ShardingSphereUser, NativePrivileges> userPrivilegeMap,
+ final DataSource dataSource, final Collection<ShardingSphereUser> users) throws SQLException {
+ try (Connection connection = dataSource.getConnection()) {
+ Statement statement = connection.createStatement();
+ try (ResultSet resultSet = statement.executeQuery(getGlobalPrivilegesSQL(users))) {
+ while (resultSet.next()) {
+ fillGlobalPrivileges(userPrivilegeMap, resultSet);
+ }
+ }
+ }
+ }
+
+ private void fillGlobalPrivileges(final Map<ShardingSphereUser, NativePrivileges> userPrivilegeMap, final ResultSet resultSet) throws SQLException {
+ Optional<ShardingSphereUser> user = findShardingSphereUser(userPrivilegeMap, resultSet);
+ if (user.isPresent()) {
+ userPrivilegeMap.get(user.get()).getAdministrativePrivileges().getPrivileges().addAll(loadPrivileges(resultSet));
+ }
+ }
+
+ private void fillSchemaPrivileges(final Map<ShardingSphereUser, NativePrivileges> userPrivilegeMap,
+ final DataSource dataSource, final Collection<ShardingSphereUser> users) throws SQLException {
+ try (Connection connection = dataSource.getConnection()) {
+ Statement statement = connection.createStatement();
+ try (ResultSet resultSet = statement.executeQuery(getSchemaPrivilegesSQL(users))) {
+ while (resultSet.next()) {
+ fillSchemaPrivileges(userPrivilegeMap, resultSet);
+ }
+ }
+ }
+ }
+
+ private void fillSchemaPrivileges(final Map<ShardingSphereUser, NativePrivileges> userPrivilegeMap, final ResultSet resultSet) throws SQLException {
+ Optional<ShardingSphereUser> user = findShardingSphereUser(userPrivilegeMap, resultSet);
+ if (user.isPresent()) {
+ String db = resultSet.getString("DB");
+ SchemaPrivileges schemaPrivileges = new SchemaPrivileges(db);
+ schemaPrivileges.getGlobalPrivileges().addAll(loadPrivileges(resultSet));
+ userPrivilegeMap.get(user.get()).getDatabasePrivileges().getSpecificPrivileges().put(db, schemaPrivileges);
+ }
+ }
+
+ private void fillTablePrivileges(final Map<ShardingSphereUser, NativePrivileges> userPrivilegeMap,
+ final DataSource dataSource, final Collection<ShardingSphereUser> users) throws SQLException {
+ Map<ShardingSphereUser, Map<String, Map<String, List<PrivilegeType>>>> privilegeCache = new HashMap<>();
+ try (Connection connection = dataSource.getConnection()) {
+ Statement statement = connection.createStatement();
+ try (ResultSet resultSet = statement.executeQuery(getTablePrivilegesSQL(users))) {
+ while (resultSet.next()) {
+ collectPrivileges(privilegeCache, resultSet);
+ }
+ }
+ }
+ fillTablePrivileges(privilegeCache, userPrivilegeMap);
+ }
+
+ private void fillTablePrivileges(final Map<ShardingSphereUser, Map<String, Map<String, List<PrivilegeType>>>> privilegeCache,
+ final Map<ShardingSphereUser, NativePrivileges> userPrivilegeMap) {
+ for (Map.Entry<ShardingSphereUser, Map<String, Map<String, List<PrivilegeType>>>> entry : privilegeCache.entrySet()) {
+ for (String db : entry.getValue().keySet()) {
+ for (String tableName : entry.getValue().get(db).keySet()) {
+ TablePrivileges tablePrivileges = new TablePrivileges(tableName, entry.getValue().get(db).get(tableName));
+ NativePrivileges privileges = userPrivilegeMap.get(entry.getKey());
+ if (!privileges.getDatabasePrivileges().getSpecificPrivileges().containsKey(db)) {
+ privileges.getDatabasePrivileges().getSpecificPrivileges().put(db, new SchemaPrivileges(db));
+ }
+ privileges.getDatabasePrivileges().getSpecificPrivileges().get(db).getSpecificPrivileges().put(tableName, tablePrivileges);
+ }
+ }
+ }
+ }
+
+ private void collectPrivileges(final Map<ShardingSphereUser, Map<String, Map<String, List<PrivilegeType>>>> privilegeCache, final ResultSet resultSet) throws SQLException {
+ String db = resultSet.getString("TABLE_CATALOG");
+ String tableName = resultSet.getString("TABLE_NAME");
+ String privilegeType = resultSet.getString("PRIVILEGE_TYPE");
+ boolean hasPrivilege = resultSet.getString("IS_GRANTABLE").equalsIgnoreCase("YES");
+ String grantee = resultSet.getString("GRANTEE");
+ if (hasPrivilege) {
+ privilegeCache
+ .computeIfAbsent(new ShardingSphereUser(grantee, "", ""), k -> new HashMap<>())
+ .computeIfAbsent(db, k -> new HashMap<>())
+ .computeIfAbsent(tableName, k -> new ArrayList<>())
+ .add(getPrivilegeType(privilegeType));
+ }
+ }
+
+ private Optional<ShardingSphereUser> findShardingSphereUser(final Map<ShardingSphereUser, NativePrivileges> privileges, final ResultSet resultSet) throws SQLException {
+ Grantee grantee = new Grantee(resultSet.getString("GRANTEE"), "");
+ return privileges.keySet().stream().filter(each -> each.getGrantee().equals(grantee)).findFirst();
+ }
+
+ private String getGlobalPrivilegesSQL(final Collection<ShardingSphereUser> users) {
+ String userHostTuples = users.stream().map(each -> String.format("'%s'", each.getGrantee().getUsername())).collect(Collectors.joining(","));
+ return String.format(GLOBAL_PRIVILEGE_SQL, userHostTuples);
+ }
+
+ private String getSchemaPrivilegesSQL(final Collection<ShardingSphereUser> users) {
+ String userList = users.stream().map(each -> String.format("'%s'", each.getGrantee().getUsername()))
+ .collect(Collectors.joining(","));
+ return String.format(SCHEMA_PRIVILEGE_SQL, userList);
+ }
+
+ private String getTablePrivilegesSQL(final Collection<ShardingSphereUser> users) {
+ String userList = users.stream().map(each -> String.format("'%s'", each.getGrantee().getUsername())).collect(Collectors.joining(", "));
+ return String.format(TABLE_PRIVILEGE_SQL, userList);
+ }
+
+ private PrivilegeType getPrivilegeType(final String privilege) {
+ switch (privilege) {
+ case "SELECT":
+ return PrivilegeType.SELECT;
+ case "INSERT":
+ return PrivilegeType.INSERT;
+ case "UPDATE":
+ return PrivilegeType.UPDATE;
+ case "DELETE":
+ return PrivilegeType.DELETE;
+ case "CREATE FUNCTION":
+ return PrivilegeType.CREATE_FUNCTION;
+ case "REFERENCES":
+ return PrivilegeType.REFERENCES;
+ case "CREATE TABLE":
+ return PrivilegeType.CREATE_TABLE;
+ case "CREATE DATABASE":
+ return PrivilegeType.CREATE_DATABASE;
+ case "EXECUTE":
+ return PrivilegeType.EXECUTE;
+ case "CREATE VIEW":
+ return PrivilegeType.CREATE_VIEW;
+ case "CREATE PROCEDURE":
+ return PrivilegeType.CREATE_PROC;
+ case "BACKUP DATABASE":
+ return PrivilegeType.BACKUP_DATABASE;
+ case "CREATE DEFAULT":
+ return PrivilegeType.CREATE_DEFAULT;
+ case "BACKUP LOG":
+ return PrivilegeType.BACKUP_LOG;
+ case "CREATE RULE":
+ return PrivilegeType.CREATE_RULE;
+ case "CONNECT":
+ return PrivilegeType.CONNECT;
+ case "ADMINISTER BULK OPERATIONS":
+ return PrivilegeType.ADMINISTER_BULK_OPERATIONS;
+ case "ALTER ANY AVAILABILITY GROUP":
+ return PrivilegeType.ALTER_ANY_AVAILABILITY_GROUP;
+ case "ALTER ANY CONNECTION":
+ return PrivilegeType.ALTER_ANY_CONNECTION;
+ case "ALTER ANY CREDENTIAL":
+ return PrivilegeType.ALTER_ANY_CREDENTIAL;
+ case "ALTER ANY DATABASE":
+ return PrivilegeType.ALTER_ANY_DATABASE;
+ case "ALTER ANY ENDPOINT":
+ return PrivilegeType.ALTER_ANY_ENDPOINT;
+ case "ALTER ANY EVENT SESSION":
+ return PrivilegeType.ALTER_ANY_EVENT_SESSION;
+ case "ALTER ANY EVENT NOTIFICATION":
+ return PrivilegeType.ALTER_ANY_EVENT_NOTIFICATION;
+ case "ALTER ANY LINKED SERVER":
+ return PrivilegeType.ALTER_ANY_LINKED_SERVER;
+ case "ALTER ANY LOGIN":
+ return PrivilegeType.ALTER_ANY_LOGIN;
+ case "ALTER ANY SERVER AUDIT":
+ return PrivilegeType.ALTER_ANY_SERVER_AUDIT;
+ case "ALTER ANY SERVER ROLE":
+ return PrivilegeType.ALTER_ANY_SERVER_ROLE;
+ case "ALTER RESOURCES":
+ return PrivilegeType.ALTER_RESOURCES;
+ case "ALTER SERVER STATE":
+ return PrivilegeType.ALTER_SERVER_STATE;
+ case "ALTER SETTINGS":
+ return PrivilegeType.ALTER_SETTINGS;
+ case "ALTER TRACE":
+ return PrivilegeType.ALTER_TRACE;
+ case "AUTHENTICATE SERVER":
+ return PrivilegeType.AUTHENTICATE_SERVER;
+ case "CONNECT ANY DATABASE":
+ return PrivilegeType.CONNECT_ANY_DATABASE;
+ case "CONNECT SQL":
+ return PrivilegeType.CONNECT_SQL;
+ case "CONTROL SERVER":
+ return PrivilegeType.CONTROL_SERVER;
+ case "CREATE ANY DATABASE":
+ return PrivilegeType.CREATE_ANY_DATABASE;
+ case "CREATE AVAILABILITY GROUP":
+ return PrivilegeType.CREATE_AVAILABILITY_GROUP;
+ case "CREATE DDL EVENT NOTIFICATION":
+ return PrivilegeType.CREATE_DDL_EVENT_NOTIFICATION;
+ case "CREATE ENDPOINT":
+ return PrivilegeType.CREATE_ENDPOINT;
+ case "CREATE SERVER ROLE":
+ return PrivilegeType.CREATE_SERVER_ROLE;
+ case "CREATE TRACE EVENT NOTIFICATION ":
+ return PrivilegeType.CREATE_TRACE_EVENT_NOTIFICATION;
+ case "EXTERNAL ACCESS ASSEMBLY":
+ return PrivilegeType.EXTERNAL_ACCESS_ASSEMBLY;
+ case "IMPERSONATE ANY LOGIN":
+ return PrivilegeType.IMPERSONATE_ANY_LOGIN;
+ case "SELECT ALL USER SECURABLES":
+ return PrivilegeType.SELECT_ALL_USER_SECURABLES;
+ case "SHUTDOWN":
+ return PrivilegeType.SHUTDOWN;
+ case "UNSAFE ASSEMBLY":
+ return PrivilegeType.UNSAFE_ASSEMBLY;
+ case "VIEW ANY DATABASE":
+ return PrivilegeType.VIEW_ANY_DATABASE;
+ case "VIEW ANY DEFINITION":
+ return PrivilegeType.VIEW_ANY_DEFINITION;
+ case "VIEW SERVER STATE ":
+ return PrivilegeType.VIEW_SERVER_STATE;
+ default:
+ throw new UnsupportedOperationException(privilege);
+ }
+ }
+
+ private Collection<PrivilegeType> loadPrivileges(final ResultSet resultSet) throws SQLException {
+ Collection<PrivilegeType> result = new LinkedList<>();
+ if ("GRANT".equals(resultSet.getString("STATE"))) {
+ result.add(getPrivilegeType(resultSet.getString("PRIVILEGE_TYPE")));
+ }
+ return result;
+ }
+
+ @Override
+ public String getType() {
+ return "SQLServer";
+ }
+}
diff --git a/shardingsphere-features/shardingsphere-authority/shardingsphere-authority-common/src/main/resources/META-INF/services/org.apache.shardingsphere.authority.provider.natived.builder.StoragePrivilegeHandler b/shardingsphere-features/shardingsphere-authority/shardingsphere-authority-common/src/main/resources/META-INF/services/org.apache.shardingsphere.authority.provider.natived.builder.StoragePrivilegeHandler
index 9087075..35e4b4a 100644
--- a/shardingsphere-features/shardingsphere-authority/shardingsphere-authority-common/src/main/resources/META-INF/services/org.apache.shardingsphere.authority.provider.natived.builder.StoragePrivilegeHandler
+++ b/shardingsphere-features/shardingsphere-authority/shardingsphere-authority-common/src/main/resources/META-INF/services/org.apache.shardingsphere.authority.provider.natived.builder.StoragePrivilegeHandler
@@ -18,3 +18,4 @@
org.apache.shardingsphere.authority.provider.natived.builder.dialect.MySQLPrivilegeHandler
org.apache.shardingsphere.authority.provider.natived.builder.dialect.PostgreSQLPrivilegeHandler
org.apache.shardingsphere.authority.provider.natived.builder.dialect.OraclePrivilegeHandler
+org.apache.shardingsphere.authority.provider.natived.builder.dialect.SQLServerPrivilegeHandler
diff --git a/shardingsphere-features/shardingsphere-authority/shardingsphere-authority-common/src/test/java/org/apache/shardingsphere/authority/provider/natived/builder/dialect/SQLServerPrivilegeLoaderTest.java b/shardingsphere-features/shardingsphere-authority/shardingsphere-authority-common/src/test/java/org/apache/shardingsphere/authority/provider/natived/builder/dialect/SQLServerPrivilegeLoaderTest.java
new file mode 100644
index 0000000..2f7efb3
--- /dev/null
+++ b/shardingsphere-features/shardingsphere-authority/shardingsphere-authority-common/src/test/java/org/apache/shardingsphere/authority/provider/natived/builder/dialect/SQLServerPrivilegeLoaderTest.java
@@ -0,0 +1,135 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.authority.provider.natived.builder.dialect;
+
+import org.apache.shardingsphere.authority.model.PrivilegeType;
+import org.apache.shardingsphere.authority.provider.natived.builder.StoragePrivilegeHandler;
+import org.apache.shardingsphere.authority.provider.natived.model.privilege.NativePrivileges;
+import org.apache.shardingsphere.authority.provider.natived.model.privilege.database.SchemaPrivileges;
+import org.apache.shardingsphere.infra.metadata.user.ShardingSphereUser;
+import org.apache.shardingsphere.infra.spi.ShardingSphereServiceLoader;
+import org.apache.shardingsphere.infra.spi.typed.TypedSPIRegistry;
+import org.junit.BeforeClass;
+import org.junit.Test;
+import org.junit.runner.RunWith;
+import org.mockito.junit.MockitoJUnitRunner;
+
+import javax.sql.DataSource;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.Arrays;
+import java.util.Collection;
+import java.util.LinkedList;
+import java.util.Map;
+import java.util.Properties;
+import java.util.concurrent.CopyOnWriteArraySet;
+import java.util.stream.Collectors;
+
+import static org.hamcrest.CoreMatchers.is;
+import static org.junit.Assert.assertThat;
+import static org.mockito.Mockito.RETURNS_DEEP_STUBS;
+import static org.mockito.Mockito.mock;
+import static org.mockito.Mockito.when;
+
+@RunWith(MockitoJUnitRunner.class)
+public final class SQLServerPrivilegeLoaderTest {
+
+ @BeforeClass
+ public static void setUp() {
+ ShardingSphereServiceLoader.register(StoragePrivilegeHandler.class);
+ }
+
+ @Test
+ public void assertLoad() throws SQLException {
+ Collection<ShardingSphereUser> users = createUsers();
+ DataSource dataSource = mockDataSource(users);
+ assertPrivileges(TypedSPIRegistry.getRegisteredService(StoragePrivilegeHandler.class, "SQLServer", new Properties()).load(users, dataSource));
+ }
+
+ private void assertPrivileges(final Map<ShardingSphereUser, NativePrivileges> actual) {
+ assertThat(actual.size(), is(1));
+ ShardingSphereUser dbo = new ShardingSphereUser("dbo", "", "");
+ assertThat(actual.get(dbo).getAdministrativePrivileges().getPrivileges().size(), is(2));
+ Collection<PrivilegeType> expectedAdminPrivileges = new CopyOnWriteArraySet<>(Arrays.asList(PrivilegeType.CONNECT, PrivilegeType.SHUTDOWN));
+ assertThat(actual.get(dbo).getAdministrativePrivileges().getPrivileges(), is(expectedAdminPrivileges));
+
+ Collection<PrivilegeType> expectedSpecificPrivilege = new CopyOnWriteArraySet(Arrays.asList(PrivilegeType.INSERT, PrivilegeType.SELECT, PrivilegeType.UPDATE,
+ PrivilegeType.DELETE));
+ SchemaPrivileges schemaPrivileges = actual.get(dbo).getDatabasePrivileges().getSpecificPrivileges().get("db0");
+ assertThat(schemaPrivileges.getSpecificPrivileges().get("t_order").hasPrivileges(expectedSpecificPrivilege), is(true));
+ }
+
+ private Collection<ShardingSphereUser> createUsers() {
+ LinkedList<ShardingSphereUser> result = new LinkedList<>();
+ result.add(new ShardingSphereUser("dbo", "", ""));
+ return result;
+ }
+
+ private DataSource mockDataSource(final Collection<ShardingSphereUser> users) throws SQLException {
+ ResultSet globalPrivilegeResultSet = mockGlobalPrivilegeResultSet();
+ DataSource result = mock(DataSource.class, RETURNS_DEEP_STUBS);
+ String userList = users.stream().map(item -> String.format("'%s'", item.getGrantee().getUsername())).collect(Collectors.joining(", "));
+
+ String globalPrivilegeSql = "SELECT pr.name AS GRANTEE, pe.state_desc AS STATE, pe.permission_name AS PRIVILEGE_TYPE"
+ + "FROM sys.server_principals AS pr JOIN sys.server_permissions AS pe"
+ + "ON pe.grantee_principal_id = pr.principal_id WHERE pr.name IN (%s) GROUP BY pr.name, pe.state_desc, pe.permission_name";
+ when(result.getConnection().createStatement().executeQuery(String.format(globalPrivilegeSql, userList))).thenReturn(globalPrivilegeResultSet);
+
+ ResultSet schemaPrivilegeResultSet = mockSchemaPrivilegeResultSet();
+ String schemaPrivilegeSql = "SELECT pr.name AS GRANTEE, pe.state_desc AS STATE, pe.permission_name AS PRIVILEGE_TYPE, o.name AS DB"
+ + "FROM sys.database_principals AS pr JOIN sys.database_permissions AS pe"
+ + "ON pe.grantee_principal_id = pr.principal_id JOIN sys.objects AS o"
+ + "ON pe.major_id = o.object_id WHERE pr.name IN (%s) GROUP BY pr.name, pe.state_desc, pe.permission_name, o.name";
+ when(result.getConnection().createStatement().executeQuery(String.format(schemaPrivilegeSql, userList))).thenReturn(schemaPrivilegeResultSet);
+
+ ResultSet tablePrivilegeResultSet = mockTablePrivilegeResultSet();
+ String tablePrivilegeSql = "SELECT GRANTOR, GRANTEE, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE, IS_GRANTABLE from INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE GRANTEE IN (%s)";
+ when(result.getConnection().createStatement().executeQuery(String.format(tablePrivilegeSql, userList))).thenReturn(tablePrivilegeResultSet);
+ return result;
+ }
+
+ private ResultSet mockGlobalPrivilegeResultSet() throws SQLException {
+ ResultSet result = mock(ResultSet.class, RETURNS_DEEP_STUBS);
+ when(result.next()).thenReturn(true, true, false);
+ when(result.getString("STATE")).thenReturn("GRANT", "GRANT");
+ when(result.getString("GRANTEE")).thenReturn("dbo", "dbo");
+ when(result.getString("PRIVILEGE_TYPE")).thenReturn("CONNECT", "SHUTDOWN");
+ return result;
+ }
+
+ private ResultSet mockTablePrivilegeResultSet() throws SQLException {
+ ResultSet result = mock(ResultSet.class, RETURNS_DEEP_STUBS);
+ when(result.next()).thenReturn(true, true, true, true, true, true, true, false);
+ when(result.getString("TABLE_CATALOG")).thenReturn("db0");
+ when(result.getString("TABLE_NAME")).thenReturn("t_order");
+ when(result.getString("PRIVILEGE_TYPE")).thenReturn("INSERT", "SELECT", "UPDATE", "DELETE", "REFERENCES");
+ when(result.getString("IS_GRANTABLE")).thenReturn("YES", "YES", "YES", "YES", "YES", "YES", "YES");
+ when(result.getString("GRANTEE")).thenReturn("dbo");
+ return result;
+ }
+
+ private ResultSet mockSchemaPrivilegeResultSet() throws SQLException {
+ ResultSet result = mock(ResultSet.class, RETURNS_DEEP_STUBS);
+ when(result.next()).thenReturn(true, false);
+ when(result.getString("STATE")).thenReturn("GRANT");
+ when(result.getString("GRANTEE")).thenReturn("dbo");
+ when(result.getString("PRIVILEGE_TYPE")).thenReturn("CONNECT");
+ when(result.getString("DB")).thenReturn("t_order");
+ return result;
+ }
+}