Solved

Problem writing a MySQL Function

Posted on 2014-03-25
9
770 Views
Last Modified: 2014-03-28
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`@`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?

Thanks.
tel2
0
Comment
Question by:tel2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 24

Accepted Solution

by:
mankowitz earned 500 total points
ID: 39954960
you need to add the execute privilege for that account

GRANT EXECUTE ON PROCEDURE myaccount_mydb.Enc1 TO myaccount_user@localhost;
0
 
LVL 12

Author Comment

by:tel2
ID: 39954987
Thanks for that, mankowitz!

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_mydb.Enc1'
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
0
 
LVL 12

Author Comment

by:tel2
ID: 39957592
After some experimentation, I see that this:

    CREATE DEFINER=`myaccount`@`localhost` 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`@`localhost` 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`@`localhost` FUNCTION `Enc1`(`col` VARCHAR(255)) RETURNS VARBINARY(255) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER RETURN AES_ENCRYPT(col, 'mykey')
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 24

Expert Comment

by:mankowitz
ID: 39957834
ok. looks good.
0
 
LVL 12

Author Comment

by:tel2
ID: 39957846
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.)?
0
 
LVL 24

Assisted Solution

by:mankowitz
mankowitz earned 500 total points
ID: 39960653
it is deterministic because it will produce the same results every time if the same input is given.

functions become nondeterministic when you introduce random numbers or data from a changing table.
0
 
LVL 12

Author Comment

by:tel2
ID: 39960673
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
?
0
 
LVL 24

Assisted Solution

by:mankowitz
mankowitz earned 500 total points
ID: 39962115
What I meant about changing data was this: Suppose that you have a function that runs a query and bases its result on the query. The function would have a different result depending on the data supplied. Since the data could change, the function is nondeterministic.

The function can only be deterministic if the results will always be the same for a given set of inputs.

For example

-- This function always returns 1 and is therefore deterministic
CREATE FUNCTION f1 (a int) RETURNS int
RETURN 1;

-- This function is also deterministic
-- when given a particular input, the result is always the same.
-- i.e. if you supply 3 as an argument, the result is always 4.
CREATE FUNCTION f1 (a int) RETURNS int
RETURN  a+1;

-- This function is NOT deterministic because it depends on SQL data
CREATE FUNCTION f1 (a int) RETURNS int
RETURN  SELECT COUNT(*) FROM t;

see?
0
 
LVL 12

Author Comment

by:tel2
ID: 39962800
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.
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question