Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Security and synchronization question

56 views
Skip to first unread message

Danny J. Lesandrini

unread,
May 15, 2012, 7:10:39 PM5/15/12
to
I just met with a new client who needs a couple things that, last I heard,
Access 2010 doesn't support: Security and Replication (synchronization).

How can a database be secured in Access 2010? Let's assume this is a
contact database with information that cannot (if stolen) be made public.
Assume also that there are various groups that are allowed to see only
those contacts to whom they have rights. How is this handled these days?

I assume I'd roll my own login system and manage data access in VBA.
Can the tables be reliably obfuscated? Does suppressing the F11 action
really work to prevent users from getting to the raw data?

As for "replication", I know that's not supported in Access 2010. What I
think they really need is "synchronization". Let's say there are 3 users
who enter and update contact info and 500 users who need only access
the data and search for contacts. Because they want this info to be
available in case of a disaster, they don't want a "web based" solution
but rather, want a local Access database that regularly gets "synched"
with the main database. What approach comes to mind?

I was thinking of using a single Access file with local tables that are
truncated and reloaded with data whenever a network connection is
detected. When off-line, as would be the case in a disaster, the list
would be as current as the date of last network access.

Ideas?
--
Danny Lesandrini
www.lesandrini.com/datafast/

Douglas J Steele

unread,
May 16, 2012, 6:45:22 AM5/16/12
to
Danny: For security, check what Tom van Stiphout, Scott Diamond, Patrick
Wood and Paul Bardinelli have at http://www.accesssecurityblog.com/. I
thought there was a sample database available at http://www.utteraccess.com,
but I was unable to locate it in a brief search just now.

For replication, it's not quite as simple. What you're describing will
certainly work for the 500 read-only users, but not so well for the 3 users
who will be performing updates (unless you're not using Autonumber keys, and
you can guarantee that 3 users will never update information for the same
records). I can remember back in the Access 2.0 days, before Replication was
part of Access, I built my own replication, and it was a lot of work! IIRC,
I added an additional field to the PK for each table, indicating which copy
of the database it came from. Unfortunately, I don't remember what I did to
handle ties.

I know SQL Server has synchronization, but I'm not sure whether you can use
it if you use SQL Express (or whatever the version that comes with Office is
called these days!)



"Danny J. Lesandrini" wrote in message
news:OnBsr.32197$xK2....@en-nntp-11.dc1.easynews.com...

Danny J. Lesandrini

unread,
May 16, 2012, 9:04:15 AM5/16/12
to
Doug, thanks for your thoughts and the links to the security blog. That's what I was looking for.

I didn't explain the 3 users well enough. The idea is that they can be simply connected to the network as multi-users
with no need of synchronization. Then the 500 users would open a different, reporting app that gets synched, so I won't
have to worry about building replication for the 3 users.

However, I got to thinking about SharePoint. I haven't used it but someone once told me it implements a form of
replication. Maybe that could be used for the reporting users, provided my client has access to a common SharePoint
server for all the users, which is something I'm not sure of.

Alternatively, I could go the truncate and reload route. If the data ends up being only a couple MBs, perhaps the load
won't take that long. I should really test it with some dummy data just to get an idea and make sure it's feasible.

--
Danny Lesandrini
www.lesandrini.com/datafast/


"Douglas J Steele" wrote in message news:jp00g3$c12$1...@dont-email.me...

rael...@gmail.com

unread,
May 16, 2012, 6:49:06 PM5/16/12
to
Hi Danny,

We have just completed a project for a client which required a similar type of synchronisation. Rather than go down the path of building something in Access we used SQL Server Replication Services (the database backend of our app is obviously in SQL Server). I am a relative novice when it comes to anything SQL Server related, but I've got to say it was a breeze to set up. It was logical and almost wizard driven. The trickiest bit was ensuring that the network security could handle the required permissions (we synchronise a 4Gb database through the internet, across different network domains).

As far as which versions of SQL Server are required, it's only the Publisher (ie: your main/live database) that needs a paid version of SQL Server (Standard and higher). The subscribers can run Express edition.

