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/29 11:43:21 UTC
[shardingsphere] branch master updated: Implement
OraclePrivilegeHandler and add unit test (#10148)
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 b77716a Implement OraclePrivilegeHandler and add unit test (#10148)
b77716a is described below
commit b77716aaa36b60be42e42b6079df68acbf19d448
Author: Liangda-w <66...@users.noreply.github.com>
AuthorDate: Thu Apr 29 13:42:30 2021 +0200
Implement OraclePrivilegeHandler and add unit test (#10148)
* Implement OraclePrivilegeHandler and add unit test
* Fix quote
---
.../builder/dialect/OraclePrivilegeHandler.java | 31 ++++++++--
.../dialect/OraclePrivilegeHandlerTest.java | 69 +++++++++++++++++++++-
2 files changed, 94 insertions(+), 6 deletions(-)
diff --git a/shardingsphere-infra/shardingsphere-infra-authority/shardingsphere-infra-authority-common/src/main/java/org/apache/shardingsphere/authority/provider/natived/builder/dialect/OraclePrivilegeHandler.java b/shardingsphere-infra/shardingsphere-infra-authority/shardingsphere-infra-authority-common/src/main/java/org/apache/shardingsphere/authority/provider/natived/builder/dialect/OraclePrivilegeHandler.java
index 86b9654..784917c 100644
--- a/shardingsphere-infra/shardingsphere-infra-authority/shardingsphere-infra-authority-common/src/main/java/org/apache/shardingsphere/authority/provider/natived/builder/dialect/OraclePrivilegeHandler.java
+++ b/shardingsphere-infra/shardingsphere-infra-authority/shardingsphere-infra-authority-common/src/main/java/org/apache/shardingsphere/authority/provider/natived/builder/dialect/OraclePrivilegeHandler.java
@@ -17,8 +17,8 @@
package org.apache.shardingsphere.authority.provider.natived.builder.dialect;
-import org.apache.shardingsphere.authority.provider.natived.builder.StoragePrivilegeHandler;
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;
@@ -32,9 +32,9 @@ import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
-import java.util.Collections;
import java.util.HashMap;
import java.util.LinkedHashMap;
+import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
@@ -45,22 +45,45 @@ import java.util.stream.Collectors;
* Oracle privilege handler.
*/
public final class OraclePrivilegeHandler implements StoragePrivilegeHandler {
+
+ private static final String CREATE_USER_SQL = "CREATE USER %s IDENTIFIED BY %s";
+
+ private static final String GRANT_ALL_SQL = "GRANT ALL PRIVILEGES TO %s";
- private static final String SYS_PRIVILEGE_SQL = "SELECT GRANTEE, PRIVILEGE, ADMIN_OPTION, INHERITED FROM DBA_SYS_PRIVS WHERE GRANTEE IN (%s)";
+ private static final String SYS_PRIVILEGE_SQL = "SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE IN (%s)";
private static final String TABLE_PRIVILEGE_SQL = "SELECT GRANTEE, TABLE_SCHEMA, TABLE_NAME, PRIVILEGE, GRANTABLE, INHERITED FROM ALL_TAB_PRIVS WHERE GRANTEE IN (%s)";
@Override
public Collection<ShardingSphereUser> diff(final Collection<ShardingSphereUser> users, final DataSource dataSource) throws SQLException {
- return Collections.emptyList();
+ Collection<Grantee> grantees = new LinkedList<>();
+ try (Connection connection = dataSource.getConnection()) {
+ Statement statement = connection.createStatement();
+ try (ResultSet resultSet = statement.executeQuery(getSysPrivilegesSQL(users))) {
+ while (resultSet.next()) {
+ grantees.add(new Grantee(resultSet.getString("GRANTEE"), ""));
+ }
+ }
+ }
+ return users.stream().filter(each -> !grantees.contains(each.getGrantee())).collect(Collectors.toList());
}
@Override
public void create(final Collection<ShardingSphereUser> users, final DataSource dataSource) throws SQLException {
+ try (Connection connection = dataSource.getConnection(); Statement statement = connection.createStatement()) {
+ for (ShardingSphereUser each : users) {
+ statement.execute(String.format(CREATE_USER_SQL, each.getGrantee().getUsername(), each.getPassword()));
+ }
+ }
}
@Override
public void grantAll(final Collection<ShardingSphereUser> users, final DataSource dataSource) throws SQLException {
+ try (Connection connection = dataSource.getConnection(); Statement statement = connection.createStatement()) {
+ for (ShardingSphereUser each : users) {
+ statement.execute(String.format(GRANT_ALL_SQL, each.getGrantee().getUsername()));
+ }
+ }
}
@Override
diff --git a/shardingsphere-infra/shardingsphere-infra-authority/shardingsphere-infra-authority-common/src/test/java/org/apache/shardingsphere/authority/provider/natived/builder/dialect/OraclePrivilegeHandlerTest.java b/shardingsphere-infra/shardingsphere-infra-authority/shardingsphere-infra-authority-common/src/test/java/org/apache/shardingsphere/authority/provider/natived/builder/dialect/OraclePrivilegeHandlerTest.java
index 36dff0c..db4c8a8 100644
--- a/shardingsphere-infra/shardingsphere-infra-authority/shardingsphere-infra-authority-common/src/test/java/org/apache/shardingsphere/authority/provider/natived/builder/dialect/OraclePrivilegeHandlerTest.java
+++ b/shardingsphere-infra/shardingsphere-infra-authority/shardingsphere-infra-authority-common/src/test/java/org/apache/shardingsphere/authority/provider/natived/builder/dialect/OraclePrivilegeHandlerTest.java
@@ -28,8 +28,10 @@ import org.junit.BeforeClass;
import org.junit.Test;
import javax.sql.DataSource;
+import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
+import java.sql.Statement;
import java.util.Arrays;
import java.util.Collection;
import java.util.LinkedList;
@@ -43,6 +45,7 @@ import static org.junit.Assert.assertEquals;
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.verify;
import static org.mockito.Mockito.when;
public final class OraclePrivilegeHandlerTest {
@@ -53,11 +56,53 @@ public final class OraclePrivilegeHandlerTest {
}
@Test
+ public void assertDiff() throws SQLException {
+ Collection<ShardingSphereUser> newUsers = createUsers();
+ newUsers.add(new ShardingSphereUser("user", "", ""));
+ DataSource dataSource = mockDataSourceForUsers(newUsers);
+ Collection<ShardingSphereUser> result = TypedSPIRegistry.getRegisteredService(StoragePrivilegeHandler.class, "Oracle", new Properties()).diff(newUsers, dataSource);
+ assertDiffUsers(result);
+ }
+
+ @Test
+ public void assertCreate() throws SQLException {
+ Collection<ShardingSphereUser> users = createUsers();
+ DataSource dataSource = mockDataSourceForUsers(users);
+ TypedSPIRegistry.getRegisteredService(StoragePrivilegeHandler.class, "Oracle", new Properties()).create(users, dataSource);
+ assertCreateUsers(users, dataSource.getConnection().createStatement());
+ }
+
+ @Test
+ public void assertGrantAll() throws SQLException {
+ Collection<ShardingSphereUser> users = createUsers();
+ DataSource dataSource = mockDataSourceForUsers(users);
+ TypedSPIRegistry.getRegisteredService(StoragePrivilegeHandler.class, "Oracle", new Properties()).grantAll(users, dataSource);
+ assertGrantUsersAll(users, dataSource.getConnection().createStatement());
+ }
+
+ @Test
public void assertLoad() throws SQLException {
Collection<ShardingSphereUser> users = createUsers();
DataSource dataSource = mockDataSource(users);
assertPrivileges(TypedSPIRegistry.getRegisteredService(StoragePrivilegeHandler.class, "Oracle", new Properties()).load(users, dataSource));
}
+
+ private void assertCreateUsers(final Collection<ShardingSphereUser> users, final Statement statement) throws SQLException {
+ for (ShardingSphereUser each : users) {
+ verify(statement).execute(String.format("CREATE USER %s IDENTIFIED BY %s", each.getGrantee().getUsername(), each.getPassword()));
+ }
+ }
+
+ private void assertDiffUsers(final Collection<ShardingSphereUser> users) {
+ assertThat(users.size(), is(1));
+ assertThat(users.iterator().next().getGrantee().getUsername(), is("user"));
+ }
+
+ private void assertGrantUsersAll(final Collection<ShardingSphereUser> users, final Statement statement) throws SQLException {
+ for (ShardingSphereUser each : users) {
+ verify(statement).execute(String.format("GRANT ALL PRIVILEGES TO %s", each.getGrantee().getUsername()));
+ }
+ }
private void assertPrivileges(final Map<ShardingSphereUser, NativePrivileges> actual) {
assertThat(actual.size(), is(1));
@@ -81,8 +126,8 @@ public final class OraclePrivilegeHandlerTest {
private DataSource mockDataSource(final Collection<ShardingSphereUser> users) throws SQLException {
ResultSet sysPrivilegeResultSet = mockSysPrivilegeResultSet();
DataSource result = mock(DataSource.class, RETURNS_DEEP_STUBS);
- String sysPrivilegeSql = "SELECT GRANTEE, PRIVILEGE, ADMIN_OPTION, INHERITED FROM DBA_SYS_PRIVS WHERE GRANTEE IN (%s)";
- String userList = users.stream().map(item -> String.format("'%s'", item.getGrantee().getUsername(), item.getGrantee().getHostname())).collect(Collectors.joining(", "));
+ String sysPrivilegeSql = "SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE IN (%s)";
+ String userList = users.stream().map(item -> String.format("'%s'", item.getGrantee().getUsername())).collect(Collectors.joining(", "));
when(result.getConnection().createStatement().executeQuery(String.format(sysPrivilegeSql, userList))).thenReturn(sysPrivilegeResultSet);
ResultSet tabPrivilegeResultSet = mockTabPrivilegeResultSet();
String tabPrivilegeSql = "SELECT GRANTEE, TABLE_SCHEMA, TABLE_NAME, PRIVILEGE, GRANTABLE, INHERITED FROM ALL_TAB_PRIVS WHERE GRANTEE IN (%s)";
@@ -90,6 +135,19 @@ public final class OraclePrivilegeHandlerTest {
return result;
}
+ private DataSource mockDataSourceForUsers(final Collection<ShardingSphereUser> users) throws SQLException {
+ ResultSet usersResultSet = mockUsersResultSet();
+ DataSource result = mock(DataSource.class, RETURNS_DEEP_STUBS);
+ Statement statement = mock(Statement.class);
+ Connection connection = mock(Connection.class);
+ String diffUsersSQL = "SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE IN (%s)";
+ String userList = users.stream().map(item -> String.format("'%s'", item.getGrantee().getUsername())).collect(Collectors.joining(", "));
+ when(statement.executeQuery(String.format(diffUsersSQL, userList))).thenReturn(usersResultSet);
+ when(connection.createStatement()).thenReturn(statement);
+ when(result.getConnection()).thenReturn(connection);
+ return result;
+ }
+
private ResultSet mockSysPrivilegeResultSet() throws SQLException {
ResultSet result = mock(ResultSet.class, RETURNS_DEEP_STUBS);
when(result.next()).thenReturn(true, true, true, false);
@@ -108,4 +166,11 @@ public final class OraclePrivilegeHandlerTest {
when(result.getString("GRANTEE")).thenReturn("admin");
return result;
}
+
+ private ResultSet mockUsersResultSet() throws SQLException {
+ ResultSet result = mock(ResultSet.class);
+ when(result.next()).thenReturn(true, false);
+ when(result.getString("GRANTEE")).thenReturn("admin");
+ return result;
+ }
}