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 2023/04/11 10:19:59 UTC

[shardingsphere] branch master updated: Throw exception instead of reply directly in MySQLAuthenticationEngine (#25108)

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 0e6fdfcbe16 Throw exception instead of reply directly in MySQLAuthenticationEngine (#25108)
0e6fdfcbe16 is described below

commit 0e6fdfcbe16084e21678f4610d5d202a0819d4fd
Author: 吴伟杰 <wu...@apache.org>
AuthorDate: Tue Apr 11 18:19:43 2023 +0800

    Throw exception instead of reply directly in MySQLAuthenticationEngine (#25108)
    
    * Throw exception instead of reply directly in MySQLAuthenticationEngine
    
    * Filter logging in FrontendChannelInboundHandler authentication
    
    * Fix checkstyle in MySQLFrontendEngineTest
---
 .../mysql/exception/AccessDeniedException.java     |  38 +++++
 .../exception/DatabaseAccessDeniedException.java   |  38 +++++
 .../mysql/exception/HandshakeException.java        |  28 ++++
 .../mysql/mapper/MySQLDialectExceptionMapper.java  |  20 ++-
 .../mapper/MySQLDialectExceptionMapperTest.java    |  10 +-
 .../netty/FrontendChannelInboundHandler.java       |   7 +-
 .../authentication/MySQLAuthenticationEngine.java  |  27 ++--
 .../frontend/mysql/MySQLFrontendEngineTest.java    | 153 ++++-----------------
 .../MySQLAuthenticationEngineTest.java             |  29 ++--
 9 files changed, 184 insertions(+), 166 deletions(-)

diff --git a/dialect-exception/mysql/src/main/java/org/apache/shardingsphere/dialect/mysql/exception/AccessDeniedException.java b/dialect-exception/mysql/src/main/java/org/apache/shardingsphere/dialect/mysql/exception/AccessDeniedException.java
new file mode 100644
index 00000000000..efda505b4b0
--- /dev/null
+++ b/dialect-exception/mysql/src/main/java/org/apache/shardingsphere/dialect/mysql/exception/AccessDeniedException.java
@@ -0,0 +1,38 @@
+/*
+ * 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.dialect.mysql.exception;
+
+import lombok.Getter;
+import lombok.RequiredArgsConstructor;
+import org.apache.shardingsphere.dialect.exception.SQLDialectException;
+
+/**
+ * Access denied exception.
+ */
+@RequiredArgsConstructor
+@Getter
+public final class AccessDeniedException extends SQLDialectException {
+    
+    private static final long serialVersionUID = -4573828491344359324L;
+    
+    private final String username;
+    
+    private final String hostname;
+    
+    private final boolean usingPassword;
+}
diff --git a/dialect-exception/mysql/src/main/java/org/apache/shardingsphere/dialect/mysql/exception/DatabaseAccessDeniedException.java b/dialect-exception/mysql/src/main/java/org/apache/shardingsphere/dialect/mysql/exception/DatabaseAccessDeniedException.java
new file mode 100644
index 00000000000..e7435fcb879
--- /dev/null
+++ b/dialect-exception/mysql/src/main/java/org/apache/shardingsphere/dialect/mysql/exception/DatabaseAccessDeniedException.java
@@ -0,0 +1,38 @@
+/*
+ * 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.dialect.mysql.exception;
+
+import lombok.Getter;
+import lombok.RequiredArgsConstructor;
+import org.apache.shardingsphere.dialect.exception.SQLDialectException;
+
+/**
+ * Database access denied exception.
+ */
+@RequiredArgsConstructor
+@Getter
+public final class DatabaseAccessDeniedException extends SQLDialectException {
+    
+    private static final long serialVersionUID = 4142387446125168244L;
+    
+    private final String username;
+    
+    private final String hostname;
+    
+    private final String databaseName;
+}
diff --git a/dialect-exception/mysql/src/main/java/org/apache/shardingsphere/dialect/mysql/exception/HandshakeException.java b/dialect-exception/mysql/src/main/java/org/apache/shardingsphere/dialect/mysql/exception/HandshakeException.java
new file mode 100644
index 00000000000..f9c2b42dd5c
--- /dev/null
+++ b/dialect-exception/mysql/src/main/java/org/apache/shardingsphere/dialect/mysql/exception/HandshakeException.java
@@ -0,0 +1,28 @@
+/*
+ * 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.dialect.mysql.exception;
+
+import org.apache.shardingsphere.dialect.exception.SQLDialectException;
+
+/**
+ * Handshake exception.
+ */
+public final class HandshakeException extends SQLDialectException {
+    
+    private static final long serialVersionUID = -3716238116675884886L;
+}
diff --git a/dialect-exception/mysql/src/main/java/org/apache/shardingsphere/dialect/mysql/mapper/MySQLDialectExceptionMapper.java b/dialect-exception/mysql/src/main/java/org/apache/shardingsphere/dialect/mysql/mapper/MySQLDialectExceptionMapper.java
index f712193d53a..ad4bb2783b2 100644
--- a/dialect-exception/mysql/src/main/java/org/apache/shardingsphere/dialect/mysql/mapper/MySQLDialectExceptionMapper.java
+++ b/dialect-exception/mysql/src/main/java/org/apache/shardingsphere/dialect/mysql/mapper/MySQLDialectExceptionMapper.java
@@ -19,9 +19,6 @@ package org.apache.shardingsphere.dialect.mysql.mapper;
 
 import org.apache.shardingsphere.dialect.exception.SQLDialectException;
 import org.apache.shardingsphere.dialect.exception.connection.TooManyConnectionsException;
-import org.apache.shardingsphere.dialect.mysql.exception.UnknownCharsetException;
-import org.apache.shardingsphere.dialect.mysql.exception.UnknownCollationException;
-import org.apache.shardingsphere.dialect.mysql.exception.UnsupportedPreparedStatementException;
 import org.apache.shardingsphere.dialect.exception.data.InsertColumnsAndValuesMismatchedException;
 import org.apache.shardingsphere.dialect.exception.syntax.database.DatabaseCreateExistsException;
 import org.apache.shardingsphere.dialect.exception.syntax.database.DatabaseDropNotExistsException;
@@ -31,6 +28,12 @@ import org.apache.shardingsphere.dialect.exception.syntax.table.NoSuchTableExcep
 import org.apache.shardingsphere.dialect.exception.syntax.table.TableExistsException;
 import org.apache.shardingsphere.dialect.exception.transaction.TableModifyInTransactionException;
 import org.apache.shardingsphere.dialect.mapper.SQLDialectExceptionMapper;
+import org.apache.shardingsphere.dialect.mysql.exception.AccessDeniedException;
+import org.apache.shardingsphere.dialect.mysql.exception.DatabaseAccessDeniedException;
+import org.apache.shardingsphere.dialect.mysql.exception.HandshakeException;
+import org.apache.shardingsphere.dialect.mysql.exception.UnknownCharsetException;
+import org.apache.shardingsphere.dialect.mysql.exception.UnknownCollationException;
+import org.apache.shardingsphere.dialect.mysql.exception.UnsupportedPreparedStatementException;
 import org.apache.shardingsphere.dialect.mysql.vendor.MySQLVendorError;
 import org.apache.shardingsphere.infra.util.exception.external.sql.type.generic.UnknownSQLException;
 import org.apache.shardingsphere.infra.util.exception.external.sql.vendor.VendorError;
@@ -82,6 +85,17 @@ public final class MySQLDialectExceptionMapper implements SQLDialectExceptionMap
         if (sqlDialectException instanceof UnknownCollationException) {
             return toSQLException(MySQLVendorError.ER_UNKNOWN_COLLATION, ((UnknownCollationException) sqlDialectException).getCollationId());
         }
+        if (sqlDialectException instanceof HandshakeException) {
+            return toSQLException(MySQLVendorError.ER_HANDSHAKE_ERROR);
+        }
+        if (sqlDialectException instanceof AccessDeniedException) {
+            AccessDeniedException ex = (AccessDeniedException) sqlDialectException;
+            return toSQLException(MySQLVendorError.ER_ACCESS_DENIED_ERROR, ex.getUsername(), ex.getHostname(), ex.isUsingPassword() ? "YES" : "NO");
+        }
+        if (sqlDialectException instanceof DatabaseAccessDeniedException) {
+            DatabaseAccessDeniedException ex = (DatabaseAccessDeniedException) sqlDialectException;
+            return toSQLException(MySQLVendorError.ER_DBACCESS_DENIED_ERROR, ex.getUsername(), ex.getHostname(), ex.getDatabaseName());
+        }
         return new UnknownSQLException(sqlDialectException).toSQLException();
     }
     
diff --git a/dialect-exception/mysql/src/test/java/org/apache/shardingsphere/dialect/mysql/mapper/MySQLDialectExceptionMapperTest.java b/dialect-exception/mysql/src/test/java/org/apache/shardingsphere/dialect/mysql/mapper/MySQLDialectExceptionMapperTest.java
index 08efa91a667..9a87f15ee61 100644
--- a/dialect-exception/mysql/src/test/java/org/apache/shardingsphere/dialect/mysql/mapper/MySQLDialectExceptionMapperTest.java
+++ b/dialect-exception/mysql/src/test/java/org/apache/shardingsphere/dialect/mysql/mapper/MySQLDialectExceptionMapperTest.java
@@ -27,6 +27,10 @@ import org.apache.shardingsphere.dialect.exception.syntax.database.UnknownDataba
 import org.apache.shardingsphere.dialect.exception.syntax.table.NoSuchTableException;
 import org.apache.shardingsphere.dialect.exception.syntax.table.TableExistsException;
 import org.apache.shardingsphere.dialect.exception.transaction.TableModifyInTransactionException;
+import org.apache.shardingsphere.dialect.mysql.exception.AccessDeniedException;
+import org.apache.shardingsphere.dialect.mysql.exception.DatabaseAccessDeniedException;
+import org.apache.shardingsphere.dialect.mysql.exception.HandshakeException;
+import org.apache.shardingsphere.dialect.mysql.exception.UnknownCharsetException;
 import org.apache.shardingsphere.dialect.mysql.exception.UnknownCollationException;
 import org.apache.shardingsphere.dialect.mysql.exception.UnsupportedPreparedStatementException;
 import org.apache.shardingsphere.dialect.mysql.vendor.MySQLVendorError;
@@ -68,7 +72,11 @@ class MySQLDialectExceptionMapperTest {
                     Arguments.of(TableModifyInTransactionException.class, MySQLVendorError.ER_ERROR_ON_MODIFYING_GTID_EXECUTED_TABLE),
                     Arguments.of(TooManyConnectionsException.class, MySQLVendorError.ER_CON_COUNT_ERROR),
                     Arguments.of(UnsupportedPreparedStatementException.class, MySQLVendorError.ER_UNSUPPORTED_PS),
-                    Arguments.of(UnknownCollationException.class, MySQLVendorError.ER_UNKNOWN_COLLATION));
+                    Arguments.of(UnknownCharsetException.class, MySQLVendorError.ER_UNKNOWN_CHARACTER_SET),
+                    Arguments.of(UnknownCollationException.class, MySQLVendorError.ER_UNKNOWN_COLLATION),
+                    Arguments.of(HandshakeException.class, MySQLVendorError.ER_HANDSHAKE_ERROR),
+                    Arguments.of(AccessDeniedException.class, MySQLVendorError.ER_ACCESS_DENIED_ERROR),
+                    Arguments.of(DatabaseAccessDeniedException.class, MySQLVendorError.ER_DBACCESS_DENIED_ERROR));
         }
     }
 }
