Prevent odbc connection to the backend mdb

  • Thread starter Thread starter Pete
  • Start date Start date
P

Pete

Re Access 2003 SP3

I have a typical front/backend setup with linked tables connecting to an mdb
file on a file server.

I have set up my own security within my Access app, but I also want to
prevent any odbc connections to prevent users changing the backend data
outside of my Access app.

I this feasible - possibly using bespoke events to catch any such
connections to the backend mdb??

Many Thanks.
 
On Fri, 8 Jan 2010 04:49:01 -0800, Pete

No, unless you go to extremes.

-Tom.
Microsoft Access MVP
 
Pete said:
Re Access 2003 SP3

I have a typical front/backend setup with linked tables connecting to an mdb
file on a file server.

I have set up my own security within my Access app, but I also want to
prevent any odbc connections to prevent users changing the backend data
outside of my Access app.

I this feasible - possibly using bespoke events to catch any such
connections to the backend mdb??

Many Thanks.

I basically agree with Tom. There's a way but be aware this is weak in
terms of security... Assign a database password to the .mdb file. They
had beefed up the encryption for .accdb format and it even can be
customized to used even a stronger encryption/password but with .mdb,
you're stuck with whatever they used for .mdb (it's done internally
whereas in .accdb they call a Windows cryptographic library hence the
flexibility in .accdb)

If you are content with merely frustrating the casual snooper, and you
can easily narrow the potential culprit to one or two other employees,
then this may be good enough for you needs. Should you go down this
route, the trick will be in not storing the password plaintext in your
front-end (too easy to do that). Even better, don't store it at all; use
something to "calculate" the password. Be aware that all objects such as
queries need not have password embedded as long there is a "active"
connection. This "active" connection is completely inaccessible from VBA
though Access will re-use this connection for whatever objects that uses
the same "connection string" (which in case of a linked .mdb is a file path)

Beyond that, I'd look at putting the data in a backend such as SQL
Server, MySQL, Oracle, PostgreSQL and have them manage the security then
you can design your front-end to authenticate without your users'
knowledge/intervention.

HTH.
 
What are the "extremes" please Tom?

Tom van Stiphout said:
On Fri, 8 Jan 2010 04:49:01 -0800, Pete

No, unless you go to extremes.

-Tom.
Microsoft Access MVP


.
 
On Fri, 8 Jan 2010 07:25:01 -0800, Pete

Well, you could have an application running on the server, monitoring
your back-end database. The ODBC spec is well defined and using that
you can potentially recognize suspect (non-Access) connections, and
then throw up your defensive measures.
Another option would be to have essential data be encrypted such that
even if one got access to the BE, there is nothing of interest to get.

Way before I would go to these extremes I would have a discussion
about what you are really trying to achieve with this. For example
Access workgroup-level security combined with RWOP queries will keep
most users out of your database. This level of security can be
defeated by the technically-savvy. Then I would consider SQL Server
which is a much more secure db platform.

-Tom.
Microsoft Access MVP
 
SQL Server is the obvious option but , having tested the mdb password option
thru odbc, this should suffice.

Many thanks for your comments Tom & Banana.
 
Back
Top