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
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)
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.
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
Yes you're right and refer this KBAhttp://support.microsoft.com/default.aspx?scid=KB;EN-US;301299 for more information. 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.
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)
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
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.
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
Check out the link here:http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10998 SQL Server MVP Adam Machanic writes something about EXECUTE AS and context switching. Not sure, if this applies to dynamic SQL, though. -- Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
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
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
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