diff --git a/proxy/frontend/core/src/main/java/org/apache/shardingsphere/proxy/frontend/netty/FrontendChannelInboundHandler.java b/proxy/frontend/core/src/main/java/org/apache/shardingsphere/proxy/frontend/netty/FrontendChannelInboundHandler.java
index 24ba89a9ca1..b2de0495633 100644
--- a/proxy/frontend/core/src/main/java/org/apache/shardingsphere/proxy/frontend/netty/FrontendChannelInboundHandler.java
+++ b/proxy/frontend/core/src/main/java/org/apache/shardingsphere/proxy/frontend/netty/FrontendChannelInboundHandler.java
@@ -31,6 +31,7 @@ import org.apache.shardingsphere.infra.util.spi.type.typed.TypedSPILoader;
 import org.apache.shardingsphere.proxy.backend.context.ProxyContext;
 import org.apache.shardingsphere.proxy.backend.session.ConnectionSession;
 import org.apache.shardingsphere.proxy.frontend.authentication.AuthenticationResult;
+import org.apache.shardingsphere.proxy.frontend.exception.ExpectedExceptions;
 import org.apache.shardingsphere.proxy.frontend.executor.ConnectionThreadExecutorGroup;
 import org.apache.shardingsphere.proxy.frontend.executor.UserExecutorGroup;
 import org.apache.shardingsphere.proxy.frontend.spi.DatabaseProtocolFrontendEngine;
