Solved

MySQL Stored Procedure access problem

Posted on 2008-10-09
9
998 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]
Comment Utility
could you please clarify where exactly the error occurs, ie when doing what...
0
 

Author Comment

by:dragboatrandy
Comment Utility
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]
Comment Utility
>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
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Congrats on your eliteness AngelIII!
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

763 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

7 Experts available now in Live!

Get 1:1 Help Now