Home / server / Questions / 907257
In Process
Ben Holness
Asked: 2018-04-12 14:09:11 +0800 CST

Is there a secure way to run multiple MySQL CLI commands without having to keep asking the user for a password?

  • 772

I have a bash script that runs a bunch of MySQL commands, separated by user input.

Each time a query is run, the password must be re-entered.

I understand it's best to avoid putting the password on the commandline, so I am wary of asking the user for the password and then passing it in on the commandline to each command.

Is there a recommended way to do this without compromising security?

mysql

5 Answers

  1. You can store the credentials in ~/.my.cnf file. Mysql will look up this file by default and use the credentials specified in it, if no other credentials are specified in the command.

    • 1

  2. It's best not to handle password in shell scripts.

    I had a similar question:

    https://security.stackexchange.com/questions/182927/what-would-be-a-secure-way-to-handle-password-prompts-in-shell

    I made the decision use a shell script as a wrapper to an expect script - based on my use case.

    It's not perfectly secure (what is?) but:

    1. the password is not revealed via ps.
    2. the password is not hardcoded anywhere.
    3. the password is not long lived in RAM - the process terms in 30s.
    4. the password is for a non-privileged account
    5. the script runs as a non-privileged account
    6. root is locked down to a small group of staff on that box.
    7. the box that execs that command is internal and hardened

    Is it 'secure'? It's good enough security for my situation.

    Plenty of docs on how to run shell script w/an eye for security. Here's one:

    https://developer.apple.com/library/content/documentation/OpenSource/Conceptual/ShellScripting/ShellScriptSecurity/ShellScriptSecurity.html

    Also consider: https://security.stackexchange.com/questions/66832/are-shell-scripts-bash-inherently-less-secure-than-other-script-languages-su

    • 0

  3. I don't have a MySQL server to test with, but you should be able to

    1. ask for the password once,
    2. create a named pipe,
    3. link that to a background mysql process, and
    4. pass all the SQL to the named pipe.
    • 0

  4. Instead of having MySQL -u root -pPASSWORD have this: `mysql —defaults-file=/etc/mysql/debian.cnf that will use the debian sys maint user

    • 0

  5. You could store the user input (the password) on a variable and pass it as a param to the MySQL CLI. That's not bad. It doesn't compromise security.

    As a user, when you are typing commands, they get stired in your ~/.bash_history in plain text. So, as a user, if you pass the password as a param, it gets stored in a file on the machine. This isn't any worse than storing the password in a config file.

    Making the user type the password over and over is annoying from a UX perspective. Saving it to a variable and passing it into the command is much better without compromising security.

    It is worth noting that this would expose the password in the process list while each one of the MySQL commands is running.

    • 0