But the change goes farther than that; there is also a security change. Oracle created a pitfall of 2 changes at the same time:

1. Variables/status moved to a different table
2. Privileges required on said table

As an example, my non-root user gets:

mysql> show session variables like 'tx_isolation';
ERROR 1142 (42000): SELECT command denied to user 'normal_user'@'my_host' for table 'session_variables'

Who gets affected by this? Nearly everyone and everything.

• Your Nagios will not be able to read status variables
• Your ORM will not be able to determine session variables
• Your replication user will fail connecting (see this post by Giuseppe)
• And most everyone else.

The problem with the above is that involves two unrelated changes to your setup, which are not entirely simple to coordinate:

1. Change your app code to choose the correct schema (information_schema vs. performance_schema)
2. GRANT the permissions on your database

Perhaps at this point you still do not consider this to be a problem. You may be thinking: well, let’s first prepare by creating the GRANTs, and once that is in place, we can, at our leisure, modify the code.

Not so fast. Can you really that simply create those GRANTs?

Migration woes

How do you migrate to a new MySQL version? You do not reinstall all your servers. You want an easy migration path, and that path is: introduce one or two slaves of a newer version, see that everything works to your satisfaction, slowly upgrade all your other slaves, eventually switchover/upgrade your master.

This should not be any different for 5.7. We would like to provision a 5.7 slave in our topologies and just see that everything works. Well, we have, and things don’t just work. Our Nagios stops working for that 5.7 slave. Orchestrator started complaining (by this time I’ve already fixed it to be more tolerant for the 5.7 problems so no crashes here).

I hope you see the problem by now.

You cannot issue a GRANT SELECT ON performance_schema.global_variables TO ‘…’ on your 5.6 master.

The table simply does not exist there, which means the statement will not go to binary logs, which means it will not replicate on your 5.7 slave, which means you will not be able to SHOW GLOBAL VARIABLES on your slave, which means everything remains broken.

Yes, you can issue this directly on your 5.7 slaves. It’s doable, but undesired. It’s ugly in terms of automation (and will quite possibly break some assumptions and sanity checks your automation uses); in terms of validity testing. It’s unfriendly to GTID (make sure to SET SQL_LOG_BIN=0 before that).

WHY in the first place?

It seems like a security thing. I’m not sure whether this was intended. So you prevent a SHOW GLOBAL VARIABLES for a normal user. Makes sense. And yet:

mysql> show global variables like 'hostname';
ERROR 1142 (42000): SELECT command denied to user 'normal_user'@'my_host' for table 'global_variables'

mysql> select @@global.hostname;
+---------------------+
| @@global.hostname   |
+---------------------+
| myhost.mydomain.com |
+---------------------+

mysql> select @@version;
+--------------+
| @@version    |
+--------------+
| 5.7.8-rc-log |
+--------------+

Seems like I’m allowed access to that info after all. So it’s not strictly a security design decision. For status variable, I admit, I don’t have a similar workaround.

Solutions?

The following are meant to be solutions, but do not really solve the problem:

• SHOW commands. SHOW GLOBAL|SESSION VARIABLES|STATUS will work properly, and will implicitly know whether to provide the results via information_schema or performance_schema tables.
  • But, aren’t we meant to be happier with SELECT queries? So that I can really do stuff that is smarter than LIKE ‘variable_name%’?
  • And of course you cannot use SHOW in server side cursors. Your stored routines are in a mess now.
  • This does not solve the GRANTs problem.
• show_compatibility_56: an introduced variable in 5.7, boolean. It truly is a time-travel-paradox novel in disguise, in multiple respects.
  • Documentation introduces it, and says it is deprecated.
    • time-travel-paradox :O
  • But it actually works in 5.7.8 (latest)
    • time-travel-paradox plot thickens
  • Your automation scripts do not know in advance whether your MySQL has this variable
    • Hence SELECT @@global.show_compatibility_56 will produce an error on 5.6
    • But the “safe” way of SHOW GLOBAL VARIABLES LIKE ‘show_compatibility_56’ will fail on a privilege error on 5.7
    • time-travel-paradox :O
  • Actually advised by my colleague Simon J. Mudd, show_compatibility_56 defaults to OFF. I support this line of thought. Or else it’s old_passwords=1 all over again.
  • show_compatibility_56 doesn’t solve the GRANTs problem.
  • This does not solve any migration path. It just postpones the moment when I will hit the same problem. When I flip the variable from “1” to “0”, I’m back at square one.

Suggestion

I claim security is not the issue, as presented above. I claim Oracle will yet again fall into the trap of no-easy-way-to-migrate-to-GTID in 5.6 if the current solution is unchanged. I claim that there have been too many changes at once. Therefore, I suggest one of the alternative two flows:

1. Flow 1: keep information_schema, later migration into performance_schema
   • In 5.7, information_schema tables should still produce the data.
   • No security constraints on information_schema
   • Generate WARNINGs on reading from information_schema (“…this will be deprecated…”)
   • performance_schema also available. With security constraints, whatever.
   • In 5.8 remove information_schema tables; we are left with performance_schema only.
2. Flow 2: easy migration into performance_schema:
   • In 5.7, performance_schema tables should not require any special privileges. Any user can read from them.
   • Keep show_compatibility_56 as it is.
   • SHOW commands choose between information_schema or performance_schema on their own — just as things are done now.
   • In 5.8, performance_schema tables will require SELECT privileges.

As always, I love the work done by the engineers; and I love how they listen to the community.

Comments are most welcome. Have I missed the simple solution here? Are there even more complications to these features? Thoughts on my suggested two flows?

