You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "TherChenYang (via GitHub)" <gi...@apache.org> on 2024/02/28 11:11:38 UTC

[I] How to support SQLServer dml encrypt with schema [shardingsphere]

TherChenYang opened a new issue, #30342:
URL: https://github.com/apache/shardingsphere/issues/30342

   - After configuring the datasource in MySQL,When we are writing DML statements, we can choose whether to include the corresponding database name in the statement. such as 
      * ```yml
         databaseName: encrypt
         
         dataSources:
           unique_ds:
             dataSourceClassName: com.zaxxer.hikari.HikariDataSource
             driverClassName: com.mysql.jdbc.Driver
             jdbcUrl: jdbc:mysql://localhost:3306/encrypt?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
             username: root
             password: 
      * `SELECT * FROM t_user` - without databaseName
   
         + ```sql
           [main] INFO ShardingSphere-SQL - Logic SQL: SELECT * FROM t_user
           [main] INFO ShardingSphere-SQL - Actual SQL: unique_ds ::: SELECT t_user.`user_name_cipher` AS `user_name` FROM t_user
      * `SELECT * FROM encrypt.t_user` - with databaseName
   
          + ```sql
            [main] INFO ShardingSphere-SQL - Logic SQL: SELECT * FROM encrypt.t_user
            [main] INFO ShardingSphere-SQL - Actual SQL: unique_ds ::: SELECT t_user.`user_name_cipher` AS `user_name` FROM t_user
      * Two types of DML statements can be rewritten correctly in ShardingSphere.
   
   - In SQLServer, besides databaseName, there is also schemaName. For example, the default schema name is dbo.If there is a schema in the DML statement, it will cause an error.When we query without specifying a schema, the system will automatically assign the schema as dbo.The current official website's SQL examples contain a large number of SQL cases with schema, and all of these should fail to encrypt.
       * ```SELECT * FROM dbo.t_user;``` - with schema
   
      * ```sql
        Exception in thread "main" java.sql.SQLException: Table or view `t_user` does not exist.
   	  at org.apache.shardingsphere.infra.exception.core.external.sql.ShardingSphereSQLException.toSQLException(ShardingSphereSQLException.java:62)
   	  at org.apache.shardingsphere.infra.exception.dialect.SQLExceptionTransformEngine.toSQLException(SQLExceptionTransformEngine.java:54)
   	  at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.executeQuery(ShardingSphereStatement.java:188)
   	  at encrypt.algorithm.sqlServer.SQLServerEncryptTest.main(SQLServerEncryptTest.java:26)```
       * At present, the encryption module seems to have no related for schema.
       


-- 
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: notifications-unsubscribe@shardingsphere.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] How to support SQLServer dml encrypt with schema [shardingsphere]

Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #30342:
URL: https://github.com/apache/shardingsphere/issues/30342#issuecomment-1970507643

   @TherChenYang Thank you for testing this out. SQLServer looks like a PostgreSQL database, which has both a database and a schema, so we should remove the database segment, not the schema segment when sql rewrite. You can take a look for RemoveTokenGenerator class, it seems that SQLServer does not follow the `owner.owner` format for parsing.
   


-- 
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: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] How to support SQLServer dml encrypt with schema [shardingsphere]

Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu closed issue #30342: How to support SQLServer dml encrypt with schema
URL: https://github.com/apache/shardingsphere/issues/30342


-- 
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: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org