Rael

Danny J. Lesandrini

unread,
May 16, 2012, 8:14:54 PM5/16/12
to
Rael:

That's definitely a possibility and I'd much rather the process be automatic. But I have 2 issues or questions:

1) Can the clients use SQL Express?
2) How difficult is it to install SQL Express?

The other issue the client raised is that some of these clients will already have critical apps installed on SQL Express
and whatever I do, whatever version I install, it can't interfere with preexisting apps. That worries me because it's
difficult to anticipate those problems.

Well, I can at least raise it as a possibility to the client. Thanks for your input.

Danny



--
Danny Lesandrini
www.lesandrini.com/datafast/
wrote in message news:10438951.1270.1337208546401.JavaMail.geo-discussion-forums@pbcrz10...

rael...@gmail.com

unread,
May 16, 2012, 9:35:15 PM5/16/12
to
Danny,
To answer your questions,

1. definitely yes. In fact, that's what we do. There is even a free Management Studio (Management Studio Express) which allows browsing of the database for troubleshooting
2. Depends :) Fortunately I work for a company which has a number of network administrators, so the set up was easy (according to them).

You do need to be careful about automating the installation of the SQL server on computers which already have it. You definitely don't want to be responsible for installing it on 500 servers/pcs with lots of different setups. If the 500 users are on the same network then you'd want to install it on a central server somewhere.

Rael

Tony Toews

unread,
May 17, 2012, 9:44:20 PM5/17/12
to
On Wed, 16 May 2012 17:14:54 -0700, "Danny J. Lesandrini"
<da...@lesandrini.com> wrote:

> 1) Can the clients use SQL Express?
> 2) How difficult is it to install SQL Express?

Yes and easy I think.

>The other issue the client raised is that some of these clients will already have critical apps installed on SQL Express
>and whatever I do, whatever version I install, it can't interfere with preexisting apps. That worries me because it's
>difficult to anticipate those problems.

Create your own instance of SQL Express. A number of instances of
SQL Server can reside on the same system. These can be the same or
different versions and each are named differently. As to how patching
works I'm not sure yet. I think Windows Update takes care of that
automagically.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

Tony Toews

unread,
May 17, 2012, 9:48:24 PM5/17/12
to
On Tue, 15 May 2012 16:10:39 -0700, "Danny J. Lesandrini"
<da...@lesandrini.com> wrote:

>How can a database be secured in Access 2010? Let's assume this is a
>contact database with information that cannot (if stolen) be made public.

Assume that someone has an evil blackmailer. Anyone can take the
Access MDB/ACCDB home with them on a thumbdrive, via email, etc. The
only way to truly secure data within Access is to not use an Access
database file to store data. You need a server based system so the
raw file can't be copied and taken home with them.

>Assume also that there are various groups that are allowed to see only
>those contacts to whom they have rights. How is this handled these days?

This is where views and stored procedures (SP) possibly using roles
come in. Various users would have roles which are then kept in the
tables as appropriate and the roles are part of the Where clauses.
Once you have this figure out you can verify this yourself by creating
users with only the roles and logging into the database with those
users. Then see what data they can access in every view and SP.

>I assume I'd roll my own login system and manage data access in VBA.
>Can the tables be reliably obfuscated? Does suppressing the F11 action
>really work to prevent users from getting to the raw data?

No, because they can take the database file home with them.

Tony Toews

unread,
May 17, 2012, 9:49:42 PM5/17/12
to
On Thu, 17 May 2012 19:44:20 -0600, Tony Toews
<tto...@telusplanet.net> wrote:

>Create your own instance of SQL Express.

Note that your instance of SQL Express must be installed on a system
where a blackmailed user doesn't have access to the C drive or the
rights to create a backup.

Now granted IT staff can be blackmailed and thus take the SQL Server
database file offsite so ....

Danny J. Lesandrini

unread,
May 17, 2012, 11:27:06 PM5/17/12
to
Tony:

