4

I am logging in with my main DB user, into Phpmyadmin page/ workbench remote access application and I have permissions issues.

It all started when I tried to alter routines that I have stored in the DB. when trying to alter those routines from the workbench applications nothing just happens. I can call these routines and execute them, but not alter or get to the scripts.

I searched for hours in distinct forums and get some answers regarding grant access commands but then I got again permissions issues with error #1142 , command denied to user(main user).

I am really lost here, and already lost hours of work in order to get to the scripts of my routines.

one last note - I have created these routines while I was connected with the same user but from different remote connection (different IP address).

Would really appreciate the help.

Mayur Birari
  • 5,837
  • 8
  • 34
  • 61
user1870247
  • 41
  • 1
  • 2
  • 1
    Permissions are not granted to a user, but to a user/hostname combination. Chances are permission to alter the routines is only granted to your user at the original location and not at your current one. – eggyal Dec 02 '12 at 12:24
  • Hi Mayur, thanks for your replay the thing is, i cannot longer be at the same location I was when created those routines, isn't any other master user who should be able to generate the script ? – user1870247 Dec 02 '12 at 15:16

2 Answers2

2

here is a solution how I fixed this:

1) Add "mysql" database to the user, you are logged in with

Advice: now you can alter functions and procedures

2) Add the global privilege "SUPER" to your user

Advice: otherwise you will get the following error if you save the procedure/function: "ERROR 1227: Access denied; you need (at least one of) the SUPER privilege(s) for this operation"

Tobias Bambullis
  • 736
  • 5
  • 17
  • 45
  • To read more about **SUPER**, refer [this](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_super). Need the SUPER privilege to create or alter stored functions if binary logging is enabled – Himalaya Garg Aug 12 '21 at 07:10
0

CREATE DEFINER = 'admin'@'localhost' PROCEDURE account_count() SQL SECURITY INVOKER BEGIN SELECT 'Number of accounts:', COUNT(*) FROM mysql.user; END;

See the above example.

You need to login using super user and change the definer parameter in the procedure based on your new username and hostname. The same definer who created can edit the stored procedure.

Rajeesh V
  • 402
  • 5
  • 19