Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
 Posted - 2002-03-05 : 12:58:09
|
| Our security model is being shaken, and I'm looking for some advice. Here are the basics:We are most concerned about access from the web (ASP). We are running Windows 2000 Server with SQL 7 (migrating to SQL 2000 in a few weeks) and the web site is on Windows 2000 Server also (separate machine). The web server is in the DMZ, and the SQL Server is behind a firewall with a conduit mapped. We have a large web site with separate top-level folders for different departments. Each department may have its own database on the SQL Server because there is not much cross-over. Some databases are shared between departments/sites. There are about 25 databases in place right now. Currently, we are using a common SQL Server Login (mixed mode) for the web pages to use that is granted SELECT and EXECUTE permissions only. All Insert/Update/Delete statements must be done in sprocs. Typically, the connection string with username/password is stored in an ASP include file for the sub-site. This didn't seem to be much of an issue since we're behind the firewall and the user listed has limited permissions. There are individual administrator accounts and a few other application-specific logins with greater rights, but these are only used by apps inside the firewall. So far so good.Now, the shakeup... The decision was recently made, without investigating the impact on security (I dropped the ball here), to allow web developers to use a new tool called ASPGrid. It's a slick tool that makes a nice data-entry / edit grid. It allows you to call a sproc to populate the grid, but it appears that it does Insert/Update/Deletes on its own authority, and therefore needs more permissions than we have been granting. It appears to have a hook to call sprocs AFTER the Insert/Update/Delete takes place, but I can't see anything for "instead-of". I'd rather not create a SQL Login with full permissions. And I'd use up a lot of political influence to get this tool killed so I'd rather not, but if I have to...Any words of wisdom?------------------------GENERAL-ly speaking... |
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-03-05 : 13:49:04
|
| Well, as a kloodgy workaround you might be able to create a set of dummy tables for this control to work with. Once it does its thing, have it call the stored procedure that will actually grab the data from the dummy tables and apply it to the real ones...That's a pretty fragile and kloodgy approach though. Have you tried to contact the manufacturers of this ASPGrid thingy, to see if they can think of a way to call SP instead of going directly against the table?Edited by - izaltsman on 03/05/2002 13:49:48 |
 |
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2002-03-05 : 14:11:31
|
| Create a view that points at the table. You should be able to insert & update this view just fine. Point the data grid at the view. Create INSTEAD OF triggers for insert, update and delete that don't do anything. The data grid will think it did it's insert just fine but nothing will happen. If anyone "breaks into" this view they won't be able to make any data changes. Your call to the proc afterwords will do the actual data updates.===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-03-05 : 19:07:38
|
Ilya and Graz, thanks for the advice. I'll try to contact the company and while I'm waiting for a reply I'll test Graz's idea.Now, to give Rob a heart-attack... I just read a note that said the underlying table for this tool to work HAS TO HAVE an IDENTITY Primary key.  ------------------------GENERAL-ly speaking... |
|
|
|
|
|
|
|
|
Security Advice