DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string
From: Dave (davena@my-deja.com)
Date: 07/09/02
- Next message: Narayana Vyas Kondreddi: "Re: DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string"
- Previous message: Richard Waymire [MS]: "Re: SQL Audit"
- Next in thread: Narayana Vyas Kondreddi: "Re: DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string"
- Reply: Narayana Vyas Kondreddi: "Re: DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string"
- Reply: Russell Fields: "Re: DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string"
- Reply: Bottomless Pit: "Re: DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: davena@my-deja.com (Dave) Date: 9 Jul 2002 10:04:36 -0700
I am trying to prevent access to a SQL Server 2000 database from other
than our web app. Our users have a long history of linking tables
from Access and other such tools, but in this particular database
there is some legally confidential data, such as names of kids up for
adoption.
I have a Windows group named CPS_ILP. I have a server group named the
same [qa-web-1\cps_ilp]. I wrote a script that GRANTS EXECUTE to
every stored procedure in the database to [qa-web-1\cps_ilp] and DENY
SELECT, INSERT, UPDATE, DELETE on all tables TO [qa-web-1\cps_ilp].
So members of the cps_ilp global group are able to run our web app
which executes stored procs but are not able to get anything with
query analyzer or Access. The trouble we are having is that the
programmer EXECUTEs a string dynamically to get data out of tables,
like so:
SET @Str = 'SELECT Name, Address, Phone FROM tblHousing'
EXECUTE @Str
Access is denied because SQL Server thinks we are SELECTing directly.
There is a statement somewhere earlier on usenet that says that if we
were using static statements in our stored procs instead of EXECUTE,
we wouldn't be having this problem. It doesn't say why or give a
workaround. We really need to be able to EXECUTE like we are now.
Any ideas how we can accomplish this? Thanks, Dave
- Next message: Narayana Vyas Kondreddi: "Re: DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string"
- Previous message: Richard Waymire [MS]: "Re: SQL Audit"
- Next in thread: Narayana Vyas Kondreddi: "Re: DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string"
- Reply: Narayana Vyas Kondreddi: "Re: DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string"
- Reply: Russell Fields: "Re: DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string"
- Reply: Bottomless Pit: "Re: DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
