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.

LOCK TABLES Privileges for MySQL

Discussion in 'General Discussion' started by CoolMike, Jul 16, 2003.

  1. CoolMike

    CoolMike Well-Known Member

    Joined:
    Sep 6, 2001
    Messages:
    307
    Likes Received:
    0
    Trophy Points:
    16
    Hi

    A customer asked me for "LOCK TABLES" Privileges in MySQL, because he need this rights for an application.

    How can I give this privileges? What's the disadvantage?

    Thanks
    Mike

    cPanel.net Support Ticket Number:
     
  2. howtobeawebmaster

    Joined:
    Jan 2, 2003
    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Lake Arrowhead, CA.
    Remote Host

    In the MySQL seciton of cpanel, you can enter your customers ip number, or use the % for a wildcard. That will allow him to access the account. But setup a seperate user account for him so that if something happens you can still blame it on him.

    Also, inform him that if you give him access and the database becomes corrupt that you will need to charge him $150 an hour to try and recover any data. You don't want to be stuck trying to recover data for free. $150 an hour should cover any cost if you had to hire someone to recover the data or if you had to do it yourself.

    Get everything in writing. You don't want a client suieing you because his or her nephew decided that they can write a php and mysql database.

    Go to the MySQL website, www.mysql.com and lookup the "Lock Tables" command to see what it's all about. I can tell you, but I want you to take the responsibility of learning what the customer may or may not be upto.

    Good luck.

    JP
    www.howtobeawebmaster.com

    cPanel.net Support Ticket Number:
     
  3. CoolMike

    CoolMike Well-Known Member

    Joined:
    Sep 6, 2001
    Messages:
    307
    Likes Received:
    0
    Trophy Points:
    16
    Hi

    Thanks for your answer. I know, for what "Look Table" is, but I would like to know, how I can give this right. I don't think it has anything to do with the remote access to a database.

    I need to know, how can I give this rights and is it dangerous to do this on a WMM/Cpanel server.

    Thanks
    Michael

    cPanel.net Support Ticket Number:
     
  4. jsteel

    jsteel Well-Known Member

    Joined:
    Jul 4, 2002
    Messages:
    646
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Atlanta, GA
    Re: Remote Host

    That's the biggest bunch of hullbalubb I've ever heard. A customer can do just as much damage through phpMyAdmin as they can using remote MySQL tools or MS Access/ODBC connections. Do you charge customers $150 if they hose something up using phpMyAdmin? The only 'real' risk with remote access is that the ID/PW is passed clear text (just like with phpMyAdmin unless they are using the secure cPanel channel).

    Now back to the issue. If 'howtobe' actually knew about LOCK TABLES, he/she could have told you that under MySQL 4, the LOCK TABLES privilege has become part of System Administration (under v3.x, it was not). As a result, all of the software out there people are trying to install that requires LOCK TABLES are having problems since you cannot grant it on a per database level any longer (impacting shared hosting, not dedicated hosting). The LOCK TABLES functionality isn't some "rogue and mysterious" feature like 'howtobe' makes it out. It simply locks the table so that simultaneous data can't be written. It's most commonly used for autonumbering when inserting a record and instantly retrieving that autonumber for another task.

    Either the developers of the software are going to have to modify the code to no longer use LOCK TABLES, or MySQL needs to once again make it possible to assign the right to individual users for individual databases (we've sent them plenty of feedback).

    Until that happens, the only alternative is going back to MySQL v3 or telling the customers their application can't be supported (for the above reason - no fault of yours). If it happens to be an open-source application, they could probably modify the code to work around it on MysQL v4.

    cPanel.net Support Ticket Number:
     
  5. rs-freddo

    rs-freddo Well-Known Member

    Joined:
    May 13, 2003
    Messages:
    832
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Australia
    cPanel Access Level:
    Root Administrator
    I am currently installing FUDforum both for myself and a client. FUDforum requires lock tables privelage. I have mysql 4.0.13

    You can grant it simply by opening mysql database and under db table, changing the "N" to "Y" for the appropriate database. Restart mysql and the software will work.

    As I see it "lock tables privelege" seems available on a per database level. (You're welcome to prove me wrong.)

    The documentation says that the user must have "select" priveleges as well as lock tables privelege to use it - so I can't see it as a security problem as the user granted lock tables privelege will only have select priveleges to their own database.

    If I am wrong here please educate me.

    cPanel.net Support Ticket Number:
     
  6. jsteel

    jsteel Well-Known Member

    Joined:
    Jul 4, 2002
    Messages:
    646
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Atlanta, GA
    The LOCK TABLES feature in v4 is a global privilege:

    http://www.mysql.com/doc/en/LOCK_TABLES.html

    In addition, this page documents the feature as part of Server Administration:

    http://www.mysql.com/doc/en/Privileges_provided.html

    We're going to play around with it again since you say it is working. Our previous tests about 2 months ago led us to discover the change. It may very well be that 4.0.13 has fixed the issue.

    Thanks for the info. We certainly are hoping it is resolved - we certainly screamed at MySQL about it enough.

    cPanel.net Support Ticket Number:
     
  7. rs-freddo

    rs-freddo Well-Known Member

    Joined:
    May 13, 2003
    Messages:
    832
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Australia
    cPanel Access Level:
    Root Administrator
    Ah, I see what you mean. Well all I can say is that FUDforum install does test to make sure it works and won't allow the install if it doesn't. The changes I made to mysql dataabse did the trick.

    Unfortunately I don't know a whole heap about mysql...

    cPanel.net Support Ticket Number:
     
  8. CoolMike

    CoolMike Well-Known Member

    Joined:
    Sep 6, 2001
    Messages:
    307
    Likes Received:
    0
    Trophy Points:
    16
    Thanks for this information, but I do not fully understand this instructions. What do you mean by opening mysql database?

    Mike

    cPanel.net Support Ticket Number:
     
  9. CoolMike

    CoolMike Well-Known Member

    Joined:
    Sep 6, 2001
    Messages:
    307
    Likes Received:
    0
    Trophy Points:
    16
    Any help?

    Mike

    cPanel.net Support Ticket Number:
     
  10. rs-freddo

    rs-freddo Well-Known Member

    Joined:
    May 13, 2003
    Messages:
    832
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Australia
    cPanel Access Level:
    Root Administrator
    Hmm, if you don't understand my instructions then you probably need someone else to do it for you - you mess up your mysql db and you're cactus. Anyway on your head.

    use phpMyAdmin to view your databases. There is one called "mysql", this holds all your permissions etc. Click on the db table, then click "browse" find the database entry that needs to have "lock" enabled. Click "change". Under "lock privelges" change the "N" to "Y". Save and restart mysql.

    If you mess up, you may destroy mysql for the server...

    cPanel.net Support Ticket Number:
     
  11. CoolMike

    CoolMike Well-Known Member

    Joined:
    Sep 6, 2001
    Messages:
    307
    Likes Received:
    0
    Trophy Points:
    16
    Thanks for the information

    But when I look at this database, every user has already the LOCK privileges:

    Code:
    
    mysql> select * from db WHERE User like "%proof%";
    +-----------+----------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+
    | Host      | Db             | User          | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv |
    +-----------+----------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+
    | localhost | proof\_session | proof_gateway | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                |
    | localhost | proof\_sbgb    | proof         | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                |
    | %         | proof          | proof         | N           | N           | N           | N           | N           | N         | N          | N               | N          | N          | Y                     | Y                |
    +-----------+----------------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+
    
    But I did not change anything and the script is still not working because of the LOCK privileges.

    BR
    Michael

    cPanel.net Support Ticket Number:
     
  12. rs-freddo

    rs-freddo Well-Known Member

    Joined:
    May 13, 2003
    Messages:
    832
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Australia
    cPanel Access Level:
    Root Administrator
    Odd. Changing the lock priveleges worked for me.

    Sorry i can't be more help.

    cPanel.net Support Ticket Number:
     
  13. CoolMike

    CoolMike Well-Known Member

    Joined:
    Sep 6, 2001
    Messages:
    307
    Likes Received:
    0
    Trophy Points:
    16
    Ok, thanks for your help so far. At least I know now where this information is stored.

    Anyone else, who know, how to do it?

    Thanks
    Michael

    cPanel.net Support Ticket Number:
     
Loading...

Share This Page