user permissions to all DB

20/08/2007, 20h24

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?

Thanks!

20/08/2007, 20h41

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

20/08/2007, 20h47

Just to verify, will that include all new databases?

On 8/20/07, Jay Pipes <jay@mysql.com> wrote:
> 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
>

20/08/2007, 20h48

Yep.

Terry wrote:
> Just to verify, will that include all new databases?
>
> On 8/20/07, Jay Pipes <jay@mysql.com> wrote:
>> 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
>>

20/08/2007, 21h07

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=redwards@swmx.com

--
Rolando A. Edwards
MySQL DBA

SWMX, Inc.
1 Bridge Street
Irvington, NY 10533
(914) 406-8406 (Main)
(201) 660-3221 (Mobile)

20/08/2007, 21h21

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
>

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, 03h51

2007/8/21, Jay Pipes <jay@mysql.com>:
> 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';
>

That's well but why not,
grant all on *.* to 'user'@'host' identified by 'pwd'; ?

20/08/2007, 20h24	#1
Terry Messages: n/a Hébergeur:	user permissions to all DB 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? Thanks!

20/08/2007, 20h41	#2
Jay Pipes Messages: n/a Hébergeur:	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

20/08/2007, 20h47	#3
Terry Messages: n/a Hébergeur:	Re: user permissions to all DB Just to verify, will that include all new databases? On 8/20/07, Jay Pipes <jay@mysql.com> wrote: > 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 >

20/08/2007, 20h48	#4
Jay Pipes Messages: n/a Hébergeur:	Re: user permissions to all DB Yep. Terry wrote: > Just to verify, will that include all new databases? > > On 8/20/07, Jay Pipes <jay@mysql.com> wrote: >> 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 >>

20/08/2007, 21h07	#5
Rolando Edwards Messages: n/a Hébergeur:	Re: user permissions to all DB 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=redwards@swmx.com -- Rolando A. Edwards MySQL DBA SWMX, Inc. 1 Bridge Street Irvington, NY 10533 (914) 406-8406 (Main) (201) 660-3221 (Mobile)

20/08/2007, 21h21	#6
Jay Pipes Messages: n/a Hébergeur:	Re: user permissions to all DB 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 >

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.

21/08/2007, 03h51	#8
solidzh Messages: n/a Hébergeur:	Re: user permissions to all DB 2007/8/21, Jay Pipes <jay@mysql.com>: > 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'; > That's well but why not, grant all on . to 'user'@'host' identified by 'pwd'; ?

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email