@@ -85,7 +86,11 @@ public final class FrontendChannelInboundHandler extends ChannelInboundHandlerAd
             // CHECKSTYLE:OFF
         } catch (final Exception ex) {
             // CHECKSTYLE:ON
-            log.error("Exception occur: ", ex);
+            if (!ExpectedExceptions.isExpected(ex.getClass())) {
+                log.error("Exception occur: ", ex);
+            } else if (log.isDebugEnabled()) {
+                log.debug("Exception occur: ", ex);
+            }
             context.writeAndFlush(databaseProtocolFrontendEngine.getCommandExecuteEngine().getErrorPacket(ex));
             context.close();
         }
diff --git a/proxy/frontend/type/mysql/src/main/java/org/apache/shardingsphere/proxy/frontend/mysql/authentication/MySQLAuthenticationEngine.java b/proxy/frontend/type/mysql/src/main/java/org/apache/shardingsphere/proxy/frontend/mysql/authentication/MySQLAuthenticationEngine.java
index b7a90d32787..08bf7de52cf 100644
--- a/proxy/frontend/type/mysql/src/main/java/org/apache/shardingsphere/proxy/frontend/mysql/authentication/MySQLAuthenticationEngine.java
+++ b/proxy/frontend/type/mysql/src/main/java/org/apache/shardingsphere/proxy/frontend/mysql/authentication/MySQLAuthenticationEngine.java
@@ -30,16 +30,18 @@ import org.apache.shardingsphere.db.protocol.mysql.constant.MySQLCharacterSet;
 import org.apache.shardingsphere.db.protocol.mysql.constant.MySQLConnectionPhase;
 import org.apache.shardingsphere.db.protocol.mysql.constant.MySQLConstants;
 import org.apache.shardingsphere.db.protocol.mysql.constant.MySQLStatusFlag;
-import org.apache.shardingsphere.db.protocol.mysql.packet.generic.MySQLErrPacket;
 import org.apache.shardingsphere.db.protocol.mysql.packet.generic.MySQLOKPacket;
-import org.apache.shardingsphere.db.protocol.mysql.packet.handshake.MySQLAuthenticationPluginData;
 import org.apache.shardingsphere.db.protocol.mysql.packet.handshake.MySQLAuthSwitchRequestPacket;
 import org.apache.shardingsphere.db.protocol.mysql.packet.handshake.MySQLAuthSwitchResponsePacket;
