privileges headache 2007-11-06 - By Baron Schwartz
Back
mysql@(protected) wrote:
> Baron Schwartz wrote:
>> hi,
>>
>> mysql@(protected) wrote:
>>>
>>> GRANT SELECT, INSERT, UPDATE ON the_database.*
>>> -> TO the_user@(protected) IDENTIFIED BY 'the_password';
>>> Query OK, 0 rows affected (0.00 sec)
>>> FLUSH PRIVILEGES;
>>> Query OK, 0 rows affected (0.00 sec)
>>
>> That statement does (at least) two things to mysql.user:
>>
>> 1) if there's no row in mysql.user for the_user@(protected), it creates it
>> 2) if there's an existing row, it updates its password.
>>
>> IT DOES NOT AFFECT ANY OTHER COLUMN IN mysql.user.
>>
>> But it does insert or update a row in mysql.db. Check there. You are
>> confused on the difference between global privileges, which are stored
>> in mysql.user, and database-level privileges, which are stored in
>> mysql.db and which you are changing with this statement.
>
> Right, I'd forgotten about the privs in db.
>
>>>
>>> Select_priv: Y
>>> Insert_priv: Y
>>> Update_priv: Y
>>>
>>> But I still cannot update any tables as this user.
>>
>> Probably because you have a database-level or table-level privilege
>> that overrides the global privileges you are granting.
>
> But this is the thing: why wouldn't the GRANT statement have affected
> the privs immediately? The changes only showed up after I'd restarted
> the server (in my 2nd msg) which, judging by the manual, should only
> happen if i don't issue a FLUSH PRIVILEGES (which I did do).
A couple of possibilities: a bug in MySQL, or something you did at the
time and have now forgotten...
SHOW GRANTS is always a good thing to check when there are weird
privilege problems. Do it as the user whom you're trying to grant
privileges to, because you can be surprised by the results otherwise.
Sounds like it's too late to understand what happened, though.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
