Timeline for SQL Server: How do you prevent data being modified through an exposed view?
Current License: CC BY-SA 3.0
13 events
when toggle format | what | by | license | comment | |
---|---|---|---|---|---|
Dec 10, 2016 at 13:24 | vote | accept | Zach Smith | ||
Dec 9, 2016 at 12:52 | comment | added | Dan Guzman | @TaraKizer, have you considered cross-database ownership chaining? That would allow you to grant SELECT permissions on the view with no granted table permissions for view users. The tables will be editable only for users that have direct table permissions. | |
Dec 8, 2016 at 22:11 | comment | added | SQLDevDBA | @TaraKizer I already do it via db_denydatareader and db_denydatawriter. Whatever user I give those to can't see any objects at all in a Database. Once I grant them specific SELECT on an object, then they're able to see the object in the Object Browser and Select from it. Just wanted to see if there were any other methods of doing so. Thanks for all your contributions! | |
Dec 8, 2016 at 22:00 | comment | added | Tara Kizer | @MguerraTorres I'm not sure, but I don't think that's possible. | |
Dec 8, 2016 at 21:51 | comment | added | SQLDevDBA | Nice, thanks. What if I don't want them to even know the other objects exist (via the Object Explorer or Information_Schema). Is deny the only way? | |
Dec 8, 2016 at 21:49 | comment | added | Tara Kizer | @MguerraTorres, you could do that, but I just grant the needed permissions and then use deny as necessary. | |
Dec 8, 2016 at 20:53 | comment | added | SQLDevDBA | (OhEmGee It's the great @TaraKizer). Related question: Is my SOP of Creating a reporting or application user with 'db_denydatareader' and 'db_denydatawriter' and only giving them CRUD (SELECT/UPDATE/DELETE) as needed on specific objects correct? It's basically my way of trying to lock down as much as possible. Any thoughts? | |
Dec 8, 2016 at 20:18 | comment | added | Tara Kizer | You could try a trigger where it will rollback with transaction if it's initiated from a remote database. I don't have the code for that though. | |
Dec 8, 2016 at 19:56 | comment | added | Zach Smith |
I want the tables in DB 1 to be editable only when accessed as that database (with a user who has permissions on that database). I want views to be select only for every user on DB 2 (I.e. no UPDATE, INSERT, DELETE, etc). However the view itself needs to be alterable by the same users that cannot update data through that view
|
|
Dec 8, 2016 at 19:52 | comment | added | Tara Kizer | @ZachSmith, but who needs those update permissions on the tables? And who needs the access denied? | |
Dec 8, 2016 at 19:50 | history | edited | Tara Kizer | CC BY-SA 3.0 |
added 197 characters in body
|
Dec 8, 2016 at 19:50 | comment | added | Zach Smith | The underlying tables (in a separate database) need to update permissions as though. Can i grant SELECT permission to just the view? | |
Dec 8, 2016 at 19:42 | history | answered | Tara Kizer | CC BY-SA 3.0 |