+import org.apache.shardingsphere.db.protocol.mysql.packet.handshake.MySQLAuthenticationPluginData;
 import org.apache.shardingsphere.db.protocol.mysql.packet.handshake.MySQLHandshakePacket;
 import org.apache.shardingsphere.db.protocol.mysql.packet.handshake.MySQLHandshakeResponse41Packet;
 import org.apache.shardingsphere.db.protocol.mysql.payload.MySQLPacketPayload;
 import org.apache.shardingsphere.db.protocol.payload.PacketPayload;
-import org.apache.shardingsphere.dialect.mysql.vendor.MySQLVendorError;
+import org.apache.shardingsphere.dialect.exception.syntax.database.UnknownDatabaseException;
+import org.apache.shardingsphere.dialect.mysql.exception.AccessDeniedException;
+import org.apache.shardingsphere.dialect.mysql.exception.DatabaseAccessDeniedException;
+import org.apache.shardingsphere.dialect.mysql.exception.HandshakeException;
 import org.apache.shardingsphere.infra.metadata.user.Grantee;
 import org.apache.shardingsphere.infra.metadata.user.ShardingSphereUser;
 import org.apache.shardingsphere.proxy.backend.context.ProxyContext;
@@ -92,14 +94,10 @@ public final class MySQLAuthenticationEngine implements AuthenticationEngine {
         }
         Grantee grantee = new Grantee(currentAuthResult.getUsername(), getHostAddress(context));
         if (!login(rule, grantee, authResponse)) {
-            writeErrorPacket(context,
-                    new MySQLErrPacket(MySQLVendorError.ER_ACCESS_DENIED_ERROR, currentAuthResult.getUsername(), grantee.getHostname(), 0 == authResponse.length ? "NO" : "YES"));
-            return AuthenticationResultBuilder.continued();
+            throw new AccessDeniedException(currentAuthResult.getUsername(), grantee.getHostname(), 0 != authResponse.length);
         }
         if (!authorizeDatabase(rule, grantee, currentAuthResult.getDatabase())) {
-            writeErrorPacket(context,
-                    new MySQLErrPacket(MySQLVendorError.ER_DBACCESS_DENIED_ERROR, currentAuthResult.getUsername(), grantee.getHostname(), currentAuthResult.getDatabase()));
-            return AuthenticationResultBuilder.continued();
+            throw new DatabaseAccessDeniedException(currentAuthResult.getUsername(), grantee.getHostname(), currentAuthResult.getDatabase());
         }
         writeOKPacket(context);
         return AuthenticationResultBuilder.finished(grantee.getUsername(), grantee.getHostname(), currentAuthResult.getDatabase());