To clarify the purpose of this application, let's say, hypothetically, that it's purpose is to provide contact
information for local and national agencies, including CIA, FBI, etc. That's the sensitive contact info that we don't
want to get out in general.

Next, assume that its most important role is to provide this contact info in the event of a disaster, where the network
will not be available. Let's say a police laptop has the app installed and the Internet is down. He could use it to
locate a particular person or phone number (assuming phones aren't down too) or to look up the location of critical
resources. It needs to be offline.

SQL Express would work ... but I forget ... does it support the same object level security model that regular SQL Server
does, and if someone stole the laptop from the cop car, would that even matter?

Not sure if this clarifies the situation or not. Your comments do help me a lot. I understand they already have SQL
Express apps on their laptops and knowing I can create an independent instance is helpful. I thought that was the case
but I've never actually experienced it myself.

Thanks Again.

--
Danny Lesandrini
www.lesandrini.com/datafast/
"Tony Toews" wrote in message news:mbabr7hvje9e72jj8...@4ax.com...

Access Developer

unread,
May 18, 2012, 2:20:24 AM5/18/12
to
"Danny J. Lesandrini" <da...@lesandrini.com> wrote

> SQL Express would work ... but I forget ... does it
> support the same object level security model that
> regular SQL Server does,

I don't know the answer to this...

> and if someone stole the laptop from the cop car,
> would that even matter?

But, regardless of the software involved, it has always been considered a
"truism of security" that if someone has the data in their possession and
seriously wants the information, they can and will obtain it.

The data in the example you cite would be worth a considerable investment of
time and material to extract. Even the most secure encryption methods are
regularly broken.

Of course another axiom is "If you run someone else's program on your
computer, it's not your computer anymore" and both Windows and Unix allow
running other people's programs, remotely. So, it's no great wonder that we
see so many security breaches.

Even if there were program defects, back in the day, if only your own
company's programs were run, they were far less likely to be exploited and
with limited or no "communication" features, if a rogue programmer/operator
did obtain data they were not supposed to have, they'd have to smuggle a
reel of tape or a stack of printer paper out of the facility.

--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010



Danny J. Lesandrini

unread,
May 18, 2012, 8:59:30 AM5/18/12
to
Thanks for your thoughts, Larry.

I agree that it's not realistic to talk about security in absolute terms. It's not, "can this data be protected from
ALL threats" but rather, "did we do all that's possible to protect from the obvious?"

I mean, think about this request: "We'd like to distribute 500 off-line copies of private data to various types of
users completely out of your control and we want you to assure us that the data is safe." This isn't realistically
possible, I know.

So here's the compromise:
1) The data isn't THAT critical. It's just contact info for key people.

I suppose a terrorist could get ahold of names of key people and their office location and plant car-bombs or assassins
to take them all out at a coordinated moment, but that's a lot of work and risk for a relatively small reward. The data
doesn't include launch codes or the location of Uranium stockpiles ... just names and phone numbers.

2) The persons getting this database are considered responsible and trustworthy.

Not that someone might not be deceiving their superiors or might not turn bad later, but we're talking about police and
fire fighters who need this contact list not hot-dog venders.

3) The potential benefits outweigh the potential downsides. At least the client thinks so.

Honestly, all this data already exists in various locations and could be hacked by someone who wants it today. This
will make it slightly easier, increasing the copies and consolidating it into a single database, but there's nothing
original here.

I'm still not sure the project will move forward. We're just in the initial stages of discussing how it could be done.
All the input I'm getting here is helping. Thanks again

--
Danny Lesandrini
www.lesandrini.com/datafast/
"Access Developer" wrote in message news:a1m816...@mid.individual.net...

Tony Toews

unread,
May 18, 2012, 4:03:44 PM5/18/12
to
On Thu, 17 May 2012 20:27:06 -0700, "Danny J. Lesandrini"
<da...@lesandrini.com> wrote:

