Re: Assign permissions to allow updates but deny select on table

Tanzen (aaron.nasby@xxxxxxxxx) writes:
I have a table that I want to deny users to select, but allow inserts,
updates and deletes. So in the permissions for that table, I have
given the security Group Update, Insert and Delete rights and Denied
Select access. I connect to the table via odbc and have been able to
get the Insert queries working fine. However, my attempts to run
Update or Delete queries continually produce errors.

Here is a sample query I'm trying to run
DELETE tblPasswordsforDirector.PassMasterID
FROM tblPasswordsforDirector
WHERE (((tblPasswordsforDirector.PassMasterID)=339));

Here is a summary of the odbc error I get:
Select Permission denied on column 'password' of object
tblPasswordsforDirector.

In troubleshooting, I tried to use the COLUMN permissions to give
Select access to the two columns I don't care if the users see, and
then Deny Select access on the two columns I don't want them to see
(one of which happens to be the 'password' column shown in error
above).

I'm not sure what you did, but this appears to work as you want:

CREATE LOGIN Julle WITH PASSWORD = 'Vetebulle'
go
CREATE USER Julle
go
CREATE TABLE spezial (a int NOT NULL,
secret varchar(23) NOT NULL)
go
GRANT DELETE, INSERT, UPDATE, SELECT ON spezial TO Julle
go
DENY SELECT ON spezial (secret) TO Julle
go
INSERT spezial (a, secret) VALUES (1, ')(2344')
INSERT spezial (a, secret) VALUES (13, '25344')
go
EXECUTE AS LOGIN = 'Julle'
go
DELETE spezial WHERE a = 13
go
REVERT
go
DROP TABLE spezial
DROP USER Julle
DROP LOGIN Julle


I tested this on SQL 2005. I also ran a similar script on SQL 2000.

Could you post a repro that demonstrates the problem?



--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages