Tag: Security

Announcing common_schema: common views & routines for MySQL

MySQL

Today I have released common_schema, a utility schema for MySQL which includes many views and functions, and is aimed to be installed on any MySQL server.

What does it do?

There are views answering for all sorts of useful information: stuff related to schema analysis, data dimensions, monitoring, processes & transactions, security, internals… There are basic functions answering for common needs.

Some of the views/routines simply formalize those queries we tend to write over and over again. Others take the place of external tools, answering complex questions via SQL and metadata. Still others help out with SQL generation.

Here are a few highlights:

There’s more. Take a look at the common_schema documentation for full listing. And it’s evolving: I’ve got quite a few ideas already for future components.

Some of these views rely on heavyweight INFORMATION_SCHEMA tables. You should be aware of the impact and risks.

What do I need to install?

There’s no script or executable file. It’s just a schema. The distribution in an SQL file which generates common_schema. Much like a dump file.

Continue reading » “Announcing common_schema: common views & routines for MySQL”

ROUTINE_PRIVILEGES implementation

MySQL

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

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”

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

Recovering a MySQL `root` password: the fourth solution

MySQL

Have just read Darren Cassar’s Recovering a MySQL `root` password – Three solutions. There’s a fourth solution: using an init-file, which leads to just one restart of the database instead of two. It also avoids the security issue involved with using skip-grant-tables.

I’ve written all about it before on Dangers of skip-grant-tables.

Darren’s 1st advice (look for password ini files, scripts, etc.) is a very good one. One password that can always be looked up in files is the replication’s password.

Replication’s password is easily forgotten: you only set it once and never use it again; never script it nor manually login with. When setting up new slaves, though, you suddenly need it.

Apparently not many realize that the replication password is written in plaintext in the master.info file. This file tells the slave all about it’s master connection: host, port, user & password are all there for you to read.

Limiting table disk quota in MySQL

MySQL

Question asked by a student: is there a way to limit a table’s quote on disk? Say, limit a table to 2GB, after which it will refuse to grow? Note that the requirement is that rows are never DELETEd. The table must simply refuse to be updated once it reaches a certain size.

There is no built-in way to limit a table’s quota on disk. First thing to observe is that MySQL has nothing to do with this. It is entirely up to the storage engine to provide with such functionality. The storage engine is the one to handle data storage: how table and keys are stored on disk. Just consider the difference between MyISAM’s .MYD & .MYI to InnoDB’s shared tablespace ibdata1 to InnoDB’s file-per table .ibd files.

The only engine I know of that has a quota is the MEMORY engine: it accepts the max_heap_table_size, which limits the size of a single table in memory. Hrmmm… In memory…

Why limit?

I’m not as yet aware of the specific requirements of said company, but this is not the first time I heard this question.

The fact is: when MySQL runs out of disk space, it goes with a BOOM. It crashed ungracefully, with binary logs being out of sync, replication being out of sync. To date, and I’ve seen some cases, InnoDB merely crashes and manages to recover once disk space is salvaged, but I am not certain this is guaranteed to be the case. Anyone?

And, with MyISAM…, who knows?

Rule #1 of MySQL disk usage: don’t run out of disk space.

Workarounds

I can think of two workarounds, none of which is pretty. The first involves triggers (actually, a few variations for this one), the second involves privileges. Continue reading » “Limiting table disk quota in MySQL”

Upgrading passwords from old_passwords to “new passwords”

MySQL

You have old_passwords=1 in your my.cnf. I’m guessing this is because you used one of the my-small.cnf, my-large.cnf etc. templates provided with your MySQL distribution.

These files can easily win the “most outdated sample configuration file contest”.

Usually it’s no big deal: if some parameter isn’t right, you just go and change it. Some variables, though, have a long-lasting effect, and are not easily reversed.

What’s the deal with old_passwords?

No one should be using these anymore. This variable makes the password hashing algorithm compatible with that of MySQL 4.0. I’m pretty sure 4.0 was released 9 years ago. I don’t know of anyone still using it (or 4.0 client libraries).

The deal is this: with old_passwords you get a 16 hexadecimal digits (64 bit) hashing of your passwords. With so called “new passwords” you get 40 hexadecimal digits (plus extra “*“). So this is about better encryption of your password. Read more on the manual.