>To clarify the purpose of this application, let's say, hypothetically, that it's purpose is to provide contact
>information for local and national agencies, including CIA, FBI, etc. That's the sensitive contact info that we don't
>want to get out in general.
>
>Next, assume that its most important role is to provide this contact info in the event of a disaster, where the network
>will not be available. Let's say a police laptop has the app installed and the Internet is down. He could use it to
>locate a particular person or phone number (assuming phones aren't down too) or to look up the location of critical
>resources. It needs to be offline.

Fair enough. As a licensed radio amateur with a strong interest in
disaster communications I fully understand where you are coming from.

>SQL Express would work ... but I forget ... does it support the same object level security model that regular SQL Server
>does, and if someone stole the laptop from the cop car, would that even matter?

Yes, SQL Express is the same as SQL Server with a little bit of
throttling added. Also in this situation the laptop should be using
an encrypted hard drive. But that's not really your problem other
than that should be mentioned on your minimum requirements. But that
also takes care of evil individuals.

>Not sure if this clarifies the situation or not. Your comments do help me a lot. I understand they already have SQL
>Express apps on their laptops and knowing I can create an independent instance is helpful. I thought that was the case
>but I've never actually experienced it myself.

I have a number of instances from SQL Server 2005/2008/2012 installed
on my laptop.

Tony Toews

unread,
May 18, 2012, 4:07:15 PM5/18/12
to
On Fri, 18 May 2012 05:59:30 -0700, "Danny J. Lesandrini"
<da...@lesandrini.com> wrote:

>The data
>doesn't include launch codes or the location of Uranium stockpiles ... just names and phone numbers.

Where's that xkcd comic where the evil guy entered the launch
coordinates once and his email account twice? <smile>

>2) The persons getting this database are considered responsible and trustworthy.
>
>Not that someone might not be deceiving their superiors or might not turn bad later, but we're talking about police and
>fire fighters who need this contact list not hot-dog venders.

So their chances of being evil are much smaller but still a
possibility.

Tony Toews

unread,
May 18, 2012, 4:10:21 PM5/18/12
to
On Fri, 18 May 2012 14:03:44 -0600, Tony Toews
<tto...@telusplanet.net> wrote:

>>SQL Express would work ... but I forget ... does it support the same object level security model that regular SQL Server
>>does, and if someone stole the laptop from the cop car, would that even matter?
>
>Yes, SQL Express is the same as SQL Server with a little bit of
>throttling added. Also in this situation the laptop should be using
>an encrypted hard drive. But that's not really your problem other
>than that should be mentioned on your minimum requirements. But that
>also takes care of evil individuals.

That last sentence should be. Having an encrypted hard drive as a
requirement solves the problem of physical access by bad guys. Mostly
but not entirely.

An encrypted hard drive doesn't solve the problem of evil individuals.

Now if their encryption is broken by social engineering well that's
not your problem now is it.

Tony Toews

unread,
May 18, 2012, 4:12:55 PM5/18/12
to
On Fri, 18 May 2012 14:07:15 -0600, Tony Toews
<tto...@telusplanet.net> wrote:

>>The data
>>doesn't include launch codes or the location of Uranium stockpiles ... just names and phone numbers.
>
>Where's that xkcd comic where the evil guy entered the launch
>coordinates once and his email account twice? <smile>

Searching for it brought me to
http://whitehouse.gov1.info/launch/index.html
Nice URL.

Tony Toews

unread,
May 18, 2012, 4:16:10 PM5/18/12
to
On Thu, 17 May 2012 20:27:06 -0700, "Danny J. Lesandrini"
<da...@lesandrini.com> wrote:

> I understand they already have SQL
>Express apps on their laptops and knowing I can create an independent instance is helpful. I thought that was the case
>but I've never actually experienced it myself.

If they already have SQL Express installed then create another
database within the current existing instance. Pretty much exactly
that same basic concept as creating an MDB file except that it's SQL
Server managing things rather than Jet/ACE.

Develop your solution using SQL Server 2005 and you'll know it will
work on newer versions. Avoid using the sp_ stored procedures as
those are deprecated and don't work on SQL Azure.

Gene Wirchenko

