November 26, 2018 at 8:16 am
I used encrypt code to save password in a table and use user's email and encrypted password for login form.
For example, if user Smith Rice login password is "Pass987$$" will save as "DLMyAdxvqXzOWZB5BuyW1YFR4JftD7jkQP/VHBHPTH8=" after encrypt.
But IT people said that this is not good enough for security because Smith Rice has full permission to copy "DLMyAdxvqXzOWZB5BuyW1YFR4JftD7jkQP/VHBHPTH8=" and replace another user's password and easier to login as typing "Pass987$$"
Is there another way to void this action?
November 26, 2018 at 8:22 am
adonetok - Monday, November 26, 2018 8:16 AMI used encrypt code to save password in a table and use user's email and encrypted password for login form.
For example, if user Smith Rice login password is "Pass987$$" will save as "DLMyAdxvqXzOWZB5BuyW1YFR4JftD7jkQP/VHBHPTH8=" after encrypt.
But IT people said that this is not good enough for security because Smith Rice has full permission to copy "DLMyAdxvqXzOWZB5BuyW1YFR4JftD7jkQP/VHBHPTH8=" and replace another user's password and easier to login as typing "Pass987$$"
Is there another way to void this action?
You will have to elaborate a bit further on this, i.e. how come any user can access the hash of the passwords? Copy the password hash to another users password's hash is not in any way shape or form a kosher permission, how come that's possible?
😎
November 27, 2018 at 9:11 am
adonetok - Monday, November 26, 2018 8:16 AMI used encrypt code to save password in a table and use user's email and encrypted password for login form.
For example, if user Smith Rice login password is "Pass987$$" will save as "DLMyAdxvqXzOWZB5BuyW1YFR4JftD7jkQP/VHBHPTH8=" after encrypt.
But IT people said that this is not good enough for security because Smith Rice has full permission to copy "DLMyAdxvqXzOWZB5BuyW1YFR4JftD7jkQP/VHBHPTH8=" and replace another user's password and easier to login as typing "Pass987$$"
Is there another way to void this action?
Check the docs for EncryptByKey, and you will see this issue (whole-value encryption substitution) detailed, with the solution: an 'authenticator' value. https://docs.microsoft.com/en-us/sql/t-sql/functions/encryptbykey-transact-sql
This applies to all EncryptBy[...] functions.
The SQL Server encryption functions include additional parameters for authenticators, which are additional pieces of data included in the encrypted value that prevents that exploit.
In this case, encrypt the password "Pass987$$" using the user's login name as the authenticator. When you decrypt the value, you must pass in the correct Authenticator value to the decryption function. If that doesn't match the authenticator that was used to encrypt the value, the decryption will fail. This method prevents the security hole you mentioned, because if a user copies their encrypted value, replaces a different user's encrypted with their own, and then tries to log in using the different user's user name but their password, your decryption would fail, because the authenticator value (the user name in this case) would not match.
Here's what that might look like for your example:/* @UserName and @Password come from your form
Encrypt the password and add the username as an authenticator */
SELECT @EncryptedPwdWithAuthenticator = EncryptByKey(
Key_GUID('UserPassKey'),
@UserPassword,
1, /* this 1 means 'use authenticator' */
CONVERT(varbinary, @UserName) /* User Name as authenticator */
);
Then, at login time, use the user name and password to decrypt the password:/* @UserName and @Password come from your form
Decrypt the password using the username as the authenticator */
IF @Password = DecryptByKey(
Key_GUID('UserPassKey'),
@UserPassword,
1, /* this 1 means 'use authenticator' */
CONVERT(varbinary, @UserName) /* User Name as authenticator */
)
BEGIN
/* user logged in, proceed... */
END
ELSE
/* failed login */
Of course, if your users can change data in the tables that control their access, they can easily bypass your efforts by taking the same steps themselves. (Instead of copying the encrypted value, just create one using any password and the targeted username).
Or, by simply skipping your application completely and reading or modifying the data directly.
Eddie Wuerch
MCM: SQL
November 27, 2018 at 9:23 am
Thank you.
November 27, 2018 at 9:51 am
This is called salting the password. As Eddie noted, the authenticator is a good way to handle this. You could use something like adding the second letter of the user name to the password and then encrypting (do the same for validation), but you have to be sure if the value you are using here changes, you decrypt and re-encrypt the password with the new salt.
November 27, 2018 at 9:52 am
Eirikur Eiriksson - Monday, November 26, 2018 8:22 AMYou will have to elaborate a bit further on this, i.e. how come any user can access the hash of the passwords? Copy the password hash to another users password's hash is not in any way shape or form a kosher permission, how come that's possible?
😎
The issue here is that any update could happen. This is a classic SQL Injection technique of adding a new user, finding a vulnerabliity, and then updating everyone's password to the value in the new user ID.
This shouldn't be possible, but it also shouldn't be something that your encryption technique allows.
November 27, 2018 at 10:28 am
If user Smith Rice can query and update tables at will, then they are totally awesome. Best thing to do in this situation is hunker down and hope they don't come after you. Or switch to a service based authentication and access architecture. Whichever works best.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply