Re: ACL enforcement via SP or trigger

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 11/14/05 

Date: Mon, 14 Nov 2005 07:02:42 -0600

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.