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.
Advertisement
Answer
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).