We are trying to deploy SQL scripts through Appian's compare and deploy, but we ran into this error: Access denied; you need (at least one of) the SUPER, SET USER privilege(s) for this operation
The user who requested this package to be deployed has system admin permissions in the target environment, and I'm 99% sure they have system admin permissions in the source environment too. They weren't the ones who built the package itself though, but I'm not too sure if that matters here?
Does anyone know what this error is referring to?
Discussion posts and replies are publicly visible
Hi there, I think they need super privileges in DB then only they can execute the script.
Who needs super privilege? The person clicking on compare and deploy on the SQL package? And what part do they need super privilege on? Because the script has stored procedures or?
https://docs.appian.com/suite/help/23.3/appian-cloud-database-administration.html#editing-stored-procedures-or-function-fails
It says to not use definer, or make sure the value is dbadmin. Our scripts have a definer but it's dbadmin:
DELIMITER $$CREATE DEFINER=`dbadmin`@`%` PROCEDURE `XXXXX`()BEGINTRUNCATE TABLE `XXXXXX`;END$$DELIMITER ;
it also says: "In general, it is a best practice not to use the DEFINER clause when running scripts in phpMyAdmin."
True, since if the db admin changes, we don't need to update our scripts. But do you think having that definer clause is the cause of the compare and deploy error in the original question?
My gut feeling says: "Probably, let's try that first!"
I strictly keep definer clauses out of my SQL scripts and never had any issues.