Saturday, April 15, 2006

Alternative privilege system including ROLES and separate host table?

I recently talked about the idea of a different privilege system that separates the host information from the user name. Of course, it would break backwards compatibility and so it's almost impossible to change the current behaviour entirely. Also, Beat Vontobel has suggested that there are situations where it is desireable to have user and host information combined.

However, there might be a (not too complicated) solution. I'm thinking of a separate database that manages the user privileges according to the alternative system. Stored Procedures and Triggers could sync the information in the new database to the mysql schema database - and maybe also the other way around. This would require no changes in the MySQL server at all and every user could decide for himself, whether or not to use the alternative system. And finally, the users managed by the new system should not interfere with users that are managed by the mysql schema alone (in the traditional way) - so it could/should be possible to combine both systems.

As I thought about it, I came to the conclusion that it shouldn't be too tough to implement such an alternative system by a separate table using Stored Procedures and Triggers. At least, it looks like an interesting project.

Your thoughts about it are - as always - welcome!


Robert Treat said...

If you want to make it really interesting, mae it postgresql compataible. We're currently discussing adding a second authentication mechanism to postgresql that can be controled from withing the database, it would be interesting if both db's used the same system.

Markus Popp said...

That sounds indeed interesting. Is there a place where I can learn more about what's planned in PostgreSQL?