Solved

MySQL Stored Procedure access problem

Posted on 2008-10-09
9
1,001 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
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.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mysql sql statement - SQL INSERT INTO SELECT 11 65
sql select time convert to  time to   a   b mysql 4 60
html input clean up 3 54
MSSQL 2008 with mySQL webservers 7 51
Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
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 Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

770 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