1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

SQL permissions

Discussion in 'SQL Exams' started by Gaz 45, May 8, 2005.

  1. Gaz 45

    Gaz 45 Kilobyte Poster

    404
    4
    39
    Anyone help me with this? I'm struggling to define the difference between DENY and REVOKE permission clauses.
     
    Certifications: MCP (70-229, 70-228), MBioch
    WIP: MCDBA (70-290)
  2. Mitzs
    Honorary Member

    Mitzs Ducktape Goddess

    3,282
    73
    152


    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_2un9.asp

    One creates the denial, while the other removes the denial or granted permissions. Is this helping at all?
     
    Certifications: Microcomputers and network specialist.
    WIP: Adobe DW, PS
  3. Crito

    Crito Banned

    505
    14
    0
    This is a little tricky actually.

    I think of grant and deny as bit fields and the grant and deny statements as functions that just set the boolean flag the field represents to on/true. The tricky part is remembering that a set deny flag always overrides a grant. The revoke statement I think of as a reset or clear function. Calling it resets the grant and revoke flags back to 0 (off/false). So, the moral of story is: if you want to make sure a grant takes effect, always clear any deny flags with revoke first! ;)
     
    Certifications: A few
    WIP: none
  4. ffreeloader

    ffreeloader Terabyte Poster

    3,661
    106
    167
    A deny explicitly denys whatever access or permission that is being referenced. A revoke removes an existing access or permission whether it be a deny or allow.

    Say you have a user who belongs to two different groups. One is denied access to the database and on is permitted access to the database. The deny overrides the allow and the user is denied access. However, if the deny is revoked then the user is allowed access as the deny is no longer in effect.
     
    Certifications: MCSE, MCDBA, CCNA, A+
    WIP: LPIC 1
  5. Crito

    Crito Banned

    505
    14
    0
    Conceptually it's easy to fall into the trap of thinking of grant as turning a switch on and deny turning the same switch off, but that's not how it works. They're seperate switches. Grant and deny statements can only turn the switches on. To turn either off you must use revoke. If you can remeber that and the fact that deny always overrides grant, you should be OK.
     
    Certifications: A few
    WIP: none
  6. Gaz 45

    Gaz 45 Kilobyte Poster

    404
    4
    39
    Thanks all. Got it now. :biggrin

    Something of a Yes/No/Maybe situation!
    Grant = Yes
    Deny = No
    Revoke = Maybe (depends what the other permissions say).
     
    Certifications: MCP (70-229, 70-228), MBioch
    WIP: MCDBA (70-290)

Share This Page

Loading...