{"id":6492,"date":"2013-08-29T07:28:48","date_gmt":"2013-08-29T05:28:48","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=6492"},"modified":"2013-08-29T12:35:03","modified_gmt":"2013-08-29T10:35:03","slug":"mysql-security-top-wish-list","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/mysql-security-top-wish-list","title":{"rendered":"MySQL security top wish list"},"content":{"rendered":"

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.<\/p>\n

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

Autherntication-only SSL<\/h4>\n

By default, MySQL client API is unencrypted and passwords are sent in cleartext<\/del>. 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.<\/p>\n

[UPDATE]<\/strong>: Thanks to Davi & Jan for correcting me on this: passwords are not<\/em> 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<\/em>, got “mysql_native_password”<\/strong> message and no cleartext password. Lesson learned!<\/p>\n

Roles<\/h4>\n

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<\/a> to date)<\/p>\n

Host aggregation<\/h4>\n

In MySQL the combination of user+host makes for a distinct account. Thus, ‘gromit’@’192.168.%’<\/strong> is a completely different account than ‘gromit’@’10.10.%’<\/strong>. I get the idea: you can have more privileges to, say, gromit@localhost<\/strong> than for gromit@’192.%’<\/strong>. In practice, this only makes a headache. In all my years, I have never<\/em> 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!<\/p>\n

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.<\/p>\n

Host blacklist<\/h4>\n

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.<\/p>\n

Catalogues<\/h4>\n

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<\/strong> 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<\/strong>, and this would apply to all databases in that catalogue.<\/p>\n

Privileges auditing<\/h4>\n

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”<\/strong>, and have a ++counter<\/strong> for every time a privilege check is made per account.<\/p>\n

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.<\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[16],"class_list":["post-6492","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-security"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1GI","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/6492","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=6492"}],"version-history":[{"count":6,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/6492\/revisions"}],"predecessor-version":[{"id":6498,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/6492\/revisions\/6498"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=6492"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=6492"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=6492"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}