unread,
May 18, 2012, 5:17:17 PM5/18/12
to
On Fri, 18 May 2012 14:07:15 -0600, Tony Toews
<tto...@telusplanet.net> wrote:

>On Fri, 18 May 2012 05:59:30 -0700, "Danny J. Lesandrini"
><da...@lesandrini.com> wrote:
>
>>The data
>>doesn't include launch codes or the location of Uranium stockpiles ... just names and phone numbers.
>
>Where's that xkcd comic where the evil guy entered the launch
>coordinates once and his email account twice? <smile>

#970. That was a good one! Mr. Munroe has a wonderful
perversity streak.

>>2) The persons getting this database are considered responsible and trustworthy.
>>
>>Not that someone might not be deceiving their superiors or might not turn bad later, but we're talking about police and
>>fire fighters who need this contact list not hot-dog venders.
>
>So their chances of being evil are much smaller but still a
>possibility.

As covered in RISKS
http://catless.ncl.ac.uk/Risks/21.58.html#subj5.1

Sincerely,

Gene Wirchenko

Gene Wirchenko

unread,
May 18, 2012, 5:21:20 PM5/18/12
to
On Fri, 18 May 2012 14:12:55 -0600, Tony Toews
<tto...@telusplanet.net> wrote:

>On Fri, 18 May 2012 14:07:15 -0600, Tony Toews
><tto...@telusplanet.net> wrote:
>
>>>The data
>>>doesn't include launch codes or the location of Uranium stockpiles ... just names and phone numbers.
>>
>>Where's that xkcd comic where the evil guy entered the launch
>>coordinates once and his email account twice? <smile>
>
>Searching for it brought me to
>http://whitehouse.gov1.info/launch/index.html
>Nice URL.

Hilarious!

The list of countries to bomb excludes the U.S.A. Having
followed RISKS for so long, I just had to check.

I bombed Canada myself. I have not noticed any difference.
Maybe, I got Toronto. (I am in British Columbia.)

Sincerely,

Gene Wirchenko

Tony Toews

unread,
May 18, 2012, 6:04:50 PM5/18/12
to
On Fri, 18 May 2012 14:21:20 -0700, Gene Wirchenko <ge...@ocis.net>
wrote:

>Having
>followed RISKS for so long, I just had to check.

I've noticed your name there a few times now.

> I bombed Canada myself. I have not noticed any difference.
>Maybe, I got Toronto. (I am in British Columbia.)

Ahhhh, I'm in Alberta. Send me an email and next time I'm in your
area we can get together for a beverage.

Don't get me started on the strike by the Quebec students. What a
farce.

Tony Toews

unread,
May 18, 2012, 6:07:57 PM5/18/12
to
On Fri, 18 May 2012 14:17:17 -0700, Gene Wirchenko <ge...@ocis.net>
wrote:

>>>Not that someone might not be deceiving their superiors or might not turn bad later, but we're talking about police and
>>>fire fighters who need this contact list not hot-dog venders.
>>
>>So their chances of being evil are much smaller but still a
>>possibility.
>
> As covered in RISKS
> http://catless.ncl.ac.uk/Risks/21.58.html#subj5.1

Or the RCMP member in the BC lower mainland who allegedly gave a list
of home addresses of abortion cliniic employees, gleaned from license
plates in the parking lot, to abortion protesters. Unfortunately as
the RCMP members then used a common userid and password they couldn't
prove it was the particular member. Now RCMP members all have their
own userid and password.

Gene Wirchenko

unread,
May 18, 2012, 6:36:28 PM5/18/12
to
On Fri, 18 May 2012 16:04:50 -0600, Tony Toews
<tto...@telusplanet.net> wrote:

>On Fri, 18 May 2012 14:21:20 -0700, Gene Wirchenko <ge...@ocis.net>
>wrote:
>
>>Having
>>followed RISKS for so long, I just had to check.
>
>I've noticed your name there a few times now.

I follow a few industry Websites and submit anything that looks
RISKy.

