Re: SQL server vulnerability through ODBC
From: JJ (jjjj_at_nospam.com)
Date: 01/14/04
- Next message: JJ Wang: "xp_sendmai error on serveral of our sql servers"
- Previous message: anonymous_at_discussions.microsoft.com: "dbo access"
- In reply to: Diane: "RE: SQL server vulnerability through ODBC"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Wed, 14 Jan 2004 13:03:46 -0500
Look into Application Roles in SQL Server.
You can grant the necessary select/insert/delete/update privileges to the
Application Role for your app, but grant more limited privileges (or no
privileges) to the users themselves.
In your application, you would need to add some small amount of code to log
in as the Application Role using sp_setapprole after you connect to the
database. From that point on, your program will have the privileges of the
Application Role, not the user.
Since you need to call a stored procedure and supply a valid password for
the Application Role, a normal user is not going to be able to get to the
tables through ODBC, unless they know how to write code to login in as an
Application Role and know the role's name and password.
"Diane" <anonymous@discussions.microsoft.com> wrote in message
news:305945FA-777E-4AC5-8A74-9062037ED34B@microsoft.com...
> Perhaps I should explain further - the problem is not with the
application. The security applied there works to prevent users from seeing
the data for the other divisions within the firm. The users have public
rights to the tables, but we control what they see through the application.
However, we have a couple of savvy users in one office who figured out they
could set up an ODBC connection to the SQL database and then went into
Access 97 and linked the tables. This opened ALL the data to them. I need
to be able to prevent this from happening. Is there a way to prevent a user
from setting up an ODBC connection or some way in SQL administration to
limit what can be seen with an ODBC connection?
>
> ----- Diane wrote: -----
>
> Security on SQL tables is provided through the application
> we have written in Visual Basic. Users are restricted on
> the data they can see based on their division. They have
> public access to the tables and stored procedures.
> However, users can access the tables through an ODBC
> connection and link to the SQL tables and see ALL the
> data. The users do need SELECT, INSERT, UPDATE, and
> DELETE access to the tables for the application. How can
> we control what the users can see from Access 97 through
> the ODBC connection?
>
- Next message: JJ Wang: "xp_sendmai error on serveral of our sql servers"
- Previous message: anonymous_at_discussions.microsoft.com: "dbo access"
- In reply to: Diane: "RE: SQL server vulnerability through ODBC"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
