You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "makssent (via GitHub)" <gi...@apache.org> on 2026/10/19 13:15:50 UTC
[I] Firebird Proxy answers CREATE ROLE, GRANT and REVOKE with Unknown exception and rejects DROP ROLE with a syntax error [shardingsphere]
makssent opened a new issue, #40266:
URL: https://github.com/apache/shardingsphere/issues/40266
### Before reporting a bug
- [x] I have searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues).
- [x] I have read the [ShardingSphere documentation](https://shardingsphere.apache.org/document/current/en/overview).
### ShardingSphere version
master, commit [`01446d4fad4`](https://github.com/apache/shardingsphere/commit/01446d4fad48450e7fc3eb3b3631b25d6bf411df)
### ShardingSphere project
ShardingSphere-Proxy
### Expected behavior
The proxy should execute [`CREATE ROLE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L1541), [`GRANT`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L932-L937), [`REVOKE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L1188-L1193) and [`DROP ROLE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L4623-L4624) as the Firebird server does.
### Actual behavior
The proxy answers each of these statements with an error and does not create the role.
The same statements were executed directly on the Firebird server and through the proxy, in this order:
| Statement | Firebird server | Proxy |
| --- | --- | --- |
| `CREATE ROLE R_AUDIT` | Success | Error [`isc_random`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L63) "Unknown exception." |
| `SELECT COUNT(*) FROM RDB$ROLES WHERE RDB$ROLE_NAME = 'R_AUDIT'` | 1, the role exists | 0, the role does not exist |
| `GRANT SELECT ON T_PLAIN TO R_AUDIT` | Success | Error [`isc_random`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L63) "Unknown exception." |
| `REVOKE SELECT ON T_PLAIN FROM R_AUDIT` | Success | Error [`isc_random`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L63) "Unknown exception." |
| `DROP ROLE R_AUDIT` | Success | Error [`isc_dsql_error`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L250) "Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'DROPROLE'" |
So through the proxy an application cannot manage roles and privileges, and for most of these statements the error does not tell it why.
### Reason analysis
- For these statements [`FirebirdDCLStatementVisitor#visitCreateRole`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/java/org/apache/shardingsphere/sql/parser/engine/firebird/visitor/statement/type/FirebirdDCLStatementVisitor.java#L57-L60), [`FirebirdDCLStatementVisitor#visitGrant`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/java/org/apache/shardingsphere/sql/parser/engine/firebird/visitor/statement/type/FirebirdDCLStatementVisitor.java#L45-L49) and [`FirebirdDCLStatementVisitor#visitRevoke`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/java/org/apache/shardingsphere/sql/parser/engine/firebird/visitor/statement/type/FirebirdDCLStatementVisitor.java#L51-L55) build [`CreateRoleStatement`](https://github.com/apache/shardingsphere
/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/statement/type/dcl/role/CreateRoleStatement.java#L26), [`GrantStatement`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/statement/type/dcl/GrantStatement.java#L35) and [`RevokeStatement`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/statement/type/dcl/RevokeStatement.java#L35), which are [`DCLStatement`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/statement/type/dcl/DCLStatement.java#L26) types. [`FirebirdPrepareStatementCommandExecutor#getFirebirdStat
ementType`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/prepare/FirebirdPrepareStatementCommandExecutor.java#L171-L212) has no branch for a [`DCLStatement`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/statement/type/dcl/DCLStatement.java#L26) and [returns `null`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/prepare/FirebirdPrepareStatementCommandExecutor.java#L211).
- [`FirebirdPrepareStatementCommandExecutor#createResponse`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/prepare/FirebirdPrepareStatementCommandExecutor.java#L142-L169) then [calls `isSelectDescribable()`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/prepare/FirebirdPrepareStatementCommandExecutor.java#L151) on this `null`, and the `NullPointerException` reaches the client as [`UnknownSQLException`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/infra/common/src/main/java/org/apache/shardingsphere/infra/exception/generic/UnknownSQLException.java#L30-L32) with the message "Unknown exception."
- The rule [`execute`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/antlr4/org/apache/shardingsphere/sql/parser/autogen/FirebirdStatement.g4#L22-L55) of the Firebird grammar of ShardingSphere has no alternative for [`DROP ROLE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L4623-L4624), so the parser rejects it.
### Steps to reproduce
#### Environment
- Firebird server 5.0.
- Client: Jaybird 6.0.7.
#### Steps
1. Create the table on the Firebird server:
```sql
CREATE TABLE T_PLAIN (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(20));
```
2. Start ShardingSphere-Proxy with this configuration:
```yaml
# global.yaml
authority:
users:
- user: root@%
password: root
privilege:
type: ALL_PERMITTED
props:
proxy-frontend-database-protocol-type: Firebird
```
```yaml
# database-firebird.yaml
databaseName: firebird_db
dataSources:
ds_0:
url: jdbc:firebirdsql://<host>/<database file>
username: <user>
password: <password>
rules:
- !SINGLE
tables:
- "*.*"
```
3. Execute the statements from the table under "Actual behavior" through the proxy and directly on the Firebird server, and compare the results. "Example code" runs all of them.
### Example code
The statements from the table on Jaybird 6.0.7. The comment above each call shows what it prints against the Firebird server and through the proxy.
```java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
public class RoleGrantStatements {
// The proxy; put the URL of the Firebird server here to compare
static final String URL = "jdbc:firebirdsql://<proxy host>:<proxy port>/firebird_db?encoding=UTF8&wireCrypt=DISABLED";
static final String USER = "root";
static final String PASSWORD = "root";
public static void main(final String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) {
// Firebird server: "CREATE ROLE R_AUDIT -> update count -1"; proxy: "CREATE ROLE R_AUDIT -> error: Unknown exception. ..."
execute(connection, "CREATE ROLE R_AUDIT");
// Firebird server: "SELECT COUNT(*) FROM RDB$ROLES WHERE RDB$ROLE_NAME = 'R_AUDIT' -> COUNT BIGINT: 1";
// proxy: "SELECT COUNT(*) FROM RDB$ROLES WHERE RDB$ROLE_NAME = 'R_AUDIT' -> COUNT BIGINT: 0"
execute(connection, "SELECT COUNT(*) FROM RDB$ROLES WHERE RDB$ROLE_NAME = 'R_AUDIT'");
// Firebird server: "GRANT SELECT ON T_PLAIN TO R_AUDIT -> update count -1";
// proxy: "GRANT SELECT ON T_PLAIN TO R_AUDIT -> error: Unknown exception. ..."
execute(connection, "GRANT SELECT ON T_PLAIN TO R_AUDIT");
// Firebird server: "REVOKE SELECT ON T_PLAIN FROM R_AUDIT -> update count -1";
// proxy: "REVOKE SELECT ON T_PLAIN FROM R_AUDIT -> error: Unknown exception. ..."
execute(connection, "REVOKE SELECT ON T_PLAIN FROM R_AUDIT");
// Firebird server: "DROP ROLE R_AUDIT -> update count -1";
// proxy: "DROP ROLE R_AUDIT -> error: Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'DROPROLE' ..."
execute(connection, "DROP ROLE R_AUDIT");
}
}
// Executes the statement and prints the described columns with the rows, the update count or the error
static void execute(final Connection connection, final String sql) {
try (Statement statement = connection.createStatement()) {
if (!statement.execute(sql)) {
System.out.println(sql + " -> update count " + statement.getUpdateCount());
return;
}
try (ResultSet resultSet = statement.getResultSet()) {
ResultSetMetaData metaData = resultSet.getMetaData();
List<String> columns = new ArrayList<>();
for (int i = 1; i <= metaData.getColumnCount(); i++) {
int type = metaData.getColumnType(i);
boolean text = Types.CHAR == type || Types.VARCHAR == type;
columns.add(metaData.getColumnLabel(i) + " " + metaData.getColumnTypeName(i) + (text ? "(" + metaData.getPrecision(i) + ")" : ""));
}
List<String> rows = new ArrayList<>();
while (resultSet.next()) {
List<String> values = new ArrayList<>();
for (int i = 1; i <= metaData.getColumnCount(); i++) {
values.add(String.valueOf(resultSet.getObject(i)));
}
rows.add(String.join(", ", values));
}
System.out.println(sql + " -> " + String.join(", ", columns) + ": " + (rows.isEmpty() ? "no rows" : String.join("; ", rows)));
}
} catch (final SQLException ex) {
System.out.println(sql + " -> error: " + ex.getMessage());
}
}
}
```
--
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