[UPDATE 2015-08-19]

Please see this followup by Morgan Tocker of Oracle.

Find who has privileges on a table called t:

select * from common_schema.sql_grants where priv_level_name='table' and object_name='t'\G

           GRANTEE: 'power1'@'localhost'
              user: power1
              host: localhost
        priv_level: `mytst`.`t`
   priv_level_name: table
     object_schema: mytst
       object_name: t
current_privileges: SELECT
      IS_GRANTABLE: NO
         sql_grant: GRANT SELECT ON `mytst`.`t` TO 'power1'@'localhost'
        sql_revoke: REVOKE SELECT ON `mytst`.`t` FROM 'power1'@'localhost'
     sql_drop_user: DROP USER 'power1'@'localhost'

or, who has privileges on the test schema:

select * from common_schema.sql_grants where priv_level_name='schema' and object_name='test' \G

           GRANTEE: 'power1'@'localhost'
              user: power1
              host: localhost
        priv_level: `test`.*
   priv_level_name: schema
     object_schema: NULL
       object_name: test
current_privileges: ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE
      IS_GRANTABLE: NO
         sql_grant: GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON `test`.* TO 'power1'@'localhost'
        sql_revoke: REVOKE ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON `test`.* FROM 'power1'@'localhost'
     sql_drop_user: DROP USER 'power1'@'localhost'

           GRANTEE: 'test'@'localhost'
              user: test
              host: localhost
        priv_level: `test`.*
   priv_level_name: schema
     object_schema: NULL
       object_name: test
current_privileges: ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE
      IS_GRANTABLE: NO
         sql_grant: GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON `test`.* TO 'test'@'localhost'
        sql_revoke: REVOKE ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON `test`.* FROM 'test'@'localhost'
     sql_drop_user: DROP USER 'test'@'localhost'

In the same manner, you can easily SELECT for all grants that are defined per-table, per-schema, … priv_level_name: is any one of ‘user’, ‘schema’, ‘table’, ‘column’, ‘routine’.

The above is a view, which aggregates data from all relevant INFORMATION_SCHEMA tables, normalizing and de-normalizing as necessary.

You might also want to look at sql_show_grants which provides with a no-breakdown GRANT for all accounts. It’s like a SHOW ALL GRANTS which doesn’t exist, plus it’s SELECTable.

Also, thanks to eval(), the above make for executable code. Consider:

Revoke any and all specific grants on private_schema.private_table:

call common_schema.eval("select sql_revoke from sql_grants where object_schema='private_schema' and object_name='private_table'")

 

It’s been around for quite a while now. We’re using it in production extensively. Try it out!

• Follow the sudo example. Don’t let all you DBAs and Ops have the password for the root account. Have each and every one of them have their own personal super-duper account, with their own personal and private password. This makes it so easy when someone leaves the company. No need to change passwords, just to remove the employee’s account.
• Block root. Either remove it completely or forbid it from logging in. Yes, there’s a way hack in MySQL to have a valid account blocked from logging in. One way of making this happen is via common_schema‘s sql_accounts. Here’s how to block root account using common_schema:

mysql> CALL common_schema.eval("SELECT sql_block_account FROM sql_accounts WHERE USER = 'root'");

• Make lots of small users. Give nagios its own user. Give collectd its own user. Give orchestrator its own user. Give innotop its own user. Give whatever its own user. Yes, it’s more users to create, but you get to have each user as limited in privileges as possible, and you don’t get to wonder why your heartbeat script has SUPER, LOCK and SHUTDOWN privileges.
• Verify: set @@old_passwords=0; before setting a new password. Make sure your configuration does not specify old_passwords = 1. There is no reason to use “old passwords“. In fact, a 5.6 client will refuse connecting with an “old password”.
• Give no access to mysql.*. No one should be tampering directly with the mysql system tables.
• Run oak-security-audit or, if you have common_schema installed (you mean you don’t?), just CALL security_audit(); I can (almost) guarantee you’d be surprised and thankful for the security breakdown. Users without passwords, users sharing same passwords, users with unreasonable privileges, and more… You’ll see them all.
• If you have web interfaces to your database or some of its aspects (e.g. Anemometer, Propagator, Orchestrator, monitoring, …), protect it via LDAP group or similar. Not everyone who has access to your network needs to see you database. Neither does every employee.

It seems that audit plugins are all the rage lately… We’ve developed out simple plugin a month ago as part of our database securing efforts; by auditing any login or login attempt we could either intercept or later investigate suspicious logins.

However we quickly realized there is much more to be gathered by this info.

In very short, you install this plugin onto your MySQL server, and your server starts writing into a text file called audit_login.log entries such as follows:

{"ts":"2013-09-11 09:11:47","type":"successful_login","myhost":"gromit03","thread":"74153868","user":"web_user","priv_user":"web_user","host":"web-87.localdomain","ip":"10.0.0.87"}
{"ts":"2013-09-11 09:11:55","type":"failed_login","myhost":"gromit03","thread":"74153869","user":"backup_user","priv_user":"","host":"web-32","ip":"10.0.0.32"}
{"ts":"2013-09-11 09:11:57","type":"failed_login","myhost":"gromit03","thread":"74153870","user":"backup_user","priv_user":"","host":"web-32","ip":"10.0.0.32"}
{"ts":"2013-09-11 09:12:48","type":"successful_login","myhost":"gromit03","thread":"74153871","user":"root","priv_user":"root","host":"localhost","ip":"10.0.0.111"}
{"ts":"2013-09-11 09:13:26","type":"successful_login","myhost":"gromit03","thread":"74153872","user":"web_user","priv_user":"web_user","host":"web-11.localdomain","ip":"10.0.0.11"}
{"ts":"2013-09-11 09:13:44","type":"successful_login","myhost":"gromit03","thread":"74153873","user":"web_user","priv_user":"web_user","host":"web-40.localdomain","ip":"10.0.0.40"}
{"ts":"2013-09-11 09:13:51","type":"successful_login","myhost":"gromit03","thread":"74153874","user":"web_user","priv_user":"web_user","host":"web-03.localdomain","ip":"10.0.0.03"}
{"ts":"2013-09-11 09:14:09","type":"successful_login","myhost":"gromit03","thread":"74153875","user":"web_user","priv_user":"web_user","host":"web-40.localdomain","ip":"10.0.0.40"}
{"ts":"2013-09-11 10:55:25","type":"successful_login","myhost":"gromit03","thread":"74153876","user":"web_user","priv_user":"web_user","host":"web-87.localdomain","ip":"10.0.0.87"}
{"ts":"2013-09-11 10:55:59","type":"successful_login","myhost":"gromit03","thread":"74153877","user":"web_user","priv_user":"web_user","host":"web-12.localdomain","ip":"10.0.0.12"}
{"ts":"2013-09-11 10:55:59","type":"failed_login","myhost":"gromit03","thread":"74153878","user":"(null)","priv_user":"(null)","host":"(null)","ip":"10.0.0.1"}

In the above your MySQL server is on gromit03, and is accepting connections from other hosts; some successful, some not. What kind of information can you gather from the above?

• You can tell how many connections are being created on your server
• Where they came from
• Where ‘root’ connections come from
• Port scans (see last row) can be identified by no credentials. These don’t have to be port scans per se; any telnet localhost 3006 followed by Ctrl+D will show the same. Typically these would be either load balancer or monitoring tools checks to see that the 3306 port is active.
• You can tell which accounts connect, and how many times
• And you can infer which accounts are stale and can be dropped — if an account does not connect within a week’s time, it’s probably stale (pick your own timeframe)

The above is quite interesting on one host; but we have dozens. We’ve installed this plugin on all our MySQL servers, and we use logstash to aggregate them. We aggregate to two destinations:

• All logs are being sent over to kibana, where they are easily searchable via lucene queries
• They are also all aggregated into one CSV formatted logfile, rotating daily; this format makes it easier for me to grep and uniq -c and generally produce very valuable data by scripting.

By aggregating the logs from multiple hosts, we are able to gather such insight as:

• How well our load balancers are distributing connections
• How our various applications servers connect on our multiple data centres to our MySQL hosts
• Catching misbehaving apps with hardcoded server IPs
• Catching naughty developers logging into our masters directly (we have more mechanisms now to prevent and audit such cases)
• Identifying differences in behaviour of monitoring tools across our data centres
• etc.

Free to use

The audit_login plugin has been made free to use, open source, licensed under GNU GPL v2. It is publicly available in our github repository, where more documentation on build and installation can be found.

As MySQL plugins go, you must compile your plugin with your particular MySQL version. For you convenience, pre-compiled binaries are available in the repository (at this time for 5.5.32 and 5.5.21 linux 64 bit; likely to add more)

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!

Had we had roles, that would be less painful; but my opinion is that an account should be identified by user only, not by user+host. The ‘host’ part should just indicate the whitelist of machines from which the user is allowed to connect.

Host blacklist

Speaking of whitelist, it would be great to have a host blacklist. If I wanted to grant access to ‘gromit’@’192.168.%’ except for ‘192.168.10.%’ — well, I would have to whitelist all the possible subnets. I can’t exclude a set of hosts.

Catalogues

Another common construct not available in MySQL: a level above “schema” and below “server”. The need for catalogues is in particular obvious when you want to grant some user SELECT access to all your schemas. Ahem, excluding, of course, the mysql schema… If I could create a “user catalogue”, as opposed to “system catalogue”, then I would have been able to GRANT SELECT ON CATALOGUE user.* TO my_user@localhost, and this would apply to all databases in that catalogue.

Privileges auditing

I’ve spent the last week or so restricting privileges to all accounts. This is hard work, because you want to make sure you’re not revoking privileges which are required by the system (in which case I would either choose not to revoke, or create a new dedicated account with requested set of privileges). It would be so much fun if I could turn a flag on, like “SET GLOBAL audit_privileges := 1”, and have a ++counter for every time a privilege check is made per account.

I guess we could go on… On a brighter note, I’ve been using the audit plugin interface by writing a login audit plugin with very good results (= good auditing & important insights); the (simple) code will be released shortly as open source; I’ll write more on this at a later stage.

• Duplicate a user (create new user with same privileges as another’s)
• Find users with identical set of grants (same roles)
• Finding redundant users (users who only have privileges on non-existing objects); I was approached on this by Sheeri K. Cabral from Mozilla.

Duplicate user

How would you duplicate a grantee? That’s easy! Just get the SHOW GRANTS output, then do text search and replace: replace the existing account (e.g. ‘existing’@’localhost’) with the new account (e.g. ‘newcomer’@’localhost’).

Ahem. And how would you get the output of SHOW GRANTS? That’s right: you can’t do this from within the server. You have to go outside the server, incoke mysql client, sed your way into it, then connect to MySQL again to invoke the GRANT query… Or you can do this by hand, of course, or you can use the new mysqluserclone tool from MySQL utilities. Bottom line: you have to go outside the server. You can’t directly do this with your favorite GUI tool unless it has this function integrated.

