1

I am in the process of evolving architecture for an 'equity analysis system'. I will be using SQLServer as the database, but I am not going the .NET route and won't be using the built-in 'membership' features. I have designed the user, user_credentials, roles and permissions tables. The 'credentials' table stores id, user f/l name, email, login id, salt, and hashed password.

The question is

a) Can the stored procedure inserting the values into the 'credentials' table do the hashing using the database's features - hashbytes/sha2_512? If so, the plain-text password needs to be passed as a parameter to the stored procedure which could be looked at by a DBA via Profiler or Tracing.

b) Should the application on the web-server, say node.js, create the salt and the hash and send only the hashes to the stored procedure thereby keeping the plain-text password completely invisible to the database? However, the passwords are still available for view at the web-server, should an admin decide to look at them.

I would like to keep the web/app server light-weight and do pretty much all the work on the database side and looks like option-a would be the way to go. At the same time, sophisticated algorithms such as bcrypt, scrypt may not be available from within the database and part of me says it is better to go with option-b. Also, it may not be the database's job to do the somewhat memory-intensive hashing work. I am sure the best practices would suggest using option-b.

I am deliberately not going the OpenID / OAuth 2.0 route. Nor considering using MySQL, PostgreSQL or any of the NoSQL databases, for now, as a lot of work has already happened in SQLServer.

Your thoughts, advice, suggestions, and pointers are much appreciated.

  • 2
    md5 isn't a very secure hashing algorithm. – πάντα ῥεῖ Dec 7 '19 at 7:15
  • 1
    The general advice is that the application does it. Do not use md5. And please use prepared statements. – Theraot Dec 7 '19 at 7:18
  • 1
    Thank you. Sure, definitely not MD5. Say, I shall use SHA2_256 or SHA2_512. – Ananda Kumar Santhinathan Dec 7 '19 at 7:21
  • 1
    You want something that is 'costly' to calculate to make it difficult to brute force. Look at bcrypt or scrypt. – GrandmasterB Dec 7 '19 at 7:40
  • 2
    The least I would consider OK as hashing solution is a SHA2_256 based HMAC using the salt as key (edit: with a very long salt). There are bcrypt, scrypt and pbkdf2 solutions virtually on every platform. Those are better. If you can, I would also like to suggest Argon2. There some good libraries that implement it, look for one for your language/runtime. – Theraot Dec 7 '19 at 7:56
4

Hashing on the application side has one major benefit: passwords/credentials are guaranteed not to be send over the network in an unencrypted fashion, so they cannot be easily found by analysing the network traffic.

Of course, today the network connection to an SQL server will normally use SSL/TLS, but if you implement the hashing at the DB side, the algorithm you use there is not the only potential breach point, the security also depends on SSL/TLS staying secure (and that noone forgets to activate these protocols, of course).

Malicious administrators with sufficient access rights, however, could always exchange components of the system by some "proxy" which logs the entered passwords, and there is no general difference in this to the application on a web server or stored procedures in a DB. This is nothing you can prevent by a technical solution, you prevent this having specific clauses in the working contracts of those persons. However, if there are different web admins and db admins, by keeping the encryption on the application side, the group of people which could potentially log the passwords may be smaller.

The only real benefit of putting the hashing feature on the db side I see is that it would allow to reuse this functionality between different web apps or different versions of the web apps.

I would not worry too much about performance before you really notice a bottleneck - authentification is usually only a very small percentage of the activities which happen in such a system, so it is not unreasonable to expect the encryption performance to be negligible.

| improve this answer | |
  • 1
    When "SSL/TLS staying secure" is a problem at the DB level, it is at the web servers level as well. And if someone is able to steal your SSL key on the database, that someone could do it on the webserver as well. So borked SSL is fatal anyways. – Thomas Junk Dec 7 '19 at 12:13

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.