>> I bombed Canada myself. I have not noticed any difference.
>>Maybe, I got Toronto. (I am in British Columbia.)
>
>Ahhhh, I'm in Alberta. Send me an email and next time I'm in your
>area we can get together for a beverage.

Done.

>Don't get me started on the strike by the Quebec students. What a
>farce.

I think back to how much I paid. I finally got my Bachelor of
Computing Science (at age 49) in 2010. I think that strike is silly.

Sincerely,

Gene Wirchenko

Tony Toews

unread,
May 18, 2012, 6:45:17 PM5/18/12
to
On Fri, 18 May 2012 15:36:28 -0700, Gene Wirchenko <ge...@ocis.net>
wrote:

>>Ahhhh, I'm in Alberta. Send me an email and next time I'm in your
>>area we can get together for a beverage.
>
> Done.

Oops. That'll bounce. tony at granite dot ab.ca

>>Don't get me started on the strike by the Quebec students. What a
>>farce.
>
> I think back to how much I paid. I finally got my Bachelor of
>Computing Science (at age 49) in 2010. I think that strike is silly.

They can protest and strike all they want. I have no problem with
that. But if they don't do the course work or exams as per the
instructor or calendar then they flunk.

The Frog

unread,
May 18, 2012, 10:40:13 PM5/18/12
to
Hi All,

Long time no speak. Its been a while indeed. Just got my PC back after
international shipping and was cruising through the group threads and
saw this one. I thought to myself that perhaps I could lend some
security experience and know how to this that might be of use to the
OP. So here goes:

What the OP has is a siutation which is as yet, from a security point
of view, not well defined. This in turn means that there is little to
no possibility of accurately understanding the risks involved.
Therefore any attempts at securing the data might be right off the
track no matter how well intentioned they may be. So what to do about
it?

Basically what you need to solve this issue is a clear risk analysis.
This is not so much a problem about what product to use to push and
pull the data around (replication - or more simply your data
transport), but one of rightful access to the appropriate data as
needed and not otherwise. Putting the products to use aside for the
time being you have to have a clear understanding of who can access
what data and when. You also need a clear understanding of the risks
involved if this data is in the wrong hands, and the likely attack
vectors that are going to be employed against you as well as your
likely adversaries in this scenario.

If we assume that your advesaries are of a sophistocated nature with
resources to expend against your security model, then you may want to
involve a security consultant who is familiar in handling these types
of risks to provide advice on how to manage them. If you are dealing
with a 'script kiddies' as an opponent then you ccan probably forgo
the heavy duty stuff and use a lot of common sense and some know how
to mitigate the risks as much as possible. That being said there is no
practical encryption method that I am aware of in the type of
situation you have described that will stop someone who is truly
determined to get to the data, including a disgruntled employee. There
are however steps that can be taken as to make the barriers to success
for these individuals quite high. Lets explore this for a moment using
credit card data as a suitable example:

You want to store data in a secure way that can be accessed with ease
by those who are allowed to and not by those who cant. The data must
be able to be securely transported either over the wire possibly
through insecure public networks, or by physical media which may also
be intercepted. So in this sense you have two issues to concern
yourself with, transmission and storage. In the case of both of these
they can to a certain degree be mitigated (mostly) by the use of
properly employed cryptography. To be clear here cryptography is not a
cure all that can simply be slapped on and 'there you go' problem
solved. Its not quite so simple.

In choosing a cryptographic methodology to match the perceived risks
you have to ask yourself how the data is going to be used and
accessed. What happens when a field user has the data on their system
but is no longer allowed to access it? What happens if a layer of your
security model is compromised and needs to be replaced? What happens
if someone has their permissions for data access changed from one set
of rules to another? How do you ensure this model is consistently used
and enforced across all the userbase? How do you know if someone has
accessed the data without just cause? Are there other scenarios that
need to be taken into consideration before deciding on a security
model to counter these threats? This is not slap dash stuff that you
can simply ignore or not take into consideration when deciding on
which way to go. What you are looking for is termed security in depth,
and it takes some thought to achieve a practical solution to handling
preceived risks.