But to have a truly automated, scriptable, server-side user-duplication you don’t need to go far, since the sql_show_grants view simulates a SHOW GRANTS output, but using plain old SQL. It produces the GRANT statement as SQL output. Which means you can REPLACE() the grantee. It’s actually a one liner, but is such a common operation that I created the duplicate_grantee() function for convenience. Just:

call duplicate_grantee('existing@localhost', 'newcomer@localhost');

(and yes, using a more relaxed form of grantee, allowing dropping of all these gory quotes).

Finding similar grantees

If the grants of an account are available via SQL, I’m also able to compare it with the grants of another account. I just need to ignore the account’s name in itself. Since sql_show_grants normalizes the grants in expected order, this works well. Again, this is a simple query, but since it is of importance, I created a view for it: just SELECT * FROM similar_grants to find out about accounts sharing same set of privileges..

Drop redundant users

Sheeri approached me with the following problem (she authorized me to publicize): as she came to work for Mozilla, she inherited the databases to manage. When she reviewed them, she realized there were numerous accounts which were redundant. Redundant how? Well, obviously not being used, but moreover not having privileges on existing objects.

She found out there were many accounts that only had privileges on non-existing schema, something like:

GRANT ALL PRIVILEGES ON penguin.* TO 'gromit'@'localhost'

Where the penguin schema does not exist anymore – it was some developer’s test database. The developer long since not working there, the database long since DROPped.

How do you quickly find and drop such accounts? Should we do crazy parsing on pt-show-grants output? Any other method?

Well, common_schema to the rescue: the sql_grants view breaks the GRANT statement into domains (in fact, this is not a true explanation; it actually builds the GRANT statements per domain; but it’s more convenient to view it as breaking). You actually get the different GRANT statement per account per domain: the GRANT the account has on *.*, the GRANT it has on world.*, the GRANT it has on sakila.*, the GRANT it has on ….

Now this one is not entirely simple. Let me introduce the solution, then explain it. The way to find & drop all such users is:

call eval("SELECT sql_revoke FROM sql_grants LEFT JOIN INFORMATION_SCHEMA.SCHEMATA ON(object_name = SCHEMA_NAME) WHERE priv_level_name='schema' AND DEFAULT_CHARACTER_SET_NAME IS NULL");
call eval("SELECT sql_drop_user FROM sql_grants GROUP BY GRANTEE HAVING COUNT(*)=1 AND SUM(current_privileges='USAGE')=1");

Explanation:

1. The LEFT JOIN query finds all GRANTs on non existing schemas.
2. But we do more than that: we request the sql_revoke column which negates such GRANTs. sql_grants provides such info: the REVOKE statement for said GRANTs.
3. We can actually evaluate the REVOKE statement via eval(). There! These privileges are now gone.
4. Now that we have revoked privileges on non-existing domains, we should be left with accounts whose only privilege is USAGE. Any such account is redundant by definition, and should be dropped. The second query looks up such accounts.
5. And, it asks for the sql_drop_user statement column for those accounts
6. Finally, it evaluates the DROP USER statement via eval().

Here is the statements output without executing the eval():

root@mysql-5.1.51> SELECT sql_revoke FROM sql_grants LEFT JOIN INFORMATION_SCHEMA.SCHEMATA ON(object_name = SCHEMA_NAME) WHERE  priv_level_name='schema'  AND DEFAULT_CHARACTER_SET_NAME IS NULL;
+-----------------------------------------------------------------+
| sql_revoke                                                      |
+-----------------------------------------------------------------+
| REVOKE INSERT, SELECT ON `not_here`.* FROM 'gromit'@'localhost' |
+-----------------------------------------------------------------+

root@mysql-5.1.51> SELECT sql_drop_user FROM sql_grants GROUP BY GRANTEE HAVING COUNT(*)=1 AND SUM(current_privileges='USAGE')=1;
+--------------------------------+
| sql_drop_user                  |
+--------------------------------+
| DROP USER 'gromit'@'localhost' |
+--------------------------------+

• Parameterized split(): take further control over huge transactions by breaking them down into smaller chunks, now manually tunable if needed
• duplicate_grantee(): copy+paste existing accounts along with their full set of privileges
• similar_grants: find which accounts share the exact same set of privileges (i.e. have the same role)
• json_to_xml(): translate any valid JSON object into its equivalent XML form
• extract_json_value(): use XPath notation to extract info from JSON data, just as you would from XML
• query_checksum(): given a query, calculate a checksum on the result set
• random_hash(): get a 40 hexadecimal digits random hash, using a reasonably large changing input

Let’s take a closer look at the above:

Parameterized split()

split takes your bulk query and automagically breaks it down into smaller pieces. So instead of one huge UPDATE or DELETE or INSERT..SELECT transaction, you get many smaller transactions, each with smaller impact on I/O, locks, CPU.

As of 1.3, split() gets more exposed: you can have some control on its execution, and you also get a lot of very interesting info during operation.

Here’s an example of split() control:

set @script := "
  split({start:7015, step:2000} : UPDATE sakila.rental SET return_date = return_date + INTERVAL 1 DAY) 
    throttle 1;
";
call common_schema.run(@script);

In the above we choose a split size of 2,000 rows at a time; but we also choose to only start with 7015, skipping all rows prior to that value. Just what is that value? It depends on the splitting key (and see next example for just that); but in this table we can safely assume this is the rental_id PRIMARY KEY of the table.

