MySQL UC: Day 3

It's day 3 at the MySQL Users Conference.

Hotel Hopping

I checked out of the Four-Points Sheridan in Sunnyvale and into the Santa Clara Westin (the conference hotel). I had planned to stay at the Westin the entire trip, but they were booked up the first few days of the conference. I definitely wanted to be there the night before my own presentation. Travelling back and forth from the Sheridan cost me at least an hour every day.

Enterprise MySQL: Views in MySQL 5.0

This was similar to yesterday's Flagship Features in MySQL 5.0, but focused exclusively on views.

In general (i.e. not just MySQL, but relational databases), not all views are updatable; some cannot be updated. Whether or not a view is updatable seems to depend mostly upon any JOIN condition that may be present in the underlying query. Aggregate functions and derived columns will also make a view (or at least the relevant columns) non-updatable. MySQL-5.0 does not support all theoretically-possible updatable views, but it supports a lot. The theoretically-possible ones it does not are mainly due to implementation or performance issues. Some of the exceptions are listed in the CREATE VIEW manual section.

When they say a view is updatable, they mean you can use UPDATE, INSERT, and DELETE.

Some new VIEW-related privileges were added. In particular, you can restrict examining the view's schema, while still allowing access. For access, you need SELECT, UPDATE, etc. on the view itself and not the underlying tables.

You can enforce constraints on views locally, or have them cascade if you have a view that is based on other views.

MySQL Security

This was an overview of best practices, and what you should do to protect your server. The Windows-only worm that was active for one day a few months back was taking advantage of three security issues. The first was that the Windows installation (unlike the default UNIX installation) was adding access for root@%. The second: A lot of admins were choosing crappy passwords. The initial worm had a small dictionary of passwords to try, like "abc123", and would simply try everything. One user there said the list got longer as time went on, which seems entirely possible, since it would connect to IRC servers and websites. The final problem: A lot of admins were using a system account to run the server, which effectively gave it root.

The worm would log in as root, and create a table with a BLOB column. Then it would INSERT a record containing the worm payload (a DLL), and then use SELECT INTO DUMPFILE to write it to the disk. Finally it would use CREATE FUNCTION to load the DLL. To midigate the problem, mysqld now will not load a shared library for a user-defined function (UDF) unless it implements the required API calls. This is not much protection, since the attacker can add these to the payload, but it would thwart a naïve worm implementation. They also removed the root@% account, or at least no longer install it, and seem to more strongly encourage running mysqld with an unprivileged account.

At least one person voiced support for adding group/role support to the privilege system, so that you could configure privileges for a few roles and then assign multiple users to that role, or to multiple roles. The presenter was fully in agreement, saying that this was something he also wanted badly. Afterward, we had a brief discussion of how something like this might be implemented now, before there is official support. I had two separate ideas. One was to add a column to the user privilege table that would indicate whether or not this was a real user or a role, the difference being that a role account could not log in, and was simply a prototype for other users with that role. Then have a separate table that would associate users with multiple roles. The actual user privileges would be manipulated outside of mysqld by some script. The other idea was use a view for the user table which would somehow consolidate all the privileges for the roles that user was a member of.

Lunch

Italian day. Pizza, pasta, etc.

Tour of the MySQL Source Code

This was a broad overview of how the MySQL source code is organized, with some information on adding UDFs and storage engines.

There already exists some UDFs to run perl and PHP, and Brian Aker told me there was one for Python, since he was the one who originally wrote it. However, my Google searches were turning up nothing relevant, so I spent the rest of the afternoon writing one. I've gotten it to work, except that there is a dynamic linking problem which causes imports of extension modules to fail: It can't find symbols that are definitely in there from libpython. I can probably fix this with the linker flags, but I ran out of time to work on it.

One of the quirks/features of the UDF API is, if you want to have a function foo, you need to define foo_init(), foo(), and maybe foo_deinit(). foo_init() is supposed to do all the initialization and argument checking. If there are any problems with the arguments, you have to report it here; foo() cannot return an error code. Checking the arguments is not really practical with Python; it can be done with introspection but would be expensive. If an exception gets raised, the only real option is to return NULL. It does send tracebacks to the log, though. foo_deinit() is supposed to clean up, but is only called if foo_init() was sucessful. There is a separate set of API functions to define for aggregate functions.