1

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?

  • "I have come to find out that limiting user/consumer database access to stored procedures in mysql is considered a better practice security-wise" You'll have to explain this, because this seems completely misguided. – Vincent Savard Apr 12 '18 at 12:59
  • "1) Limiting user access to the database to only be able to access stored procedures should somewhat prevent unintended access. This should be done by privilege management that would allow only debug access for database tables through a debug/dev user. and normal interactions should have a consumer database user that can only call stored procedures. 2) stored procedures prevent sql-injections" This is what i think so far, if something is incorrect or inaccurate then, please correct it as a part of the answer. – zessi Apr 12 '18 at 13:10
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.

0

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.

  • Each microservice will have its own database. Now, I'm researching if i should use hibernate or stored procedures. (or both separately for the sake of the current reference project). – zessi Apr 12 '18 at 13:31
  • @zessi I have edited to better reflect my though. My conclusion is that, it not like they'r eincompatible, but you will be extremely limited in what you use for hibernate to the point that it might not be so worth of it. – Walfrat Apr 12 '18 at 13:40
  • Ill try to use hibernate alone and stored procedures alone. so that both styles are there as a reference. Thanks for the reply, i did up-vote it. but my votes are too low to be visible :) – zessi Apr 12 '18 at 17:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.