I'm trying to create a function in MySQL 5.5 which will encrypt any field passed to it. The final function will be more complex than this, but this is my first attempt:
CREATE DEFINER=`myaccount`@`localhost` FUNCTION `Enc1`(`IN` VARCHAR(255)) RETURNS VARBINARY(255) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER RETURN AES_ENCRYPT(firstname, 'mykey')
If I then run this SQL command:
mysql> SELECT Enc1(firstname) FROM person WHERE id = 1;
I get this error:
ERROR 1370 (42000): execute command denied to user 'myaccount_user'@'localhost' for routine 'myaccount_mydb.Enc1'
What am I doing wrong?
Or should I be using a procedure?