Terry,

I absolutely agree with Rolando on this.

Rolando,

Although I agree with you, I was only trying to answer Terry's question

Cheers,

Jay

Rolando Edwards wrote:
> You must be very careful when granting permissions on every database this way.
>
> Here is why:
>
> By giving a user permissions on all databases this way,
> you also give away permissions to the 'mysql' schema.
> This is where the grant tables live.
>
> A person could
> 1) insert new users into mysql.user like this
> INSERT INTO mysql.user VALUES (...);
>
> 2) delete users from mysql.user like this
> DELETE FROM mysql.user WHERE host='...' AND user='...';
>
> 3) maliciously or accidently change passwords like this
> UPDATE mysql.user SET PASSWORD=PASSWORD('<insert new password>') WHERE host='...' AND user='...';
>
> 4) grants additional privileges to himself like this
> UPDATE mysql.user SET grant_priv='Y',execute_priv='Y',... WHERE host='...' AND user='...';
>
> After setting those privilges, the person would then run "FLUSH PRIVILEGES;"
> Then, all the privileges the user gave himself would go into effect !!!
> Of course, the user would need the RELOAD privilege to do "FLUSH PRIVILEGES;"
>
> Even if the user does not have RELOAD privilege, the user could still give himself this privilege in a delayed way like this
> UPDATE mysql.user SET reload_priv='Y' WHERE host='...' AND user='...';
>
> Then the next time mysqld is restarted, all the privileges the user gave himself would go into effect !!!
>
> It is therefore NOT RECOMMENDED the user be granted privileges over the 'mysql' schema.
>
> Instead to this:
> GRANT SELECT, INSERT, CREATE, ... ON db1.* TO 'username'@'hostname' IDENTIFIED BY 'password';
> GRANT SELECT, INSERT, CREATE, ... ON db2.* TO 'username'@'hostname' IDENTIFIED BY 'password';
> GRANT SELECT, INSERT, CREATE, ... ON db3.* TO 'username'@'hostname' IDENTIFIED BY 'password';
> GRANT SELECT, INSERT, CREATE, ... ON db4.* TO 'username'@'hostname' IDENTIFIED BY 'password';
>
> Grant the necessary privileges to each database individually and leave out 'mysql'.
>
> Unfortunately, you cannot grant privileges to all databases and revoke privileges from one schema ('mysql' in this instance)
> You must enumerate the databases you specifically want to grant the user privileges to.
>
> GUARD THE mysql SCHEMA WITH YOUR LIFE, PLEASE !!!!
>
> ----- Original Message -----
> From: "Jay Pipes" <jay@mysql.com>
> To: "Terry" <td3201@gmail.com>
> Cc: mysql@lists.mysql.com
> Sent: Monday, August 20, 2007 3:41:52 PM (GMT-0500) America/New_York
> Subject: Re: user permissions to all DB
>
> Terry wrote:
>> Hello,
>>
>> I want to grant a user all permissions to all DBs on the system as
>> well as any new DBs that show up. I want to avoid having to modify
>> permissions everytime a new DB is added. Is there a way to do this?
>
> GRANT SELECT, INSERT, CREATE, ... ON *.* TO 'username'@'hostname'
> IDENTIFIED BY 'password';
>
> Cheers,
>
> Jay
>