@@ -110,18 +108,16 @@ public final class MySQLAuthenticationEngine implements AuthenticationEngine {
         try {
             handshakeResponsePacket = new MySQLHandshakeResponse41Packet((MySQLPacketPayload) payload);
         } catch (IndexOutOfBoundsException ex) {
-            writeErrorPacket(context, new MySQLErrPacket(MySQLVendorError.ER_HANDSHAKE_ERROR));
             if (log.isWarnEnabled()) {
                 log.warn("Received bad handshake from client {}: \n{}", context.channel(), ByteBufUtil.prettyHexDump(payload.getByteBuf().resetReaderIndex()));
             }
-            return AuthenticationResultBuilder.continued();
+            throw new HandshakeException();
         }
         String database = handshakeResponsePacket.getDatabase();
         authResponse = handshakeResponsePacket.getAuthResponse();
         setCharacterSet(context, handshakeResponsePacket);
         if (!Strings.isNullOrEmpty(database) && !ProxyContext.getInstance().databaseExists(database)) {
-            writeErrorPacket(context, new MySQLErrPacket(MySQLVendorError.ER_BAD_DB_ERROR, database));
-            return AuthenticationResultBuilder.continued();
+            throw new UnknownDatabaseException(database);
         }
         String username = handshakeResponsePacket.getUsername();
         String hostname = getHostAddress(context);
@@ -167,11 +163,6 @@ public final class MySQLAuthenticationEngine implements AuthenticationEngine {
         return socketAddress instanceof InetSocketAddress ? ((InetSocketAddress) socketAddress).getAddress().getHostAddress() : socketAddress.toString();
     }
     
-    private void writeErrorPacket(final ChannelHandlerContext context, final MySQLErrPacket errPacket) {
-        context.writeAndFlush(errPacket);
-        context.close();
-    }
-    
     private void writeOKPacket(final ChannelHandlerContext context) {
         context.writeAndFlush(new MySQLOKPacket(MySQLStatusFlag.SERVER_STATUS_AUTOCOMMIT.getValue()));
     }
diff --git a/proxy/frontend/type/mysql/src/test/java/org/apache/shardingsphere/proxy/frontend/mysql/MySQLFrontendEngineTest.java b/proxy/frontend/type/mysql/src/test/java/org/apache/shardingsphere/proxy/frontend/mysql/MySQLFrontendEngineTest.java
index 9eca8103982..b9bc4fd00e9 100644
--- a/proxy/frontend/type/mysql/src/test/java/org/apache/shardingsphere/proxy/frontend/mysql/MySQLFrontendEngineTest.java
+++ b/proxy/frontend/type/mysql/src/test/java/org/apache/shardingsphere/proxy/frontend/mysql/MySQLFrontendEngineTest.java
@@ -18,176 +18,73 @@
 package org.apache.shardingsphere.proxy.frontend.mysql;
 
 import io.netty.channel.Channel;
-import io.netty.channel.ChannelHandlerContext;
-import lombok.SneakyThrows;
-import org.apache.shardingsphere.authority.config.AuthorityRuleConfiguration;
-import org.apache.shardingsphere.authority.rule.AuthorityRule;
-import org.apache.shardingsphere.authority.rule.builder.AuthorityRuleBuilder;
-import org.apache.shardingsphere.db.protocol.mysql.constant.MySQLConnectionPhase;
 import org.apache.shardingsphere.db.protocol.mysql.constant.MySQLConstants;
-import org.apache.shardingsphere.db.protocol.mysql.packet.generic.MySQLErrPacket;
-import org.apache.shardingsphere.db.protocol.mysql.packet.generic.MySQLOKPacket;
-import org.apache.shardingsphere.db.protocol.mysql.packet.handshake.MySQLHandshakePacket;
-import org.apache.shardingsphere.db.protocol.mysql.payload.MySQLPacketPayload;
-import org.apache.shardingsphere.infra.config.algorithm.AlgorithmConfiguration;
-import org.apache.shardingsphere.infra.config.props.ConfigurationProperties;
-import org.apache.shardingsphere.infra.database.type.dialect.MySQLDatabaseType;
-import org.apache.shardingsphere.infra.metadata.ShardingSphereMetaData;
-import org.apache.shardingsphere.infra.metadata.database.ShardingSphereDatabase;
-import org.apache.shardingsphere.infra.metadata.database.rule.ShardingSphereRuleMetaData;
-import org.apache.shardingsphere.infra.metadata.user.ShardingSphereUser;
-import org.apache.shardingsphere.mode.manager.ContextManager;
-import org.apache.shardingsphere.mode.metadata.MetaDataContexts;
-import org.apache.shardingsphere.metadata.persist.MetaDataPersistService;
+import org.apache.shardingsphere.db.protocol.mysql.constant.MySQLServerInfo;
+import org.apache.shardingsphere.db.protocol.mysql.netty.MySQLSequenceIDInboundHandler;
 import org.apache.shardingsphere.proxy.backend.context.ProxyContext;
-import org.apache.shardingsphere.proxy.frontend.authentication.AuthenticationResult;
-import org.apache.shardingsphere.proxy.frontend.connection.ConnectionIdGenerator;
-import org.apache.shardingsphere.proxy.frontend.mysql.authentication.MySQLAuthenticationEngine;
+import org.apache.shardingsphere.proxy.backend.session.ConnectionSession;
+import org.apache.shardingsphere.proxy.frontend.mysql.command.query.binary.MySQLStatementIDGenerator;
+import org.apache.shardingsphere.proxy.frontend.netty.FrontendChannelInboundHandler;
 import org.apache.shardingsphere.test.mock.AutoMockExtension;
 import org.apache.shardingsphere.test.mock.StaticMockSettings;
 import org.junit.jupiter.api.BeforeEach;
 import org.junit.jupiter.api.Test;
 import org.junit.jupiter.api.extension.ExtendWith;
 import org.mockito.Answers;
-import org.mockito.ArgumentMatcher;
 import org.mockito.Mock;
-import org.mockito.internal.configuration.plugins.Plugins;
+import org.mockito.MockedStatic;
 import org.mockito.junit.jupiter.MockitoSettings;
 import org.mockito.quality.Strictness;
 
-import java.net.InetAddress;
-import java.net.InetSocketAddress;
-import java.net.UnknownHostException;
-import java.util.Collections;
-import java.util.HashMap;
-import java.util.Map;
-import java.util.Properties;
 import java.util.concurrent.atomic.AtomicInteger;
 
-import static org.hamcrest.CoreMatchers.is;
-import static org.hamcrest.MatcherAssert.assertThat;
-import static org.junit.jupiter.api.Assertions.assertFalse;
-import static org.junit.jupiter.api.Assertions.assertNull;
-import static org.junit.jupiter.api.Assertions.assertTrue;
 import static org.mockito.ArgumentMatchers.any;
-import static org.mockito.ArgumentMatchers.argThat;
+import static org.mockito.ArgumentMatchers.eq;
 import static org.mockito.ArgumentMatchers.isA;
-import static org.mockito.Mockito.RETURNS_DEEP_STUBS;
 import static org.mockito.Mockito.mock;
+import static org.mockito.Mockito.mockStatic;
 import static org.mockito.Mockito.verify;
 import static org.mockito.Mockito.when;
 
 @ExtendWith(AutoMockExtension.class)
-@StaticMockSettings(ProxyContext.class)
+@StaticMockSettings({ProxyContext.class, MySQLStatementIDGenerator.class})
 @MockitoSettings(strictness = Strictness.LENIENT)
 class MySQLFrontendEngineTest {
     
-    private static final String SCHEMA_PATTERN = "schema_%s";
-    
     private MySQLFrontendEngine engine;
     
-    @Mock
-    private ChannelHandlerContext context;
-    
     @Mock(answer = Answers.RETURNS_DEEP_STUBS)
     private Channel channel;
     
-    @Mock
-    private MySQLPacketPayload payload;
-    
     @BeforeEach
-    void setUp() throws ReflectiveOperationException {
+    void setUp() {
         engine = new MySQLFrontendEngine();
-        Plugins.getMemberAccessor().set(ConnectionIdGenerator.class.getDeclaredField("currentId"), ConnectionIdGenerator.getInstance(), 0);
-        when(context.channel()).thenReturn(channel);
     }
     
     @Test
     void assertInitChannel() {
         engine.initChannel(channel);
         verify(channel.attr(MySQLConstants.MYSQL_SEQUENCE_ID)).set(any(AtomicInteger.class));
+        verify(channel.pipeline())
+                .addBefore(eq(FrontendChannelInboundHandler.class.getSimpleName()), eq(MySQLSequenceIDInboundHandler.class.getSimpleName()), isA(MySQLSequenceIDInboundHandler.class));
     }
     
     @Test
-    void assertHandshake() {
-        assertTrue(engine.getAuthenticationEngine().handshake(context) > 0);
-        verify(context).writeAndFlush(isA(MySQLHandshakePacket.class));
-    }
-    
-    @Test
-    void assertAuthWhenLoginSuccess() {
-        setConnectionPhase(MySQLConnectionPhase.AUTH_PHASE_FAST_PATH);
-        ContextManager contextManager = mockContextManager(new ShardingSphereUser("root", "", ""));
-        when(ProxyContext.getInstance().getContextManager()).thenReturn(contextManager);
-        when(payload.readInt1()).thenReturn(1);
-        when(payload.readStringNul()).thenReturn("root");
-        when(payload.readStringNulByBytes()).thenReturn("root".getBytes());
-        when(channel.remoteAddress()).thenReturn(new InetSocketAddress("localhost", 3307));
-        AuthenticationResult actual = engine.getAuthenticationEngine().authenticate(context, payload);
-        assertThat(actual.getUsername(), is("root"));
-        assertNull(actual.getDatabase());
-        assertTrue(actual.isFinished());
-        verify(context).writeAndFlush(isA(MySQLOKPacket.class));
-    }
-    
-    @Test
-    void assertAuthWhenLoginFailure() {
-        setConnectionPhase(MySQLConnectionPhase.AUTH_PHASE_FAST_PATH);
-        ContextManager contextManager = mockContextManager(new ShardingSphereUser("root", "error", ""));
-        when(ProxyContext.getInstance().getContextManager()).thenReturn(contextManager);
-        when(payload.readInt1()).thenReturn(1);
-        when(payload.readStringNul()).thenReturn("root");
-        when(payload.readStringNulByBytes()).thenReturn("root".getBytes());
-        when(channel.remoteAddress()).thenReturn(new InetSocketAddress("localhost", 3307));
-        AuthenticationResult actual = engine.getAuthenticationEngine().authenticate(context, payload);
-        assertNull(actual.getUsername());
-        assertNull(actual.getDatabase());
-        assertFalse(actual.isFinished());
-        verify(context).writeAndFlush(isA(MySQLErrPacket.class));
-    }
-    
-    @Test
-    void assertErrorMsgWhenLoginFailure() throws UnknownHostException {
-        setConnectionPhase(MySQLConnectionPhase.AUTH_PHASE_FAST_PATH);
-        ContextManager contextManager = mockContextManager(new ShardingSphereUser("root", "error", ""));
-        when(ProxyContext.getInstance().getContextManager()).thenReturn(contextManager);
-        when(payload.readInt1()).thenReturn(1);
-        when(payload.readStringNul()).thenReturn("root");
-        when(payload.readStringNulByBytes()).thenReturn("root".getBytes());
-        when(channel.remoteAddress()).thenReturn(new InetSocketAddress(InetAddress.getByAddress(new byte[]{(byte) 192, (byte) 168, (byte) 0, (byte) 102}), 3307));
-        AuthenticationResult actual = engine.getAuthenticationEngine().authenticate(context, payload);
-        assertNull(actual.getUsername());
-        assertNull(actual.getDatabase());
-        assertFalse(actual.isFinished());
-        verify(context).writeAndFlush(argThat((ArgumentMatcher<MySQLErrPacket>) argument -> "Access denied for user 'root'@'192.168.0.102' (using password: YES)".equals(argument.getErrorMessage())));
-    }
-    
-    @SneakyThrows(ReflectiveOperationException.class)
-    private void setConnectionPhase(final MySQLConnectionPhase connectionPhase) {
-        Plugins.getMemberAccessor().set(MySQLAuthenticationEngine.class.getDeclaredField("connectionPhase"), engine.getAuthenticationEngine(), connectionPhase);
-    }
-    
-    private ContextManager mockContextManager(final ShardingSphereUser user) {
-        ContextManager result = mock(ContextManager.class, RETURNS_DEEP_STUBS);
-        MetaDataContexts metaDataContexts = new MetaDataContexts(
-                mock(MetaDataPersistService.class), new ShardingSphereMetaData(createDatabases(), createGlobalRuleMetaData(user), new ConfigurationProperties(new Properties())));
-        when(result.getMetaDataContexts()).thenReturn(metaDataContexts);
-        return result;
-    }
-    
-    private Map<String, ShardingSphereDatabase> createDatabases() {
-        Map<String, ShardingSphereDatabase> result = new HashMap<>(10, 1);
-        for (int i = 0; i < 10; i++) {
-            ShardingSphereDatabase database = mock(ShardingSphereDatabase.class, RETURNS_DEEP_STUBS);
-            when(database.getProtocolType()).thenReturn(new MySQLDatabaseType());
-            result.put(String.format(SCHEMA_PATTERN, i), database);
+    void assertSetDatabaseVersion() {
+        String databaseName = "db";
+        String databaseVersion = "version";
+        try (MockedStatic<MySQLServerInfo> mockedStatic = mockStatic(MySQLServerInfo.class)) {
+            engine.setDatabaseVersion(databaseName, databaseVersion);
+            mockedStatic.verify(() -> MySQLServerInfo.setServerVersion(databaseName, databaseVersion));
         }
-        return result;
     }
     
-    private ShardingSphereRuleMetaData createGlobalRuleMetaData(final ShardingSphereUser user) {
-        AuthorityRuleConfiguration ruleConfig = new AuthorityRuleConfiguration(Collections.singletonList(user), new AlgorithmConfiguration("ALL_PERMITTED", new Properties()), null);
-        AuthorityRule rule = new AuthorityRuleBuilder().build(ruleConfig, Collections.emptyMap(), mock(ConfigurationProperties.class));
-        return new ShardingSphereRuleMetaData(Collections.singleton(rule));
+    @Test
+    void assertRelease() {
+        ConnectionSession connectionSession = mock(ConnectionSession.class);
+        int connectionId = 1;
+        when(connectionSession.getConnectionId()).thenReturn(connectionId);
+        engine.release(connectionSession);
+        verify(MySQLStatementIDGenerator.getInstance()).unregisterConnection(connectionId);
     }
 }
diff --git a/proxy/frontend/type/mysql/src/test/java/org/apache/shardingsphere/proxy/frontend/mysql/authentication/MySQLAuthenticationEngineTest.java b/proxy/frontend/type/mysql/src/test/java/org/apache/shardingsphere/proxy/frontend/mysql/authentication/MySQLAuthenticationEngineTest.java
index 14854ac0902..89ec69ac598 100644
--- a/proxy/frontend/type/mysql/src/test/java/org/apache/shardingsphere/proxy/frontend/mysql/authentication/MySQLAuthenticationEngineTest.java
+++ b/proxy/frontend/type/mysql/src/test/java/org/apache/shardingsphere/proxy/frontend/mysql/authentication/MySQLAuthenticationEngineTest.java
@@ -22,6 +22,7 @@ import io.netty.channel.Channel;
 import io.netty.channel.ChannelHandlerContext;
 import io.netty.util.Attribute;
 import lombok.SneakyThrows;
+import org.apache.shardingsphere.authority.provider.simple.model.privilege.AllPrivilegesPermittedShardingSpherePrivileges;
 import org.apache.shardingsphere.authority.rule.AuthorityRule;
 import org.apache.shardingsphere.db.protocol.constant.CommonConstants;
 import org.apache.shardingsphere.db.protocol.mysql.constant.MySQLCapabilityFlag;
@@ -31,6 +32,10 @@ import org.apache.shardingsphere.db.protocol.mysql.packet.generic.MySQLErrPacket
 import org.apache.shardingsphere.db.protocol.mysql.packet.generic.MySQLOKPacket;
 import org.apache.shardingsphere.db.protocol.mysql.packet.handshake.MySQLHandshakePacket;
 import org.apache.shardingsphere.db.protocol.mysql.payload.MySQLPacketPayload;
+import org.apache.shardingsphere.dialect.exception.syntax.database.UnknownDatabaseException;
+import org.apache.shardingsphere.dialect.mysql.exception.AccessDeniedException;
+import org.apache.shardingsphere.dialect.mysql.exception.DatabaseAccessDeniedException;
+import org.apache.shardingsphere.dialect.mysql.exception.HandshakeException;
 import org.apache.shardingsphere.dialect.mysql.vendor.MySQLVendorError;
 import org.apache.shardingsphere.infra.config.props.ConfigurationProperties;
 import org.apache.shardingsphere.infra.metadata.ShardingSphereMetaData;
@@ -66,6 +71,7 @@ import java.util.Properties;
 
 import static org.hamcrest.CoreMatchers.is;
 import static org.hamcrest.MatcherAssert.assertThat;
+import static org.junit.jupiter.api.Assertions.assertThrows;
 import static org.junit.jupiter.api.Assertions.assertTrue;
 import static org.mockito.ArgumentMatchers.any;
 import static org.mockito.Mockito.RETURNS_DEEP_STUBS;
@@ -100,9 +106,7 @@ class MySQLAuthenticationEngineTest {
         ChannelHandlerContext context = mockChannelHandlerContext();
         authenticationEngine.handshake(context);
         try (MockedConstruction<MySQLErrPacket> ignored = mockConstruction(MySQLErrPacket.class, this::assertBadHandshakeError)) {
-            authenticationEngine.authenticate(context, new MySQLPacketPayload(Unpooled.wrappedBuffer(new byte[]{0x02, 0x03}), StandardCharsets.UTF_8));
-            verify(context).writeAndFlush(any(MySQLErrPacket.class));
-            verify(context).close();
+            assertThrows(HandshakeException.class, () -> authenticationEngine.authenticate(context, new MySQLPacketPayload(Unpooled.wrappedBuffer(new byte[]{0x02, 0x03}), StandardCharsets.UTF_8)));
         }
     }
     
@@ -146,6 +150,9 @@ class MySQLAuthenticationEngineTest {
         when(channelHandlerContext.channel()).thenReturn(channel);
         setAuthenticationResult();
         AuthorityRule rule = mock(AuthorityRule.class);
+        ShardingSphereUser user = new ShardingSphereUser("root", "", "127.0.0.1");
+        when(rule.findUser(user.getGrantee())).thenReturn(Optional.of(user));
+        when(rule.findPrivileges(user.getGrantee())).thenReturn(Optional.of(new AllPrivilegesPermittedShardingSpherePrivileges()));
         when(rule.getAuthenticatorType(any())).thenReturn("");
         ContextManager contextManager = mockContextManager(rule);
         when(ProxyContext.getInstance().getContextManager()).thenReturn(contextManager);
@@ -169,9 +176,7 @@ class MySQLAuthenticationEngineTest {
         when(ProxyContext.getInstance().getContextManager()).thenReturn(contextManager);
         when(ProxyContext.getInstance().databaseExists("foo_db")).thenReturn(true);
         try (MockedConstruction<MySQLErrPacket> ignored = mockConstruction(MySQLErrPacket.class, (mock, mockContext) -> assertAuthenticationErrorPacket(mockContext.arguments()))) {
-            authenticationEngine.authenticate(context, getPayload("root", "foo_db", authResponse));
-            verify(context).writeAndFlush(any(MySQLErrPacket.class));
-            verify(context).close();
+            assertThrows(AccessDeniedException.class, () -> authenticationEngine.authenticate(context, getPayload("root", "foo_db", authResponse)));
         }
     }
     
@@ -191,9 +196,7 @@ class MySQLAuthenticationEngineTest {
                 MockedConstruction<AuthenticatorFactory> mockedAuthenticatorFactory = mockConstruction(AuthenticatorFactory.class,
                         (mock, mockContext) -> when(mock.newInstance(user)).thenReturn(mock(Authenticator.class)));
                 MockedConstruction<MySQLErrPacket> mockedErrPacket = mockConstruction(MySQLErrPacket.class, (mock, mockContext) -> assertAuthenticationErrorPacket(mockContext.arguments()))) {
-            authenticationEngine.authenticate(context, getPayload("root", "foo_db", authResponse));
-            verify(context).writeAndFlush(any(MySQLErrPacket.class));
-            verify(context).close();
+            assertThrows(AccessDeniedException.class, () -> authenticationEngine.authenticate(context, getPayload("root", "foo_db", authResponse)));
         }
     }
     
@@ -214,9 +217,7 @@ class MySQLAuthenticationEngineTest {
         when(ProxyContext.getInstance().getContextManager()).thenReturn(contextManager);
         when(ProxyContext.getInstance().databaseExists("foo_db")).thenReturn(true);
         try (MockedConstruction<MySQLErrPacket> ignored = mockConstruction(MySQLErrPacket.class, (mock, mockContext) -> assertDatabaseAccessDeniedErrorPacket(mockContext.arguments()))) {
-            authenticationEngine.authenticate(context, getPayload("root", "foo_db", authResponse));
-            verify(context).writeAndFlush(any(MySQLErrPacket.class));
-            verify(context).close();
+            assertThrows(DatabaseAccessDeniedException.class, () -> authenticationEngine.authenticate(context, getPayload("root", "foo_db", authResponse)));
         }
     }
     
@@ -234,9 +235,7 @@ class MySQLAuthenticationEngineTest {
         ContextManager contextManager = mockContextManager(rule);
         when(ProxyContext.getInstance().getContextManager()).thenReturn(contextManager);
         try (MockedConstruction<MySQLErrPacket> ignored = mockConstruction(MySQLErrPacket.class, (mock, mockContext) -> assertInvalidDatabaseErrorPacket(mockContext.arguments()))) {
-            authenticationEngine.authenticate(context, getPayload("root", "invalid_db", authResponse));
-            verify(context).writeAndFlush(any(MySQLErrPacket.class));
-            verify(context).close();
+            assertThrows(UnknownDatabaseException.class, () -> authenticationEngine.authenticate(context, getPayload("root", "invalid_db", authResponse)));
         }
     }