PHWinfo - Afficher un message

21/08/2007, 03h23

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 <jay@mysql.com> 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/mysql?unsub=r..._dba@yahoo.com

---------------------------------
Got a little couch potato?
Check out fun summer activities for kids.

21/08/2007, 03h23	#7
Rolando Edwards \ Messages: n/a Hébergeur:	Re: user permissions to all DB 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 <jay@mysql.com> 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/mysql?unsub=r..._dba@yahoo.com --------------------------------- Got a little couch potato? Check out fun summer activities for kids.