[SOLVED] MySQL Perl DBI connect failed: Access Denied for user Granted Privi

[potentsinfo]

Not knowing the user account the Apache WS is in the Fedora 17 Linux system (I think it is “apache’), my question does is matter what user account calls the Perl program if I’ve already defined the SQL user, password, and database in the DBI query?

for instance if I type ./testdbi.pl command line returns:

DBI connect('','apache',...) failed: Access denied for user 'apache'@'localhost' (using password: YES) at ./testdbi.pl line 17
Undefined subroutine &main::cgidie called at ./testdbi.pl line 17.

I’ve granted SELECT privileges to apache in the database requiring same password…

but it also fails when I also tried my account at it, which created the databases, something not being passed with the DBI? Is there a Secure Linux tweak?

My Test Program:

#!/usr/bin/perl

use CGI;
use DBI;

my($convey) = new CGI;
my($sqlserver) = $convey->param('address') || 'localhost';
my($sqlaccount) = $convey->param('username') || 'apache';
my($sqlpassword) = $convey->param('password') || ' -p = ******';
my($sqldatabase) = "rugodwww";

print $convey->header;
print "\n<h1>TEST DBI</h1>\n";

$dbh = DBI->connect("DBI:mysql:$database","$sqlaccount","$sqlpassword") || cgidie("No handshake to $dbh connect to DBI, change after development");

my ($cursor);
$cursor=$dbh->prepare("show databases") || cgidie("the show".$cursor-errstr);
$cursor->execute;
my (@databases);
while (@databases = $cursor -> fetchrow) {
        print "\n<br>$databases[0]";
}

[potentsinfo]

my grants statements...

GRANT SELECT, INSERT ON rugodwww.* TO 'apache'@'localhost' IDENTIFIED BY '********';
GRANT SELECT ON geowerus.* TO 'apache'@'localhost' IDENTIFIED BY '********';

of course I haven't actually seen the user apache on my fedora box (seems I remember it as the owner of the www directory though) assuming show databases is same as SELECT priviledges?

[atreyu]

Hi,

There are some problems w/your CGI script, but that is not the real problem. Have you tried authenticating from the command line, on the local machine? E.g.:

mysql -u apache -p -e 'show databases'

You will be prompted for the password. If you want, you can include it on the command line - just put it directly after the -p with no spaces, e.g.:

mysql -u apache -pmypassword -e 'show databases'

However, I would NOT use the apache user as a mysql user. There is no need. Specify the username in your script - it can be whatever username you want that is a valid mysql user. I would create a new one as root, like this:

mysql -u root -p

and then:

mysql> GRANT ALL ON rugodwww.* TO 'dbuser'@'localhost' IDENTIFIED BY 'dbpassword';
mysql> GRANT ALL ON rugodwww.* TO 'dbuser'@'%' IDENTIFIED BY 'dbpassword';
mysql> FLUSH PRIVILEGES;

then try to connect, e.g.:

mysql -u dbuser -pdbpassword rugodwww -e 'show tables'

If you can connect from the command line, then we'll tackle the CGI - but it is pretty much there as it is (just syntax problems really).

[potentsinfo]

OK… CHANGED THE CODE, WORKS FOR ‘shane’ MY SUPER ACCOUNT.

#!/usr/bin/perl
use CGI;
use DBI;

my($convey) = new CGI;
my($sqlserver) = $convey->param('address') || 'localhost';
my($sqlaccount) = $convey->param('username') || 'shane';
my($sqldatabase) = "rugodwww";

print $convey->header;
print "\n<h1>TEST DBI</h1>\n";

$dbh = DBI->connect("DBI:mysql:$sqldatabase","$sqlaccount") || die("No handshake to $dbh connect to DBI, change after development");

my ($sql) = "select * from states";
my ($sth) = $dbh->prepare($sql);
$sth->execute or die "SQL error: $DBI::errstr\n";

my (@rowfetch);

while (@rowfetch = $sth -> fetchrow_array) {
        print "\n<br>@rowfetch\n";
}

SO CREATED USER

mysql> GRANT ALL on rugodwww.* TO 'sqlconvey'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON rogwww.* TO 'sqlconvey'@'%' IDENTIFIED BY 'password‘;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Changed username to sqlconvey to script, and access was denied, same as before. HOWEVER BOTH NAMES WORK FROM YOUR COMMAND QUERY!!!!!

[shane@rd cgi-bin]$ mysql -u apache -ppassword -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| geowerus           |
| rugodwww           |
| test               |
+--------------------+
[shane@rd cgi-bin]$ mysql -u sqlconvey -ppassword -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| rugodwww           |
| test               |
+--------------------+

STARTING TO AVERT MY EYES LIKE A SUPERSTITOUS ANCILARY. REALLY thought I was being smart by assigning limited access, especially during development, wouldn’t want some cgi-programming bug to drop a database. What am I missing?



OUTPUT FROM PROGRAM with ‘sqlconvey’ as

[shane@rd cgi-bin]$ ./testdbi.pl
Content-Type: text/html; charset=ISO-8859-1


<h1>TEST DBI</h1>
DBI connect('rugodwww','sqlconvey',...) failed: Access denied for user 'sqlconvey'@'localhost' (using password: NO) at ./testdbi.pl line 16
No handshake to  connect to DBI, change after development at ./testdbi.pl line 16.

OUTPUT FROM PROGRAM with ‘shane’ (only change)

[shane@rd cgi-bin]$ ./testdbi.pl
Content-Type: text/html; charset=ISO-8859-1


<h1>TEST DBI</h1>

<br>Alabama AL

<br>Alaska AK

<br>Arizona AZ

<br>Arkansas AR

[potentsinfo]

OK. Atreyu,

She Works... used the convey password without the original "-p" string for 'sqlconvey', RUSTY OLD CODE... think were' creating tutorial here. heh
ummm thanks?

[atreyu]

that's good. so your limited access mysql user can access the database via the CGI page now?

[potentsinfo]

Yes, Limited user works great, has only select on two, and insert on one. Was an extra letter in my Perl programming... heh. well our conversation will be popular on web searches... things doing well now. Searching to see if I should use a capcha generator.

[atreyu]

Great, I'll go ahead and mark this one as solved then.

Edit: er never mind, i guess it was!