Schema is not being passed to the mysql-connector from JdbcOAuth2AuthorizationService

[droar]

Describe the bug

Slowness was detected when starting an application, and since this application is monitoring the SQLs, we noticed it started issuing lots of SHOW DATABASES and SHOW TABLES when searching for the oauth2_authorizationtable.

To Reproduce

Start an oauth2 authorizationserver application (in this case we are using spring-security-oauth2-authorization-server 0.4.2) in which the datasource user has permissions to view many different databases, and not just the one having the oauth2 authorizationtable.

Expected behavior
The table ´oauth2 authorization´is picked up properly from the database schema specified in the datasource configuration, instead of scanning all the databases that the user has permission to see until finding the table.

I noticed that in the JdbcOAuth2AuthorizationService.java class there is this method:

private static ColumnMetadata getColumnMetadata(JdbcOperations jdbcOperations, String columnName, int defaultDataType) { Integer dataType = (Integer)jdbcOperations.execute((conn) -> { DatabaseMetaData databaseMetaData = conn.getMetaData(); ResultSet rs = databaseMetaData.getColumns((String)null, (String)null, "oauth2_authorization", columnName); if (rs.next()) { return rs.getInt("DATA_TYPE"); } else { rs = databaseMetaData.getColumns((String)null, (String)null, "oauth2_authorization".toUpperCase(), columnName.toUpperCase()); return rs.next() ? rs.getInt("DATA_TYPE") : null; } }); return new ColumnMetadata(columnName, dataType != null ? dataType : defaultDataType); }

Here we can see that on: ResultSet rs = databaseMetaData.getColumns((String)null, (String)null, "oauth2_authorization", columnName);

The schema is being passed always as null, instead of trying to get it from the jdbcOperations or from another new parameter.

Is this something intentional by any reason? For now the only solution we can think of is reducing the permissions to the user to the database containing the oauth2_authorization table.

[jgrandja]

@droar

Here we can see that on: ResultSet rs = databaseMetaData.getColumns((String)null, (String)null, "oauth2_authorization", columnName);

The schema is being passed always as null, instead of trying to get it from the jdbcOperations or from another new parameter.

The schema being passed is null because it is not known by JdbcOAuth2AuthorizationService. The only requirement is that the oauth2_authorization table is defined in a schema. The schema name is defined during environment setup and the application configuration is responsible for defining the datasource (with assigned schema) and configuring it with JdbcOperations and then associate it with JdbcOAuth2AuthorizationService.

Is this something intentional by any reason?

The initColumnMetadata() was added in gh-604. Please see that issue for further details.

For now the only solution we can think of is reducing the permissions to the user to the database containing the oauth2_authorization table.

The oauth2_authorization table should only be accessed by an admin user not a regular user since it contains sensitive information. A dedicated admin user for the oauth2 tables would solve the slowness at startup.

I'm going to close this as JdbcOAuth2AuthorizationService works as expected and the startup slowness can be solved by environment configuration/tuning.