Views, Table Permissions and Stored Procedures

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

Date: Tue, 28 Oct 2003 09:47:51 -0500

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: Business objects, subset of collection
    ... SQL databases sucks for searching large data sets, ... TPC implementations rely heavily on stored procedures. ... Application modules that are executed on the client side but are stored on the server side. ... Lets say you want to find all unpaid invoices. ...
    (comp.object)
  • Re: Portable stored procedures
    ... > JDatastore lets you store binary streams outside the table scema. ... Other databases usually do not store BLOB's ... > you will have to write the function in the client in Java (or whatever ... >> stored procedures written in some proprietary language. ...
    (comp.lang.java.databases)
  • Re: how to calculate bandwidth requirement for client-server applicati
    ... > Private Network to connect to the SQL database. ... update/insert/delete data from other stored procedures. ... You can use the Show Client Statistics option in Query Analyzer to see ...
    (microsoft.public.sqlserver.tools)
  • Re: SQL Parameter
    ... Even more reason to use stored procedures for secuirty. ... If your client app ... I do use Stored Procedures (in Delphi apps, I am only just migrating to C#). ... SqlCommand cmd = new SqlCommand( ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: stored procedure access
    ... Yes, it's true that you can encrypt stored procedures, and it's also ... true that this encryption mechanism has been compromised, ... client is aware of this and desires to decrypt your sprocs, ...
    (microsoft.public.sqlserver.server)