What minimum database privileges does spring.jpa.hibernate.ddl-auto=validate require?

Question

I am trying to use Spring Boot with an Oracle database. During development we have been using the hibernate setting spring.jpa.hibernate.ddl-auto=validate but now that we are moving towards a production release we want to lock down the connection permissions. However, it would be nice to retain the sanity check provided by the hibernate validate startup processing even in the production release.

Sadly, when I try to do this with a database connection that has sufficient privileges to run the Spring Boot application, the hibernate validate stage fails during bean initialisation because it says the tables are missing. Now I know that is not true, so I am betting that hibernate uses additional queries to validate the schema and that these queries require additional access. If I set the value to none then the application works but does not check for schema compatibility. And if I grant the DBA privilege to the connection user and all works fine including the validate. But I am definitely not going to provide that level of access from the application in production.

So, does anyone know the least privileges I can add to the user account such that I can enable hibernate validation? Ideally I would just provide SELECT, INSERT, UPDATE and DELETE on the tables and the CONNECT privilege on the user account but that is not sufficient to enable the validation.

I have considered creating a special automatic schema version variable somewhere and validating this at runtime, but it seems messy. If I could find an acceptable combination of Oracle privileges that enables the validation step without opening the connection too much then I would prefer to use that approach.

I could also define a special job on our Continuous Integration server such that it temporarily enables all the database permissions just for the one job. But I feel this is a sledgehammer approach and does not feel elegant.

Any hints gratefully received.

Many thanks, Jonathan

Answer 1

Whoops!

I'm posting this answer to my own question because it turns out that I made a mistake. Due to a well-hidden error message in my SQL scripting, I had not actually assigned all of the basic permissions to the tables (and other categories such as indexes). But I hadn't run all workflows within the application which would have revealed this. Once I fixed the access problems, that provided sufficient access for the Hibernate validate processing step as well.

Thank you to everyone who looked at my question, the answer is to make sure that you actually do have at least read access (SELECT privilege) to everything in your Oracle schema. (Hibernate checks them all at startup so was more rigorous than my simplistic manual tests).