The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Keeping MySQL permissions

Discussion in 'General Discussion' started by sparek-3, Apr 23, 2003.

  1. sparek-3

    sparek-3 Well-Known Member

    Joined:
    Aug 10, 2002
    Messages:
    1,384
    Likes Received:
    23
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
    I am wanting to give a particular user File Privileges on the server. I use the line:

    update user set File_Priv="Y" where user=username;

    Note, the username that I am referring to, is the account username, not a username that this user has created. The user is wasnting to upload a data file, and use LOAD DATA INFILE from within phpMyAdmin. I can make this change, and it fixes the problem, however the settings will not stay. Everytime I fix it, I write the client and then they reply back saying it is not working, and sure enough the settings have been returned to their normal state. Is there anyway around this?

    Thanks
     
  2. purplepaws

    purplepaws Well-Known Member

    Joined:
    Jan 15, 2002
    Messages:
    153
    Likes Received:
    0
    Trophy Points:
    16
    did you get a fix for this at all? i also sent you a PM.
     
  3. phantasmp

    phantasmp Member

    Joined:
    Feb 19, 2003
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Canada
    i'm also having this problem, i tried using

    update user set File_priv='Y' where User=username
    and
    grant file on *.* to username@localhost

    then flushing the privliges, and they both worked the first time but not after that.
     
  4. Godcore

    Godcore Active Member

    Joined:
    Dec 31, 2002
    Messages:
    39
    Likes Received:
    0
    Trophy Points:
    6
    I am having the same problem. Anyone find a solution for this?

    We downgraded to MySql 3 because of other issues... is this true of you as well?
     
  5. JustinK

    JustinK Well-Known Member

    Joined:
    Sep 4, 2001
    Messages:
    251
    Likes Received:
    0
    Trophy Points:
    16
    Having the same issue. Downgraded to 3 from 4 as well. :|
     
  6. sparek-3

    sparek-3 Well-Known Member

    Joined:
    Aug 10, 2002
    Messages:
    1,384
    Likes Received:
    23
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
    I made a simple perl script to monitor the database, I have it set up to run hourly via crontab. Please note that this is intended to be run as root on the server. If you are not the server administrator, then you will need to contact your host. They might decide to use this, or they might not. This isn't an actual fix, just more of a work around. Someone can probably edit this and make it better. You will need the DBI perl module to be installed:

    Code:
    
    #!/usr/bin/perl
    
    use DBI;
    
    $dbh = DBI->connect('DBI:mysql:mysql:localhost','root','<root db password>');
    
    $query = "select File_priv from user where user='<user name>'";
    $sth = $dbh->prepare($query);
    $sth->execute;
    
    @row = $sth->fetchrow_array;
    
    $sth->finish;
    
    if ($row[0] eq "N") {
    
            $date = `date`;
            chop($date);
    
            open(MAIL,"|mail -s \"MySQL permissions on <Domain name or anything, this is the subject>\" <email\@address be sure to escape the @ symbol");
    
            print MAIL "File_priv on <user name> database changed on $date\n";
            print MAIL "Value now set to: $row[0]\n";
            print MAIL "Changing value...\n";
    
            $query = "update user set File_priv='Y' where user='<user name>'";
            $sth = $dbh->prepare($query);
            $sth->execute;
    
            $query = "flush privileges";
            $sth = $dbh->prepare($query);
            $sth->execute;
    
            $sth->finish;
    
            print MAIL "Value updated\n";
    
    }
    
    
    Change <root db password> to the correct password for the root mysql user.
    Change <user name> to the account username of the domain. This is not a username that was created via a user's control panel. This is the username of the actual account.
    You can set the domain name in the subject, it doesn't matter. It just reminds me what its doing.

    Be sure the email address is in the form:

    test\@test.com

    The @ symbol must be escaped out.

    I have this set up to run through crontab every hour. You can have it run however often. An hour seems to be good. I still have not been able to notice any particular pattern, to no what process is changing the permissions back. If you are not the server administrator, you can have your host run the command:

    update user set File_priv='Y' where user='<user name>';
    flush privileges;

    and see if that fixes the problem temporarily. However, they will notice that within a day or so, the File_priv will not be set to Y instead it will be back at N.

    If you do use this script, be sure that you chmod it to 700 so that it can be run and only read by root.

    Hope this helps.
     
  7. phantasmp

    phantasmp Member

    Joined:
    Feb 19, 2003
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Canada
    i went back to mysql 4, and since the reason we downgraded was cause users were unable to lock tables, i went to the mysql site and found that i have to give the right priv for it, so i just granted all on the user to their database, and now they can lock, and now the grant file command will keep
     
  8. smachol

    smachol Well-Known Member

    Joined:
    Oct 19, 2001
    Messages:
    57
    Likes Received:
    0
    Trophy Points:
    6
    I'm having the LOCK problem too. What do I need to do to grant this privilege to all my db users?
     
  9. ZeroFill

    ZeroFill Active Member

    Joined:
    May 4, 2003
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    1
    Same here. I'm just a reseller, but I want to be able to lock/unlock/grant tables as well. this (lock/unlock) is required when restoring a database from mysqlbackup --opt. can't cPanel just give these permissions by default whenever users create a database? I mean, they're the ones who made it, they should have all permissions since they are the dba. coming from an HSphere background, we were able to select what privs each account had to the selected database.

    cPanel.net Support Ticket Number:
     
  10. smachol

    smachol Well-Known Member

    Joined:
    Oct 19, 2001
    Messages:
    57
    Likes Received:
    0
    Trophy Points:
    6
    This is really getting irritatiing. Based on some of the things I've found by searching around, I tried running:

    mysql_fix_privilege_tables ROOT_MYSQL_PASSWORD

    But I get this error:

    ERROR 1060 at line 1: Duplicate column name 'max_questions'
    ERROR 1060 at line 1: Duplicate column name 'Create_tmp_table_priv'
    ERROR 1060 at line 4: Duplicate column name 'Create_tmp_table_priv'

    cPanel.net Support Ticket Number:
     
Loading...

Share This Page