terrymanu commented on issue #38767: URL: https://github.com/apache/shardingsphere/issues/38767#issuecomment-4618632257
Hi @liangdaqin, thanks for the question. This should be handled as `type: question`. The ShardingSphere-supported path is to use ShardingSphere-JDBC as a JDBC driver when the database management tool can load a custom JDBC driver and its dependencies. For SQL Server / Oracle backends in ShardingSphere 5.5.2, ShardingSphere-Proxy does not provide a SQL Server or Oracle frontend protocol, so a tool that connects directly to the physical SQL Server / Oracle database bypasses ShardingSphere and will only see ciphertext. The recommended configuration model is: configure the tool with driver class `org.apache.shardingsphere.driver.ShardingSphereDriver`, put ShardingSphere-JDBC plus the required backend JDBC driver and dependencies on that tool's driver classpath, and use a ShardingSphere JDBC URL such as `jdbc:shardingsphere:absolutepath:/path/to/config.yaml` or `jdbc:shardingsphere:classpath:config.yaml`. The YAML should contain the logical data source and the `!ENCRYPT` rule. In that model, the tool queries logical tables through ShardingSphere-JDBC, so decryptable encrypted columns can be returned as plaintext by ShardingSphere. Generic database management tools usually interact through `java.sql.Driver`; they usually do not call `YamlShardingSphereDataSourceFactory` directly unless the tool has its own extension mechanism. If a tool cannot load the custom JDBC driver, supporting jars, and reachable YAML configuration, then ShardingSphere-JDBC cannot be transparently inserted into that tool. For Proxy, SQL Server / Oracle frontend protocol support would be a separate enhancement/design topic, not existing 5.5.2 functionality. The practical choices today are to use a custom ShardingSphere-JDBC driver setup in the management tool, or provide a small internal operational Java tool/service that embeds ShardingSphere-JDBC for authorized plaintext inspection. Community members are welcome to share concrete custom-driver examples for database tools. I suggest labeling this as `type: question` and closing it once the usage guidance is clear. The reply above is based on the analysis below; the detailed reasoning is kept here for reference and follow-up contributors. ### Problem Understanding - **Issue:** The issue asks how DBAs/developers can view decrypted data through database management tools when using ShardingSphere-JDBC 5.5.2, backend SQL Server / Oracle, and Data Encrypt. Source: https://github.com/apache/shardingsphere/issues/38767 - **Topology:** ShardingSphere-JDBC; governance mode and registry/config center are not specified and do not change the supportability decision. ShardingSphere-Proxy is discussed only as an alternative access path. - **Observed Evidence:** `OBS-1`, `OBS-2`, `OBS-3`, `OBS-4`, `OBS-5`, `OBS-6`, `OBS-7` - **OBS-1:** The issue states that direct physical SQL Server / Oracle access sees encrypted ciphertext, while the desired path is decrypted logical data through ShardingSphere. Source: https://github.com/apache/shardingsphere/issues/38767 - **OBS-2:** The 5.5.2 JDBC Driver docs state that ShardingSphere-JDBC provides a JDBC Driver and name `org.apache.shardingsphere.driver.ShardingSphereDriver`. Source: https://shardingsphere.apache.org/document/5.5.2/en/user-manual/shardingsphere-jdbc/yaml-config/jdbc-driver/ ; `docs/document/content/user-manual/shardingsphere-jdbc/yaml-config/jdbc-driver/_index.en.md:9` - **OBS-3:** The 5.5.2 JDBC Driver docs show `DriverManager.getConnection(jdbcUrl)` and Hikari examples with `jdbc:shardingsphere:classpath:config.yaml`. Source: https://shardingsphere.apache.org/document/5.5.2/en/user-manual/shardingsphere-jdbc/yaml-config/jdbc-driver/ ; `docs/document/content/user-manual/shardingsphere-jdbc/yaml-config/jdbc-driver/_index.en.md:37` - **OBS-4:** The 5.5.2 known implementation docs support loading YAML by `jdbc:shardingsphere:classpath:config.yaml` and `jdbc:shardingsphere:absolutepath:/path/to/config.yaml`. Source: https://shardingsphere.apache.org/document/5.5.2/en/user-manual/shardingsphere-jdbc/yaml-config/jdbc-driver/known-implementation/ ; `docs/document/content/user-manual/shardingsphere-jdbc/yaml-config/jdbc-driver/known-implementation/_index.en.md:38` - **OBS-5:** `ShardingSphereDriver` implements `java.sql.Driver`, registers itself through `DriverManager`, and accepts URLs starting with `jdbc:shardingsphere:`. Source: `jdbc/src/main/java/org/apache/shardingsphere/driver/ShardingSphereDriver.java:36` - **OBS-6:** The 5.5.2 Proxy property `proxy-frontend-database-protocol-type` supports `MySQL`, `PostgreSQL`, and `openGauss`, not SQL Server or Oracle. Source: https://shardingsphere.apache.org/document/5.5.2/en/user-manual/shardingsphere-proxy/yaml-config/props/ ; `docs/document/content/user-manual/shardingsphere-proxy/yaml-config/props.en.md:28` - **OBS-7:** The 5.5.2 Proxy frontend implementations are `MySQLFrontendEngine`, `PostgreSQLFrontendEngine`, and `OpenGaussFrontendEngine`; no SQL Server / Oracle frontend engine is present in that tag. Source: `proxy/frontend/type/mysql/src/main/java/org/apache/shardingsphere/proxy/frontend/mysql/MySQLFrontendEngine.java:37`, `proxy/frontend/type/postgresql/src/main/java/org/apache/shardingsphere/proxy/frontend/postgresql/PostgreSQLFrontendEngine.java:35`, `proxy/frontend/type/opengauss/src/main/java/org/apache/shardingsphere/proxy/frontend/opengauss/OpenGaussFrontendEngine.java:34` - **OBS-8:** Encrypt result merging decrypts encrypt columns before returning logical query results when the query goes through ShardingSphere. Source: `features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/merge/dql/EncryptMergedResult.java:58`, `features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rule/column/item/CipherColumnItem.java:85` ### Root Cause - **Observation:** ShardingSphere decrypts encrypted columns only when the SQL request goes through a ShardingSphere access path, such as ShardingSphere-JDBC or a supported ShardingSphere-Proxy frontend protocol (`OBS-2`, `OBS-5`, `OBS-8`). - **Observation:** ShardingSphere-Proxy 5.5.2 does not expose SQL Server / Oracle frontend protocol support (`OBS-6`, `OBS-7`). - **Inference:** Direct SQL Server / Oracle physical database connections bypass the ShardingSphere encrypt merge/decrypt path, so ciphertext is expected there (`OBS-1`, `OBS-8`). - **Inference:** A database management tool can receive decrypted logical results only if it can run through `org.apache.shardingsphere.driver.ShardingSphereDriver` with the ShardingSphere YAML and dependencies available (`OBS-2`, `OBS-3`, `OBS-4`, `OBS-5`). - **Confidence:** High. ### Problem Analysis - **Issue Type:** Question - **Evidence:** There is no documented or code-level mismatch. The project provides a JDBC driver access model for ShardingSphere-JDBC, and the Proxy frontend protocol boundary excludes SQL Server / Oracle in 5.5.2. - **Supported Usage:** Use the database tool as a generic JDBC client of `org.apache.shardingsphere.driver.ShardingSphereDriver`, with a `jdbc:shardingsphere:*` URL and reachable YAML configuration. - **Unsupported Expectation:** ShardingSphere-Proxy 5.5.2 cannot be used as a SQL Server / Oracle protocol proxy endpoint. - **Enhancement Boundary:** SQL Server / Oracle frontend protocol support for Proxy would require a new protocol frontend implementation and should be tracked separately as an enhancement/design issue if the community wants to pursue it. - **Label Recommendation:** `type: question` ### Problem Conclusion - **Evidence Confidence:** High - **Impact Scope:** Operational/plaintext inspection path for Data Encrypt with SQL Server / Oracle backends; no runtime bug is shown. - **Topology:** JDBC + Standalone/Cluster unknown; registry/config center unknown; Proxy SQL Server / Oracle frontend unsupported in 5.5.2. - **Issue Type:** Question - **Recommended Labels:** `type: question` - **Next Action:** Answer with the ShardingSphere-JDBC custom driver guidance above, invite concrete tool configuration examples from the community, and close once clarified. -- 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
