Prevent access to data via adp tables from mdb file

[aa]

I'm pretty new to adp development and I'm hoping someone can tell me how to
fix my security hole.
At the moment it seems that anyone who can see my adp file can read and
extract all the data that my adp can see just be creating an mdb file and
importing the tables from the adp file. I'm assuming I've missed something
pretty basic here and there is some way to prevent this, but I can't work
out how.

Can someone enlighten me, please ?

Per

 

[Steve Jorgensen]

I'm pretty new to adp development and I'm hoping someone can tell me how to
fix my security hole.
At the moment it seems that anyone who can see my adp file can read and
extract all the data that my adp can see just be creating an mdb file and
importing the tables from the adp file. I'm assuming I've missed something
pretty basic here and there is some way to prevent this, but I can't work
out how.

Can someone enlighten me, please ?

Per

If you need users to have data access through the application that they can't
get any other way, then it seems to me an ADP application is simply not the
way to go.

Much of the benefit you get form an ADP is from its ability to look behind the
structure of stored procedures and views to access the underlying tables for
updating. To really lock down database access, though, you need to deny the
users direct access to the tables, and give them only limited access, mainly
via stored procedures. If the ADP can't acccess the underlying tables with
the user's permissions, you can't update data through bound forms, so you end
up coding all inserts and updates using unbound forms, and at that point, you
should probably be writing a VB or .NET app.

If you want to stay with Access, you may find that an MDB actually works
better with a locked-down back-end because DAO simply works with what it's
given and doesn't try to go around behind your stored procedures and views.

 

[aa]

If you need users to have data access through the application that they can't
get any other way, then it seems to me an ADP application is simply not the
way to go.

I'm not really sure what this means. Are you saying that if you want to
create a SQL Server based application, then only use Access ADPs as a last
resort if you can't find any other approach?

Much of the benefit you get form an ADP is from its ability to look behind the
structure of stored procedures and views to access the underlying tables for
updating. To really lock down database access, though, you need to deny the
users direct access to the tables, and give them only limited access, mainly
via stored procedures. If the ADP can't acccess the underlying tables with
the user's permissions, you can't update data through bound forms, so you end
up coding all inserts and updates using unbound forms, and at that point, you
should probably be writing a VB or .NET app.

I'm having to read between the lines again here to relate what you are
saying to my problem.. I am understanding this as meaning that once I have
created my ADP with direct access to tables then I cannot stop ANY third
party linking to my ADP and getting free access to the SQL Server data (no
they can't edit it, but that's not very reassuring in respect of data
security)

If you want to stay with Access, you may find that an MDB actually works
better with a locked-down back-end because DAO simply works with what it's
given and doesn't try to go around behind your stored procedures and

views.

If what I have understood from your reply is correct then it seems I have
wasted my time producing an ADP at all.
I thought Microsoft were trying to persuade Access developers to use ADPs
more. At least with MDB files the hacker has to do some work to get to the
data.

Per

 

[Eva Etxebeste]

Hello Per

When you connect your ADP to your database, you must set a UserName and a
Password. Set a correct user and pass (create a new user in SQL Server if
you need), don't use empty passwords, and don't let the user save the pass
with the database. Perhaps you need to create a login form, and use it to
prevent you users to read the database when not autorized, even if they use
MDBs or SQL Server's Database Administrator

Regards

 

[aa]

Thank you for your reply but I don't think you have understood the issue -
perhaps my explanation is not good enough.

If user2 can see user1's .adp file then they (User2) can simply create a
new mdb and import the tables from the adp. As far as I can tell, User2
does not have to have any permissions in SQL Server. When user2 has
imported the tables they have local Jet tables which contain all the data
from the corresponding SQL Server tables.
The data on SQL Server cannot be changed of course but User2 now has a copy
of all the data for any purpose.

I interpreted Steve's reply as confirming this, because he said that it is
necessary to use unbound forms if you do not want to use direct SQL Server
table access and that once you have taken that decision there is little
point in using an adp.

I was hoping that as a new adp developer I had just missed something obvious
but I am quite concerned now.

Per

 

[Lyle Fairfield]

Thank you for your reply but I don't think you have understood the issue
- perhaps my explanation is not good enough.

If user2 can see user1's .adp file then they (User2) can simply create
a new mdb and import the tables from the adp.

Sure they can if the last person to use user1's adp file
1. connected with a login that had permissions to access the tables
2. allowed saving of the password.
But who would be that careless? Probably not someone who is employed (for
long).

If user1 logs in with a password that has no permissions for the tables
then user1 won't be able to see the tables, much less to import or link to
them, whether he logs in from user1's adp, or user2's mdb or adp.

If user1 logs in with a password that has permissions for the tables then
he/she can mess with the tables; is this surprising?

If you leave a loaded gun lieing around someone might shoot it. So, unless
you're Charlton, the Barbarian, don't do it.

 

[Steve Jorgensen]

I'm pretty new to adp development and I'm hoping someone can tell me how to
fix my security hole.
At the moment it seems that anyone who can see my adp file can read and
extract all the data that my adp can see just be creating an mdb file and
importing the tables from the adp file. I'm assuming I've missed something
pretty basic here and there is some way to prevent this, but I can't work
out how.

Can someone enlighten me, please ?

Per

Ah, I must apologize. After reading more of the thread, I see the issue is
not restricting access from a user who -does- have permission to see data
through the application, it's restricting access from other users who do -not-
normally have permission, with or without the ADP. Right?

So, what others have said is true. Either use integrated security, so only a
user who is already logged on to Windows can access the database using the
permissions granted to that Windows user, or don't save the password with the
ADP, and make each user use a separate SQL Server logon to access the
database, and type it in each time they run the ADP.

 

[Eva Etxebeste]

Hello again.

Thank you for your reply but I don't think you have understood the issue -
perhaps my explanation is not good enough.

Or perhaps my terrible english...

If user2 can see user1's .adp file then they (User2) can simply create a
new mdb and import the tables from the adp. As far as I can tell, User2
does not have to have any permissions in SQL Server. When user2 has
imported the tables they have local Jet tables which contain all the data
from the corresponding SQL Server tables.

Ok, if you want to execute an ADP, you need:
1. network rights to access and open the adp file. I think this is what do
you mean when you say "If user2 can see user1's .adp"
2. SQL Server rights, you need rights to see/add/delete.. data in SQL Server

If user2, because of his network configuration, can see the user1's adp, he
can open it. If you use the "sa" user (with blank pass) to connect to the
SQL Server database, user2 can import all the data to his local database.

You can (should) change "sa" user's pass, and define new SQL Server users.
Then you
can ask you adp user for an valid SQL Server user and pass. So, user2 can
open user1's adp file, but can't access the database.

Regards