You don’t have to use these control parameters. But they can save you some time and effort.

And, look at some interesting info about the splitting process:

set @script := "
  split(sakila.film_actor) 
    select $split_columns as columns, $split_range_start as range_start, $split_range_end as range_end
";
call common_schema.run(@script);
+----------------------+-------------+------------+
| columns              | range_start | range_end  |
+----------------------+-------------+------------+
| `actor_id`,`film_id` | '1','1'     | '39','293' |
+----------------------+-------------+------------+

+----------------------+-------------+------------+
| columns              | range_start | range_end  |
+----------------------+-------------+------------+
| `actor_id`,`film_id` | '39','293'  | '76','234' |
+----------------------+-------------+------------+

+----------------------+-------------+-------------+
| columns              | range_start | range_end   |
+----------------------+-------------+-------------+
| `actor_id`,`film_id` | '76','234'  | '110','513' |
+----------------------+-------------+-------------+

+----------------------+-------------+-------------+
| columns              | range_start | range_end   |
+----------------------+-------------+-------------+
| `actor_id`,`film_id` | '110','513' | '146','278' |
+----------------------+-------------+-------------+

+----------------------+-------------+-------------+
| columns              | range_start | range_end   |
+----------------------+-------------+-------------+
| `actor_id`,`film_id` | '146','278' | '183','862' |
+----------------------+-------------+-------------+

+----------------------+-------------+-------------+
| columns              | range_start | range_end   |
+----------------------+-------------+-------------+
| `actor_id`,`film_id` | '183','862' | '200','993' |
+----------------------+-------------+-------------+

In the above you get to be told exactly how table splitting occurs: you are being told what columns are used to split the table, and what range of values is used in each step. There’s more to it: read the split() documentation.

similar_grants

Out of your 100 different grants, which ones share the exact same set of privileges? MySQL has non notion of roles, but that doesn’t mean the notion does not exist. Multiple accounts share the same restrictions and privileges. Use similar_grants to find out which. You might just realize there’s a few redundant accounts in your system.

mysql> SELECT * FROM similar_grants;
+-------------------------------+----------------+-------------------------------------------------------+
| sample_grantee                | count_grantees | similar_grantees                                      |
+-------------------------------+----------------+-------------------------------------------------------+
| 'root'@'127.0.0.1'            |              3 | 'root'@'127.0.0.1','root'@'myhost','root'@'localhost' |
| 'repl'@'10.%'                 |              2 | 'repl'@'10.%','replication'@'10.0.0.%'                |
| 'apps'@'%'                    |              1 | 'apps'@'%'                                            |
| 'gromit'@'localhost'          |              1 | 'gromit'@'localhost'                                  |
| 'monitoring_user'@'localhost' |              1 | 'monitoring_user'@'localhost'                         |
+-------------------------------+----------------+-------------------------------------------------------+

duplicate_grantee()

Provide an existing account, and name your new, exact duplicate account. The complete set of privileges is copied, and so is the password. duplicate_grantee() is your Copy+Paste of MySQL accounts.

Let’s begin with some pre-existing account and see how it duplicates:

mysql> show grants for 'world_user'@'localhost';
+------------------------------------------------------------------------------------------------------------------------+
| Grants for world_user@localhost                                                                                        |
+------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'world_user'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'      |
| GRANT ALL PRIVILEGES ON `world`.* TO 'world_user'@'localhost'                                                          |
| GRANT EXECUTE, ALTER ROUTINE ON FUNCTION `sakila`.`get_customer_balance` TO 'world_user'@'localhost' WITH GRANT OPTION |
+------------------------------------------------------------------------------------------------------------------------+

mysql> call duplicate_grantee('world_user@localhost', 'copied_user@10.0.0.%');
Query OK, 0 rows affected (0.06 sec)

mysql> show grants for 'copied_user'@'10.0.0.%';
+------------------------------------------------------------------------------------------------------------------------+
| Grants for copied_user@10.0.0.%                                                                                        |
+------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'copied_user'@'10.0.0.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'      |
| GRANT ALL PRIVILEGES ON `world`.* TO 'copied_user'@'10.0.0.%'                                                          |
| GRANT EXECUTE, ALTER ROUTINE ON FUNCTION `sakila`.`get_customer_balance` TO 'copied_user'@'10.0.0.%' WITH GRANT OPTION |
+------------------------------------------------------------------------------------------------------------------------+

The routine is quite relaxed in grantee format. copied_user@10.0.0.%, copied_user@’10.0.0.%’ and ‘copied_user’@’10.0.0.%’ are all just fine, and represent the same account. Saves trouble with all that quoting.

json_to_xml()

JSON is becoming increasingly popular in storing dynamically-structured data. XML’s tags overhead and its human unfriendliness make it less popular today. However, the two share similar concepts, and conversion between the two is possible. json_to_xml() will translate your valid JSON data into its equivalent XML format. The rules are simple (all-nodes-and-data, no attributes, arrays as repeating nodes, objects as subnodes) and the results are valid XML objects.

Sample data taken from json.org:

mysql> SET @json := '
{
  "menu": {
    "id": "file",
    "value": "File",
    "popup": {
      "menuitem": [
        {"value": "New", "onclick": "CreateNewDoc()"},
        {"value": "Open", "onclick": "OpenDoc()"},
        {"value": "Close", "onclick": "CloseDoc()"}
      ]
    }
  }
}
';

