Milos Stojanovic created SPARK-60089:
----------------------------------------
Summary: Surface remote JDBC insufficient-privilege errors as a
dedicated error class
Key: SPARK-60089
URL: https://issues.apache.org/jira/browse/SPARK-60089
Project: Spark
Issue Type: Improvement
Components: SQL
Affects Versions: 4.2.0
Reporter: Milos Stojanovic
When \{{JDBCRDD.resolveTable}} resolves the output schema of a JDBC read, it
classifies driver errors through dialect hooks: \{{isObjectNotFoundException}},
\{{isNotSelectableObjectException}} and \{{isSyntaxErrorBestEffort}}. The last
one raises \{{JDBC_EXTERNAL_ENGINE_SYNTAX_ERROR}}.
A permission denial from the remote engine has no hook of its own, so it ends
up as a different error depending on the dialect:
* Dialects that treat class-42 or \{{42000}} SQLSTATEs as syntax errors (MySQL,
Oracle, Snowflake, Teradata, DB2, Databricks) report it as
\{{JDBC_EXTERNAL_ENGINE_SYNTAX_ERROR}}. MySQL, for example, returns SQLSTATE
\{{42000}} for both syntax errors and access-denied errors.
* PostgreSQL has propagated \{{42501}} as an unclassified driver error since
SPARK-59336. Through the JDBC catalog it surfaces as
\{{FAILED_JDBC.LOAD_TABLE}}, and the driver message is only in the cause chain.
In both cases callers can't tell "the user lacks privileges" apart from "the
SQL is invalid".
h3. Proposal
Follow the existing syntax-error path:
* Add \{{JdbcDialect.isInsufficientPrivilegeException(e: SQLException):
Boolean}}, defaulting to \{{false}}.
* Check it first in \{{JDBCRDD.resolveTable}}, before the not-found and
syntax-error checks, and raise a new
\{{JDBC_EXTERNAL_ENGINE_INSUFFICIENT_PRIVILEGE}} error (SQLSTATE \{{42501}}).
The error carries the pushed-down query, the remote SQLSTATE and the remote
message.
* \{{AggregatedDialect}} returns true if any of its delegate dialects
recognizes the error, so a user-registered dialect that doesn't override the
hook can't turn off built-in detection.
* Per-dialect detection:
||Dialect||Codes||
|PostgreSQL|SQLSTATE 42501|
|MySQL / MariaDB|1044, 1142, 1143, 1227, 1370|
|SQL Server|229, 230, 262, 297, 300|
|Oracle|ORA-01031|
|Snowflake|3001|
|Teradata|3523, 3524, 5315|
|DB2|-551, -552|
|Databricks|SQLSTATE 42501, or INSUFFICIENT_PERMISSIONS in the message|
Oracle ORA-00942 and Snowflake 2003 ("does not exist or not authorized") are
ambiguous, so they stay classified as object-not-found.
This can't be a new subclass of \{{JDBC_EXTERNAL_ENGINE_SYNTAX_ERROR}}, because
SQLSTATE is defined per error class and that class uses \{{42000}}.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
