Re: Views, Table Permissions and Stored Procedures

From: Sue Hoegemeier (Sue_H_at_nomail.please)
Date: 10/28/03 

Date: Tue, 28 Oct 2003 08:49:56 -0700

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)