Re: Views, Table Permissions and Stored Procedures

From: Robert Strickland (strickra_at_rjsonline.net)
Date: 10/28/03 

Date: Tue, 28 Oct 2003 12:50:24 -0500

Thanks for the reply.

"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
news:mj3tpvo8ll724lpejt5l5vtaqrhikc2k8q@4ax.com...
> Often times, the preferred way is to find a way to not use
> dynamic SQL as there can be ways around using it. Depending
> on your requirements, sometimes an option is to check a
> condition and use if statements that check the value and
> then execute whichever SQL statement pertains to the
> condition. You can find some examples on how to do this and
> other possible work arounds in the following article at
> Erland Sommarskog's site:
> Dynamic Search Conditions in T-SQL
> http://www.algonet.se/~sommar/dyn-search.html
> A related article at this site worth reading is:
> The Curse and Blessings of Dynamic SQL
> http://www.algonet.se/~sommar/dynamic_sql.html
>
> -Sue
>
> On Tue, 28 Oct 2003 09:47:51 -0500, "Robert Strickland"
> <strickra@rjsonline.net> wrote:
>
> >We have an application where clients have their database at a central
> >location; one database handles one or more clients. We want to secure the
> >database by allowing just execution rights on the stored procedures for
each
> >client and use views to limit the viewable data based on the user id in
the
> >connection string (we use the CURRENT_USER in the where clause of the
view).
> >For example, client A can run a stored procedure that selects data but
gets
> >back data for client A. There is no way for Client A to view Client B's
> >data. All works fine until we run stored procedures that have dynamic sql
> >and use the 'exec' and 'sp_executesql'. The client user id (each client
has
> >a different user id and password) does not have rights to the views -
just
> >the stored procedures. However, the dynamic sql in the stored procs runs
> >under the context of the logon user id and fails because that user does
not
> >have permissions for that view. We would have to allow
select/insert/delete
> >etc. permissions for each view for each user id which is not what we want
> >from a security point.
> >
> >What is the prefered way or best practice for using dynamic sql in stored
> >procedures but still lock down all resources except the stored
procedures?
> >
> >Thanks
> >Bob
> >
>

Relevant Pages

  • Re: SQL Statement in C#
    ... This is mainly MS-SQL thinking, with other databases ... they do not think this way dynamic SQL is the prime method. ... people who have a problem with stored procedures have it with CRUD ... statements, if you are doing something that multiple database pulls, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Inline SQL or stored procs for my C# windows app?
    ... how hard is it to move a stored procedure from one database to ... If you have dynamic sql, then you are going to run into the same ... the sql) to make sure you are not using any database-specific features. ... (you won't have to worry about changing how you call stored procedures from ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: SQL Injection Prevention
    ... > under the impression that all stored procedures contain dynamic SQL. ... more than 1.5 MLoC of Fortran code + more than 1.2 MLoC of C++ ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Injection Prevention
    ... > under the impression that all stored procedures contain dynamic SQL. ... more than 1.5 MLoC of Fortran code + more than 1.2 MLoC of C++ ...
    (microsoft.public.dotnet.security)
  • Views, Table Permissions and Stored Procedures
    ... We have an application where clients have their database at a central ... client A can run a stored procedure that selects data but gets ... All works fine until we run stored procedures that have dynamic sql ...
    (microsoft.public.sqlserver.security)