Post reply

function inside a stored proc - is this a bug

  • May 13, 2010 at 8:20 am

    #221478

    Hi,

    Create a table function

    Deny a user the select right on the function – ie they can’t use it directly.

    Create a proc that encapsulates the function

    Allow the user permission to exec the proc

    If executed by the user, will the proc run the function and show the data?

    Yes it does!!!!!!!!

    Please explain

    (everything is owned by dbo)

  • May 13, 2010 at 9:22 am

    #1166995

    I don't think you understand the security model;

    it is very common, for example, that an end user does not have any rights to tables, and all the operations occur only via stored procedures. That's by design...

    the security model is if you grant them EXECUTE to a stored procedure, the proc can do anything it has been scripted to do to the underlying tables, , which might mean select/update/delete from tables the user has no access to, or call other functions the end user doesn't know exist, etc, etc, without having to check if the user has permissions to the objects or whatever it's going to fiddle with.

    so in your case, even if you explcitly DENY EXECUTE to a function on a specific user, if they can call a procedure which calls the function, it'll still be run.

    the user cannot call the function directly, but procs he has access to can call objects he has no direct access to.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 4, 2010 at 8:01 am

    #1176529

    Lowell (5/13/2010)

    the security model is if you grant them EXECUTE to a stored procedure, the proc can do anything it has been scripted to do to the underlying tables

    Almost correct, but you are forgetting about ownership chaining. If the procedure is owned by userA and the procedure selects from table1 and table2. table1 is owned by userA and table2 is owned by userB. userC is granted exec on the procedure. If userC does not have SELECT permissions on table2 the procedure will fail, because the owners of the procedure and table2 are different. userC will therefore need explicit SELECT permissions on table2 for the procedure to succeed.

    A more detailed explanation here: Ownership Chaining

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply