Assign permissions to allow updates but deny select on table

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 guess I'm a little confused at why a DELETE query requires Select
permissions. And if it does, then why does SQL give you the ability to
Allow DELETE permissions and Deny SELECT permissions on the same
table?
Any help would be most appreciated.
Thanks in advance.Tanzen (aaron.nasby@.gmail.com) 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@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Some of the commands you show in your example don't seem to work for
me. I am running SQL 2000. Forgive my ignorance, but I'm not sure what
those commands should be in SQL 2000|||Tanzen (aaron.nasby@.gmail.com) writes:
> Some of the commands you show in your example don't seem to work for
> me. I am running SQL 2000. Forgive my ignorance, but I'm not sure what
> those commands should be in SQL 2000
I hope you've now learnt that you should always say which version of SQL
Server you are using. :-)
Below is the SQL 2000 version of the script that I posted. By the way,
you did not include the full text of your error message.
sp_addlogin Julle, 'Vetebulle'
go
sp_adduser 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
SETUSER 'Julle'
go
DELETE spezial WHERE a = 13
go
SETUSER
go
DROP TABLE spezial
EXEC sp_dropuser Julle
EXEC sp_droplogin Julle
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Jul 11, 3:24 pm, Erland Sommarskog <esq...@.sommarskog.se> wrote:
> Tanzen (aaron.na...@.gmail.com) writes:
> I hope you've now learnt that you should always say which version of SQL
> Server you are using. :-)
> Below is the SQL 2000 version of the script that I posted. By the way,
> you did not include the full text of your error message.
> sp_addlogin Julle, 'Vetebulle'
> go
> sp_adduser 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
> SETUSER 'Julle'
> go
> DELETE spezial WHERE a = 13
> go
> SETUSER
> go
> DROP TABLE spezial
> EXEC sp_dropuser Julle
> EXEC sp_droplogin Julle
> --
> Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodte
chnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousv
ersions/books.mspx
Your commands were very helpful. I was able to change the script for
my existing table and confirmed the security settings are working when
I use query analyzer. If I try to do a select queryI am denied
permission. If I run a Delete query, it succeeds.
I am using an MS Access 2003 database with an ODBC connection to the
tables to perform the same delete queries, but they fail. The error
when I run the delete query from MS Access 2003 is:
--
ODBC --call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server] Select permissio
n
denied on column 'password' of object 'tblPasswordsforDirector',
database 'Passwords_IT", owner 'dbo'. (#230) [Microsoft][ODBC SQL
Server Driver][SQL server]SELECT permission denied on column
'UserName' of object 'tblPasswordsforDirector', database
'Passwords_IT', owner 'dbo'. (#230)
--
I'm assuming I need to post this to an Access forum.
So unless you have any ideas, I'll move this on to another forum.
Thank you again for your help.|||Tanzen (aaron.nasby@.gmail.com) writes:
> I am using an MS Access 2003 database with an ODBC connection to the
> tables to perform the same delete queries, but they fail. The error
> when I run the delete query from MS Access 2003 is:
> --
> ODBC --call failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server] Select permiss
ion
> denied on column 'password' of object 'tblPasswordsforDirector',
> database 'Passwords_IT", owner 'dbo'. (#230) [Microsoft][ODBC SQL
> Server Driver][SQL server]SELECT permission denied on column
> 'UserName' of object 'tblPasswordsforDirector', database
> 'Passwords_IT', owner 'dbo'. (#230)
> --
> I'm assuming I need to post this to an Access forum.
Yes, they may be more helpful. But you could use SQL Server profiler
to see what statements that Access is submitting. Maybe it's better to
use a stored procedure instead. But the Access folks may know how tell
Access to do the right thing.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx