Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2010
    Posts
    5

    Unanswered: create proc with execute as

    What do you think will happen when you do this?

    The dbo creates a stored procedure with execute as caller option. Say, the stored procedure deletes some records from a table.

    There is a user in that database and all he has is an execute permission on the above stored procedure. He does not belong to any fixed database roles, so no read, no write, nothing. Only execute on the stored proc.

    What do you think will happen when the user executes that stored procedure. Obviously, he will be able to execute it because he has the execute permission. But do you expect the user to encounter any permission issues when the stored proc tries to delete from the table? Remember, the user calling the stored proc does not belong to db_datawriter role.

    I did this test and found that the user was ABLE to delete, eventhough the procedure was created with 'execute as caller' option. Does this make sense to anyone?

  2. #2
    Join Date
    Oct 2010
    Posts
    5
    If you want to try this, please follow this example. Am I wrong to expect the caller of this stored proc to not be able to delete the data?


    -- create a dummy table and insert something.
    create table A (col int)
    insert into A values (1)

    -- verify the data
    select * from A

    -- now create a proc to delete from table A
    -- note the execute as option
    create proc delete_A with execute as caller
    as begin
    delete from A
    end

    -- create a dummy user and map him to the DB where the procedure exists.
    -- and give him execute permission on that procedure
    -- do not give him any fixed database roles, so the only thing he can do is run the procedure
    -- log in as that dummy user and run the procedure.
    -- what do you expect will happen?
    execute delete_A

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This behavior seems peculiar if you take "EXECUTE AS CALLER" literally, without considering the ownership chains.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Nov 2010
    Posts
    1
    Thanks for the info

  5. #5
    Join Date
    Oct 2010
    Posts
    5
    Quote Originally Posted by Pat Phelan View Post
    This behavior seems peculiar if you take "EXECUTE AS CALLER" literally, without considering the ownership chains.

    -PatP
    Ok, so I think I get the ownership chain concept. But it looks like I need to read more about object ownership in detail.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Like many things with computers in general, the devil is in the details!

    As a side note, welcome to DBForums! This certainly looks like a class assignment, but whether it is or is not you approached the problem well. You constructed a reasonable test, ran that test, and then published what you'd done and asked for comment. All too often we see people post what is clearly an assignment with the expectation that we'll simply do it for them. I like your approach a lot better!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •