{"id":3677,"date":"2011-06-14T09:30:45","date_gmt":"2011-06-14T07:30:45","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=3677"},"modified":"2011-06-14T09:30:45","modified_gmt":"2011-06-14T07:30:45","slug":"pop-quiz-what-is-the-most-basic-privilege-an-account-can-be-assigned-with","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/pop-quiz-what-is-the-most-basic-privilege-an-account-can-be-assigned-with","title":{"rendered":"Pop quiz: what is the most basic privilege an account can be assigned with?"},"content":{"rendered":"

I asked this during my presentation on the MySQL Conference this year. And I got a unanimous answer from the crowd. Take a moment to think this over, before coming up with the answer. Meanwhile, an intermezzo.<\/p>\n

Tam dam dam…<\/strong><\/em><\/p>\n

Pom pom Pom pom Pom pom…<\/strong><\/em><\/p>\n

If your answer is that the most basic privilege an account can be assigned with is the USAGE privilege, you are right!<\/p>\n

And then again, you’re also wrong<\/em>.<\/p>\n

Technically, USAGE<\/strong> is the right answer. Conceptually, there’s something far more fundamental than USAGE<\/strong>. An account with only USAGE<\/strong> privilege cannot do much, right? Well, I argue on that as well, but bear with me. How did that user get to login in the first place?<\/p>\n

That’s right, the USAGE<\/strong> privilege first and foremost allows one to login<\/em>. I wrote on this<\/a> before, and I cannot stress this enough: there should be a LOGIN<\/strong> privilege for MySQL, one which can be turned off with REVOKE<\/strong>.<\/p>\n

Can you REVOKE<\/strong> the USAGE<\/strong> privilege? You cannot. Once an account exists, it is allowed to login and do stuff. Actually:<\/p>\n

\n
USAGE = LOGIN + USE + ABUSE YOUR SYSTEM\r\n<\/pre>\n<\/blockquote>\n
Which is why ABUSAGE<\/strong> is a more fitting name for this privilege. There is no justification to the many things a user can do with USAGE<\/strong>.<\/p>\n
Am I exaggerating? What does USAGE<\/strong> allow one to do? Let’s look at some unconventional usage:<\/p>\n
\n
SELECT benchmark(10000000000000000, sin(sqrt(rand())));\r\n+-------------------------------------------------+\r\n| benchmark(10000000000000000, sin(sqrt(rand()))) |\r\n+-------------------------------------------------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0           \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\r\n+-------------------------------------------------+\r\n1 row in set (gazillion seconds to complete, one core down<\/strong>)\r\n<\/pre>\n<\/blockquote>\n
\n
SELECT\r\n DISTINCT 0 * COUNT(*) AS result\r\nFROM\r\n INFORMATION_SCHEMA.COLLATIONS c0,\r\n INFORMATION_SCHEMA.COLLATIONS c1,\r\n INFORMATION_SCHEMA.COLLATIONS c2,\r\n INFORMATION_SCHEMA.COLLATIONS c3,\r\n INFORMATION_SCHEMA.COLLATIONS c4,\r\n INFORMATION_SCHEMA.COLLATIONS c5,\r\n INFORMATION_SCHEMA.COLLATIONS c6,\r\n INFORMATION_SCHEMA.COLLATIONS c7,\r\n INFORMATION_SCHEMA.COLLATIONS c8,\r\n INFORMATION_SCHEMA.COLLATIONS c9\r\nGROUP BY\r\n c1.COLLATION_NAME, c7.SORTLEN\r\n;\r\n+--------+\r\n| result |\r\n+--------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\r\n+--------+\r\n1 row in set (yet again gazillion seconds to complete, with huge disk temporary table<\/strong>)\r\n<\/pre>\n<\/blockquote>\n
\n
SELECT COUNT(DISTINCT SLEEP(1000)) FROM INFORMATION_SCHEMA.TABLES;\r\n+-----------------------------+\r\n| COUNT(DISTINCT SLEEP(1000)) |\r\n+-----------------------------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0    1 |\r\n+-----------------------------+\r\n1 row in set (want to gamble how much time your DB will spend in complete lockdown?<\/strong>)\r\n<\/pre>\n<\/blockquote>\n
And I should also mention open many concurrent connections (thankfully there is syntax to limit this!).<\/p>\n","protected":false},"excerpt":{"rendered":"
I asked this during my presentation on the MySQL Conference this year. And I got a unanimous answer from the crowd. Take a moment to think this over, before coming up with the answer. Meanwhile, an intermezzo. Tam dam dam… Pom pom Pom pom Pom pom… If your answer is that the most basic privilege […]<\/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":[16],"class_list":["post-3677","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-Xj","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3677","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=3677"}],"version-history":[{"count":21,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3677\/revisions"}],"predecessor-version":[{"id":3717,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3677\/revisions\/3717"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=3677"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=3677"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=3677"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}