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)

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)

Comments are closed.