SQL Server Performance

Windows Authentication

Discussion in 'General DBA Questions' started by danielreber, Oct 18, 2005.

  1. danielreber New Member

    My clients have been asking that I add Windows Authentication to my data analysis application. I told them that if that is done the users will have direct access to the base tables and this is bad because of HIPPA (new hospital privacy law.) My reasoning is that I am unable to give permissions to just stored procedures only and not to the base tables because my application is 100% ad-hoc. Since I need to give select permissions to all base tables, any user can view the data using any oledb tool and thus circumventing security and auditing settings. Am I right in my reasoning?

    Thanks

    Dan Reber
  2. FrankKalis Moderator

    So now all users connect using SQL authentication? Do they all use the same login?
    And correct me if I'm wrong, but access to the base tables in case of dynamic sql is not dependent from the authentication mode you use.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  3. satya Moderator

    Instead you can create a role that can have EXEC permissions on all stored procedures and allow the users to keep in this role.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. danielreber New Member

    Frank,
    All users authenticate to a table of users in my database that has an encrypted password. The application logs into sql server with the same username/password for everybody. However, the sql server username/password is not known by anybody using the application.

    "but access to the base tables in case of dynamic sql is not dependent from the authentication mode you use."

    Not quite sure what you mean by that.

    satya,
    Since the SQL that will be sent to SQL Server is ad-hoc I am unable to use stored procedures. Also, I believe that if I use EXEC (@sql) or sp_executesql in a stored procedure the user that logs in will need to have select permission granted to the base table. I am right on this assumption?


    Daniel Reber
    Datamasters, Inc
  5. satya Moderator

  6. FrankKalis Moderator

    Actually I meant, that dynamic sql always run within the security context of the user who is logged in.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  7. danielreber New Member

    satya,
    Thanks for the helpful link. Based on the information that I gave you am I correct in not allowing Windows Authentication?



    Daniel Reber
    Datamasters, Inc
  8. satya Moderator

    If you can allow the user with db_datareader permission, then you can try to deploy any auditing tool to monitor the activity for confidential data.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  9. danielreber New Member

    That would help with the auditing (I already have one built into the app though) but they will still circumvent the vertical (fields available) and horizontal (rows available) partitioning of the data.

    By the way, does SQL Server 2005 behave in the same way in regards to "dynamic sql always run within the security context of the user who is logged in"




    Daniel Reber
    Datamasters, Inc
  10. FrankKalis Moderator

  11. danielreber New Member

    Great news, it looks like SQL 2005 will allow the stored procedure creator to define which user to execute all sql (including dynamic) as.

    Check out the link that I got from your link:http://searchsqlserver.techtarget.com/originalContent/0,289142,sid87_gci1102100,00.html

    Thanks for your help

    Daniel Reber
    Datamasters, Inc
  12. Twan New Member

    Could you use an application role? SO the user logs in as themselves with no special privileges, then then application sets its application role (supplying its own password) The user's privileges are then elevated within that application.

    This would allow your auditing and still keep the data hidden?

    Cheers
    Twan
  13. danielreber New Member

    This is one of the threads that I came across while researching if my application can use application roles.

    http://www.sqlmag.com/forums/messageview.cfm?catid=22&threadid=764

    The main point was this statement:

    Limitations: The application role remains active until the connection is closed and can only be terminated by the closing of the connection. Any attempt to change database context with a 'USE dbname' command will give an error. Any attempt to access another database using multipart naming (e.g. dbname.ownername.objectname) will not be successful unless the target database has a guest user account configured, in which case this connection will get guest privileges.

    Since we allow the users to include data from multiple databases in a single query we do not want to give select permission to a guest account.

    Daniel Reber
    Datamasters, Inc

Share This Page