myDBR Forums » Installation

mydbr user permission to database

(8 posts) (2 voices)

  1. jcstevens, Member

    Good morning,

    We have been running mydbr on a Windows Server named 'corpappswimp' connecting to a MySQL db on a Linux box named 'atdmariadblive'. In order to connect the mydbr installation to the db server, we had to create user permissions for a user named 'mydbr@corpappswimp' which means that our mydbr_config.php looks like the following:
    <?php // myDBR configuration file. Created: 2014-10-27 14:27:29 define( "DB_VENDOR", 'mysql' ); define( "DB_HOST", 'atdmariadblive' ); define( "DB_PORT", 3306 ); define( "DB_NAME", 'mydbr' ); define( "DB_USER", 'mydbr' ); define( "DB_PASSWORD", 'readonly!@#$' ); define( "SETUP_DONE", true );

    We recently switched to running atdmariadblive through an HAProxy, which broke this configuration. We now get "Connection failed: Check the username and the password in mydbr_config.php." for all pages at corpappswimp/mydbr. My server admin let me know that we need to switch the mydbr user from 'mydbr@corpappswimp' to 'mydbr@%'. What is the best way to set this up?

  2. myDBR Team, Key Master

    The database permissions are defined in the database server. User is identified by username@connection. Check from the server what connection string your setup is using and grant permissions to that connection. You can also grant connection to the username regardless of the connection by using syntax mydbr@%.

    --
    myDBR Team

  3. jcstevens, Member

    The issue that we have is that the user in the mydbr_config file automatically has the webserver's suffix added. So mydbr_config (living on corpappswimp) shows user 'mydbr' and myDBR sends the string over to the db server as 'mydbr@corpappswimp'.

    I believe that we tried to set DB_USER as 'mydbr@%' when we first installed myDBR and myDBR took that definition literally and tried to connect as 'mydbr@%@corpappswimp'. Just to clarify, if we define DB_USER in mydbr_config as 'mydbr@%', and we set up mydbr@% with permissions on the db server, we should be able to authenticate?

  4. jcstevens, Member

    Just to note, we copied the db permissions we had set up for 'mydbr@corpappswimp' to a new user 'mydbr@%'. I changed the mydbr_config.php to show:
    <?php // myDBR configuration file. Created: 2014-10-27 14:27:29 define( "DB_VENDOR", 'mysql' ); define( "DB_HOST", 'atdmariadblive' ); define( "DB_PORT", 3306 ); define( "DB_NAME", 'mydbr' ); define( "DB_USER", 'mydbr@%' ); define( "DB_PASSWORD", 'readonly' ); define( "SETUP_DONE", true );

    Same error as above.
    I changed the mydbr_config to the following and received a php error in line 7 of mydbr_config.php:
    <?php // myDBR configuration file. Created: 2014-10-27 14:27:29 define( "DB_VENDOR", 'mysql' ); define( "DB_HOST", 'atdmariadblive' ); define( "DB_PORT", 3306 ); define( "DB_NAME", 'mydbr' ); define( "DB_USER", 'mydbr'@'%' ); define( "DB_PASSWORD", 'readonly' ); define( "SETUP_DONE", true );

    What should I use?

  5. myDBR Team, Key Master

    The DB_USER should contain just the username, in your case 'mydbr'.

    The permission configuration is be made in the database server using the GRANT-command. There is nothing you need to change in myDBR side.

    --
    myDBR Team

  6. jcstevens, Member

    That was the first of my last two examples, and it did not work. Let me try to phrase this another way:
    When we first installed a few months back, we found that using mydbr_config DB_USER 'mydbr' did not work. Assuming that this was because our myDBR installation lives on corpappswimp, we went ahead and created the user in our db as 'mydbr@corpappswimp' which then worked. This tells me that myDBR is creating a connection string of DB_USER+@+<mydbr_hosting_server>. My server admin confirmed that our db server is receiving 'mydbr@%'@'172.25.4.120'. How do I get myDBR to drop the appended server name/ip?

  7. myDBR Team, Key Master

    In MySQL when a connection is made to the database, the server gets username@ip_address. This combination needs to have access rights defined with GRANT command. Again, this GRANT is done in the database server, not in myDBR.

    In your case you have defined the username as "mydbr'@'%" and your connection is made from server 172.25.4.120, hence the server sees myDBR connection as mydbr@%'@'172.25.4.120 which is probably not what you want. If you change the username back to original one (mydbr), the server sees the connection as 'mydbr'@'172.25.4.120'. You need then to grant access to that username@connection by issuing a command:

    mysql> grant all on mydbr.* to 'mydbr'@'172.25.4.120';

    This will give the connection permissions to myDBR database in the server to user mydbr coming from 172.25.4.120.

    Another thing that you may need to check is that when the user has rights to the stored procedures if you created them under other username. You can also grant select access to mysql.proc to allow myDBR to read/modify the procedures.

    --
    myDBR Team

  8. jcstevens, Member

    Thanks for the info. My server admin reviewed our user settings and found that our Maria Galera Clustering doesn't automatically replicate user permissions across all of the clustered servers. When he manually replicated user permissions to all machines in the cluster, the issue was resolved. Thanks again for sticking with me on this!


Reply

You must log in to post.