MySQL security top wish list

Security seems to have no boundaries. I’ve been tightening our database security lately, and it seems like this could go on forever: from app to console to privileges to server, there are so many aspects to managing database security. Unfortunately, this is a field where MySQL is in particular weak, and with very little work done in the many years I’ve been working with MySQL.

My very own top-wanted security features for MySQL follows. Surely this is but a small subset, your mileage may vary.

Autherntication-only SSL

By default, MySQL client API is unencrypted and passwords are sent in cleartext. MySQL supports SSL, but it an “all or nothing” deal: if you want to use SSL, then everything goes by SSL: any query, SELECT, DDL and whatnot.

[UPDATE]: Thanks to Davi & Jan for correcting me on this: passwords are not sent via cleartext. I’m not sure by which constellation I saw cleartext passwords being sent — but obviously that was long time ago. Just verified via tcpdump, got “mysql_native_password” message and no cleartext password. Lesson learned!

Roles

Need I elaborate? This is a fundamental construct in a database grant system. The effort of maintaining multiple accounts with similar/identical privileges is overwhelming. (PS I haven’t used Securich to date)

Host aggregation

In MySQL the combination of user+host makes for a distinct account. Thus, ‘gromit’@’192.168.%’ is a completely different account than ‘gromit’@’10.10.%’. I get the idea: you can have more privileges to, say, gromit@localhost than for gromit@’192.%’. In practice, this only makes a headache. In all my years, I have never encountered nor designed a privilege set where two accounts of the same user had different set of privileges. Never ever ever. It is confusing and pointless: if an account has a different set of roles, just call it by another name! Continue reading » “MySQL security top wish list”

Trick: recovering from “no space left on device” issues with MySQL

Just read Ronald Bradford’s post on an unnecessary 3am (emergency) call. I sympathize! Running out of disk space makes for some weird MySQL behaviour, and in fact whenever I encounter weird behaviour I verify disk space.

But here’s a trick I’ve been using for years to avoid such cases and to be able to recover quickly. It helped me on such events as running out of disk space during ALTER TABLEs or avoiding purging of binary logs when slave is known to be under maintenance.

Ronald suggested it — just put a dummy file in your @@datadir! I like putting a 1GB dummy file: I typically copy+paste a 1GB binary log file and call it “placeholder.tmp”. Then I forget all about it. My disk space should not run out — if it does it’s a cause for emergency. I have monitoring, but sometimes I’m hoping to make an operation on 97%99% utilization.

If I do run out of disk space: well, MySQL won’t let me connect; won’t complete an important statement; not sync transaction to disk — bad situation. Not a problem in our case: we can magically recover 1GB worth of data from the @@datadir, buying us enough time (maybe just minutes) to gracefully complete so necessary operations; connect, KILL, shutdown, abort etc.

common_schema 2.2: better QueryScript isolation & cleanup; TokuDB; table_rotate, split params

common_schema 2.2 is released. This is shortly after the 2.1 release; it was only meant as bug fixes release but some interesting things came up, leading to new functionality.

Highlights of the 2.2 release:

  • Better QueryScript isolation & cleanup: isolation improved across replication topology, cleanup done even on error
  • Added TokuDB related views
  • split with “index” hint (Ike, this is for you)
  • table_rotate(): a logrotate-like mechanism for tables
  • better throw()

Drill down:

Better QueryScript isolation & cleanup

common_schema 2.1 introduced persistent tables for QueryScript. This also introduced the problem of isolating concurrent scripts, all reading from and writing to shared tables. In 2.1 isolation was based on session id. However although unique per machine, collisions were possible across replication topology: a script could be issued on master, another on slave (I have such use cases) and both use same (local) session id.

With 2.2 isolation is based on server_id & session id combination; this is unique across a replication topology.

Until 2.1, QueryScript used temporary tables. This meant any error would just break the script, and the tables were left (isolated as they were, and auto-destroyed in time). With persistent tables a script throwing an error meant legacy code piling up. With common_schema 2.2 and on MySQL >= 5.5 all exceptions are caught, cleanup is made, leaving exceptions to be RESIGNALled.

TokuDB views

A couple TokuDB related views help out in converting to TokuDB and in figuring out tables status on disk: Continue reading » “common_schema 2.2: better QueryScript isolation & cleanup; TokuDB; table_rotate, split params”

Tool of the day: q

If you work with command line and know your SQL, q is a great tool to use:

q allows you to query your text files or standard input with SQL. You can:

SELECT c1, COUNT(*) FROM /home/shlomi/tmp/my_file.csv GROUP BY c1

And you can:

SELECT all.c2 FROM /tmp/all_engines.txt AS all LEFT JOIN /tmp/innodb_engines.txt AS inno USING (c1, c2) WHERE inno.c3 IS NULL

And you can also combine with your favourite shell commands and tools:

grep "my_term" /tmp/my_file.txt | q "SELECT c4 FROM - JOIN /home/shlomi/static.txt USING (c1)" | xargs touch

Some of q‘s functionality (and indeed, SQL functionality) can be found in command line tools. You can use grep for pseudo WHERE filtering, or cut for projecting, but you can only get so far with cat my_file.csv | sort | uniq -c | sort -n. SQL is way more powerful for working with tabulated data, and so q makes for a great addition into one’s toolbox.

The tool is authored by my colleague Harel Ben-Attia, and is in daily use over at our company (it is in fact installed on all production servers).

It is of course free and open source (get it on GitHub, where you can also find documentation), and very easy to setup. Enjoy!