Solved

MySQL Stored Procedure access problem

Posted on 2008-10-09
9
999 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
  • 4
  • 4
9 Comments
 
LVL 142

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 142

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
 

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 142

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 50

Expert Comment

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

947 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now