Skip to content

# Pop quiz: what is the most basic privilege an account can be assigned with?

MySQL Security June 14, 2011

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 an account can be assigned with is the USAGE privilege, you are right!

And then again, you’re also wrong.

Technically, USAGE is the right answer. Conceptually, there’s something far more fundamental than USAGE. An account with only USAGE 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?

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

Can you REVOKE the USAGE privilege? You cannot. Once an account exists, it is allowed to login and do stuff. Actually:

>     USAGE = LOGIN + USE + ABUSE YOUR SYSTEM

Which is why ABUSAGE is a more fitting name for this privilege. There is no justification to the many things a user can do with USAGE.

Am I exaggerating? What does USAGE allow one to do? Let’s look at some unconventional usage:

>     SELECT benchmark(10000000000000000, sin(sqrt(rand())));
>     +-------------------------------------------------+
>     | benchmark(10000000000000000, sin(sqrt(rand()))) |
>     +-------------------------------------------------+
>     |                                               0 |
>     +-------------------------------------------------+
>     1 row in set (gazillion seconds to complete, one core down)

>     SELECT
>      DISTINCT 0 * COUNT(*) AS result
>     FROM
>      INFORMATION_SCHEMA.COLLATIONS c0,
>      INFORMATION_SCHEMA.COLLATIONS c1,
>      INFORMATION_SCHEMA.COLLATIONS c2,
>      INFORMATION_SCHEMA.COLLATIONS c3,
>      INFORMATION_SCHEMA.COLLATIONS c4,
>      INFORMATION_SCHEMA.COLLATIONS c5,
>      INFORMATION_SCHEMA.COLLATIONS c6,
>      INFORMATION_SCHEMA.COLLATIONS c7,
>      INFORMATION_SCHEMA.COLLATIONS c8,
>      INFORMATION_SCHEMA.COLLATIONS c9
>     GROUP BY
>      c1.COLLATION_NAME, c7.SORTLEN
>     ;
>     +--------+
>     | result |
>     +--------+
>     |      0 |
>     +--------+
>     1 row in set (yet again gazillion seconds to complete, with huge disk temporary table)

>     SELECT COUNT(DISTINCT SLEEP(1000)) FROM INFORMATION_SCHEMA.TABLES;
>     +-----------------------------+
>     | COUNT(DISTINCT SLEEP(1000)) |
>     +-----------------------------+
>     |                           1 |
>     +-----------------------------+
>     1 row in set (want to gamble how much time your DB will spend in complete lockdown?)

And I should also mention open many concurrent connections (thankfully there is syntax to limit this!).

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
8
Leave a Reply

8 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
4 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
Daniël van Eeden

Luckily there is oak-block-account. And a account with USAGE could be limited further by using resource limits.

Author
shlomi

@Daniël,
10 points for oak-block-account. Using resource limits somewhat helps, as I mentioned above, with concurrent connections or number of queries; but this is really the wrong way to go, I believe.

Guest
Sergei Golubchik

last example: did you try it? I couldn’t repeat any “lockdown”, one connection was waiting on a SLEEP while others were happily executing their queries, and selecting from I_S.TABLES.

Author
shlomi

@Sergei, I’ve had many occasions where a query on INFORMATION_SCHEMA.TABLES/COLUMNS on a busy server brought the server to a complete halt, with dozens of queries locked. For completeness, I confess this was not done with a user which only had USAGE privilege; your question is fair, and I will try and take courage to test it. I’m afraid this happens mostly on production servers, which I do not really want to bring down. Whenever I need to make an INFORMATION_SCHEMA query (tables, keys, columns) I prefer to do it on a SLAVE. So, I will try and complete this; but… Read more »

Guest
Stewart Smith

Drizzle makes one of these queries rather hard to execute: drizzle> SELECT -> DISTINCT 0 * COUNT(*) AS result -> FROM -> DATA_DICTIONARY.COLLATIONS c0, -> DATA_DICTIONARY.COLLATIONS c1, -> DATA_DICTIONARY.COLLATIONS c2, -> DATA_DICTIONARY.COLLATIONS c3, -> DATA_DICTIONARY.COLLATIONS c4, -> DATA_DICTIONARY.COLLATIONS c5, -> DATA_DICTIONARY.COLLATIONS c6, -> DATA_DICTIONARY.COLLATIONS c7, -> DATA_DICTIONARY.COLLATIONS c8, -> DATA_DICTIONARY.COLLATIONS c9 -> GROUP BY -> c1.COLLATION_NAME, c7.SORTLEN -> ; ERROR 1104 (42000): The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay drizzle> set SQL_BIG_SELECTS=1; Query OK, 0 rows affected (0.000432 sec) drizzle> SELECT DISTINCT 0 *… Read more »

Author
shlomi

@stewart:
In situation #1, would Drizzle actually start to execute the query in order to find the number of joined rows exceeds the limit? Or is it part of query evaluation that tables are scanned?

In situation #2, I don’t get it. How CAN I perform such query without Drizzle complaining about “Implicit cartesian join attempted”? Seems like the SQL_BIG_SELECTS did not make a change.

Guest
Stewart Smith

So… for 1) I think it’s just part of query evaluation, and about the same time when detection for an attempted cartesian join. My guess is it’s getting row estimate from engine and then multiplying out and going “whooooo – huge!”.

2) you have to use the explicit CROSS JOIN syntax. http://www.linuxjedi.co.uk/?p=89

Author
shlomi

@Stewart,

1) Sounds to me like it breaks in the middle of execution plan, then

2) cool

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.