Mysql (GRANT ALL ON *.*) not working.

webaccounts · 03-04-2009, 08:23 AM

Why does GRANT ALL ON *.* TO 'username'@'anotherservername' IDENTIFIED BY 'password'; not work but GRANT ALL ON database.* TO 'username'@'anotherservername' IDENTIFIED BY 'password'; does?

This code doesn't work although it looks like it.

Code:

mysql> GRANT ALL ON *.* to 'username'@'webserver' identified by 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

This code does work.

Code:

mysql> GRANT ALL ON mu.* to 'username'@'webserver' identified by 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

I have over 100 databases so I don't want to do this manually.

When I grant all on *.* and go into mysql administrator, it shows the username@webserver but it has no access to any databases like it should.

Databases are not located in the normal location of /var/lib/mysql, it is at /data/mysql.

Selinux has mysqld disabled for protection.

Please excuse me if this is the wrong place to put this.

Thanks,
Shawn

int0x80 · 03-04-2009, 11:25 AM

I'm not sure that GRANT will work that way. What you could do is:

Code:

use mysql;
SELECT Db FROM db INTO OUTFILE '/tmp/my_databases.txt';

Then edit /tmp/my_databases.txt with vim and use the following substitution

Code:

:%s/^\(.\+\)$/GRANT ALL ON \1.* TO 'username'@'webserver' IDENTIFIED BY 'password'/g

Lastly, add a FLUSH PRIVILEGES at the end and run the queries

Code:

echo "FLUSH PRIVILEGES" >> /tmp/my_databases.txt
mysql -u root -p < /tmp/my_databases.txt

webaccounts · 03-04-2009, 01:03 PM

Ok thanks. I will try that. Didn't know grant doesn't work on all databases like that.

webaccounts · 03-04-2009, 02:01 PM

I should though of this eariler. But I just made a script to do it. Thanks.

Code:

#!/bin/bash
# All databases to grant.
# Reset counts
count=0
varcount=0
cd /data/mysql # Not default.  Default is normally /var/lib/mysql
for i in */; do
basename=`basename "$i"`
if [ $basename != "mysql" ]
then
        let count=count+1
        varcount=$count
        echo "GRANT ALL ON $basename.* to 'username'@'webserver' identified by 'password'; FLUSH PRIVILEGES" | /usr/bin/mysql -u root --password=ofcourseyouwantit
fi
done
echo "Total of $varcount databases granted."

norobro · 03-04-2009, 07:26 PM

Shawn,

Do you understand why your grant statements didn't work? I don't.

The mysql docs give an example that is similar to what you were doing:

Quote:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' -> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' -> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
. . . . . . .
. . . . . . .

The accounts created by these GRANT statements have the following properties:

* Two of the accounts have a username of monty and a password of some_pass. Both accounts are superuser accounts with full privileges to do anything. One account ('monty'@'localhost') can be used only when connecting from the local host. The other ('monty'@'%') can be used to connect from any other host. Note that it is necessary to have both accounts for monty to be able to connect from anywhere as monty. Without the localhost account, the anonymous-user account for localhost that is created by mysql_install_db would take precedence when monty connects from the local host. As a result, monty would be treated as an anonymous user.

Could the problem have been that you were using mysql administrator through the local host and the new account was seen as an anonymous user as stated above?

What is the output of:

Code:

show grants;

Inquiring minds want to know

Norm

T74marcell · 03-04-2009, 08:42 PM

I agree with 'norobro' that the 'GRANT ALL PRIVILEGES ON *.* ...' is supposed to work, as long as the user entering the command is really authorized to act on that particular server as the omnipotent entity.

The location of the mySQL databases on the system is merely a question of installation preferences, and as long as configuration files are set accordingly, then this should not affect the GRANT procedure.

Maybe try to login as root at the physical location of the server (not remotely) and try the GRANT query.

Arch Linux

webaccounts · 03-05-2009, 08:04 AM

I don't know why it doesn't work. I was doing it from the actual machine and from the shell, not mysql administrator. I just went in to mysql administrator to make sure everything was doing well.

I have no idea why it isn't working. It does fine 1 database at a time, but not all at once. I even turned off mysqld in selinux and still no go.

I'm using Centos 5.2 64bit, so not sure if that would have anything by default in the mysql code to prevent this.

I have also moved the default database location from /var/lib/mysql to /data/mysql. If its supposed to work then this might be the problem I'm guessing???

sundialsvcs · 03-05-2009, 09:32 AM

Don't forget that you must do FLUSH PRIVILEGES; after issuing any GRANTs.

The identity (and web-location) of the requesting user must match exactly, and the MySQL server log will contain useful messages to help you diagnose cases where they don't.

Also... although it takes a bit more work, you are well-advised to use the privilege-system for the purpose it was designed for. Practice the "principle of least privilege." Any user (or process) should acquire no more privileges against the database than are required to perform the chosen task. You might not be able to stop an intruder from injecting a DROP TABLE statement into some web-site's request stream, but you can certainly ensure that the attempt will not succeed! Therefore, "do so."