This blog post discusses how to use MySQL shell securely from Bash.
The Bourne shell is everywhere. It is part of the most basic Linux install. You will find it on the biggest SPARC machines down to a Raspberry Pi. It is nice to know it will always be there. Unlike other, more complex scripting environments such as Perl and Python, it doesn’t require any additional dependencies to be installed.
Anyone that has automated a MySQL task using a Bourne shell such as Bash will be familiar with the following message:
|
1 |
Warning: Using a password on the command line interface can be insecure. |
This semi-ominous warning describes a security flaw in passing credentials on a process command line. Any unprivileged user on the system can use a command like ps aux to find these credentials. While the MySQL shell has added some additional protections to hide these credentials, other information such as database user names, host names, ports and sockets can still be determined by process scanning.
The recommended approach to get around this warning is to use a configuration file to store these credentials. However, in the case of a self-contained script, we may not want to require the user to create a credential file. It would need to be maintained, and might interfere with other tools that use the MySQL shell. For creating automated tools in the Percona Development Services department, I came up with a couple of methods for integrating the MySQL shell into Bash (or any other Bourne Shell) securely.
This first script demonstrates the shell function mysql_exec(). This is for use with small queries that are normally passed to the MySQL shell via the -e parameter.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
#!/bin/sh # call mysql client from shell script without # passing credentials on command line # This demonstrates small single queries using # the -e parameter. Credentials and connection # info are sent through standard input. # david . bennett @ percona . com - 12/27/2016 mysql_user=root mysql_password=password mysql_host=127.0.0.1 mysql_port=3306 mysql_database=test mysql_exec() { local query="$1" local opts="$2" mysql_exec_result=$( printf "%s\n" \ "[client]" \ "user=${mysql_user}" \ "password=${mysql_password}" \ "host=${mysql_host}" \ "port=${mysql_port}" \ "database=${mysql_database}" \ | HOME="/sys" mysql --defaults-file=/dev/stdin "${opts}" -e "${query}" ) } mysql_exec "select 'Hello World' as Message" echo "${mysql_exec_result}" |
The above script allows the specification of credentials and connection information via variables in the script. As with any other shell script, these can be moved into a configuration file and secured with chown/chmod, then included with the source or . command. The mysql_exec() function creates a default my.cnf [client] on the fly and passes it to the MySQL shell via –defaults-file=/dev/stdin. The configuration is never written to disk, which makes this method a bit more secure.
Sometimes, you need to process too many queries to pass on the command line. In this case, there is another technique for passing the credentials.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
mysql_exec_from_file() { local query_file="$1" local opts="$2" local tmpcnf="$(mktemp)" chmod 600 "${tmpcnf}" printf "%sn" "[client]" "user=${mysql_user}" "password=${mysql_password}" "host=${mysql_host}" "port=${mysql_port}" "database=${mysql_database}" > "${tmpcnf}" mysql_exec_from_file_result=$( mysql --defaults-file="${tmpcnf}" "$opts" < "${query_file}" ) rm "${tmpcnf}" } |
This technique uses a temporary file, which allows the queries to be passed from a file or input device. Restrictive permissions are set on the file before the configuration is written. The temporary configuration is removed immediately after the shell exits.
While other languages may offer cleaner ways to access your MySQL database, you’ll always know that you’ll be able to execute your shell-based MySQL job scripts across all of the Unix machines in your enterprise.
You can download these scripts directly from my github account.
Happy scripting!
Discover Percona Server for MySQL, an enhanced, fully open source alternative to standard MySQL databases. Combining the freedom of MySQL Community Edition with enterprise-grade features akin to MySQL Enterprise Edition, Percona Server for MySQL offers unparalleled flexibility, performance, security, and reliability. Embrace a database solution that meets your business demands without the common lock-in and licensing fees of proprietary software.
About the Author
David BennettDavid is a Director of Server Engineering at Percona in charge of the Percona XtraDB Cluster, Percona XtraBackup, and Build/Release teams. He joined Percona in 2014. With over 20 years experience in software development, he brings an abundance of skills and knowledge to the Percona QA team. David is well versed in MySQL, MongoDB, PostgreSQL, Linux, Perl, C/C++ and other open source technologies.
It isn’t KISS
I use the
mysql_config_editorutility to create a file in the users home folder, containing the connection details and an encrypted password, then usemysql --login-path=${connection}within my scripts, negating the need for the password to be in a shell script anywhere, or in plain text.I know it’s possible to extract the password from this file, but it makes things a lot simpler to use 🙂
Why create a script, which is stored on disk, then generates a my.cnf configuration on the fly, instead of simply creating a ~/.my.cnf with the same credentials and chmod restriction? This way you never have the password in the command line, and there are no worries about a wrapper not properly passing queries.
“However, in the case of a self-contained script, we may not want to require the user to create a credential file. It would need to be maintained, and might interfere with other tools that use the MySQL shell.”
This honestly makes no sense and just introduces another possible point of failure.
Take the example where 1000 users need to run a given script, for example a report.
We can now do two things;
1) Create a script using David’s method and give the users access to that script using a Linux group. The credentials in the script can be fixed, and it’s privileges restricted in mysqld. It would be quite secure from attacks like ‘checking the process list for credentials’ etc.
2) Create 1000 individual ~/.my.cnf files – either with a fixed credentials as in (1) – but then we have a problem when any individual needs to connect with other credentials – or with individual per-user credentials (to avoid said issue), but that would mean having to create 1000 mysqld user accounts. Thus, it would mean having introduced 2000 possible points of failure, and each one needs to be maintained – as David pointed out.
is there any trics to use
mysql_exec
with a dump ? Like :
…”database=${mysql_database}”\
| mysql–defaults-file=/dev/stdin” ${opts} < dump.sql
doesn't work as in this simplier test
echo "un" | cat < <(echo "deux")
show "deux"
mysql doesn't seems to have an option to get the dump
Use the mysql_exec_from_file() function from the article.
the content of <dump.sql will send to /dev/stdin(the –defaults-file value), and then config file error will be occured
you right
so i have succeed with a simple query “source ;” (i have forget we can do this way)
so ‘mysql_exec()’ is a really good function
query “source the dump.sql ;”
(wp comments didn’t like some chars i see)
yes, the following is also ok:
printf ….. | mysql –defaults-file=/dev/stdin ${opts} -e “$(cat dump.sql)”
Or use the mysql_exec_from_file() function
David, I love this inventive solution.
It looks like a great way to securely connect without the details being visible (for example in the process list) anywhere, and without the credentials being stored anywhere except in the one location where they have to be – the script from which a function is executed.
Thank you!
😉
I was confused for a second as this is MySQL Monitor (mysql) and not MySQL Shell (mysqlsh)… But a cool solution anyways. probably more secure that using environment variables or any other option.
David,
We also can provide password as
MYSQL_PWD=secret mysql -h192.168.109.201
It may still be possible to access the password from the process environment:
find /proc/ \
-name ‘environ’ \
-type f \
-exec grep -q MYSQL_PWD {} \; \
-exec sh -c “cat environ | tr ‘\0’ ‘\n’ | grep MYSQL_PWD” \; \
2> /dev/null \
| uniq
David – does not this require Root ?
This approach seems to have substantial downside. If .mylogin.cnf file is present it will override the options in defaults-file causing wrong credentials used.
http://dev.mysql.com/doc/refman/5.7/en/mysql-config-editor.html
“When you invoke a client program to connect to the server, the client uses .mylogin.cnf in conjunction with other option files. Its precedence is higher than other option files, but less than options specified explicitly on the client command line”
As currently .mylogin.cnf is rarely used this is especially dangerous as it can have scripts using this method to magically break when such file is added to the system.
Thank you for the report Peter. Apparently the .mylogin.cnf file overrides the –defaults-file option. This issue can be resolved by changing the HOME directory for the mysql process to a location that does not contain .a mylogin.cnf file.
By adding HOME=”/sys” before the call to
mysqlin the script you can set the process home directory. /sys is always read-only and will never contain mysql configuration files. This will ensure that any user configurations do not conflict with the scripts operation.For example:
HOME=”/sys” mysql –defaults-file=/dev/stdin “${opts}” -e “${query}”
I have made changes to the repository: https://github.com/dbpercona/mysql_shell_secure