Separation of powers … not just for governments (are you listening MySQL!?)

May 3, 2007

What do government systems and databases have in common? The concept of the separation of powers. In a Westminster-style democracy, the three arms of power – the executive, the legislature, and the judiciary – each have separate well defined powers that are forbidden to the other arms of government. For example (and something the crowd at Microsoft should remember), the power to determine punishments is exclusively the right of the judiciary … not criminally convicted monopolists who engage in “self help” :-). But I digress.

The same idea holds in database land. Separate powers and privileges out into discrete realms, and grant those well-defined powers to appropriate users. Works well for DB2, Oracle, SQL Server … but crashes into the safety barrier of poor design when we get to MySQL.

MySQL gets it right in the more-recently added powers, such as creating and executing routines, but its flakey bedrock is shown in the way basic object creation privileges are managed. Creating and dropping tables and databases are controlled by the same two privileges. Read that again – the create privilege spans tables and databases, and the drop privilege is equally-poorly scoped. What it means in practice is that developers who should be given a restricted authority to create objects can now create entire databases, and worse, the power to drop a table comes bundled with the “oops, was that the dev or production database I just wiped” ease of use feature. These should be separate powers … so let’s see how many other people have requested this in the past on the MySQL forums/bug site, and if in the unlikely event that count is zero, I’ll submit it and we’ll see what happens. Stay tuned to watch the fun.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: