liangdaqin opened a new issue, #38767:
URL: https://github.com/apache/shardingsphere/issues/38767
# Question: How can database management tools view decrypted data in SQL
Server / Oracle encryption scenarios with ShardingSphere-JDBC?
Hi ShardingSphere community,
I would like to ask for some guidance about an encryption scenario involving
**SQL Server / Oracle** and database management tools.
We are using Apache ShardingSphere's data encryption feature to protect
sensitive data.
For Java applications, ShardingSphere-JDBC works well because it can be
embedded into the Java application and transparently encrypt/decrypt data
through the JDBC layer.
However, in some operational, troubleshooting, or data verification
scenarios, DBAs or developers need to use database management tools, such as
DBeaver or other database management tools, to inspect database data.
For MySQL / PostgreSQL scenarios, one possible approach is to connect the
database management tool to ShardingSphere-Proxy. In this way, the tool
accesses logical tables through ShardingSphere, and the query results can be
returned as decrypted plaintext data.
However, according to the current documentation, ShardingSphere-Proxy mainly
provides MySQL and PostgreSQL protocols. SQL Server and Oracle are mainly
supported through ShardingSphere-JDBC as JDBC-standard databases.
Therefore, in our scenario, because ShardingSphere-Proxy currently cannot
act as a SQL Server / Oracle protocol proxy, database management tools that
connect directly to the physical SQL Server / Oracle database can only see
encrypted ciphertext stored in the database, rather than decrypted plaintext.
We would like to confirm whether there is a recommended way to allow
database management tools, such as DBeaver or other database management tools,
to view decrypted plaintext data when the backend database is SQL Server or
Oracle.
## Specific questions
1. Is it possible for database management tools, such as DBeaver or other
database management tools, to directly use ShardingSphere-JDBC?
In other words, can database management tools query logical tables
through ShardingSphere-JDBC and receive decrypted plaintext results?
2. If this approach is feasible, what is the recommended configuration
method?
For example:
- Should ShardingSphere-JDBC and its YAML configuration be packaged as a
custom JDBC driver?
- Can database management tools directly initialize a ShardingSphere-JDBC
DataSource?
- Are there any examples or best practices for this kind of integration?
3. If this approach is not feasible, is it because ShardingSphere-JDBC is
mainly designed to be integrated into Java applications, rather than being used
as a standalone JDBC driver for general-purpose database management tools?
4. If ShardingSphere-JDBC cannot be directly used by database management
tools, are there any other recommended solutions for SQL Server / Oracle
encryption scenarios?
The goal is to allow database management tools or operations staff to
safely view decrypted data.
## Expected guidance
Could the community please help clarify the recommended architecture for
this scenario?
We would mainly like to understand:
- Whether ShardingSphere-JDBC can practically be used by database management
tools.
- Whether there are any existing examples of using ShardingSphere-JDBC as a
custom JDBC driver in database management tools.
- Whether SQL Server / Oracle protocol support in ShardingSphere-Proxy is
planned or technically feasible.
- Whether there are any other recommended solutions for safely viewing
decrypted data in SQL Server / Oracle encryption scenarios.
## Environment
```text
Apache ShardingSphere version: 5.5.2
Mode: ShardingSphere-JDBC
Backend database: SQL Server / Oracle
Database management tool: DBeaver or other database management tools
Feature: Data Encrypt
--
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]
