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" <[EMAIL PROTECTED]>
To: "Terry" <[EMAIL PROTECTED]>
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]
--
Rolando A. Edwards
MySQL DBA
SWMX, Inc.
1 Bridge Street
Irvington, NY 10533
(914) 406-8406 (Main)
(201) 660-3221 (Mobile)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
