Re: DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string
From: Russell Fields (rlfields@sprynet.com)
Date: 07/09/02
- Next message: kenneth r philp: "Security and encryption"
- Previous message: Narayana Vyas Kondreddi: "Re: DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string"
- In reply to: Dave: "DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string"
- Next in thread: Bottomless Pit: "Re: DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: "Russell Fields" <rlfields@sprynet.com> Date: Tue, 9 Jul 2002 13:43:44 -0400
Dave,
Once you go to dynamic SQL, you are outside the ownership / security context
of the stored procedure.
Essentially, you have issued a new command, "Select ... from Table" that now
must have its security context evaluated. It is evaluated and the person
who issued the query does not have rights to the table, so it fails.
There is no dodge to turn this off.
Options? Yes, but they require work.
1. If you really "need" dynamic SQL you could establish a middle-tier
component from your web app (even a scripted component) that can be
registered with its own domain login. (Read up on COM+ if this is not
familiar.) That login should be granted rights to the table so that it
would be able to execute dynamic SQL.
Now, rewrite your app to submit dynamic SQL through the middle-tier that has
access.
2. Find a way to eliminate the dynamic SQL. Look at what is being done and
see if it can be managed in stored procedures, even if they are complex to
write. This is usually possible, but it takes some thought. It would
resolve the security problem.
Russell Fields
PS - On the strange front, you could encrypt the confidential data and make
sure that your application is the only one that can decrypt it. Then you
could grant table level access to encrypted data. I don't recommend this.
"Dave" <davena@my-deja.com> wrote in message
news:38ba16eb.0207090904.6427cf70@posting.google.com...
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: kenneth r philp: "Security and encryption"
- Previous message: Narayana Vyas Kondreddi: "Re: DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string"
- In reply to: Dave: "DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string"
- Next in thread: Bottomless Pit: "Re: DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
