{"id":6482,"date":"2014-07-28T11:13:36","date_gmt":"2014-07-28T09:13:36","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=6482"},"modified":"2014-07-28T11:13:36","modified_gmt":"2014-07-28T09:13:36","slug":"some-mysql-security-tips","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/some-mysql-security-tips","title":{"rendered":"Some MySQL security tips"},"content":{"rendered":"<p>This is a brief list of security tips for MySQL. It is by no means complete.<\/p>\n<ul>\n<li>Follow the <strong>sudo<\/strong> example. Don&#8217;t let all you DBAs and Ops have the password for the <strong>root<\/strong> 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&#8217;s account.<\/li>\n<li>Block <strong>root<\/strong>. Either remove it completely or forbid it from logging in. Yes, there&#8217;s a <del>way<\/del> hack in MySQL to have a valid account blocked from logging in. One way of making this happen is via <em>common_schema<\/em>&#8216;s <a href=\"https:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/sql_accounts.html\">sql_accounts<\/a>. Here&#8217;s how to block root account using common_schema:<\/li>\n<\/ul>\n<blockquote>\n<pre>mysql&gt; CALL common_schema.eval(\"SELECT sql_block_account FROM sql_accounts WHERE USER = 'root'\");<\/pre>\n<\/blockquote>\n<ul>\n<li>Make lots of small users. Give <em>nagios<\/em> its own user. Give <em>collectd<\/em> its own user. Give <em>orchestrator<\/em> its own user. Give <em>innotop<\/em> its own user. Give <em>whatever<\/em> its own user. Yes, it&#8217;s more users to create, but you get to have each user as limited in privileges as possible, and you don&#8217;t get to wonder why your heartbeat script has <strong>SUPER<\/strong>, <strong>LOCK<\/strong> and <strong>SHUTDOWN<\/strong> privileges.<\/li>\n<li>Verify: <strong>set @@old_passwords=0;<\/strong> before setting a new password. Make sure your configuration does not specify <strong>old_passwords = 1<\/strong>. There is no reason to use &#8220;<a href=\"http:\/\/code.openark.org\/blog\/mysql\/upgrading-passwords-from-old_passwords-to-new-passwords\">old passwords<\/a>&#8220;. In fact, a <strong>5.6<\/strong> client will refuse connecting with an &#8220;old password&#8221;.<\/li>\n<li>Give no access to <strong>mysql.*<\/strong>. No one should be tampering directly with the <strong>mysql<\/strong> system tables.<\/li>\n<li>Run <a href=\"https:\/\/openarkkit.googlecode.com\/svn\/trunk\/openarkkit\/doc\/html\/oak-security-audit.html\">oak-security-audit<\/a> or, if you have <em>common_schema<\/em> installed (you mean you don&#8217;t?), just <strong><a href=\"https:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/security_audit.html\">CALL security_audit()<\/a>;<\/strong> I can (almost) guarantee you&#8217;d be surprised and thankful for the security breakdown. Users without passwords, users sharing same passwords, users with unreasonable privileges, and more&#8230; You&#8217;ll see them all.<\/li>\n<li>If you have web interfaces to your database or some of its aspects (e.g. Anemometer, Propagator, Orchestrator, monitoring, &#8230;), protect it via LDAP group or similar. Not everyone who has access to your network needs to see you database. Neither does every employee.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>This is a brief list of security tips for MySQL. It is by no means complete. Follow the sudo example. Don&#8217;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 [&hellip;]<\/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-6482","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-1Gy","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/6482","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=6482"}],"version-history":[{"count":13,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/6482\/revisions"}],"predecessor-version":[{"id":6944,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/6482\/revisions\/6944"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=6482"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=6482"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=6482"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}