I want to discuss wether good or bad idea:

I got a MySQL DB and created a common table "user" to authenticate an login.

|User|
|user_id|username|password|created_at| 

I implemented a stored function and some triggers.

First of all:

ON BEFORE UPDATE

will generate a SHA256 hash and salt when password was changed. salt is generated out witha mix of created_at, user_id and a global salt_mod which is stored in another "config-table".

So when entering 123 in "password" via normal UPDATe it will produce user-unique password and salt hashes.

Next I implemented a stored function

checkUserAuth('username', 'password') 

returns: bool true or false

Ofcourse: receives PLAIN username and password, replicates same hashing logic and returns bool "true" or "false", depends on if the credentials have been correct.

Pro:

• This makes completely sync of password algorithm changes obsolete to any connected apps.
• Database-account that is being used by the app can work without SELECT privileges on "username", "password" and "salt" privileges.
• Even if the user-account of the database is stolen, passwords are still safe due to lacking permissions to read the FUNCTIONS source-code and the columns that store login-information. We only have an EXECUTE privilege here.

Contra:

• Well if someone breaks in on the DB with root privileges, pretty much the source-code on "how to generate the hashes" is leaked (pretty much the salting formula) together with all information in one place. However its still hard to rainbow-table that due to unique hashes on created_at date and user_id in mix with global_salt.

The question to above scenario:

Is this a good approach or totally vulnerable in point of data-security? 

I mean on one side, a break-in inside the database is always a problem that should never happen

On the other side, even with stolen source-code of the apps or stolen database-account by a bug in the app you cannot steal any login related data.

The root account is ofcourse excluded from being using elsewhere then on the localhost/server.

What do you think about this?

CodePudding user response：

The primary weakness is that you pass the password in plaintext when you create the row, and every time you call your checkUserAuth() function.

This can be wiretapped (unless you ensure using SSL connections between app and database) and it will be written to the query log if you have the query log enabled. It's also visible in the processlist and the performance_schema statements history tables.

This is a good reason to do the hashing in the client, and send only the salted hash of the password when you create the row. When you authenticate, fetch the salted hash from the database, and validate it against user input in the client.