Sorry if this is the wrong place to ask this question.

I have created a function that inserts values into many tables, and I want the web front-end to execute this function. I have granted execute permission to the web front-end's login role, but I do not want the web front-end code to be able to insert values directly into the tables, I want the web front-end to use only the function that I have created for this purpose.

If I grant execute permission for the web front-end's login role but I do not grant insert permissions on the tables that are inserted into within the function, then an access error occurs.

How can I go about this?


You have to have another role, that has privileges to write directly to tables. Then you create the function using this another role, and add to function definition "SECURITY DEFINER" clause. And then you grant execute on this function to your web role.

You can read more about it in documentation.

You might also want to check blog post that I once wrote about securing database.

  • Thanks for that information. Now I just have to make doubly sure that these functions are safe for 'crafted input' :) – dreamlax Sep 21 '09 at 5:12
  • As long as you don't do "EXECUTE" in them - you should be fine. – user13185 Sep 21 '09 at 11:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.