Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MySQL Stored Procedure access problem

Posted on 2008-10-09
9
Medium Priority
?
1,030 Views
Last Modified: 2012-05-05
I developed a site that uses a MySQL stored procedure to access layout parameters.  I had it working fine on a different domain, but when I went to move the database over to the live server, it tells me it cannot move the stored procedure, because I need SUPER privileges.  I use PremiumSoft Navicat to manage my databases, but I do not know how to set this up so that it can read the stored procedure. The sql simply takes the page filename and filters the database to grab the settings for CSS and graphics.  I would like to have my user profile (that only does select statements) throughout the site be the connection, but couldn't seem to get that to work.

I call the procedure with:
$currentFile = $_SERVER["SCRIPT_NAME"];
    $parts = Explode('/', $currentFile);
    $currentFile = $parts[count($parts) - 1];
      $currentFile = explode(".", $currentFile);
      $currentFile = $currentFile[0];
      
 $link = mysqli_connect($hostname_connAdmin, $username_connAdmin, $password_connAdmin, $database_connAdmin);
 $selectedStyle = mysqli_query($link, "call SelectedStyle('".$currentFile."')");
 $row = mysqli_fetch_array($selectedStyle) ;

Then here is the stored procedure:
SELECT
Styles.StyleID,
Styles.CSSStyleSheet,
Styles.StyleDescription,
Layout.ID,
Layout.Page,
Layout.FriendlyPageName,
Layout.Header,
Layout.MainText,
Layout.PageTitle,
Layout.PageHeading,
Layout.Theme,
Layout.LeftColumnContent,
Layout.LeftColumnContent2
FROM
Layout
Inner Join Styles ON Layout.Theme = Styles.StyleID
WHERE
Layout.Page =  parameter1

IN parameter1 text
0
Comment
Question by:dragboatrandy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22689842
could you please clarify where exactly the error occurs, ie when doing what...
0
 

Author Comment

by:dragboatrandy
ID: 22689954
The mysqli call is the very first function on the page.  If you load the page in the browser, it just says "Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in /home/tearoom/public_html/index.php on line 11

I did a basic echo statment, and I know that it is passing the page filename to the query.  When I load the page, that error occurs and then continues to load the page.  When I try to run the procedure with Navicat I get the error "1370 - execute command denied to user 'tearoom_admin@% for routine 'tearoom_data.SelectedStyle.  tearoom_admin@% is set as the define of the procedure, but it will not allow it run.

I hope this explains it, I am not real sure how else to explain it.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22690009
>When I try to run the procedure with Navicat I get the error "1370 - execute command denied to user 'tearoom_admin@% for routine 'tearoom_data.SelectedStyle

try to grant execute permissions to that user/procedure anyhow:

GRANT EXECUTE ON tearoom_data.SelectedStyle TO 'tearoom_admin'@'%';

Open in new window

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:dragboatrandy
ID: 22690083
Would I do that on the php page?  I am just getting my feet wet on this subject.  Could you explain a little more in depth?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 22690117
as that is a "one-shot" action, I would use the mysql command line tool, being connected with root powers...
0
 

Author Comment

by:dragboatrandy
ID: 22690878
I am not sure how to login as the root user.  I am on a dedicated server, but not exactly sure how to do it.  I used the main username and password that I setup for the site on my server.  I have a Console tool within Navicat that I believe is command line, but I get this message:

ERROR 1142 : GRANT,EXECUTE command denied to user 'tearoom'@'adsl-99-137-17-187.dsl.chi2ca.sbcglobal.net' for table 'SelectedStyle'

Sorry to sound like such a bonehead, but I am venturing into uncharted waters for me.  I just began using stored procedures and some it if just goes over my head.
0
 

Author Comment

by:dragboatrandy
ID: 22690967
I think I figured out how to login as the root.  I accessed PHPMyAdmin from CPanel's WHM.  It shows me all of the databases on my server but when I  ran the statment you gave me I got this error message

#1144 - Illegal GRANT/REVOKE command; please consult the manual to see which privileges can be used
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 22691571
Congrats on your eliteness AngelIII!
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22693057
thanks for the congrats.

for anyone reading this, the keyword PROCEDURE was missing:
http://dev.mysql.com/doc/refman/5.0/en/grant.html


GRANT EXECUTE ON PROCEDURE tearoom_data.SelectedStyle TO 'tearoom_admin'@'%';

Open in new window

0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question