mysql> SELECT json_to_xml(@json) AS xml \G
*************************** 1. row ***************************
xml: <menu><id>file</id><value>File</value><popup><menuitem><value>New</value><onclick>CreateNewDoc()</onclick></menuitem><menuitem><value>Open</value><onclick>OpenDoc()</onclick></menuitem><menuitem><value>Close</value><onclick>CloseDoc()</onclick></menuitem></popup></menu>

Beautified form of the above result:

<menu>
  <id>file</id>
  <value>File</value>
  <popup>
    <menuitem>
      <value>New</value>
      <onclick>CreateNewDoc()</onclick>
    </menuitem>
    <menuitem>
      <value>Open</value>
      <onclick>OpenDoc()</onclick>
    </menuitem>
    <menuitem>
      <value>Close</value>
      <onclick>CloseDoc()</onclick>
    </menuitem>
  </popup>
</menu>

Note that linked examples page uses sporadically invented attributes; common_schema prefers using well-defined nodes.

extract_json_value()

Which means things you can do with XML can also be done with JSON. XPath is a popular extraction DSL, working not only for XML but also for Object Oriented structures (see Groovy’s nice integration of XPath into the language, or just commons-beans for conservative approach). JSON is a perfect data store for XPath expressions; by utilizing the translation between JSON and XML, one is now easily able to extract value from JSON (using same example as above):

mysql> SELECT extract_json_value(@json, '//id') AS result;
+--------+
| result |
+--------+
| file   |
+--------+

mysql> SELECT extract_json_value(@json, 'count(/menu/popup/menuitem)') AS count_items;
+-------------+
| count_items |
+-------------+
| 3           |
+-------------+

Implementations of json_to_xml() and extract_json_value() are CPU intensive. There is really just one justification for having these written in Stored Procedures: their lack in the standard MySQL function library. This is reason enough. Just be aware; test with BENCHMARK().

query_checksum()

It looks like this:

mysql> call query_checksum('select id from world.City where id in (select capital from world.Country) order by id');
+----------------------------------+
| checksum                         |
+----------------------------------+
| 5f35070b90b0c079ba692048c51a89fe |
+----------------------------------+

mysql> call query_checksum('select capital from world.Country where capital is not null order by capital');
+----------------------------------+
| checksum                         |
+----------------------------------+
| 5f35070b90b0c079ba692048c51a89fe |
+----------------------------------+

The two queries above yield with the same result set. As consequence, query_checksum() produces the same checksum value for both. The next query produces a different result set, hence a different checksum:

mysql> call query_checksum('select id from world.City where id in (select capital from world.Country) order by id limit 10');
+----------------------------------+
| checksum                         |
+----------------------------------+
| 997079c2dfca34ba87ae44ed8965276e |
+----------------------------------+

The routine actually invokes the given queries (modifying them a bit along the way) and uses a deterministic incremental checksum to get the final result.

Its use? As a handy built-in mechanism for comparing your table data. This is meant for relatively small result sets – not for your 20GB table. Inspired by Baron’s old trick, and works on server side (Windows/GUI/automated clients to benefit).

random_hash()

Random hashes come handy. The naive way to produce them is by executing something like SELECT SHA1(RAND()). However the RAND() function just doesn’t provide enough plaintext for the hash function. The SHA/MD5 functions expect a textual input, and produce a 160/128 bit long hash. The maximum char length of a RAND() result is 20 characters or so, and these are limited to the 0-9 digits. So at about 10^20 options for input, which is about 64 bit. Hmmmm. a 64 bit input to generate a 160 bit output? I don’t think so! random_hash() provides additional input in the form of your current status (at about 830 characters) as well as RAND(), SYSDATE() and server ID.

Bugfixes

Any bugfix adds at least one test; typically more. Currently with over 470 tests, common_schema is built to work.

Get common_schema

common_schema 1.3 is available under the permissive New BSD License. Find the latest download here.

If you like to support common_schema, I’m always open for ideas and contributions. Or you can just spread the word!

• Account blocking
• Security audit
• RANGE partition management
• Slave status
• Better blocking and idle transaction management
• QueryScript goodies:
  • echo, report
  • while-otherwise statement; foreach-otherwise statement
  • Better variable scope handling
  • Complete support for variable expansion
  • Transaction support within QueryScript
• More summary info and SQL statements in processlist-related views

A closer look at these follows:

Account blocking

A new view called sql_accounts, inspired by oak-block-account (also see here and here) provides with the means of blocking use accounts (and releasing them, of course) without revoking their privileges. It offers the SQL statements to block an account (by modifying its password in a symmetric way) and to release an account (by modifying its password back to normal). It really works like a charm. Together with killall() and sql_accounts this gives the administrator great control over accounts.

Security audit

Imported from openark kit, and implemented via QueryScript, the security_audit() procedure will audit your accounts, passwords and general settings to find problems, pitfalls and security hazards. I will write more on this later.

RANGE partition management

The sql_range_partitions view manages your RANGE and RANGE COLUMNS partitioned tables by providing with the SQL statements to drop oldest partitions and to create the next (in sequence) partitions. See my earlier post.

Slave status

This is a hack providing a minified version of SHOW SLAVE STATUS, but as a view (slave_status). It only provides with 5 columns:

mysql> SELECT * FROM slave_status \G
*************************** 1. row ***************************
 Slave_Connected_time: 82077
     Slave_IO_Running: 1
    Slave_SQL_Running: 1
        Slave_Running: 1
Seconds_Behind_Master: 5

For me, the Seconds_Behind_Master is one critical value I am interested in getting using a query. So here it is.

mysql> SELECT (Seconds_Behind_Master < 10) IS TRUE AS slave_is_up_to_date FROM slave_status;
+---------------------+
| slave_is_up_to_date |
+---------------------+
|                   1 |
+---------------------+

