Post reply

Grant, Revoke, Deny

  • December 20, 2005 at 6:41 am

    #89083

    Hello,

    Please can someone give me a succinct description of how the above works.  My problem is that in the past we have had stored procedures which do not have any permissions set (i.e. the boxes are blank, no green tick or red cross) and which work fine.  We have now moved to a new content management system on our intranet, and I am now having to expressly grant permissions to stored procedures (i.e green tick in exec box).  I don't understand why, but I'd like to know what the default is.  If there is no check mark in the box is the user allowed to execute the stored proc by default, or should they be denied access by default?  I was always under the impression that a blank meant allow unless they were denied elsewhere.

    Thanks for any help.

    Paula.

  • December 20, 2005 at 7:15 am

    #610561

    GRANT = User or role has been explicitly given access.

    DENY = User or role has been explicitly denied access. This trumps any GRANT.

    REVOKE = This is the "undo." It will remove either a GRANT or DENY.

    In the past there are a couple of reasonable explanations. One, access was granted through a different user or role (different user in the case of a separate user mapping to a separate Windows group, for instance). Don't forget about the public role, which all users are a member of, for this case. Two, the user coming in was mapping as dbo, which doesn't do permission checks.

    By default, unless the user is aliased to dbo, a permissions check will occur on the stored procedure. In that case, the user needs a GRANT somewhere along the way (the green check). Now, if a stored procedure calls another object, so long as both the stored procedure and the second object have the same owner, no additional check is required. This is ownership chaining and explains why when you grant access to the stored procedure but not the table it references, everything still works. However, ownership must be the same on both objects.

    K. Brian Kelley
    @kbriankelley

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

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