CODEWITHSUNDEEP
mysqlstored-procedures
Dmitry Makovetskiyd
Dmitry Makovetskiyd

Reputation: 7053

Stored procedure privilege error

I tried to execute this stored procedure:

DELIMITER |
DROP PROCEDURE IF EXISTS HelloWorld|
CREATE PROCEDURE HelloWorld()
BEGIN
UPDATE sites_niche SET `short_review` = REPLACE(`short_review`, '’',"'");
END |
DELIMITER ;

However, it tells me this:

Access denied for user 'bestcam728'@'%' to database 'bestcamdir_main'

The problem is that I am already logged into the database.. that error prevents me from creating stored procedures... how do I overcome this? Also there is a problem of not being able to create new users

Upvotes: 1

Views: 3605

Answers (3)

Devart
Devart

Reputation: 121922

Run SHOW GRANTS command to view all privileges user 'bestcam728'@'%' has.

To grant privileges to 'bestcam728'@'%' connect as root and give privileges you need using GRANT statement. It seems that CREATE or CREATE ROUTINE privilege is missing. You can grant these privileges globally or for specific database -

-- Grant database privileges
GRANT CREATE ON `bestcamdir_main`.* TO 'bestcam728'@'%';
GRANT CREATE ROUTINE ON `bestcamdir_main`.* TO 'bestcam728'@'%';

-- Grant global privileges
GRANT CREATE ON *.* TO 'bestcam728'@'%';
GRANT CREATE ROUTINE ON *.* TO 'bestcam728'@'%';

Then reconnect as 'bestcam728'@'%' and try to create procedure again.

Upvotes: 3

Andy Thompson
Andy Thompson

Reputation: 304

Check permissions in mysql.db and potentially mysql.user.

Upvotes: 1

Ed Heal
Ed Heal

Reputation: 59997

Use the workbench and log in as root. You can then grant the correct privileges for the user to create stored procedures.

Upvotes: 0

Related Questions