MySQL security: inconsistencies

MySQL

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

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

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?”

TIMESTAMP vs. DATETIME, which should I be using?

MySQL

They both look the same, have roughly the same use. Nevertheless, they differ in many respects. I’ll note the difference, and note a few pitfalls and peculiarities.

Range

TIMESTAMP starts with the epoch, ‘1970-01-01 00:00:01’ UTC and ends with ‘2038-01-19 03:14:07’ UTC. This is all very nice today, and may actually hold up till our retirement, but the younger readers may yet have to deal with the Bug2K+38™, certain to arrive.

DATETIME starts with ‘1000-01-01 00:00:00’ and lasts up to ‘9999-12-31 23:59:59’. More on that later.

In respect of range, your current event logs may well use a TIMESTAMP value, while your grandfather’s and granddaughter’s birth dates may require DATETIME.

In general I would suggest that anything that relates to now, can be a TIMESTAMP. A new entry is added? It is added now. It can be represented by a TIMESTAMP. Anything has an expiry time of a few minutes, perhaps a few days or a month? You’ll be safe using it up till late 2037. Anything else had better use a DATETIME. In particular, dates relating to issues such as birth, insurance, the market etc. fall into this category.

History, however, does not even hold up with DATETIME. Rome fell long before MIN(DATETIME). You will have to manage your own. Not even YEAR will help you out.

Storage

TIMESTAMP makes for 4 bytes. DATETIME makes for 8 bytes. Now that we have this behind us, let’s see why. Continue reading » “TIMESTAMP vs. DATETIME, which should I be using?”

Problems with MMM for MySQL

MySQL

I recently encountered troubling issues with MMM for MySQL deployments, leading me to the decision to cease using it on production.

At the very same day I started writing about it, Baron published What’s wrong with MMM?. I wish to present the problems I encountered and the reasons I find MMM is flawed. In a period of two weeks, two different deployments presented me with 4 crashes, in 3 different scenarios.

In all the following scenarios, there is an Active/Passive Master-Master deployment, with one VIP (virtual IP) set for writer role, one VIP set for reader role.

Problem #1: unjustified failover, broken replication

Unjustified failover must be the common scenario. It’s also a scenario I can live with. A few seconds of downtime are OK with me once in a couple of months.

But on two different installations, a few days apart, I had two seemingly unjustified failovers followed by a troubling issue: replication got broken. Continue reading » “Problems with MMM for MySQL”

Pop quiz answered: “what would be the results of the following queries?”

MySQL

The quiz presented poses with an uncommon, though valid SQL syntax: one is allowed to use quoted name aliases. Thus, it is valid to write:

SELECT Name AS 'n', Continent AS 'c' FROM countries

But what does the above mean? Let’s see the results of our three questions: Continue reading » “Pop quiz answered: “what would be the results of the following queries?””

Impressions from MySQL conf 2011, part IV

MySQL

This post concludes my impressions from some of the talks I’ve been to.

Thursday

I opened this day’s sessions with a smile.

Antony Curtis and Arjen Lentz have authored the OQGraph storage engine a while back. I had every intention to try it out, but never got round to it. Which is why I was happy to find this session in the conference. OQGraph is a specialized engine, and comes to solve hierarchal or otherwise graph-related queries, such as: “who are the descendants of a given node”, “find a route from a to b”, etc. MySQL does not support the RECURSIVE syntax as Oracle does, and does not provide out-of-the-box solution for that.

The syntax is just excellent: you just write something like SELECT * FROM my_graph WHERE from_node = ’empusa fasciata’ AND to_node = ‘homo sapiens’ to find a route. Otherwise just use WHERE from_node = ‘Heathrow Central’ to find all outgoing links. So this is just plain old SQL, no new syntax involved.

I rounded corners. It is also possible (and required) to specify an algorithm. Do you want Djekstra? BFS? You specify it in the query. The result of a route query is a rowset, where each row is a step in the route, along with its sequence within the route. So you can do your ORDER BY, LIMIT etc. I find that syntax-wise, OQGraph is very intuitive! Continue reading » “Impressions from MySQL conf 2011, part IV”

Impressions from MySQL conf 2011, part III: BoF Replication

MySQL

This post continues my impressions from some of the talks I’ve been to. I’ll dedicate this post to a single session.

Wednesday

  • BoF: Replication, Lars Thalmann, Mat Keep (Oracle)

Lars Thallman presented the MySQL 5.5 replication features, as well as the expected 5.6 features. Among other features, one could notice parallel replication, binlog checksums, sub-second resolution and more. There was an open discussion about these features, asking for comments; looking for new ideas and suggestion from the audience.

I can’t possibly cover it all. I’ll note two discussion I participated in, and which have interested me. This also serves for noting down to myself my ideas and thoughts. Continue reading » “Impressions from MySQL conf 2011, part III: BoF Replication”