Re: Query Analyzer Connect Option

From: Erland Sommarskog (sommar_at_algonet.se)
Date: 02/29/04

  • Next message: Dr. StrangeLove: "Re: Get starting...." 
    Date: Sun, 29 Feb 2004 23:10:01 +0000 (UTC)

    David Greer (dgreer@nextcorp.com) writes:
    > The situation is that I have several users on an application that
    > controls their access to the DB. The user ID has full rights to the DB
    > but the application stops them from making changes to data. Now I have
    > a user that needs to be able to create and run SELECT statements in a
    > “Query Analyser” type of environment. I can publish a Query Analyser
    > session with locked credentials using Citrix, but I don’t want him to be
    > able to use the File => Connect option to reconnect with one of the
    > application Ids thus giving him full rights in an open environment.

    So what you need to do is to redesign your application a bit. One
    way to go would be to use appliction roles. In this case, the application
    submits sp_setapprole to SQL Server, with the password for the role hidden
    somewhere in app, preferably encrypted. The role has full access to
    the database, but the users only have SELECT access. There a few gotchas
    with application roles, one major is that it does not play well with
    connection pooling.

    Another way to go, is to have the application to perform all its access
    through stored procedures. The procedures would be owned by dbo, and
    EXEC access granted to the users. The user who needs to run queries on
    his own can get SELECT access, but he will not be able to modify data.
    While this is even more work than switching to application roles, this
    is the normal way to lock down your database.

    As Mary pointed out, giving users full access to the database, and
    hoping that the application will shield them, is a security incident
    waiting to happen. 

    -- 
Erland Sommarskog, SQL Server MVP, sommar@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
  • Next message: Dr. StrangeLove: "Re: Get starting...."

    Relevant Pages

    • Re: Unable to view system stored procedures from .NET IDE and unable to debug SQL
      ... But what is the reason for the debug problem I mentioned below? ... try to step into a stored procedure in the Northwind database, ... Run SQL server setup or contact database ... I can't see any system stored procedures in master. ...
      (microsoft.public.dotnet.framework.adonet)
    • Re: Identical database w/ identical stored procedures?
      ... > creating the maintence nightmare of updating the same stored procedure ... First of all, put your source code, tables, stored procedures and all ... This can be achieved with a help table in the database. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
      (comp.databases.ms-sqlserver)
    • Re: Does nesting stored procedures make sense to increase performance?
      ... Reducing client or middle-tier round trips to the database is a good idea in that it will reduce network traffic. ... In SQL Server 2000, because recompiles of query plans are done on a per-procedure basis, it's helpful some execute one SQL statement per procedure as well. ... If you're going to combine many calls into a single call, you'll need to add robust error handling to the "driver" procedure so as not to call procedures 2,3, and 4 if procedure 1 fails, etc. You'd need to replicate your existing client/middle-tier code's concept of success/failure results quit the batch of procedures on a failure and the return the results/return code that the client/middle-tier sees in the original. ... I've recently became aware of nesting stored procedures in SQL Server. ...
      (microsoft.public.sqlserver.programming)
    • Re: Debugging in VS.NET
      ... > "Cannot debug stored procedures because the SQL Server database is not ...
      (microsoft.public.sqlserver.msde)
    • Re: MS Access DAO -> ADO.NET Migration
      ... William Vaughn ... Microsoft MVP ... Hitchhiker's Guide to Visual Studio and SQL Server ... My migration app works building a SSCE database file with imported data ...
      (microsoft.public.dotnet.framework.adonet)