Re: SQL 2000 Sproc Role security bypassed when using QBF?



I should add that the dynamically built Varchar is run using the syntax
"Execute(@theVCstring)".

I am aware I can reduce my admin by adding to the existing role, or creating
an additional new role that manages the direct table select permissions (I
don't need or want to let the app use db_datareader), but I wonder why the
EXECUTE function requires the additional SELECT permission that sprocs
normally inherit?




"Alec MacLean" <alec.maclean@xxxxxxxxxxxxxxxxxxx> wrote in message
news:ewOnPGkwHHA.3444@xxxxxxxxxxxxxxxxxxxxxxx
Hi,

Platform: SQL Server 2000 SP4, VB.NET

I've got a Query By Form (QBF) sproc that is used in a search process by
one of our internal business apps.
All sprocs for this application have access controlled by membership to a
custom Role.
Membership to the Role is via a fixed SQL user account, which is part of
the application's connection string (I'm using the MS-P&P Enterprise
Library DAAB mechanisms).

The QBF code suits the application's search requirements nicely, but I am
concerned over the additional security permissions it requires beyond a
normal SPROC.

Essentially, the QBF syntax (basically, a query built dynamically as a
varchar in the sproc, according to variable input parameters) seems to
cause the SPROC to ignore the normal permission mechanism. It requires me
to set a SELECT permission for the USER instead of just EXECUTE on the
sproc.

Does anyone have some guidance/tips they could provide me with about this
aspect?
E.g.
- Is this approach deprecated in favour of a better technique?
- Is it bad practice to use this approach?

I'd prefer not to have to set the additional SELECT permission directly on
a set of tables if I can avoid it.

Thanks.

Al



.