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.

MySQL. Access between cPanel accounts.

Discussion in 'General Discussion' started by Lironcareto, Jul 31, 2009.

  1. Lironcareto

    Lironcareto Registered

    Joined:
    Jul 31, 2009
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    Hi all
    I have two domains in the same hosting services. Each of them has a database named dom1_db and dom2_db respectively, and each of them has a user named dom1_userdb and dom2_userdb. Is there any way for dom1_userdb to login and access to dom2_db? I'd like to share some tables, because replicating information should be a bit dangerous because it exposes to data inconsistencies. It would be a big help to access to the other database.

    Thanks in advance!
     
  2. david510

    david510 Well-Known Member

    Joined:
    Aug 22, 2004
    Messages:
    473
    Likes Received:
    0
    Trophy Points:
    16
    You can grant access from for the d1 to dbuser2 and vice versa. If you have shell access, you can use the following steps from the mysql prompt.
    Code:
    grant all privileges on db1.* to dbuser2@localhost identified by 'password';
    grant all privileges on d21.* to dbuser1@localhost identified by 'password';
    
     
  3. Lironcareto

    Lironcareto Registered

    Joined:
    Jul 31, 2009
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    Thanks, David, but when I try to grant access that way, MySQL returns me the error
    Access denied for user 'dbuser2'@'xx.yy.zz.tt' to database 'db2' :( It's own DB!! And of course I have access with dbuser2 to db2 because it's the main user, the user which I login to DB from MySQL GUI tools with, and the admin user which I login to cPanel for that domain with. :(

    Both databases are in the same server, as I stated before.

    It seems that cPanel not only creates users with no access to other databases (which is of common sense) but also with no privilege to grant access to other users, by default. So I was asking for any other way to do that.
     
  4. cPanelDavidG

    cPanelDavidG Technical Product Specialist

    Joined:
    Nov 29, 2006
    Messages:
    11,279
    Likes Received:
    8
    Trophy Points:
    38
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    What I do in this scenario is create another user for my DB in cPanel and grant it all privileges (or whatever privileges are desired) to the database(s) I want that user to have access to.

    Then from my other website, I authenticate to MySQL as this user I created. Even though this user was created under another domain, this other website can still authenticate to it since it's on the same server and it is using the MySQL user's authentication credentials.
     
  5. Lironcareto

    Lironcareto Registered

    Joined:
    Jul 31, 2009
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    Hi DavidG, and thanks for your answer.
    Since MySQL users created from cPanel are preceded by the cPanel user (often the domain name) in the way "cpuser_dbuser", any user created for domain1 from domain2 are different.
    And if I try to solve this executing the create user command from MySQL Query Browser I get an access denied error.

    Could you explain with detail your workaround for this scenario, please?

    Thanks in advance.
     
  6. cPanelDavidG

    cPanelDavidG Technical Product Specialist

    Joined:
    Nov 29, 2006
    Messages:
    11,279
    Likes Received:
    8
    Trophy Points:
    38
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    Sure, I was using the below setup myself up until 9 months ago:

    1. Two separate cPanel accounts, let's call them DomainA and DomainB
    2. I had a old database used by DomainA, so I had MySQL user under DomainA, let's call it DomainA_dbuser with access to DomainA_db
    3. Eventually I created DomainB on the same server, but wanted to access DomainA's databases from DomainB.
    4. I just configured my PHP script on DomainB to connect to DomainA_db while authenticating as DomainA_dbuser - connecting to the MySQL server on localhost.
    5. Great Success!


    Now, if you are using a tool to connect to the database remotely (e.g. MySQL's Query Browser Tool), be sure to add your workstation's IP to the allowed IP list, or else you will get an Access Denied message. You can do this by going to the cPanel interface where the database was created and clicking on the "Remote MySQL" icon. If you don't know your workstation's IP address, you can visit a website like WhatIsMyIPAddress .com
     
  7. Lironcareto

    Lironcareto Registered

    Joined:
    Jul 31, 2009
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    I understand your solution, David, but I'm afraid it doesn't apply to my case. It wouldn't allow me to join tables between databases in the way select * from db1.table1, db2.table2 where... etc, which is what I really need. :(

    Thanks anyway.
     
Loading...

Share This Page