Re: ACL enforcement via SP or trigger
From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 11/15/05
- Next message: David J. Cartwright: "Permissions WTF"
- Previous message: zeplynne: "System.Data error '80131904'"
- Maybe in reply to: Dan Guzman: "Re: ACL enforcement via SP or trigger"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Mon, 14 Nov 2005 19:59:26 -0600
Triggers will fire if enabled. The exception is bulk insert, which requires
that the FIRE_TRIGGERS hint be specified in order for triggers to fire.
This probably isn't an issue in your case since access is strictly via
procs.
You are right that triggers are great for auditing/logging changes. I
understood from your initial post that you were looking at triggers for
securing data access and modification.
-- Hope this helps. Dan Guzman SQL Server MVP "Lee" <Lee@discussions.microsoft.com> wrote in message news:08E2AA76-DE7F-48C2-8890-526B328272CE@microsoft.com... > Dan - > > Thanks for the response. > > Your assumption is correct. I have no need to allow any access other than > through the SPs. I.e., no ad-hoc queries. I have since learned through a > MS > whitepaper about using views to support this functionality, if desired. > > FYI, this is a multi-user "secure items manager", AKA a password keeper. > So > each item has its own set of users that can view it. > > On another note, I did find a good use for triggers: logging. Whenever an > Ins,Upd,or Del is performed, the trigger logs the action in an event log > table. But are triggers 100% reliable to fire? I wasn't so sure - that's > actually why I posed the original question. I couldn't otherwise see why > triggers wouldn't be the best solution because of their lowest-level > integration. > > Thanks again! > Lee > > "Dan Guzman" wrote: > >> If I understand your question correctly, you have encapsulated your >> row-level security checking in a stored procedure. The proc determines >> whether or not a user is permitted to perform the requested function >> based >> on the specified data partitioning value. >> >> The main issue with this technique is that it makes set-based processing >> difficult because the security proc needs to be called for each distinct >> partitioning value. However, this won't be a problem if all data access >> in >> your app is done through procs that allow only a single partitioning >> value >> to be specified. I would execute the security proc in the parent procs. >> This will provide a consistent pattern for data retrieval as well as DML. >> >> >> -- >> Hope this helps. >> >> Dan Guzman >> SQL Server MVP >> >> "Lee" <Lee@discussions.microsoft.com> wrote in message >> news:56232657-1C3D-4704-98F4-E01576CBEA81@microsoft.com... >> > Greetings. >> > I have an app with the typical row-level security requirement. >> > >> > Supporting this is a reliable store proc. written that does ACL >> > checking >> > for >> > all other code. However, I am wondering if I should put the calls to >> > this >> > SP >> > in triggers or in the parent SPs. (We're already assuming there are no >> > user-level table permissions here.) >> > >> > What is best practice? >> > This scenario seems like it would be very commonplace, but I do not do >> > DB >> > stuff full-time and so look to ones that do! :) >> > >> > Thanks for any tips. >> >> >>
- Next message: David J. Cartwright: "Permissions WTF"
- Previous message: zeplynne: "System.Data error '80131904'"
- Maybe in reply to: Dan Guzman: "Re: ACL enforcement via SP or trigger"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
