OK. I am just a little pumped up these days.
I just passed my Certification Exams.
Chapter 34 of the Certification Book is still fresh in my mind.
Even though I'm in the US, Cheers everybody !!!
Jay Pipes <[EMAIL PROTECTED]> wrote:
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('') 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"
> To: "Terry"
> 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
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
---------------------------------
Got a little couch potato?
Check out fun summer activities for kids.