How do I upgrade to new password format?

You can’t just put a comment on the “old_passwords=1” entry in the configuration file. If you do so, the next client to connect will attempt to match a 41 characters hashed password to your existing 16 characters entry in the mysql.users table. So you need to make a simultaneous change: both remove the old_passwords entry and set a new password. You must know all accounts’ passwords before you begin.

Continue reading » “Upgrading passwords from old_passwords to “new passwords””

Tool of the day: autossh

MySQL

Maybe I’m like an old replication server, lagging way behind, but a couple of weeks ago I found autossh, which is a wrapper around ssh, that keeps reconnecting the session if it breaks.

With public key encryption, I am now able to work out pretty reliable SSH tunneling among servers, which doesn’t break. It seems to be working well during these couple of weeks. And it’s in my favorite distro’s repository 🙂

I suppose use cases are as many as those for SSH or SSH tunneling, and I’m putting it to an interesting use. But I suppose the most obvious use in the MySQL world would be to encrypt client connections over unsafe network, or make the network more reliable, for that matter. Yes, there’s SSL connections, but opening your 3306 port on your firewall? Too risky for my taste.

mylvmbackup HOWTO: minimal privileges & filesystem copy

MySQL

This HOWTO discusses two (unrelated) issues with mylvmbackup:

  • The minimal privileges required to take MySQL backups with mylvmbackup.
  • Making (non compressed) file system copy of one’s data files.

Minimal privileges

Some just give mylvmbackup the root account, which is far too permissive. We now consider what the minimal requirements of mylvmbackup are.

The queries mylvmbackup issues are:

  • FLUSH TABLES
  • FLUSH TABLES WITH READ LOCK
  • SHOW MASTER STATUS
  • SHOW SLAVE STATUS
  • UNLOCK TABLES

Both SHOW MASTER STATUS & SHOW SLAVE STATUS require either the SUPER or REPLICATION CLIENT privilege. Since SUPER is more powerful, we choose REPLICATION CLIENT.

The FLUSH TABLES * and UNLOCK TABLES require the RELOAD privilege.

However, we are not done yet. mylvmbackup connects to the mysql database, which means we must also have some privilege there, too. We choose the SELECT privilege.

Continue reading » “mylvmbackup HOWTO: minimal privileges & filesystem copy”

Passwords which are bad for your health

MySQL

I’ve seen some passwords to take a few years from my life.

I mean, we all know about dictionary words, right? And we’ve all seen Spaceballs, right? But choosing 12345 as your password is not the only careless option: there are many more! The more I get familiar with user’s password, the more I see how so much alike they all are.

Let’s review some of the commonly used bad password practices:

  • Empty passwords. Need I say more? Apparently yes. So what if “there’s only access through firewall from our company’s IP”?
  • Dictionary passwords: real English words like ‘falcon‘ or ‘tiger‘. Don’t use these! These are the easiest to attack.
  • Well known words: how about ‘Gandalf‘? It’s not dictionary, but it’s popular enough to appear in any respectable list. For that matter, look at how well filtered passwords are on RedHat: you can’t choose a password which is a common first or last name in the US, Italy, or even Israel; which is great!
  • Common substitues: enough with ‘1nsi9ht‘ and ‘@dm1n‘! These are almost as easy to break as dictionary words; it’s just a matter of a few more combinations per word.
  • Keyboard clustered: say No! to ‘1qa2ws‘. Don’t use ‘$rty&*io‘. They seems to be random at first sight, but look for them on the keyboard: it’s just your common “how shall I create a password that’s so easy to remember I will never forget it?”. Now REPLACE(“remember”, “break”) and REPLACE(“never forget”, “always regret”).
  • Children’s names, birth dates, 123456, your car’s license plate number, your Yahoo! mail password, etc. etc. etc.

There are many guidelines for choosing strong passwords. And everyone seems to know about it. But I’m still surprised when I find out the MySQL root password is ‘zxcvbn‘ or ‘pa55wd‘.

MySQL allows for any character in your password, so you may use punctuations, spaces, and other symbols. This is stronger than plain characters and digits.

Continue reading » “Passwords which are bad for your health”