Solved

MySQL Stored Procedure access problem

Posted on 2008-10-09
9
1,007 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
Webinar: MongoDB® Index Types

Join Percona’s Senior Technical Services Engineer, Adamo Tonete as he presents “MongoDB Index Types, How, When and Where Should They be Used?” on Wednesday, July 12, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).

 

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 500 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

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

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

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

691 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