QueryScript goodies

• while-otherwise statement: while (some_condition) { … } otherwise { /* this gets executed if the while never performs a single iteration */ }
• foreach-otherwise statement, likewise
• echo, report statements: echo your statements before applying them, or just echo your comments along the code. Generate a (beautified) report at the end of script execution (which is how security_audit() works).
• Better variable scopes: now allowing variables of same name to be declared when their scopes do not overlap. This makes for the expected behavior a programmer would expect.
• Complete variable expansion handling: expanded variables are now recognized anywhere within the script, including inside a while or foreach expression.
• Transactions are now handled by QueryScript and immediately delegated to MySQL. This completes the transaction management in QueryScript. Just start transaction, commit or rollback at will.

InnoDB idle transactions, blocking transactions

The innodb_transactions view now lists idle transactions, as well as their idle time. It also provides with the SQL statements to kill the query or connection for each transaction. This allows for a quick track or track-and-kill of idle transactions.

The innodb_locked_transactions view now offers the wait time and SQL statements for killing the query or connection of a blocking  transaction. This allows for a quick track or track-and-kill long time blocking transactions.

I will write more in depth on both in a future post.

Processlist-related views

The new processlist_states view aggregates processlist by thread state. This view, and all other processlist views now provide with median or 95% median runtime for processes, in addition to the less informative AVG provided earlier.

Get it!

common_schema is free and licensed under the New BSD License. It is nothing but a SQL file, so you simply import it into your MySQL server. common_schema installs on any MySQL >= 5.1 server, including Percona Server and MariaDB, tested on 5.1, 5.5 and 5.6 RC.

Go to common_schema download page.

• eval(): Evaluates the queries generated by a given query
• match_grantee(): Match an existing account based on user+host
• processlist_grantees: Assigning of GRANTEEs for connected processes
• candidate_keys: Listing of prioritized candidate keys: keys which are UNIQUE, by order of best-use.
• easter_day(): Returns DATE of easter day in given DATETIME’s year.

Let’s take a slightly closer look at these:

eval()

I’ve dedicated this blog post on MySQL eval() to describe it. In simple summary: eval() takes a query which generates queries (most common use queries on INFORMATION_SCHEMA) and auto-evaluates (executes) those queries. Read more

match_grantee()

As presented in Finding CURRENT_USER for any user, I’ve developed the algorithm to match a connected user+host details (as presented with PROCESSLIST) with the grantee tables (i.e. the mysql.user table), in a manner which simulates the MySQL server account matching algorithm.

This is now available as a stored function: given a user+host, the function returns with the best matched grantee. Read more

processlist_grantees

This view relies on the above, and maps the entire PROCESSLIST onto GRANTEEs. The view maps each process onto the GRANTEE (MySQL account) which is the owner of that process. Surprisingly, MySQL does not provide one with such information.

The view also provides with the following useful metadata:

• Is said process executes under a SUPER privilege?
• Is this a replication thread, or serving a replicating client?
• Is this process the current connection (myself)?

In the spirit of common_schema, it provides with the SQL commands necessary to KILL and KILL QUERY for each process. A sample output:

mysql> SELECT * FROM common_schema.processlist_grantees;
+--------+------------+---------------------+------------------------+--------------+--------------+----------+---------+-------------------+---------------------+
| ID     | USER       | HOST                | GRANTEE                | grantee_user | grantee_host | is_super | is_repl | sql_kill_query    | sql_kill_connection |
+--------+------------+---------------------+------------------------+--------------+--------------+----------+---------+-------------------+---------------------+
| 650472 | replica    | jboss00.myweb:34266 | 'replica'@'%.myweb'    | replica      | %.myweb      |        0 |       1 | KILL QUERY 650472 | KILL 650472         |
| 692346 | openarkkit | jboss02.myweb:43740 | 'openarkkit'@'%.myweb' | openarkkit   | %.myweb      |        0 |       0 | KILL QUERY 692346 | KILL 692346         |
| 842853 | root       | localhost           | 'root'@'localhost'     | root         | localhost    |        1 |       0 | KILL QUERY 842853 | KILL 842853         |
| 843443 | jboss      | jboss03.myweb:40007 | 'jboss'@'%.myweb'      | jboss        | %.myweb      |        0 |       0 | KILL QUERY 843443 | KILL 843443         |
| 843444 | jboss      | jboss03.myweb:40012 | 'jboss'@'%.myweb'      | jboss        | %.myweb      |        0 |       0 | KILL QUERY 843444 | KILL 843444         |
| 843510 | jboss      | jboss00.myweb:49850 | 'jboss'@'%.myweb'      | jboss        | %.myweb      |        0 |       0 | KILL QUERY 843510 | KILL 843510         |
| 844559 | jboss      | jboss01.myweb:37031 | 'jboss'@'%.myweb'      | jboss        | %.myweb      |        0 |       0 | KILL QUERY 844559 | KILL 844559         |
+--------+------------+---------------------+------------------------+--------------+--------------+----------+---------+-------------------+---------------------+

Finally, it is now possible to execute the following:  “Kill all slow queries which are not executed by users with the SUPER privilege or are replication threads”. To just generate the commands, execute:

mysql> SELECT sql_kill_connection FROM common_schema.processlist_grantees WHERE is_super = 0 AND is_repl = 0;

Sorry, did you only want to kill the queries? Those which are very slow? Do as follows:

