Can't create stored proc on remote MySQL 5.0 database

Posted on 2007-07-20
Last Modified: 2008-01-09
I am having trouble createing a stored procedure on a remote database hosted by my ISP and am getting the following error:

Access denied for user 'ABCDEF'@'%' to database 'test_db'

I can take the following script and run it locally just fine.  I'm assuming it's permissions, but the database managment/configuration tool provided by my ISP for setting up MySQL does not allow me to change permissions.

   use 'test_db;
       SELECT PLAN_ID   FROM 'test_db.PLAN;
   END $$

I'm new to MySql and this the first time trying to deploy to my hosting provider.
Tampa, FL
Question by:VBert
    LVL 9

    Accepted Solution

    Hi Ray,

    It does indeed sound like a permissions issue.  You need to be able to grant CREATE ROUTINE and ALTER ROUTINE to your user ABCDEF in order to be able to add procedures/functions.

    Is ABCDEF your main MySQL account, or do you have another account that has ALL permissions on the database?  Also, what configuration tool do you have deployed?
    LVL 1

    Author Comment

    Hey Rurne,

    I tried GRANT CREATE ROUTINE ON 'test_db'.* TO 'ABCDEF'@%';  But I get the error "SQL Error: Access denied for user "ABCDEF'@'%' to database 'test_db'.  It is definitely a permissions error.  If I do a SHOW GRANTS for ABCDEF; it does not show the CREATE/ALTER ROUTINE permission.  My problem is that this database is hosted by and their Database Manager is less that desirable.  It allows me to create users, but not grant permissions. In fact, it does not even allow you to execute scripts so I've been connecting with both MySql Administrator and HeidiSQL.  Both give me the SQL Error mentioned above.  I've called their tech support and they are escaling the issue.  Very strange.
    LVL 4

    Assisted Solution

    Hey Ray,

    Some hosting providers (especially the low-cost ones) are in a shared-hosting environment. Please verify with them if they allow mysql remote connection using SSH, or better yet run the mysql query in their provided phpmyadmin software in your control panel.

    LVL 1

    Author Comment


    This hosting provider does not even include phpMyAdmin in their control panel.  I can however connect remotely via HeidiSql and MySql Administrator.  It is definitely a grants thing.  If I do SHOW GRANTS FOR [Acct]; I can see that the CREATE ROUTINE/ALTER ROUTINE grants are not there.  I escaled this to their tech support and I just received email notification that they cannot grant administrative rights to the database.  No kidding.  I just want the above grants.  Their support is clueless when I talk to them on the phone and most don't even know what a stored procedure is.

    This is my first time deploying a MySQL database and also my first time deploying to a server with a hosting company.  Is it common for low cost hosting providers to not allow the creation of stored procedures?  Again, they are allowing remote access.  

    LVL 9

    Expert Comment

    I've yet to experience a hosting provider that would allow remote access and general data-defniition rights without allowing procedures as well.  It's not as if you can obtain access to data for which you didn't already have privileges.  

    You may want to get your ticket escalated to someone who handles reprovisioning, as they generally can tell you why you are not allowed to run stored procedures.  General helpdesk support can't do much past a script and a few automated tools, but deployment and reprovisioning are two departments that will get you where you need to be.
    LVL 1

    Author Comment

    After multiple calls and escalation to their Tier 2 support, the hosting provider came back and finally admitted to me that they do not allow stored procedures to be created in their MySql databases.  Not sure I understand the security implications why, but there is no changing their policy.  Thanks for all the input.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
    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…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    794 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

    16 Experts available now in Live!

    Get 1:1 Help Now