Granting account access to multiple databases

daveatride

• August 02, 2019 09:36

I"m trying to share a database between two domains. I have two sites, let"s call them siteA and siteB. Each site has it"s own cpanel account (siteA_user and siteB_user) and each site has it"s own database (siteA_db and siteB_db) which reside on the same server. siteA is an interactive site that allows the users to enter and maintain orders. We are now developing siteB to be a management overview tool of siteA (and other sites but we will retrieve data via apis as they are on different servers). I am trying to setup access to siteA_db for siteB_user so data can be retrieved for use in creating activity statistics. My dilemma is how can I grant siteB_user access to siteA_db so data retrieval can be accomplished using a query (select * from siteA_db.table where criteria). I saw an old thread "Can a domain communicate with database on another account?" and I was following the steps in the Details section as I though it better to have WHM setup things than to try to edit files. It looks like I"m using WHM version 76.0.22 (as stated on the top of the page). I select SQL Services->Database Map Tools. From the displayed list of accounts I select siteB and click the Select button. I then entered siteB_db,siteA_db in the Database names field and clicked the Submit button. At this point I am getting a message display indicating siteA_db already has an owner and siteB_user does not have permission to select on siteA_db. How can I setup an account to access multiple database while leaving the existing accounts un-effected. These are just two of the accounts on this server as we have several others but I am only interested in these two.

• 

  Henry Carter

  • August 05, 2019 12:40

  Hi Dave, In order to achieve this we have an option as root user to map the DB of other site DB user using the below commandline "GRANT ALL PRIVILEGES ON dbTest.* To 'user'@'hostname' IDENTIFIED BY 'password';" But I believe this will make conflict while running the site assignend with the DB user(siteA_user) of the other site database(siteB_db). Ofcourse while using "WHM >> Home >> SQL Services >> Database Map Tool" will show error "Already owned by other user".

  0
• 

  daveatride

  • August 06, 2019 13:35

  Thank you for this info, what I found is I needed to use terminal and login into mysql as root and then I was able to grant the permission to the other userB on databaseA using GRANT SELECT ON siteA_db to siteB_usr@localhost IDENTIFIED BY {password}. All is working as desired now.

  0
• 

  cPanelLauren

  • August 06, 2019 21:13

  I'm glad to see that you were able to get your issue resolved at @daveatride @Henry Carter thanks for helping out!

  0
• 

  Henry Carter

  • August 07, 2019 12:42

  Your welcome:)

  0

Please sign in to leave a comment.