tel2
asked on
Problem writing a MySQL Function
Hi Experts,
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`@`local host` 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'@'localhos t' for routine 'myaccount_mydb.Enc1'
What am I doing wrong?
Or should I be using a procedure?
Thanks.
tel2
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`@`local
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'@'localhos
What am I doing wrong?
Or should I be using a procedure?
Thanks.
tel2
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
After some experimentation, I see that this:
CREATE DEFINER=`myaccount`@`local host` FUNCTION `Enc1`(`IN` VARCHAR(255)) RETURNS VARBINARY(255) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER RETURN AES_ENCRYPT(firstname, 'mykey')
should have been this:
CREATE DEFINER=`myaccount`@`local host` FUNCTION `Enc1`(`firstname` VARCHAR(255)) RETURNS VARBINARY(255) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER RETURN AES_ENCRYPT(firstname, 'mykey')
But it could be used on any column, so I might as well write it like this:
CREATE DEFINER=`myaccount`@`local host` FUNCTION `Enc1`(`col` VARCHAR(255)) RETURNS VARBINARY(255) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER RETURN AES_ENCRYPT(col, 'mykey')
CREATE DEFINER=`myaccount`@`local
should have been this:
CREATE DEFINER=`myaccount`@`local
But it could be used on any column, so I might as well write it like this:
CREATE DEFINER=`myaccount`@`local
ok. looks good.
ASKER
Thanks mankowitz.
From a quick look at the documentation, it looks as if this "DETERMINISTIC" / "NOT DETERMINISTIC" thing is just for performance. Would this function be considered to be deterministic, given that the same parameters could produce different results depending on the value of the column passed (i.e. if firstname is always passed as the parameter, then it could give one result for one row, and another result for another row.)?
From a quick look at the documentation, it looks as if this "DETERMINISTIC" / "NOT DETERMINISTIC" thing is just for performance. Would this function be considered to be deterministic, given that the same parameters could produce different results depending on the value of the column passed (i.e. if firstname is always passed as the parameter, then it could give one result for one row, and another result for another row.)?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks mankowitz.
When you say "changing data", do you mean:
1. For the duration of an job (e.g. a number of transactions which might run for a second or so) which uses the function, or
2. For many instances of the above
?
When you say "changing data", do you mean:
1. For the duration of an job (e.g. a number of transactions which might run for a second or so) which uses the function, or
2. For many instances of the above
?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for that, mankowitz. That's pretty clear.
The thing that was not clear to me before was, do those "inputs" include the data in the database. It seems they don't.
I found this regarding SQL Server. Might also apply to MySQL:
"Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database. Nondeterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same."
Source: http://technet.microsoft.com/en-us/library/ms178091.aspx
Seems to agree with what you have said.
So I guess that means my function will be not deterministic.
The thing that was not clear to me before was, do those "inputs" include the data in the database. It seems they don't.
I found this regarding SQL Server. Might also apply to MySQL:
"Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database. Nondeterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same."
Source: http://technet.microsoft.com/en-us/library/ms178091.aspx
Seems to agree with what you have said.
So I guess that means my function will be not deterministic.
ASKER
I'm using cPanel and phpMyAdmin on a shared hosting environment, and I tried your command via phpMyAdmin's "SQL" menu, like this:
GRANT EXECUTE ON PROCEDURE myaccount_mydb.Enc1 TO myaccount_user@localhost;
but got this error:
#1370 - grant command denied to user 'myaccount'@'localhost' for routine 'myaccount_mydb.myaccount_
So I tried this:
GRANT EXECUTE ON PROCEDURE Enc1 TO myaccount_user@localhost;
and got this error:
#1370 - grant command denied to user 'myaccount'@'localhost' for routine 'myaccount_mydb.Enc1'
Or should I have done:
GRANT EXECUTE ON FUNCTION ... ?
Got the same error for that.
But after some experimentation, I found that under cPanel > Databases, if I gave the user for that database "EXECUTE" permission, then reran my SELECT command:
SELECT Enc1(firstname) FROM person WHERE id = 1;
I then got this error:
ERROR 1054 (42S22): Unknown column 'firstname' in 'order clause'
I don't see any order clause. Any ideas?
Also, I'd like this command to be able to be run on any column, so how can I make it so that will work?
Thanks again.
tel2