MS Access Security Problem

[FA]

Hi, I have a question which i can not figure out how to solve. I have
..mde file with the linked tables with SQL Server with ODBC connection.
I have six users each has user id and password to access SQL Server.
There is no authentication on the front end (.mde file). I am wondering

if they have access to SQL Server tables, any user can create an mdb
file and link tables from SQL Server and do anything they want to do.
In my .mde file they can only update certain data. Users are not
allowed to delete any record in only one table but they are not allowed

to delete any record in any other table.
If they can make a ODBC connection since they has user id and password
to sql server, they can do anything they want in sql tables. I do not
think it would help if i add another layer of authentication in my .mde

file since they can create a mdb file and have access to all the data.

Please let me know what is the solution to this problem.


Your replies would be greatly appreciated.


Thanks
Moe

 

[Rick Brandt]

Hi, I have a question which i can not figure out how to solve. I have
.mde file with the linked tables with SQL Server with ODBC connection.
I have six users each has user id and password to access SQL Server.
There is no authentication on the front end (.mde file). I am
wondering

if they have access to SQL Server tables, any user can create an mdb
file and link tables from SQL Server and do anything they want to do.
In my .mde file they can only update certain data. Users are not
allowed to delete any record in only one table but they are not
allowed

to delete any record in any other table.
If they can make a ODBC connection since they has user id and password
to sql server, they can do anything they want in sql tables. I do not
think it would help if i add another layer of authentication in my
.mde

file since they can create a mdb file and have access to all the data.

Please let me know what is the solution to this problem.

You control this by granting them the rights you want them to have ON THE SERVER
and nothing else. When you use a server database as your back end then you have
to set up security on the server. You cannot rely on any rules in the front end
for the very reasons you stated.

 

[FA]

Thanks Rick, but i was thinking may be if i have one super user id to
the server and that id is transparent to the users and when they log in
the front end that super id assigns the role of the user on the fly in
MS Access? This way nobody can create any .mdb file and get external
data via ODBC and read the data.

What do you think about it ?

 

[Rick Brandt]

Thanks Rick, but i was thinking may be if i have one super user id to
the server and that id is transparent to the users and when they log
in the front end that super id assigns the role of the user on the
fly in MS Access? This way nobody can create any .mdb file and get
external data via ODBC and read the data.

What do you think about it ?

Not much. It would deter your average clerk so if that is all your
concerned about then it might be okay.

Try building a new MDB and then importing all of the table links from your
app and see if you have full access to the data. I'm guessing you would.