| Author |
Topic |
|
steamngn
Constraint Violating Yak Guru
306 Posts |
 Posted - 2006-10-13 : 16:00:27
|
| Man,one thing after another. I have an issue where I need to DENY DELETE on a table. The app that uses this DB does not have any users added to SQL server, so I am assuming (maybe incorrectly) that the app logs in using sa. I know, scary thought. Anyway, could this be done with a simple trigger? Any bad effects from this?AndyThere's never enough time to type code right, but always enough time for a hotfix... |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-10-13 : 16:11:06
|
| You can use an INSTEAD OF DELETE trigger.It will not prevent a TRUNCATE.CODO ERGO SUM |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-10-13 : 16:19:06
|
| Create a delete trigger, and have ROLLBACK TRAN as the first statement. I can't imagine why you wouldn't know what the application is using to connect to the database. Can't you check via sp_who?Tara Kizer |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-10-13 : 16:58:30
|
Something like this should do it.create trigger [dbo].[TR_PREVENT_DELETE_FROM_MY_TABLE]on [dbo].[MY_TABLE]instead of deleteasset nocount ondeclare @ERROR_MESSAGE varchar(400)select @ERROR_MESSAGE = 'Trigger TR_PREVENT_DELETE_FROM_MY_TABLE - '+ 'Delete not allowed on table [dbo].[MY_TABLE]'raiserror( @ERROR_MESSAGE, 16, 1 )if @@trancount > 0 begin rollback endreturngo CODO ERGO SUM |
|
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2006-10-14 : 08:12:48
|
| hi Gang!Right down the list:Brett: I tried 'DENY DELETE' for all users(including sa) on the table, but the app (delphi code) still allowed deletion of records. I seem to recall reading somewhere that denying isn't always explicit, or that it can be overridden by other permissions. At any rate this didn't work (Damn!)Tara: Motherhood; Congratulations! I did run sp_who, and I only saw one login for 'sa' and the login for myself. At the time there was 20 users logged in through the app, plus myself logged in to QA as user 1. This confused me; wouldn't/shouldn't each workstation show a login even if it is the same user?MVJ: Thank you so much for the piece of code. This will at least stop the deletions until I can answer the login issue. I emailed the vendor to find out what I should be seeing for logins, but I will not hold my breath waiting for an answer.To All:THANKS!!Andyupdate 10/14/2006 9:54AM:I got an answer from the vendor re: not seeing the logins. The app is indeed installed to log in using 'sa', which is wrong. apparently from the dealer right from day one. There is also a service pack that addresses the login issue. They have agreed to come out and repair the install, even though it is several years old. Nice. I will let you know what happens...Have a nice weekend! 10/14/2006 3:20 PM:Ok,file this under 'I just don't get it.' The vendor downloaded and installed the service pack. Now there is an individual login for each workstation, and yes, it is 'sa'. Why would it show only one login before???There's never enough time to type code right, but always enough time for a hotfix... |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-10-15 : 01:52:04
|
| As I said before, the trigger will not prevent users logged in as SA from truncating the table.Also, nothing would prevent a user logged in as SA from disabling the trigger and then running a delete. Or dropping the trigger, table, or database.Is there some reason this application cannot use another login? My experience is that many software vendors are relatively clueless about database security. I found that a vendor had installed a building security system at one of our sites where access was controlled by information in an MSDE database. The application connects to the database with SA, and the SA password is blank!CODO ERGO SUM |
|
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2006-10-15 : 11:54:34
|
quote:
My experience is that many software vendors are relatively clueless about database security.
That's the answer!I was really flustered when I did not see a login for each workstation (Still learning, but I KNEW something wasn't right), and now that the service pack is in, I'm flustered even more. sure enough, a login for each workstation. Sure enough, sa. And sure enough, BLANK PASSWORD!!! Unreal. I called the dealer, who told me there was no way to modify this. I called the Vendor, and they let me download the DB utility they run during initial install. I went in today (Sunday; how nice), and did some digging. The dealer had their head you-know-where. The setup utility asks all of the standard SQL and ODBC type questions: use an SQL login, use integrated windows security, etc...So, I added a login for each domain user, gave them public, datareader, and datawriter permissions, and reset the app to connect accordingly. Works like a charm. Now I will work on granting and denying delete to each user as needed, and drop the trigger. Here's a question:Is there an easy way to programatically set datareader and datawriter values for many users at once? I'm sure I'm looking right at a solution, but I don't see it...AndyThere's never enough time to type code right, but always enough time for a hotfix... |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-10-15 : 19:37:25
|
| I would stay away from granting datareader and datawriter to users.Create a database role that is used to grant access, and add the database users to that role. Then grant permissions on the individual database objects to that role. Then you can restrict access to individual database objects the way you want.Do not grant object permissions directly to any database user. If you need to grant specific permissions to specific users, create a new role for that, add the users to that role, and grant the needed object access to that role.CODO ERGO SUM |
|
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2006-10-16 : 08:37:55
|
| Oh, Ok...So the idea is to use a role for each specific set of permissions. Makes sense: Then we can make changes to the role and that affects all users at once...Excellent tip. That will be this weeks project!Thanks Mike, This will go a long way. Any good articles you can suggest on security?AndyThere's never enough time to type code right, but always enough time for a hotfix... |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-16 : 08:43:21
|
| You could have used a domain group instead. Put all authorized users in the group and add the group to the database. DENY DELETE for the group.This is what we do. It is easier to let network group handle members for authorized users, and I only handle the group's rights in the database.Peter LarssonHelsingborg, Sweden |
|
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2006-10-16 : 12:11:10
|
| Hey Peso,Good idea. I haven't gotten back to it yet today, but I will during the week. Something tells me once I get things sorted out there will be several groups needed, so I am trying to get input from the rest of management before I start. I'll keep everyone posted!AndyThere's never enough time to type code right, but always enough time for a hotfix... |
|
|
|
|
Trigger to prevent DELETE of records