List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:January 30 2002 9:53pm
Subject:Re: New MySQL privileges
View as plain text  
Hi!

>>>>> "Alexander" == Alexander Keremidarski <salle@stripped>
> writes:

Alexander> Hi,
Alexander> We discussed it before.


Alexander> Michael Widenius wrote:

>> Hi!
>> 
>> One 'should' always have the full rights to any temporary tables on
>> create
>> 
>> The problem is however that we need to change how the current
>> privileges are checked to allow this.  Current we only check the
>> privileges when one does a DROP TABLE;  In the future we would need to
>> check first if there is a matching temporary table and then, if there
>> wasn't, check the normal privileges.
>> 
>> This could be tricky when one tries to do things like
>> 
>> CREATE TEMPORARY TABLE mysql.user (a int);
>> DROP TABLE mysql.user,mysql.user;

Alexander> Great. Now you can see bad side of current privileges system.
Alexander> I don't want to blame it. From many points of view it is very good.

This has actually nothing to do with the current privileges; You would
get the same problem with any table that is protected from the user.

The problem is that we can't in this case test for the privileges
'before' we do the operation, as the first drop would make the second,
protected, table visible.  We have move some of the access checking
logic around and this is why I want to wait a bit with this.

Alexander> Privilege tables (mysql.*) are something special for MySQL but created 
Alexander> as usual database tables.

Actually there is nothing much special with them when it comes to
security.  You grant protection to them as you would with any other
tables.

Alexander> Dealing with them is strictly administrative task. Regular users and 
Alexander> client side programs has nothing to do with them.
Alexander> Even DBA must be able to deal with privileges only with using 
Alexander> GRANT/REVOKE and SHOW GRANTS, so basically all other SQL statements are 
Alexander> not needed at all.

To be able to work flexible, you still need SQL access to privilege
tables (even if they can be just 'syntactic' tables).

Alexander> To say it different mysql.* is Admin space not User space

In practice there isn't really a big difference.

Alexander> So you are free to change it dramatically and no user can complain about
> it.

Users will complain, as many applications are manipulating the tables
directly.

Alexander> While CREATE and DROP can be considered usefull for some recovery tasks, 
Alexander> nothing in the Universe can let me believe anyone, but MySQL developers 
Alexander> needs ALTER_Priv.

In this case you are actually wrong. To quite 'SQL 99 Complete,
Really', page 282:

'Many vendors have added their own privileges to the SQL standard...'

...
The most popular non-standard Privilege is 'GRANT ALTER'.


Alexander> Currently GRANT and REVOKE works well. The only problem is that SHOW 
Alexander> PRIVILEGES works only if you provide it strict set of parameters 
Alexander> (db.tbl, user@host)

Alexander> Extend SHOW PRIVILEGES so it can answer questions like:
Alexander> SELECT DISTINCT user FROM mysql.user;
Alexander> SELECT user, host FROM mysql.db WHERE db = 'mydb';

We have on our TODO to extend all SHOW commands so that you can use
them similar as SELECT.

<cut>

Regards,
Monty
Thread
New MySQL privilegesMichael Widenius27 Jan
  • RE: New MySQL privilegesJorge del Conde27 Jan
    • Re: New MySQL privilegesSinisa Milivojevic28 Jan
  • Re: New MySQL privilegesBrian Aker27 Jan
    • Re: New MySQL privilegesMichael Widenius30 Jan
  • Re: New MySQL privilegesAlexander Keremidarski28 Jan
    • Re: New MySQL privilegesMichael Widenius30 Jan
  • Re: New MySQL privilegesAlexander Keremidarski28 Jan
    • Re: New MySQL privilegesSinisa Milivojevic29 Jan
    • Re: New MySQL privilegesMichael Widenius30 Jan
  • Re: New MySQL privilegesSasha Pachev28 Jan
    • Re: New MySQL privilegesMichael Widenius30 Jan
  • Re: New MySQL privilegesAlexander Keremidarski30 Jan
    • Re: New MySQL privilegesMichael Widenius30 Jan
  • Re: New MySQL privilegesJeremy Zawodny31 Jan
  • Re: New MySQL privilegesAlexander Keremidarski31 Jan
RE: New MySQL privilegesMichael Widenius30 Jan