Skip to content

# Month: June 2011

## ROUTINE_PRIVILEGES implementation

MySQL INFORMATION_SCHEMA Open Source Security Stored routines Views June 22, 2011June 27, 2011

Following up on MySQL security: inconsistencies, and on MySQL bug #61596, I was thinking it may take a long time till the non-existent ROUTINE_PRIVILEGES view is implemented. Here’s my own implementation of the view.

I’ve followed the somewhat strange conventions used in the *_PRIVILEGES tables in INFORMATION_SCHEMA, where the IS_GRANTABLE is a separate column, although in 2nd 1st normal form.

I present it here as a query, using session variables, rather than a view definition: Continue reading » “ROUTINE_PRIVILEGES implementation”

## MySQL security: inconsistencies

MySQL INFORMATION_SCHEMA Security June 22, 2011

Doing some work with MySQL security, I’ve noticed a few inconsistencies. They’re mostly not-too-terrible for daily work, except they get in my way right now.

#### The ALL PRIVILEGES inconsistency

The preferred way of assigning account privileges in MySQL is by way of using GRANT.

With GRANT, one assigns one or more privileges to an account, such as SELECT, UPDATE, ALTER, SUPER ,etc. Sometimes it makes sense for an account to have complete control over a domain. For example, the root account is typically assigned with all privileges. Or, some user may require all possible privileges on a certain schema.

Instead of listing the entire set of privileges, the ALL PRIVILEGES meta-privilege can be used. There is a fine issue to notice here; typically this is not a problem, but I see it as a flaw. Assume the following account:

>     root@mysql-5.1.51> GRANT ALL PRIVILEGES ON world.* TO 'world_user'@'localhost';

>     root@mysql-5.1.51> SHOW GRANTS FOR 'world_user'@'localhost';
>     +---------------------------------------------------------------+
>     | Grants for world_user@localhost                               |
>     +---------------------------------------------------------------+
>     | GRANT USAGE ON *.* TO 'world_user'@'localhost'                |
>     | GRANT ALL PRIVILEGES ON `world`.* TO 'world_user'@'localhost' |
>     +---------------------------------------------------------------

This makes sense. We granted ALL PRIVILEGES and we see that the account is granted with ALL PRIVILEGES.

Now notice the following: Continue reading » “MySQL security: inconsistencies”

## MySQL User Group Meeting, June 2011, Israel

MySQL User Group June 18, 2011June 19, 2011

It’s been quite a while since a MySQL User Group Meeting was held in Israel. I’m happy to announce that the next meeting will take place on June 28th, starting 16:30. Talks will be in Hebrew. Full details here.

This meeting is organized by Valinor, who will also be hosting it, and who are planning to make this a tradition. Thumbs up!

- Rotem Fogel will be presenting on Reaching best Scalability on MySQL Based Applications
- Gabi Davis will be presenting on Working with MySQL in Real Life: Do’s and Don’ts
- I will be presenting on MySQL Replication Solutions and Enhancements.

The meeting is open to everyone, do come!

## 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.

Continue reading » “Pop quiz: what is the most basic privilege an account can be assigned with?”