To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
VIEW MEMBER FEEDBACK TO THIS ASK THE EXPERT Q&A.
Query Analyzer is only a vehicle for executing queries, it does not by itself grant access to data. Conversely, it does not restrict access to data. The access to your data is defined by the user account (or role, more specifically) used with Query Analyzer (or Enterprise Manager or OSQL.exe).
If your intention is to prevent your USERS from having direct access to your data (via Query Analyzer, Enterprise Manager, or OSQL, etc.), you must make sure to restrict their access using user accounts. If, as I suspect, they are all using the "SA" password, that is your first area of improvement--create user accounts for all of them (or save time and use Windows authentication).
MEMBER FEEDBACK TO THIS ASK THE EXPERT Q&A:
I think what the developer was trying to ask was this: If you setup security groups that allow a user access to a database, whether it be read or read/write to the data itself, can you also restrict how the user access the data? This developer would like the user to access data via his program.
—Joseph D.
******************************************
No. If a user has been granted access to a table using proper SQL credentials, SQL will not enforce by which method the user can connect to that data. You could, in theory, create some triggers that would launch upon each SELECT statement that would check to see which sysprocesses::program_name the user was employing to get to the data. If they're using Enterprise Manager or Query Analyzer you could boot them out or refuse to run the query. That, however, would be a pretty big performance hit.
You could also put a "CHECK" constraint for certain tables (such as CHECK APP_NAME()='MySpecialApp.exe') but you would also see a performance hit. If it is of supreme importance that users only use your program to get to the data I would encrypt the data and have your program perform the decryption.
— Steven Andres, Security Expert
******************************************
Another option for the developer would be using Application Roles and Application Passwords. If the application uses a very restricted user account or role to initially connect to the database and then issues an application password that grants the application all the rights it requires, no other users or roles would need to be assigned to the database. This would restrict access to the database through the application, except for those with administrative access to the SQL Server.
—Lynn P.
Do you have comments on this Ask the Expert Q&A? Let us know.
|
