{"id":443,"date":"2009-03-05T11:44:40","date_gmt":"2009-03-05T09:44:40","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=443"},"modified":"2011-02-08T10:51:42","modified_gmt":"2011-02-08T08:51:42","slug":"blocking-user-accounts","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/blocking-user-accounts","title":{"rendered":"Blocking user accounts"},"content":{"rendered":"<p>A long time missing feature in MySQL is temporarily blocking accounts: denying a user to log in, without affecting any other of her privileges.<\/p>\n<p>There is no such privilege as &#8216;LOGIN&#8217; in the grants table, as the ability to log in is the most basic one MySQL allows. This basic privilege is called USAGE.<\/p>\n<p>I&#8217;ll present a hack around this, one which <a title=\"openark kit: oak-block-account\" href=\"http:\/\/openarkkit.googlecode.com\/svn\/trunk\/openarkkit\/doc\/html\/oak-block-account.html\">oak-block-account<\/a> implements. Before presenting the hack, lets lay down some requirements:<\/p>\n<ul>\n<li>A user can be blocked from logging in to MySQL.<\/li>\n<li>Such a blocked user can later be &#8216;released&#8217;, re-enabling him to log in.<\/li>\n<li>It should be possible to determine if a certain user is currently blocked or not.<\/li>\n<\/ul>\n<p><!--more-->A first attempt to answer the above requirements is to change the account&#8217;s password. As a naive example, we can set an account&#8217;s password to &#8216;aaaaaaaaa&#8217;. But let&#8217;s consider the following:<\/p>\n<ul>\n<li>Will the user be unable to find, by some algorithm or by brute force, the new password?<\/li>\n<li>How can we revert the new password to the original one?<\/li>\n<\/ul>\n<p>Time to look at how MySQL stores passwords, then.<\/p>\n<p>We begin by distinguishing old_passwords (variable <strong>old_passwords<\/strong>=1) from new passwords.<\/p>\n<ul>\n<li>Old passwords are 16 characters long. These are hexadecimal characters.<\/li>\n<li>New passwords are 41 characters long: a leading &#8216;*&#8217; followed by 40 hexadecimal characters.<\/li>\n<\/ul>\n<h4>Blocking with &#8216;new passwords&#8217;<\/h4>\n<p>To disable an account using a &#8216;new password&#8217;, the trick is simply to reverse the password in the <strong>mysql.user<\/strong> table. So if my password was &#8216;123456&#8217; (strong one, isn&#8217;t it?), then <strong>mysql.user<\/strong> will have:<\/p>\n<blockquote>\n<pre>mysql&gt; select PASSWORD('123456');\r\n+-------------------------------------------+\r\n| PASSWORD('123456')                        |\r\n+-------------------------------------------+\r\n| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |\r\n+-------------------------------------------+<\/pre>\n<\/blockquote>\n<p>To encode, we do:<\/p>\n<blockquote>\n<pre>SET PASSWORD FOR 'some_user'@'some_host' = '9DA2AC2DE76CD7ADD8654EE50192347BE7384BB6*'<\/pre>\n<\/blockquote>\n<p>Let&#8217;s consider the implication of what we just did:<\/p>\n<ul>\n<li>The new password is valid, as far as MySQL is concerned. No questions asked.<\/li>\n<li>The user cannot log in with his old password.<\/li>\n<li>Nor can the user log in with <em>any other<\/em> password, since the <strong>PASSWORD()<\/strong> function will never return a password ending with &#8216;*&#8217;.<\/li>\n<li>It is easy to see that the user is &#8216;blocked&#8217;: his password ends with &#8216;*&#8217;.<\/li>\n<li>It is easy to restore the original password: we simply reverse the text and call <strong>SET PASSWORD<\/strong> again.<\/li>\n<\/ul>\n<h4>Blocking with &#8216;old passwords&#8217;<\/h4>\n<p>This part really assumes you&#8217;re using MySQL 4.1 or above. If you&#8217;re one of those &#8216;few&#8217; lucky people, but are unfortunately using old_passwords, here&#8217;s the deal:<\/p>\n<p>Reversing an old password won&#8217;t do, since:<\/p>\n<ul>\n<li>The reverse may still consist of an encoding for some password<\/li>\n<li>It&#8217;s impossible to tell if a user is blocked or not.<\/li>\n<\/ul>\n<p>MySQL will only allow 16 or 41 character long passwords (anyway that&#8217;s my finding). So to encode a 16 characters long password, we pad it with 25 (= 41-16) &#8216;~&#8217; characters. Thus, the encoded password &#8216;abcdef0123456789&#8217; turns to &#8216;~~~~~~~~~~~~~~~~~~~~~~~~~abcdef0123456789&#8217;. Again, note the following:<\/p>\n<ul>\n<li>The new password is accepted by MySQL as valid.<\/li>\n<li>The user cannot log in with his old password.<\/li>\n<li>Nor can the user log in with <em>any other<\/em> password, since the <strong>PASSWORD()<\/strong> function will never return a password starting with &#8216;~&#8217;.<\/li>\n<li>It is easy to see that the user is &#8216;blocked&#8217;: his password starts with 25 &#8216;~&#8217; characters.<\/li>\n<li>It is easy to restore the original password: we simply remove the leading &#8216;~&#8217; characters and call <strong>SET PASSWORD<\/strong> again.<\/li>\n<\/ul>\n<h4>oak-block-account<\/h4>\n<p>No need to work all this by hand. <a title=\"openark kit: oak-block-account\" href=\"http:\/\/openarkkit.googlecode.com\/svn\/trunk\/openarkkit\/doc\/html\/oak-block-account.html\">oak-block-account<\/a> is a utility which does exactly that. It can block, release, and even kill accounts. It will automatically detect if the password is &#8216;old&#8217; or &#8216;new&#8217;, or if the account is already blocked or not.<\/p>\n<h4>Other possibilities<\/h4>\n<p>RENAME USER is another trick which could be used: we could take a user&#8217;s login (e.g. &#8216;webuser&#8217;) and rename it to an unknown value, like &#8216;webuser__1q98d4f&#8217;. While it serves the same purpose &#8211; of blocking the user, it has a disadvantage: if by luck or hack the new login is discovered, it could still be used to access the database. The change of password solution ensures there is no user\/password combination which will work on the blocked account.<\/p>\n<p>Other options may involve removing the account from <strong>mysql.user<\/strong>, to put it elsewhere, from where to restore the row when the time comes. I prefer a solution which works on the <strong>mysql<\/strong> schema itself.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>A long time missing feature in MySQL is temporarily blocking accounts: denying a user to log in, without affecting any other of her privileges. There is no such privilege as &#8216;LOGIN&#8217; in the grants table, as the ability to log in is the most basic one MySQL allows. This basic privilege is called USAGE. I&#8217;ll [&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":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[34,16],"class_list":["post-443","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-openark-kit","tag-security"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-79","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/443","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=443"}],"version-history":[{"count":16,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/443\/revisions"}],"predecessor-version":[{"id":633,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/443\/revisions\/633"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=443"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=443"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=443"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}