Home ยป Content

Permissions

So, Markus Popp's recent blog entry about trying to give a user permissions to all databases except one got me thinking.

MySQL has grown immensely, and like many products, new features are compatible with old features. . . somewhat.

Review/baseline:

For current versions of MySQL, permissions are granted and revoked by the GRANT and REVOKE commands. In older versions, administrators had to muck with the access control tables by hand, and then FLUSH PRIVILEGES to enable the new access controls.

The blog entry got me thinking. Currently, if you want to remove all rights from a user, including the ability to login, you have to REVOKE privileges and then DELETE from the mysql.user table. And then, of course, FLUSH PRIVILEGES because you manually changed that table.

It would be great if an administrator never HAD to play with the mysql.user table.

Now, that seems like a small request. But there also does not seem to be a SHOW ALL GRANTS command. Currently an administrator has to run SELECT host,user FROM mysql.user to see who might have access, and then run a SHOW GRANTS command to see who has access to what.

An alternative to that is to check out the other tables in the mysql database. However, what I'd like to see is the ability to see privileges easier than checking out tables with over a dozen fields.

This would include extending the SHOW GRANTS syntax to be able to show grants for all users, and showing grants for a user (for instance, SHOW ALL GRANTS FOR USERNAME="sheeri").

And, of course, similar functionality in REVOKE syntax. I'd love to revoke grants from a user at all their entries with a touch of a button.

In the current system, administrators get punished for having tight security and only allowing what's necessary. There are more entries in the mysql.users table, and adding, seeing, and removing privileges requires many steps.

Perhaps what I am describing is overkill for the database? Maybe I am just describing an admin interface? But I really think that having commands to do half the functionality (seeing, granting and revoking per user/hostname combination) and having to play with tables for the other half (revoking login access, finding out the user/hostname combination) is somewhat lacking.

Or is this a function of the fact that SHOW GRANTS is available to many users by default and access to the mysql database is not? I could see security implications if the syntax was extended. . .but that's easy enough to do by extending the access rights and adding a SHOW GRANTS right (if there is not already one)

(by the way, I'm using 4.1 and haven't had a chance to play with 5.0 or 5.1, so if this functionality is in a later version, or coming soon, please let me know)

I also think that the better

I also think that the better option would be if MySQL would also delete the grants if a database is deleted.

However, it shouldn't be hard to create a Stored Procedure or even quite a simple view that checks if there are any grants for databases that don't exist (a little LEFT JOIN query should be able to do this) and it should also be not too tough to create something like a role system based on stored procedures.

I don't know if Giuseppe Maxia already has something like this in his SP library project - SPs to emulate groups could be a good topic for this ;-).

Nah, you can't add a trigger

Nah, you can't add a trigger to whatever system view in the information schema (and, you shouln't). I knew this on beforehand, but I tried anyway (you get a really funny if not clumsy error message: ERROR 1146 (42S02): Table 'information_schema.schemata' doesn't exist)

Anyway, the fact the a drop database seems to retain the grants is a potential risk if you're not aware of it. I'm still trying to think of a reason why that could be useful...no success so far.

I think it would be very nice indeed if MySQL would support some sort of role entity so you can group together a bunch of privileges and grant (or revoke) that as an unit.

I think, the problem will be

I think, the problem will be that it's not possible to store objects inside INFORMATION_SCHEMA. If a trigger should react to actions inside INFORMATION_SCHEMA, it would have to reside there.

The "tables" in INFORMATION_SCHEMA are technically SYSTEM VIEWs - I don't know, is it possible to assign triggers to views? I haven't tested that but I don't think so.

hmm... DROP DATABASE will

hmm... DROP DATABASE will update meta data in INFORMATION_SCHEMA, right? Has anyone tried to write a trigger "after delete" for `schemata` table? :)

  • By Dmitry (not verified) at 01/28/2006 - 01:05
  • reply

Well, the easier ways that

Well, the easier ways that exist in 5.0+ are available indirectly through Stored Procedures that somebody can write manually and by the meta data that's available in INFORMATION_SCHEMA.

There are only little differences in the SQL commands themselves (CREATE USER and DROP USER are new commands as of 4.1, I think, but the functionality of DROP USER has been extended, I think - the GRANT and REVOKE commands still work the way like before, AFAIK), but there are new possibilities through the other feature that are now available ;-).

Dmitry -- you are agreeing

Dmitry -- you are agreeing with the part where you can do 1/2 the work. Now let's say you have a database, and one of your developers, username "tcabral", quits.

What are the commands to find out what permissions he has? You have to look at the mysql.users table. That's where my gripe is.

Markus -- great point about dropping a database. Sometimes folks drop a database and create it again, so I could see cases in which you want to keep permissions even though the database went away.

When you say However, it should of course also be possible to do everything without directly editing the mysql privilege database - but I believe that it is possible to do that.

That's exactly my post. Why is it not possible?

*nod* I thought there might be an easier way in 5.0+. Thank you.

One more important thing is

One more important thing is - if I drop a database, the users who had access to this database still keep their privileges for that database, even though it doesn't exist anymore. If another user creates a database with the same name again, all the users who had access to the database before, will have access to the new database again (something that the creator might not intend to).

Generally, MySQL's privilege system is quite comfortable - I even think that it's an advantage that it's possible (!) to access the privileges in database tables. However, it should of course also be possible to do everything without directly editing the mysql privilege database - but I believe that it is possible to do that.

There is now (I think, since 5.0) a DROP USER command that really completely deletes a user. In former versions (was it 4.1?) there were some limitations to that command, but now with 5.0 I believe that it works quite well.

To view the privileges, there are also the INFORMATION_SCHEMA tables since 5.0. You still don't see all the relevant information for a particular user in one table - however, it's not very difficult to create a stored procedure that transforms the data to get exactly this result ;-).

  • By Markus Popp (not verified) at 01/26/2006 - 19:47
  • reply

um, so what was a problem?

um, so what was a problem? :)

GRANT USAGE ON mysql.* TO php@localhost;
GRANT SELECT ON `%`.* TO php@localhost;
SET PASSWORD FOR php@localhost = [censored];

  • By Dmitry (not verified) at 01/26/2006 - 19:45
  • reply