So, back to the credit card data scenario: Lets assume there is a
similar need / use case for this type of data that matches the
description the OP has provided. Before settling on a database
technology to use you need to design a suitable data handling
methodology. In this case you might say something like "We're going to
encrypt the column of card data with a strong password". Fair enough.
So is it appropriate that an all-or-nothing approach to the card data
be used? Do all the users of this hypothetical system need full access
to all the data (assuming they have permission of course)? Probably
not. How on earth could you track this? How do you distribute a new
password to everyone when the old one is deemed insecure? So a few
practical problems start to appear.

You decide perhaps to use a more sophistocated approach. Each credit
cards details are secured by a unique password, and we will have a
table that allows users with certain privilidges to access certain
cards (technically a little oversimplified but lets work with it for a
moment). So you have limited the access to data to one card at a time.
What happens if you need to work with more than one card at a time?
What is a 'customer' has more than one card? So now you need a
slightly more complex model again: one that uses a unique key (Im
going to stop using password here for this purpose) per customer, that
then allows access to that customers card(s) data. You might add to
this a method of assigning users to customers, but this then gets
tedious as you need to manage both. Its not impossible to do this, and
I have seen those who have done it, but I can assure you for any
practical system it is a pain to look after.

So we move to the next level of complexity: a two stage system. This
is a set of principals similar to those used in EFS. The records have
unique keys applied to them, but these keys are in turn protected by
another layer of cryptography. So the keys for the records are unique,
but are in turn encrypted with a 'master key'. Now this could be done
on a per record basis or a per table basis, or per column etc.... To
access the master key for a given set of data the user has to have
this provided to them, and in turn secured, so yet another layer of
cryptography. The users access to record master keys needs to be
secured the same way the record master keys themselves are. By now you
are beginning to see, even before we look at specific technologies or
products, that the application of cryptography is not so straight
forward as one might assume. In fact the possibility exists to extend
this logic quite a few steps further.

The question really is: How do you need to apply security in depth
(most likely cryptography and a few other things) given your scenario?
I really dont mean to be the boogieman here but I do wish to point out
that the coments above, though well intentioned, are not necessarily
suitable to your risks. What I would advise you to do is to take the
time to have a risk assessment done so that at least you know where
you have to start from. Without that you are just guessing and more
than likely, as is the case even with experienced people, miss
something important. I would not be focussing on any given technology
at this point, at least until you have had the chance to come up with
a suitable security model. Once you have a model that addresses the
risks then you can start looking at the technologies available to
implement that model. This is not to say doing this is necessarily
hard, but it can be tedious.

Security is one of those areas, especially in technology terms, that
you dont really hear a lot about unless something goes wrong. It is
poorly understood by most, even experienced developers, simply because
you have to be trained to deal with it properly. Cryptography itself
and the application of it to any given scenario is a bit of an art,
and one well worth the learning. But I urge you not to simply jump in
without all the necessary steps being taken, especially when it deals
with handling people, as is the scenario you have mentioned. You may
find that there is actually a standard that needs to be adhered to for
handling this type of data and scenario. I would urge you to find out.

The tools are available to manage most risks, but you need to
understand the risks clearly first before employing any tools to
manage them. What appears to be discussed in previous posts is doing
this backwards to proper security methodologies. It is great that you
have stepped up and asked for help, that is most definitely the right
thing to do. So in short, my advice to you is start at the beginning
and get a risk assessment done and go from there. I am sure that we
here in this forum will offer whatever assistance we can when we know
clearly what the model to be implemented is. Dealing with poeples
private data is something has is commonly done, but I feel that your
given scenario is a little more delicate and demanding that the
average.

Feel free to conact me directly if you wish, but I would ask that most
requests be posted here in the forum so that others can learn from
them as well.

Cheers

The Frog
(not my real name!)

The Frog

unread,
May 18, 2012, 10:43:57 PM5/18/12
to
PS, to directly answer the OPs first post. What you ask can be done in
Access, possibly using SQL Express as a Back End (BE), or some other
database. The technical side of these topics have been covered before
but would need to be applied to your situation when a known security
model (and table structure appropriate to this model) are determined.

