privileges headache 2007-11-06 - By mysql@(protected)
Back
mysql@(protected) wrote:
> I rarely need to add users to a running setup but I always seem to run
> into the same sort of problem: I grant privs to a user but they refuse
> to show up. This user will be the one a web application goes through to
> access the DB.
>
> As the root user:
>
> 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)
>
I've resolved this, but I had to restart the MySQL server in order for
the privileges to be reloaded. This appears completely counter to what
the manual suggests.
(running 5.0.24, btw)
From the manual:
http://dev.mysql.com/doc/refman/5.0/en/privilege-changes.html
-- snip --
If you modify the grant tables indirectly using statements such as
GRANT, REVOKE, or SET PASSWORD, the server notices these changes and
loads the grant tables into memory again immediately.
If you modify the grant tables directly using statements such as INSERT,
UPDATE, or DELETE, your changes have no effect on privilege checking
until you either restart the server or tell it to reload the tables. To
reload the grant tables manually, issue a FLUSH PRIVILEGES statement or
execute a mysqladmin flush-privileges or mysqladmin reload command.
If you change the grant tables directly but forget to reload them, your
changes have no effect until you restart the server. This may leave you
wondering why your changes do not seem to make any difference!
-- snip --
Now, I first used the GRANT syntax, including the FLUSH line. I then
tried updating the user table manually, issuing the FLUSH again. Still
no joy. It wasn't until i logged out and restarted the DB server that
the privilege changes appeared.
So, though I've got the result I wanted, I am indeed wondering why my
changes did not make a difference.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
