myDBR Forums » General discussion

Permission problem in MySQL reading report's parameters!

(13 posts) (2 voices)

  1. jcstevens, Member

    I have a working installation of myDBR which was pointing to a TEST MySQL db. This morning, I used mysqldump to copy the TEST mydbr database to the LIVE mysql server (using the --routines option to copy the SPs over as well), then switched the myDBR config over to the LIVE MySQL db, which prompted a reinstallation. In the reinstallation, via the web interface, I was prompted to have mydbr recreate objects which completed successfully.

    Now, however, when I go to edit a mydbr report, I get the following error message:

    Permission problem in MySQL reading report's parameters!

    In order for myDBR to read report's parameters, reports needs to be created under the same user account the myDBR is running (jcstevens@corpappswimp.meditech.com).
    The report 'sp_DBR_Case_Narrative_2014-10-06' is now created under username jcstevens@%.

    I found the following topic from 5 years ago, but the advice doesn't make sense to me.

    My production reports are now inaccessible and I need to get these back up and running ASAP. Any ideas?

  2. myDBR Team, Key Master

    Your reports are created under login 'jcstevens@%', but myDBR is running under login 'jcstevens@corpappswimp.meditech.com'. While both share the same account name-part, MySQL logins have two parts, username and the connection-part. This means that user 'jcstevens@%' is different from user 'jcstevens@corpappswimp.meditech.com' and MySQL's default permissions prevent user jcstevens@corpappswimp.meditech.com from reading user 'jcstevens@%''s procedures.

    You have couple of options:

    1) Recreate the procedures under username jcstevens@corpappswimp.meditech.com

    Search and replace the string ''jcstevens@%' from the dump file and recreate the procedures.

    2) Grant user jcstevens@corpappswimp.meditech.com read access to mysql.proc where the definitions are stored_

    mysql> grant select on mysql.proc to 'jcstevens@corpappswimp.meditech.com'

    --
    myDBR Team

  3. jcstevens, Member

    Thank you for the quick response. I'm curious, though, as to why this was working when installed on corpappswimp.meditech.com pointing to our test server, and I was able to copy stored procedures without any issues then. But after reinstalling on corpappswimp.meditech.com to point to our live server I get errors. My user's settings and permissions are the save on mysql TEST and LIVE, so it appears that myDBR was using username jcstevens@% in TEST and now forces me to use jcstevens@corpappswimp... in LIVE. What changed?

  4. jcstevens, Member

    • We went ahead and created a new user, mydbr@corpappswimp.meditech.com, and granted FULL access to the mydbr db and SELECT access to our LIVE MySQL db.
    • I reinstalled myDBR on corpappswimp.meditech.com under the mydbr user
    • I recreated db objects during the installation and Started the application


    Now (same as before with jcstevens) I am able to run reports, but when I duplicate an existing report then edit the duplicate, I get the error message regarding permissions. However, the error before was showing jcstevens@% versus jcstevens@corpappswimp.meditech.com; now, the error is showing mydbr@corpappswimp.meditech.com in both parts of the error:
    Permission problem in MySQL reading report's parameters!

    In order for myDBR to read report's parameters, reports needs to be created under the same user account the myDBR is running (mydbr@corpappswimp.meditech.com).
    The report 'sp_DBR_Case_Narrative_2014-10-06v2' is now created under username mydbr@corpappswimp.meditech.com.

    Something has to have changed on the mydbr end... please help!

  5. myDBR Team, Key Master

    Hi,
    we do understand your frustration, but there are no changes in myDBR causing this. The problem is purely a permission problem.

    For some reason, the login you are using is now able to read the procedure definition. You can try to see if an error by logging in with the same login myDBR uses (select user() shows you the login) and then try to read the procedure definition:

    mysql> show create procedure sp_DBR_Case_Narrative_2014-10-06v2

    This should show the procedure code in the 'Create Procedure' column. If the column is null, you are missing permissions.

    You can use the option 2 and grant myDBR user select access to mysql.proc. This should fix the issue.

    --
    myDBR Team

  6. myDBR Team, Key Master

    A correction to previous answer. If you are able to execute a procedure, this means that myDBR can read the report definition. The problem is still a permission problem, just missing some other permission.

    What is the exact command in the editor that fails?

    - Can you duplicate (=create a new) a procedure?
    - Can you run a "drop procedure"-command from the editor?
    - Can you run "create procedure"-command from the editor.

    Note that in the SQL Editor when you edit a procedure, myDBR runs two commands drop and create. Which one is the one that fails?

    --
    myDBR Team

  7. jcstevens, Member

    I am able to log into MySQL as mydbr (on corpappswimp.meditech.com) and I am able to show create procedure sp_DBR_Case_Narrative_2014-10-06v2 without any issues. I have SUPER user access to the server as mydbr@corpappswimp.meditech.com.

    Just to be sure, I duplicated an SP in myDBR and went to edit the duplicated report and got the same error message:

    Permission problem in MySQL reading report's parameters!

    In order for myDBR to read report's parameters, reports needs to be created under the same user account the myDBR is running (mydbr@corpappswimp.meditech.com).
    The report 'sp_DBR_Case Narrative_2014-10-06v3' is now created under username mydbr@corpappswimp.meditech.com.

    This is now an installation which used to work without issue, was pointed to a new server with the same permissions and the same user, then had a new SuperUser created with a fresh install under that user and mydbr cannot access a duplicated SP with the same user. All permissions are correct. What should I do next?

  8. jcstevens, Member

    I am able to go to the Admin menu, select EDIT on an existing report and scroll down and choose to Duplicate. The Duplication occurs successfully.

    However, when I then click to RUN or EDITthat DUPLICATED report, I get the above errors saying essentially that mydbr@corpappswimp.meditech.com isn't the same as mydbr@corpappswimp.meditech.com (same user!)

    When I choose to edit REPORT CODE for that duplicated report, it immediately fails with the error message in the first line of the SQL Editor: "/* Error reading the procedure definition for sp_DBR_Case Narrative_2014-10-06v3. */"

    I am able to Run Reports previously created and migrated without any issue.

  9. myDBR Team, Key Master

    Trying to pinpoint which permission you are missing. Most likely "CREATE ROUTINE".

    To see what is going on:
    - Can you create a new report in SQL Editor? Just try to create a dummy one.
    - Can you drop a procedure in SQL Editor? Try to drop the dummy one if you were able to create it.
    - Do you have a report which you can execute?

    Open the SQL Editor, run the command:

    show create procedure sp_DBR_Case Narrative_2014-10-06v3

    and use the same command for a procedure which is a report you can execute.

    --
    myDBR Team

  10. jcstevens, Member

    • I was able to create a new Stored Procedure, sp_DBR_dummy().
    • I was able to get results for this SP via the SQL Editor > Query1 tab.
    • I made an edit to the SP and it executed which shows that it Dropped and Recreated the SP in SQL Editor.
    • I then dropped the SP in SQL Editor successfully and confirmed that Query1 (call sp_DBR_dummy()) failed because the SP no longer exists.
    • I am able to execute all existing reports.
    • I can execute show create procedure sp_DBR_Case Narrative_2014-10-06v3 in SQL Editor
    • I can execute show create procedure sp_DBR_Case_Search (a report I can run)

  11. myDBR Team, Key Master

    So what is the thing that fails?

    Was the "sp_DBR_Case Narrative_2014-10-06v3" the report you cannot edit resulting an error "Error reading the procedure definition for"?

    What is the output for "show create procedure sp_DBR_Case Narrative_2014-10-06v3

    Do you really have a space in the procedure name?

    --
    myDBR Team

  12. myDBR Team, Key Master

    The problem is likely caused by the space in the procedure name. We'll take a look at it.

    --
    myDBR Team

  13. jcstevens, Member

    That's the fix. I didn't even realize that I was leaving a space in the procedure name, but copying without the space allowed me to edit the report and SP.

    Initially, this was a permissions issue between the host user and the % user, but eventually that was fixed and the space continued to cause problems. Sorry for the confusion, and thanks for your patience with me.


Reply

You must log in to post.