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!

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

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
>

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
>>

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)

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
>

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.

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'; ?

solidzh wrote:
> 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'; ?

Because then you give the user SUPER, FILE, ALTER, SHUTDOWN, and PROCESS
privileges, which probably isn't a good idea...

Cheers,

Jay