Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 5 down vote accepted

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.

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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