Vulnerable security with Access/SQL Server

[jesbin]

Hi,
We have client/server applications using SQL Server and VB. The users connect to the database using the application. However, any smart user can just create an ODBC and link the tables in the SQL Server using a simple tool like Access and make changes to the data directly.
I have explored the possibility of using Application roles but I dont like it so much. Is there a better way to prevent such kind of access in SQL Server 7.0.
Jesbin [sig][/sig]

 

[foxdev]

Any smart user can create an ODBC connection and link to the tables - if they know the username/password to login to the database.

You could restrict access to stored procedures only, and use the app_name() function to see if they are running your desired (Access) application, and disallow access if they are not. [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[crystalized]

I would love to hear more information about the app_name() function (I will look in the books on line). But can any one give a real world opinion of how it works.

In addition to the earlier question I wonder, if access to stored procedures is granted then am I correct in thinking that you do not need to grant access to the tables that are the foundation for the stored procedures? [sig]<p>Crystal<br><a href=mailto:crystals@genesis.sk.ca>crystals@genesis.sk.ca</a><br><a href= > </a><br>--------------------------------------------------<br>
Experience is one thing you can't get for nothing.<br>
-Oscar Wilde<br>
[/sig]

 

[jesbin]

Hi,
App_Name() is just a simple role without any members. An application has to use that role (using sp_setapprole) to work with the database.
The store procedure approach is fine however, we also have clients using Access for reports. You know how VB is with reports ;-) ;-) So for such clients, they need RO access to the tables.
Regards,
Jesbin


[sig][/sig]

 

[calahans]

I think that foxdev's suggestion is a good one (the stored procedures). Using sp's also allows more transaction management.

You could look at the application level security using roles. I saw this used on one system and it worked well.

Lastly you could allow your userd=s access to views rather than tables. Then if they log in they will only be able to access the view rather than the table [sig]<p>Cal<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]