September 24, 2004 at 10:50 am
A software vendor provides my company with an application that uses SQL Server 2000 as the back-end. A new user needs to be granted access to the server, to the database, and also within the vendors application there is a place where I setup a user, assign roles etc.
Any user who is setup to use this application can connect to the backend DB using their username and passward, with something like MS Access (ODBC) and view/edit the raw data without the controls that are setup in the front-end application. It has not happened to date (that I am aware of).
How can I block users from accessing the database directly.
Thank you for any info you can provide.
Paul
September 24, 2004 at 11:32 am
It depends on the application. It sounds like the application is coded to work under the signed-in user's authority vs using a single shared user for its SQL Server connections. That is neither a good or bad thing. Its just an implementation choice (it makes maintenance more involved, but allows you to track activity by the user's signon which can be very nice). If the application is coded using dynamic SQL then you are going to have trouble unless someone else can come up with a solution for you. However, if the application uses stored procedures or can be converted to use only stored procedures without any dynamic SQL then you can grant execute permision for the stored procedures to the users and not grant the users any data access rights.
Another possibility might be that the database is simply too visible to the users. If the application installs an ODBC connection on the client machines then the users can easily find and access them. If you are able to change the applications connection properties to connect directly to SQL Server instead of going through ODBC (and not start crashing) then you could drop the ODBC connection path effectively "hiding" the SQL Server database. Note that it would still be accessable through other means (for example the user could just recreate the ODBC connection).
If anyone has another option please let us know. I'd like to grow my knowledge base also.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply