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 |
|
1fred
Posting Yak Master
158 Posts |
 Posted - 2002-12-11 : 10:56:09
|
| This disallow users to delete anything from my tables, I made triggers on delete rollback transaction. But this trigger does not works if a user type the truncate table statement. Any idea how to solve this security hole in my database? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-12-11 : 11:34:31
|
| TRUNCATE TABLE is only available to db_owner, db_ddladmin, and sysadmin roles. Make sure the users do not belong to any of these roles. |
 |
|
|
1fred
Posting Yak Master
158 Posts |
Posted - 2002-12-11 : 11:46:29
|
| What I wanted to do is a security if one of those users is making a mistake. Restore the DB with the backup is a solution but some work time is waste. Is there a way to disable the truncate command? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-12-11 : 12:13:32
|
| Nope.The only way to enforce security is through permissions. Even a trigger like the one you have is no guarantee...someone could run ALTER TABLE...DISABLE TRIGGER. Frankly, if you're expressing concern about this then you absolutely should NOT allow anyone the kind of permissions that can cause this problem. Security should always start from the ground up, not the top down. Grant them NOTHING at first, then add each bit they NEED after review.Best thing you can do is evaluate the needs of the users and provide a method for them to accomplish it through a controlled avenue. Granting them full access to the base tables will never fully allow you to control what they do. You can always create views that SELECT from the table and revoke permissions on the table itself. DELETEs should be done through stored procedures. Failing that, if you have SQL 2000, grant DELETE permissions on the views and add INSTEAD OF triggers to intervene if/when needed. |
|
|
|
|
|
|
Table Security