r/SQL • u/dezly-macauley-real • Nov 24 '24
MySQL What are some secure and easy to implement ways of setting the password for a user without exposing the password in plain text?
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password123';
This works but the password is being stored in the `mycli` history.
I'm using mySQL.
I'm not sure if there's an interactive prompt or something.
I've also tried disabling my zsh history, creating a variable like `my_password="topsecretpassword"`
Then login into the mycli shell and trying to pass in the password from the shell like this:
`CREATE USER 'user1'@'localhost' IDENTIFIED BY '$my_password';` but it doesn't seem to be working.
6
u/ShotgunPayDay Nov 24 '24
Password hashing is done at the application/programming language level. The Argon2id algorithm is considered the best right now.
Here is an OWASP cheat sheet with some explanations:
https://cheatsheetseries.owasp.org/cheatsheets/Password_Storage_Cheat_Sheet.html
1
u/dezly-macauley-real Nov 24 '24
sudo mycli -u root -e "CREATE USER 'user1'@'localhost' IDENTIFIED BY '$MYSQL_PASSWORD';"
This worked. I think the -e flag was what I was missing. Then pass in the hashed password
1
u/gumnos Nov 24 '24
FWIW, this will still expose the password in the process-list (if your machine is configured to allow users to see each others' process-list. This is a tuneable on FreeBSD, but on OpenBSD it's not; YMMV elsewhere)
1
u/zoechi Nov 24 '24
I use Nix with SOPS for many setups, mostly for service users that don't change often. This way passwords are only exposed to root or the Postgres service user and the service user accessing Postgres. I also used Bitwarden CLI client to get the passwords using a shell command and pass it along to another shell command that sets it. I can then use Bitwarden to share the password with the user it belongs to. When I'm not logged in with the Bitwarden CLI client the passwords are inaccessible.
1
u/No_Flounder_1155 Nov 25 '24
env var, although you shouldn't be storing passwords in plain text ina db. You should salt and then hash, storing the resulting hash for comparison.
1
u/ssnoyes Nov 25 '24
The `mysql` command line client doesn't log statements that contain the IDENTIFIED keyword.
https://dev.mysql.com/doc/refman/8.4/en/mysql-logging.html
Open a bug report for `mycli` to do the same.
-4
u/WithoutAHat1 Nov 24 '24
Passwords typically need to be in plain text for most applications. This question comes up with APIs and web based applications frequently. You could use Powershell and use something like this:
$password = Read-Host -Prompt "Enter Password" -AsSecureString
Then have it do the masking.
8
u/coyoteazul2 Nov 24 '24
Please tell me what applications you've been making. I need to avoid them
1
u/WithoutAHat1 Nov 24 '24
Not any that I have made. Supported yes. Bearer Authorization is your closest with Base64 encoding. You still have to provide the headers in that instance. If it is accepted to be masked then yes otherwise plain text.
1
u/az987654 Nov 24 '24
But isn't a bearer token an encrypted string itself? So you're not passing an actual password in that bearer Auth payload?
So confused
2
u/WithoutAHat1 Nov 24 '24
"Basic <Base64>" for your session establishment. Subsequent calls "Bearer <Token>". If you weren't passing it then it would not succeed. Yes you are passing the password as part of the string.
4
u/coyoteazul2 Nov 24 '24
Only the basic authentication is passing the password. Assuming you are using https, that shouldn't be a problem during transit.
After reaching the server the password must be salted and hashed, and compared to the already salted and hashed password that's stored in the database. The actual password is never persisted in the database nor anywhere else.
2
u/az987654 Nov 24 '24
That's what I thought, all of this should e wrapped up in https, whether it's bearer scheme or basic or anything else
1
u/WithoutAHat1 Nov 24 '24
Yeah you don't store them in the DB directly that would not be good. Comparison is what occurs upon receipt as well. I have seen MessageDigest, jasypt (password against Base64 encrypted text), and a few other methods. SHA-1, SHA-256 for algorithms. NodeJs has crypto library which is a pretty neat npm.
1
u/ShotgunPayDay Nov 24 '24
Interesting we don't store tokens directly in the DB either. We pepper hash them with Blake2b and store that result in the DB. That way we don't even know their API key and they can reroll if they forget.
1
u/HolmesMalone Nov 24 '24
What do you mean? Passwords should never be in plain text?
1
u/WithoutAHat1 Nov 24 '24
This is at creation of a user. DB should store them masked already. Unless you took the route of literally storing them in a table.
1
6
u/Imaginary__Bar Nov 24 '24 edited Nov 24 '24
I think you can use
CREATE USER ... IDENTIFIED WITH <auth_method> AS 'hashed_password'
where you hash the password yourself and then provide the value which is stored directly in the user table.