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.