SQL 2000 Sproc Role security bypassed when using QBF?

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


.

Relevant Pages

  • Re: SQL 2000 Sproc Role security bypassed when using QBF?
    ... I should add that the dynamically built Varchar is run using the syntax ... EXECUTE function requires the additional SELECT permission that sprocs ... I've got a Query By Form sproc that is used in a search process by ...
    (microsoft.public.sqlserver.security)
  • Re: Grant select permission on all Tables to a DB role
    ... The db_reader role has SELECT permission on all tables and views. ... Columnist, SQL Server Professional ... have select permission to any table until I grant permission to specific ... > There might be another way, but you could use the sp_msforeachtable sproc. ...
    (microsoft.public.sqlserver.security)
  • Re: Grant select permission on all Tables to a DB role
    ... Does db_reader role has select permission on all existing tables? ... > Columnist, SQL Server Professional ... > There might be another way, but you could use the sp_msforeachtable sproc. ... >> I am trying to figure out one single query will give a permission to all ...
    (microsoft.public.sqlserver.security)
  • Re: Grant select permission on all Tables to a DB role
    ... but you could use the sp_msforeachtable sproc. ... Will grant select on every table in the current database to my_role. ... > I am trying to figure out one single query will give a permission to all ... > roles and it is pain to run a query that give a permission on one table at ...
    (microsoft.public.sqlserver.security)