Windows Integrated Authentication problem with ODBC connections

[José António Silva]

Several years ago, I was very motivated to use Windows Integrated
Authentication in my ODBC Access/SQL Applications. No passwords, more secure,
fully integrated, etc.

However there is an issue not yet solved. By using Windows Integrated
Authentication somebody who configures an ODBC connection can modify SQL data
bypassing the business rules coded in the mde.
The only way I know to solve this is to turn back to SQL Authentication and
use a hidden password, perhaps coded in the mde. I don’t like this!!!

Anybody knows any other solution that works with Windows Integrated
Authentication???
JS

 

[Rick Brandt]

Several years ago, I was very motivated to use Windows Integrated
Authentication in my ODBC Access/SQL Applications. No passwords, more
secure, fully integrated, etc.

However there is an issue not yet solved. By using Windows Integrated
Authentication somebody who configures an ODBC connection can modify
SQL data bypassing the business rules coded in the mde.
The only way I know to solve this is to turn back to SQL
Authentication and use a hidden password, perhaps coded in the mde. I
don't like this!!!

Anybody knows any other solution that works with Windows Integrated
Authentication???
JS

Basically when you use a server engine and its security then there should
not be any business rules that are not enforced by the server. When you
give someone permissions on the server you cannot make any assumptions about
the tool that they will use to interface with the data.

To stay with integrated security you would deny access to all tables and do
all edits with stored procedures. Then the rules can be enforced within the
SP along with any other constraints the server has in place.

 

[José António Silva]

This is a cool answer but, how do I do all edits with stored procedures
having, at the same time, Access forms using mde ODBC linked tables to SQL
tables?
José António Silva

 

[Rick Brandt]

This is a cool answer but, how do I do all edits with stored
procedures having, at the same time, Access forms using mde ODBC
linked tables to SQL tables?

You don't generally use linked tables and bound forms if that is the
environment you want.

I give users access to tables they need access to and then use Access as you
are now. Could they link to the tables from another file or program and do
some damage? Certainly. And if they did and it was discovered they would be
reprimanded or fired. These people could also set off fire alarms or walk
off with company property or any number of things harmful to the company.

For the databases that I manage our company is comfortable enough with our
backups weighed against the importance of the data and the resources
required to lose or re-enter one days worth so we pretty much don't worry
about the level of security you seem to want.

For cases where that would be the level I would want or need then I wouldn't
use a traditional Access app with bound forms and linked tables.

 

[david]

There is a way - grant permissions to applications, rather than to users.
Unfortunately, MS never implemented this for Access applications,
so you have to treat all Access applications and users as untrusted.
If you want security, you have to treat Access applications and users
as if they were web users.

(david)

 

[José António Silva]

Generally, web users have IIS, or a web service, or .net between them and the
data. What do you really mean about “treat Access applications and users as
if they were web users.†I can’t imagine this, in practice.

José António Silva

 

[david]

I just mean that if you are correct: giving users full access to the
database is a security problem. You can build signed C++
applications and give the application permission, but you can't
do that with Access.

(david)