Optimal use of hibernate with mysql's stored procedures

Question

I am working on a project (to use as a reference). The project uses mysql, hibernate and spring (REST) and is supposed to be a single microservice.

I have come to find out that limiting user/consumer database access to stored procedures in mysql is considered a better practice security-wise (no unintended operations, and no sql-injection). As a result i created stored procedures for every possible user/consumer interaction with the database.

To use hibernate with stored procedures. I declared @NamedStoredProcedureQuery for every stored procedure.

The problem (practice wise) is that this approach seem to be very verbose. especially when it comes to stored procedures that return custom (multiple-table) queries (almost all querying stored procedures). As I'll have to define @SqlResultSetMapping (as shown here) for each custom query returned by a stored procedure.

Should i continue using this practice?

Are there better ways to achieve the same (or better) result?

Answer 1

You don't need stored procedures to prevent SQL injection. Sure it provides one more barrier between the database and the outside world, but if you use HQL parameterized queries, the criteria API etc. nothing can really "slip through" anyway.

Using stored procedures for everything can also become cumbersome as logic is divided between the database and the codebase, not to mention playing around with different user roles inside the database.

I have seen a lot of code doing this however, and I've always assumed that it's due to company policies that developers and DBAs are isolated from each other (or rather the developers are isolated from the database). I can see advantages in DBAs writing efficient queries and giving them for the developers to use (who may not understand much about databases unfortunately). As a security measure I don't see any advantage (unless we're trying to secure the database from developers who might destroy it).

Answer based on years of Java, SQL and other development.

Answer 2

What I know about hibernate :

• Security wise : use Hibernate API and Hibernate Criteria instead of writing your own HQL and you're safe. It is extremely unlikely that you want to manage multiple users for your database. At worst you may need one normal connection and one admin for sensitive tables.
• Hibernate is an ORM, the goal of an ORM is not to replace the database but to make your life easier by not having to write your own query, specially insert/delete/update writing yourself your own stored procedure seems like defeating this purpose.
• Performance : Hibernate use two level cache, the first is a transaction level cache and is always enabled you will need to use method like "flush"/"refresh" if your data are modified without Hibernate, the second is an inter-transaction cache disabled by default.
• Functionnalities, Hibernate can provide OptimisticLocking capabilities, module to generate automatically changes history and so on.

There is one thing that I don't know about hibernate vs stored procedure : does Hibernate have a way to know which object will be modified by the store procedure in order to flush his own cache ? if he doesn't, that's clearly a huge risk of going into troubles. But even without that, I see already quite a lot of drawbacks of using stored procedure.

Though the intent of managing access of tables seems good, there is in fact not that much in practice to do. Since you do microservice, all those tables will likely need to be accessed by the same user, so I don't see the point of adding extra security. If you want to have multiple microservice separated on the same database server, use different schema or even database instance.

In conclusion , by using stored procedure, you will be extremely limited in what you can do with Hibernate while having the extra weight of using it. Basically, you will have the mapping and that's all.