What is the correct SQLException / spring data exception for missing permissions

user_1714215 0
Viewed 70

I just noticed that when you try to execute a query that does not have required permissions (only tested on a mysql 5.7 database using the mysql-connector-java v8 driver) it is translated to a org.springframework.jdbc.BadSqlGrammarException. This is because the mysql driver itself throws a java.sql.SQLSyntaxErrorException.

For instance: java.sql.SQLSyntaxErrorException: INSERT command denied to user 'myuser'@'127.0.0.1' for table 'mytable'

Is this correct and/or intentional? I tried to look through the various existing exceptions in both java.sql and org.springframework.jdbc without finding an obvious alternative candidate.

I looked at the mysql documentation for this error:

Error number: 1142; Symbol: ER_TABLEACCESS_DENIED_ERROR; SQLSTATE: 42000

Message: %s command denied to user '%s'@'%s' for table '%s'

As far as I can tell SQLSTATE 42000 is what the driver uses to determine that it is a syntax error.

mysql-connector spring-jdbc jdbc mysql java
1 Answers

The sql standard defines the return code for sql statements, kmown as sqlstate.

Sqlstate 42000 means

syntax error or access rule violation

Access denied falls into this category, so mysql followed the standard. Java also broadly followed the standard as it reports such errors as syntax error.

Unfortunately, the answer is you cannot use the type of the error to drive your access denied check, you need to look at the error number (1142 in this case).

user_53899970
Related