MySQL Bugs: #104547: myslsh interactive query audit capabilities via syslog can be disabled by user

Bug #104547	myslsh interactive query audit capabilities via syslog can be disabled by user
Submitted:	5 Aug 2021 13:12	Modified:	16 Aug 2021 9:23
Reporter:	Eduardo Ortega	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Shell General / Core Client	Severity:	S4 (Feature request)
Version:	8.0.25	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Mysqlsh 8.0.24 added --syslog flag to allow interactive SQL statements to be syslogged. This allows for auditing which is good and useful. However, it can be easily disabled by the user connected by doing this:

MySQL localhost:5000 ssl Py> shell.options['history.sql.syslog']=False

After this is done, logging stops, thus allowing a user to bypass/disable logging, which is undesirable if the goal is to have reliable auditing of commands issued. Therefore, it would be useful if we could start the mysqlsh with an additional parameter to log SQL statements and make the logging configuration options read-only within the shell. This would enable us to start the shell for the user and securely record all the things they do within the shell.

How to repeat:
- Connect to MySQL using mysqlsh --syslog
- Go to SQL mode and issue a query
- Verify that the query is properly logged in syslog
- Go to Python mode and run `shell.options['history.sql.syslog']=False`
- Go to SQL mode and issue a query
- The last query is not logged in syslog

Suggested fix:
Have a set of options that are not modifiable within the shell, to create a "restricted mysqlsh". We suggest that the logging options should be in this set of options as an initial implementation. It could be extended to restrict local infile or outfile, for exmaple, in future.

An example invocation would be
mysqlsh --syslog --restricted

Hello Eduardo Ortega,

Thank you for the reasonable feature request!

regards,
Umesh

Hi Eduardo,

While the `--restricted` setting would prevent disabling the logging of the commands to syslog, anybody with bad intent can just use the regular mysql client, use another binary that does not have the restricted mode or just run mysqlshell without restricted mode enabled, on the same host or on another host. 

So in my opionino there are several ways to go around this auditing, regardless of implementing `--restricted`. Maybe I don't see the use case for this. Please explain your specific use case where this feature would provide this solid auditing.

Kenny

Note that it is also possible to bypass --restricted and execute another mysqlsh process from python mode or even using something like \pager mysqlsh root@some-server, or execute SQL through scripting mode (e.g. session.runSql("shutdown"))

Server side audit logging is safer and more reliable.

Hi, Kenny and Alfredo:

Our use case is this:

Devs have access to so called application staging servers to perform certain operations. One of the operations is direct connection to MySQL (using the credentials of the application) to, for example, troubleshoot production issues. For systems under certain regulatory/compliance requirements (e.g. SOx), there is a need to audit exactly what they do in the database. Hence we would like to use the syslog feature for it, but it only makes sense if they can't disable it. Notice that we ONLY want to audit the queries that are run by people using the staging server, not the queries executed by apps running on the same server.

The concerns about devs bypassing auditing by connecting with the classical mysql client or other clients are not applicable in our case, because they don't really get bash/zsh shell access on the staging servers. Instead, when they ssh, they land in a custom shell that only has a menu with a set of very limited things that they can do. One of them is connecting to MySQL. If they choose this, this custom shell could spawn the mysqlsh process, passing the credentials and the --syslog flag, so that the devs never have access to the MySQL credentials, or the chance to control what client application is used for connecting. That being said, this whole approach breaks down if from inside mysqlsh, the user can disable auditing.

I understand that auditing on the server can be more reliable, but the server has no way to know whether the incoming connection comes from the application itself (no need to do logging of queries) or from a person using mysqlsh (needs to be logged), as the connection credentials are the same - the credentials used by the application that runs on that server to access the database. Furthermore, we run MySQL community, not Enterprise, and to the best of my knowledge, only Enterprise has the audit plugin. Unless you suggest we try something with Percona's audit plugin. We could try, but still have the issue of distinguishing queries coming from the app vs coming from a human-triggered mysqlsh process.

Does this make sense?

Regards,

You can tell apart the source application using session connect attributes (https://dev.mysql.com/doc/refman/8.0/en/performance-schema-connection-attribute-tables.htm...), which is exposed to audit plugins and is set by mysql, mysqlsh and most recent Connectors

For example, I'm connected to the same server using mysqlsh and mysql:

 MySQL   localhost:3306   SQL  select * from performance_schema.session_connect_attrs where attr_name='program_name';
+----------------+--------------+------------+------------------+
| PROCESSLIST_ID | ATTR_NAME    | ATTR_VALUE | ORDINAL_POSITION |
+----------------+--------------+------------+------------------+
|             69 | program_name | mysqlsh    |                5 |
|             70 | program_name | mysql      |                6 |
+----------------+--------------+------------+------------------+

Hi, Alfredo:

Thanks for the clarification. Can you provide extra info? I am afraid that I cannot find a way to filter logs based on the value of connection attributes in the audit filter plugin docs at https://dev.mysql.com/doc/refman/8.0/en/audit-log-filter-definitions.html

It also does not seem like Percona's plugin has the capability to filter based on connection attributes: https://www.percona.com/doc/percona-server/5.7/management/audit_log_plugin.html

Or are you suggesting that we build our own audit plugin?

Regards,

E.

Hi Eduardo,

You're right. I checked with the team working on Audit and while it is possible to log the session attributes, it is not yet possible to filter on it.

As for this FR, we potentially consider implementing some functionality in mysqlsh, but we really are concerned that it will not fulfill the requirements as we have cannot guarantee that queries will always be logged, regardless of `--restricted` mode. We've outlined some examples before.