Bug #18340 Granting/revoking access privleges
Submitted: 19 Mar 2006 3:53
Reporter: Michael Stanton Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: General Severity:S4 (Feature request)
Version: OS:
Assigned to: CPU Architecture:Any

[19 Mar 2006 3:53] Michael Stanton
Description:
The heirachy of the privlege system is extremely primitive, and forces one into the tedious task of being quite explicit when granting and denying table access.

For example, suppose you have a database containing say 100 tables, all of which say 30 users have a common access to. This is solved quite simply by adding 1 entry to the mysql "db" table for each user = 30 entries total. Every time you add 1 table to your database, no privlege action is required and everytime you add 1 user, 1 privlege action required - simple.

Now suppose you wish to add 1 new table to the database and allow access to it by only 1 user. Suddenly, you are faced with an enormous task - firstly scrapping the "db" entries for each user and then explicity creating an access record in the "tables_priv" table for each table for each user = 100 * 30 = 3000 entries total, oh plus 1 entry for the new table for the privleged user = 3001 entries, just for adding a special case table!

Further, every time you add another table (assuming all users will have access to it also), you must add 1 entry to the "tables_priv" table for each user = another 30 entries - not so daunting perhaps. But everytime you add another user you must add 1 entry to the "tables_priv" table for each table = 100 entries. All this not-withstanding "columns_priv" issues.

Of course, it could be argued that there are other solutions to this problem, such as placing the "special" table into another database, but this approach is really a bad compromise leading to greater coding effort. It is also seriously compounded in "real world" examples of mixed table privleges and multiple users anyway. Am I missing something, here, perhaps?

How to repeat:
Easy

Suggested fix:
Surely it would be better to allow grants at "db" level which may then be revoked by explicit "denials" at "tables_priv" and/or "columns_priv" level.
[27 Apr 2006 18:17] Alfredo Kojima
I am recategorizing this, as it's not an Administrator specific issue.