tultalk has asked for the wisdom of the Perl Monks concerning the following question:

Hi:

Me again with a problem in code that worked years ago.

User jalamior_user has all privileges. Accessing database with no problems.

This database had different name before. When host switched plesk to cPanel rghe had to rename data base ad everything else on a contraction jalamior including user. I set up new password to go with jalamior_user and it is working elsewhere in program.

jala-mi.org MySQL Account Maintenance Manage User Privileges

User: jalamior_user Database: jalamior_assoc_mgr 

ALL PRIVILEGES
ALTER     ALTER ROUTINE
CREATE     CREATE ROUTINE
CREATE TEMPORARY TABLES     CREATE VIEW
DELETE     DROP
EVENT     EXECUTE
INDEX     INSERT
LOCK TABLES     REFERENCES
SELECT     SHOW VIEW
TRIGGER     UPDATE

[download]

Error message: Sun Mar 12 18:48:05 2017 update_tables-development.cgi: Unable to execute query: Access denied for user 'jalamior_user'@'localhost' (using password: YES) at update_tables-development.cgi line 124.

Did search on this issue (many responses). username and password are correct as evidenced by their success in other parts of program

Error produced in the sub:

Suggestions welcomed 

#-------------------------------------------------------
# Clear User Table Then Add New Data From Delimited File
#-------------------------------------------------------
sub UpdateUserTable
{
my $dbh = shift;
my ($sth, $stmt);
    warn("Entered sub UpdateUserTable");
    $stmt =    "DELETE
                FROM users";

    $sth = $dbh->prepare ($stmt);
    $sth->execute () or die "Unable to execute query: " .    $sth->err
+str;


    $stmt =    "LOAD DATA INFILE '/home/jalamior/public_html/httpsdocs
+/securefiles/accesscontrol.txt'
                INTO TABLE users
                FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'";


    $sth = $dbh->prepare ($stmt);
    $sth->execute () or die "Unable to execute query: " . $sth->errstr
+;
}

[download]

my $dbh = manageusers::OpenConnection();

This is connection used all over successfully.

This is call to sub 

if ($action eq "updatetable_3")
{
    warn("Entered update_tables.cgi with action = UpdateTable_3");
    UpdateUserTable($dbh);
}

[download]

Replies are listed 'Best First'.
Re: MySQL access denied
by huck (Prior) on Mar 13, 2017 at 02:47 UTC
[reply]

      Hi: Line 124 is the last line.

      Privileges selected when setting up user. All shown. Of course I don't see write there. Update is there though. 

      ALL PRIVILEGES
ALTER     ALTER ROUTINE
CREATE     CREATE ROUTINE
CREATE TEMPORARY TABLES     CREATE VIEW
DELETE     DROP
EVENT     EXECUTE
INDEX     INSERT
LOCK TABLES     REFERENCES
SELECT     SHOW VIEW
TRIGGER     UPDATE

      [download]

      I think you pinted me yto a place to look as the dlete did empty the table (so no one can log on now. HaH!!

      Did something change in perl in the last 5 yrs that would have changes how this stuff works?

[reply]
[d/l]

        as the dlete did empty the table

        So it was the load, and probably the apache-user cant read the file

        the user/group of apache prob changed. You prob want to adjust the group of the apache-user so it can read those secure dirs/files

[reply]

        Thanks for tip. All working now. Had a time with the relative path 

        sub UpdateUserTable
{
my $dbh = shift;
my ($sth, $stmt);
my $infile = "../securefiles/accesscontrol.txt";
    warn("Entered sub UpdateUserTable");
    $stmt =    "DELETE
                FROM users";

    $sth = $dbh->prepare ($stmt);
    $sth->execute () or die "Unable to execute query: " . $sth->errstr
+;


    #$stmt =    "LOAD DATA LOCAL INFILE '/home/jalamior/public_html/ht
+tpsdocs/securefiles/accesscontrol.txt'
    $stmt =    "LOAD DATA LOCAL INFILE '$infile' INTO TABLE users
                FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'";





    $sth = $dbh->prepare ($stmt);
    $sth->execute () or die "Unable to execute query: " . $sth->errstr
+;
}

        [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom