LinuxQuestions.org
Latest LQ Deal: Latest LQ Deals
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 03-04-2009, 09:23 AM   #1
webaccounts
Member
 
Registered: May 2008
Location: Columbus Ohio
Distribution: CentOS 5.9/6.4 x64
Posts: 44

Rep: Reputation: 19
Mysql (GRANT ALL ON *.*) not working.


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

Last edited by webaccounts; 03-04-2009 at 09:24 AM.
 
Old 03-04-2009, 12:25 PM   #2
int0x80
Member
 
Registered: Sep 2002
Posts: 310

Rep: Reputation: Disabled
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
 
Old 03-04-2009, 02:03 PM   #3
webaccounts
Member
 
Registered: May 2008
Location: Columbus Ohio
Distribution: CentOS 5.9/6.4 x64
Posts: 44

Original Poster
Rep: Reputation: 19
Ok thanks. I will try that. Didn't know grant doesn't work on all databases like that.
 
Old 03-04-2009, 03:01 PM   #4
webaccounts
Member
 
Registered: May 2008
Location: Columbus Ohio
Distribution: CentOS 5.9/6.4 x64
Posts: 44

Original Poster
Rep: Reputation: 19
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."
 
Old 03-04-2009, 08:26 PM   #5
norobro
Member
 
Registered: Feb 2006
Distribution: Debian Sid
Posts: 792

Rep: Reputation: 331Reputation: 331Reputation: 331Reputation: 331
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
 
Old 03-04-2009, 09:42 PM   #6
T74marcell
Member
 
Registered: Mar 2009
Posts: 102

Rep: Reputation: 18
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

Last edited by T74marcell; 03-14-2009 at 01:48 AM.
 
Old 03-05-2009, 09:04 AM   #7
webaccounts
Member
 
Registered: May 2008
Location: Columbus Ohio
Distribution: CentOS 5.9/6.4 x64
Posts: 44

Original Poster
Rep: Reputation: 19
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???

Last edited by webaccounts; 03-05-2009 at 09:05 AM.
 
Old 03-05-2009, 10:32 AM   #8
sundialsvcs
LQ Guru
 
Registered: Feb 2004
Location: SE Tennessee, USA
Distribution: Gentoo, LFS
Posts: 10,927
Blog Entries: 4

Rep: Reputation: 4013Reputation: 4013Reputation: 4013Reputation: 4013Reputation: 4013Reputation: 4013Reputation: 4013Reputation: 4013Reputation: 4013Reputation: 4013Reputation: 4013
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."
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Grant Privileges - MySQL Jannyno Linux - General 2 06-23-2012 03:00 AM
Why mysql GRANT does not work? robertoneto123 Linux - Software 2 01-23-2004 08:33 AM
mysql grant issues Robin01 Linux - Newbie 1 10-29-2003 10:56 PM
help! mysql --skip-grant-options not working linuxquestionsu Linux - Software 3 10-15-2003 08:56 PM
MYSQL GRANT etc etc markopolox Programming 8 09-23-2003 03:58 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 01:55 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration