1

I have configured an Azure Database for MySQL and able to connect to it successfully. I am also able to create New Tables and run queries on those tables but when creating a stored procedure, I am getting below error:-

Er 1227. Access error Code: denied; you need (at least one of) the SUPER privilege(s) for this operation

Example procedures is tried is 

DELIMITER $$

CREATE DEFINER='user'@'server'

PROCEDURE `sp_getComplaintsByBranchCode`(
  in branchCode Varchar(50),
  in companyCode Varchar(50),
  in RowsPerPage INT, 
  in pageNumber INT
)
NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER
BEGIN
DECLARE pageFrom INT;
DECLARE rowLimit INT;
DECLARE companyid varchar(50);
DECLARE branchid varchar(50);
DECLARE offval INT;

SET pageFrom = pageNumber;
SET rowLimit = RowsPerPage;
SET branchid = branchCode;
SET companyid = companyCode;
SET offval = ((pageFrom-1) * rowLimit);

select * from complaints c
WHERE c.branchCode = branchid AND c.fb_companyID = companyid
ORDER BY added_on DESC limit rowLimit OFFSET offval;

END

I checked user permissions and the database admin user which got created during initial setup doesn't have super privileges. The only user with super privileges is "azure_superuser" and I don't have connection password for it. Please help me with below issues or point me to some references which can help:-

1) How to connect to 'azure_superuser' OR 2) How to grant superuser privileges to the admin user I have without login as root user. 3) How to enable a user with super_priviledge as 'N' to create stored procedures in Azure database for MySQL.

Some references:-

Stack Overflow Question

Github

Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
mohit mathur
  • 1,094
  • 1
  • 7
  • 10

1 Answers1

2

Please remove DEFINER clause from the statement. It should work.

The SUPER privilege is not supported on Azure Database for MySQL. The closest you can get is to create another admin level user with the same rights as the server admin you created to do this you can run:

CREATE USER 'testuser'@'%' <span>IDENTIFIED </span>BY 'your_password_here';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'testuser'@'%' WITH GRANT OPTION;
Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30