mysql> SELECT sql_kill_connection FROM common_schema.processlist_grantees JOIN INFORMATION_SCHEMA.PROCESSLIST USING(ID) WHERE TIME > 10 AND is_super = 0 AND is_repl = 0;

But, really, we don’t just want commands. We really want to execute this!

Good! Step in eval():

mysql> CALL common_schema.eval('SELECT sql_kill_query FROM common_schema.processlist_grantees JOIN INFORMATION_SCHEMA.PROCESSLIST USING(id) WHERE TIME > 10 AND is_super = 0 AND is_repl = 0');

Read more

candidate_keys

A view which lists the candidate keys for tables and provides ranking for those keys, based on some simple heuristics.

This view uses  the same algorithm as that used by oak-chunk-update and oak-online-alter-table, tools in the openark kit. So it provides with a way to choose the best candidate key to walk through a table. At current, a table’s PRIMARY KEY is always considered to be best, because of InnoDB’s structure of clustered index. But I intend to change that as well and provide general recommendation about candidate keys (so for example, I would be able to recommend that the PRIMARY KEY is not optimal for some table).

Actually, after a discussion initiated by Giuseppe and Roland, starting here and continuing on mail, there are more checks to be made for candidate keys, and I suspect the next version of candidate_keys will be more informational.

Read more

easter_day()

Many thanks to Roland Bouman who suggested his code for calculating easter day for a given year. Weehee! This is the first contribution to common_schema! Read more

Get it

common_schema is an open source project. It is released under the BSD license.

Find it here.

However, the user/host from which the connection is made are not the same as the user/host as specified in the account. For example, the account may be created thus:

CREATE USER 'temp'@'10.0.0.%' IDENTIFIED BY '123456';

The host as specified in the above account is a wildcard host. A connection by the ‘temp’ user from ‘10.0.0.3’ can map into that account. It thus happens that the connected user is ‘temp’@’10.0.0.3’, yet the assigned account is ‘temp’@’10.0.0.%’.

MySQL provides with the USER() and CURRENT_USER() which map to the connected user and the assigned account, respectively, and which lets the current session identify the relation between the two. Read more on this on the MySQL docs.

The problem

And the trouble is: MySQL only provides this functionality for the current session. Surprisingly, given a user/host combination, I cannot get MySQL to tell me which account matches those details.

The inconsistency

And I care because there is an inconsistency. Namely, when I do SHOW PROCESSLIST MySQL tells me the user & host from which the connection is made. It does not tell me the account for which the process is assigned.

root@mysql-5.1.51> SHOW PROCESSLIST;
+----+------+----------------+---------------+---------+------+-------+------------------+
| Id | User | Host           | db            | Command | Time | State | Info             |
+----+------+----------------+---------------+---------+------+-------+------------------+
| 16 | temp | 10.0.0.3:54142 | common_schema | Query   |    0 | NULL  | SELECT id, ...   |
+----+------+----------------+---------------+---------+------+-------+------------------+

The absurdness is that a super user, the manager of a MySQL server, has the full listing of connections, yet is unable to map those connections to accounts.

I got into this because of a suggestion by Matthew Montgomery to include a tool of his into common_schema.The tool says “Kill all slow queries which are not executed by users with the SUPER privilege”.

Great idea! But then, how do you identify such users?

The tool attempts to find an exact match between INFORMATION_SCHEMA.PROCESSLIST‘s user/host and INFORMATION_SCHEMA.USER_PRIVILEGES‘s user/host. This will do well when the account is ‘root’@’localhost’, but less so when it is ‘maatkit’@’%.mydomain’.

Unfortunately, many things fall under SUPER‘s attention, and such accounts as monitoring, backup, management may require that privilege.

Account matching

Matching is achievable, but not completely trivial. If you’re not aware of this, you should note that ‘temp’@’10.0.0.3’ can match any of the following:

+------+----------+
| temp | 10.0.%   |
| %    | 10.0.0.3 |
| temp | 10.0.0.3 |
| temp | 10.0.0.% |
+------+----------+

And the rule is we must match by most specific host first, then by most specific user. The order of matching should be this:

+------+----------+
| temp | 10.0.0.3 |
| %    | 10.0.0.3 |
| temp | 10.0.0.% |
| temp | 10.0.%   |
+------+----------+

The first row to match our connection’s user/host is the matched account.

The good news

This means the problem is reduced to an ORDER BY and to regular expressions. Easy enough to do with SQL. We prefer hosts with no wildcard to those with; we prefer more subdomains, we prefer no wildcard for users.

The code

The following query assumes you have two session variables: @connection_user and @connection_host.

SELECT
  user, host
FROM
  mysql.user
WHERE
  @connection_user RLIKE
    CONCAT('^',
      REPLACE(
        user,
        '%', '.*'),
      '$')
  AND SUBSTRING_INDEX(@connection_host, ':', 1) RLIKE
    CONCAT('^',
      REPLACE(
      REPLACE(
        host,
        '.', '\\.'),
        '%', '.*'),
      '$')
ORDER BY
  CHAR_LENGTH(host) - CHAR_LENGTH(REPLACE(host, '%', '')) ASC,
  CHAR_LENGTH(host) - CHAR_LENGTH(REPLACE(host, '.', '')) DESC,
  host ASC,
  CHAR_LENGTH(user) - CHAR_LENGTH(REPLACE(user, '%', '')) ASC,
  user ASC
LIMIT 1
;

There is still a slight fine-tuning to do for the above, but it should work for the majority of security setups.

The above (in rewritten form) and derivative work will, of course, be part of the next common_schema release, expected early September.