Grant, Revoke, Deny

Context: SQLServer 2000
I have a single table in the database that I want role "Apps" to have SELECT
access only, so using the UI, I find the table and check the SELECT column
for the table and choose the X (to deny) for INSERT, UPDATE, DELETE.
Next, I want to ensure that no one that has public rights to the database
can do anything with this table. First time through I put an X (to deny) for
all four permissions, which prevented everyone (including sa) from
selecting, etc. I found this to be 'by design' behavior so I using the Query
Analyzer to issue the REVOKE method on the table for all 4 items. sa was
then able to still do everything.
My problem is that anyone in the db_datawriter role (or db_datareader) can
still write (read) from the table. I am unable to reassign permissions to
special roles.
I guess I could not use the special role for my datareaders/writers but the
advantage of these roles is that I don't have to continuously go and grant
permissions. If this is my only option in this scenario, then I guess I will
have to go with it - I was just hoping there was something more elegant.
Thanks in advancePermissions are cumulative and DENY takes precedence. REVOKE removes GRANT
and DENY permissions previously assigned. Because all users are members of
public, you should DENY permissions to public only when you want to prohibit
access by all database users.
'sa' is a member of the sysadmin fixed server role and permissions are not
checked for sysadmin role members. I believe you are mistaken when you say
that you prevented 'sa' from accessing the table.
One method to allow Apps users to SELECT from the table and prevent other
'db_datareader' and 'db_datawriter' access:
1) GRANT SELECT ON YourTable TO Apps
2) DENY INSERT, UPDATE, DELETE ON YourTable TO Apps
3) create a new role to prevent access (e.g. 'DenyCertainTables')
4) DENY ALL ON YourTable TO DenyCertainTables
5) add all 'db_datareader' and 'db_datawriter' role members, except Apps
role members, to the 'DenyCertainTables' role.
Hope this helps.
Dan Guzman
SQL Server MVP
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:uqHH2W8tFHA.3528@.TK2MSFTNGP15.phx.gbl...
> Context: SQLServer 2000
> I have a single table in the database that I want role "Apps" to have
> SELECT
> access only, so using the UI, I find the table and check the SELECT column
> for the table and choose the X (to deny) for INSERT, UPDATE, DELETE.
> Next, I want to ensure that no one that has public rights to the database
> can do anything with this table. First time through I put an X (to deny)
> for
> all four permissions, which prevented everyone (including sa) from
> selecting, etc. I found this to be 'by design' behavior so I using the
> Query
> Analyzer to issue the REVOKE method on the table for all 4 items. sa was
> then able to still do everything.
> My problem is that anyone in the db_datawriter role (or db_datareader) can
> still write (read) from the table. I am unable to reassign permissions to
> special roles.
> I guess I could not use the special role for my datareaders/writers but
> the
> advantage of these roles is that I don't have to continuously go and grant
> permissions. If this is my only option in this scenario, then I guess I
> will
> have to go with it - I was just hoping there was something more elegant.
> Thanks in advance
>|||Thanks Dan,
I made a freshman mistake and when I thought the 'sa' couldn't access I was
in the wrong window.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ez9IfD9tFHA.2592@.TK2MSFTNGP09.phx.gbl...
> Permissions are cumulative and DENY takes precedence. REVOKE removes
GRANT
> and DENY permissions previously assigned. Because all users are members
of
> public, you should DENY permissions to public only when you want to
prohibit
> access by all database users.
> 'sa' is a member of the sysadmin fixed server role and permissions are not
> checked for sysadmin role members. I believe you are mistaken when you
say
> that you prevented 'sa' from accessing the table.
> One method to allow Apps users to SELECT from the table and prevent other
> 'db_datareader' and 'db_datawriter' access:
> 1) GRANT SELECT ON YourTable TO Apps
> 2) DENY INSERT, UPDATE, DELETE ON YourTable TO Apps
> 3) create a new role to prevent access (e.g. 'DenyCertainTables')
> 4) DENY ALL ON YourTable TO DenyCertainTables
> 5) add all 'db_datareader' and 'db_datawriter' role members, except Apps
> role members, to the 'DenyCertainTables' role.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
> news:uqHH2W8tFHA.3528@.TK2MSFTNGP15.phx.gbl...
column[vbcol=seagreen]
database[vbcol=seagreen]
can[vbcol=seagreen]
to[vbcol=seagreen]
grant[vbcol=seagreen]
>sql