The Frog

The Frog

unread,
May 18, 2012, 10:45:39 PM5/18/12
to
PPS: Hows it going guys? Its been a long time! (Sorry for that)

Patrick Finucane

unread,
May 19, 2012, 12:39:57 PM5/19/12
to
On May 15, 6:10 pm, "Danny J. Lesandrini" <da...@lesandrini.com>
wrote:
> I just met with a new client who needs a couple things that, last I heard,
> Access 2010 doesn't support:  Security and Replication (synchronization).
>
> How can a database be secured in Access 2010?  Let's assume this is a
> contact database with information that cannot (if stolen) be made public.
> Assume also that there are various groups that are allowed to see only
> those contacts to whom they have rights.  How is this handled these days?
>
> I assume I'd roll my own login system and manage data access in VBA.
> Can the tables be reliably obfuscated?  Does suppressing the F11 action
> really work to prevent users from getting to the raw data?
>
> As for "replication", I know that's not supported in Access 2010.  What I
> think they really need is "synchronization".  Let's say there are 3 users
> who enter and update contact info and 500 users who need only access
> the data and search for contacts.  Because they want this info to be
> available in case of a disaster, they don't want a "web based" solution
> but rather, want a local Access database that regularly gets "synched"
> with the main database.  What approach comes to mind?
>
> I was thinking of using a single Access file with local tables that are
> truncated and reloaded with data whenever a network connection is
> detected.  When off-line, as would be the case in a disaster, the list
> would be as current as the date of last network access.
>
> Ideas?
> --
> Danny Lesandriniwww.lesandrini.com/datafast/

I've followed this thread but it is now into things I'm never going to
be concerned with.

Unfortunately, David Fenton passed away so the person in the
replication know-how can't help I decided to punch in his name and
the word Replication into Google and got this link.
http://dfenton.com/DFA/Replication/index.php?title=Replication_Wiki_Frequently_Asked_Jet_Replication_Questions_%28FAQ%29

I did this a while ago. I created a front end/back end. I opened the
backend, and encrypted the database with the password. I then opened
the frontend, and encrypted it. With the tables linked, I was able to
open the fronted, be prompted for a pw and was able to use the tables
in the backend.

The next step would be to make the front end an accde. Lock the
database window down and scroll-break and not full menus.

I'd think the DB is pretty safe at this point. If a person stole both
fe/be, the person would not be able to change the table links...at
least I don't think so...

JMT

Tony Toews

unread,
May 20, 2012, 9:07:13 PM5/20/12
to
On Fri, 18 May 2012 19:45:39 -0700 (PDT), The Frog
<mr.frog...@googlemail.com> wrote:

>PPS: Hows it going guys? Its been a long time! (Sorry for that)

Hard at work on the Web edition of the Auto FE Updater.

Gene Wirchenko

unread,
May 20, 2012, 11:52:57 PM5/20/12
to
On Fri, 18 May 2012 16:45:17 -0600, Tony Toews
<tto...@telusplanet.net> wrote:

>On Fri, 18 May 2012 15:36:28 -0700, Gene Wirchenko <ge...@ocis.net>
>wrote:
>
>>>Ahhhh, I'm in Alberta. Send me an email and next time I'm in your
>>>area we can get together for a beverage.
>>
>> Done.
>
>Oops. That'll bounce. tony at granite dot ab.ca

It did, and I have reposted.

>>>Don't get me started on the strike by the Quebec students. What a
>>>farce.
>>
>> I think back to how much I paid. I finally got my Bachelor of
>>Computing Science (at age 49) in 2010. I think that strike is silly.
>
>They can protest and strike all they want. I have no problem with
>that. But if they don't do the course work or exams as per the
>instructor or calendar then they flunk.

It has been nastier than just protesting. Bystander businesses
are losing significant amounts of business per one article I read.

Sincerely,

Gene Wirchenko
0 new messages