Impossible to secure an access Db

[Guest]

Ok, just checking again. I have had a rude awakening that basically there is
NO security with an access database!!!!!! I was told to use user level
security, then use RWOP queries, etc., which I did. However, WHAT NO ONE TOLD
ME WAS THAT IT IS NOT POSSIBLE TO SECURE AN ACCESS DATA BASE (.mdb file)!!!!
AND THAT ULS ONLY WORKS IF THE USERS PROMISE NOT TO COPY THE BE AND OPEN IT
WITH READILY AVAILABLE SOFTWARE!!!!!.
Now i have a database that is useless and that the University will not let
me use because it can't be secured!!!!!! It would have been nice if someone
had mentioned this. It took the IT people only a few minutes to run a
commercial DB recovery program that 'recovered' my ULS protected Db but now
without ULS. Now I am being told that I must migrate to SQL server etc. if I
want to use it. Come on, is Access really this wimpy?? how can people be
writing applications for taking credit cards, people's phone numbers,
birthdates etc. and expect anyone to use them??

I would GREATLY appreciate any help/direction on what to do now. For
example, is it easy to migrate to SQL Server if I have essentially no data in
the BE? I would like to keep the FE, and I have to completely secure the BE.
PLEASE HELP!!!!!!!!!!!!!

 

[Rick Brandt]

Ok, just checking again. I have had a rude awakening that basically
there is NO security with an access database!!!!!! I was told to use
user level security, then use RWOP queries, etc., which I did.
However, WHAT NO ONE TOLD ME WAS THAT IT IS NOT POSSIBLE TO SECURE AN
ACCESS DATA BASE (.mdb file)!!!! AND THAT ULS ONLY WORKS IF THE USERS
PROMISE NOT TO COPY THE BE AND OPEN IT WITH READILY AVAILABLE
SOFTWARE!!!!!.
Now i have a database that is useless and that the University will
not let me use because it can't be secured!!!!!! It would have been
nice if someone had mentioned this. It took the IT people only a few
minutes to run a commercial DB recovery program that 'recovered' my
ULS protected Db but now without ULS. Now I am being told that I must
migrate to SQL server etc. if I want to use it. Come on, is Access
really this wimpy?? how can people be writing applications for taking
credit cards, people's phone numbers, birthdates etc. and expect
anyone to use them??

People writing such apps don't use an MDB to store the data (if they know
what they're doing). I have posted on these groups numerous times..."If you
need to protect your data from non-users, use network security. If you need
to protect your data from users then DON'T store it in an MDB".

I would GREATLY appreciate any help/direction on what to do now. For
example, is it easy to migrate to SQL Server if I have essentially no
data in the BE? I would like to keep the FE, and I have to completely
secure the BE. PLEASE HELP!!!!!!!!!!!!!

Depending on the app's design, moving to SQL Server can be a snap or it can
be a big chore. There is no catch-all answer. Making the switch and
getting the app to basically function should be no big deal. Making it
function *well* could take considerably more work.

 

[Guest]

People writing such apps don't use an MDB to store the data (if they know
what they're doing). I have posted on these groups numerous times..."If you
need to protect your data from non-users, use network security. If you need
to protect your data from users then DON'T store it in an MDB".


Depending on the app's design, moving to SQL Server can be a snap or it can
be a big chore. There is no catch-all answer. Making the switch and
getting the app to basically function should be no big deal. Making it
function *well* could take considerably more work.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com




It might be worth your while looking at Access 2007 which has encryption and passwords like proper databases.

However I'm concerned that Rick hasn't mentioned this - but its still worth
a look.

IanO

 

['69 Camaro]

Hi.

Now I am being told that I must migrate to SQL server etc. if I
want to use it. Come on, is Access really this wimpy??

_All_ file-based databases cannot be secured, so don't blame Access. And
here's some news for IT people: anyone with Windows administrator
permissions on the directory where the SQL Server database file is located
can attack that database file, too. The ability to secure a database file
lies in the fact that non-authorized people can't access the file through
the operating system. Of course, file-based databases such as Access
require the user to have direct access to the file to be able to read and
write data. Client/server databases such as SQL Server and Oracle don't,
and that's why they can be secured more easily, but it's certainly possible
to set them up where security is non-existent, and is very common with
untrained people trying to act as if they were DBA's.

how can people be
writing applications for taking credit cards, people's phone numbers,
birthdates etc. and expect anyone to use them??

Access can safely be used as a front end to the data source in a
client/server database, as long as the user's credentials to log into the
client/server database are not stored in the Access database file.

I would GREATLY appreciate any help/direction on what to do now.

That depends. If your organization is already a "SQL Server shop," then go
that route. If your organization is already an "Oracle shop," then go that
route. If you have a large organization, then the expertise should be
readily available to help you migrate to a client/server. If the expertise
isn't available, then you have some book learning and hands-on experience to
work on before you'll be able to successfully handle this yourself.

For
example, is it easy to migrate to SQL Server if I have essentially no data
in
the BE?

That depends. Did you design it to be a split database or a
single-user-always-on-my-own-workstation database? If you set it up as a
split database, then you would have followed most of the steps outlined in
Access MVP Tom Wickerath's article, "Implementing a Successful Multiuser
Access/JET Application," on the following Web page, and migration issues
will be cut to mainly just the differences in SQL dialects and data types:

http://www.access.qbuilt.com/html/multiuser_applications.html

How well do you know SQL Server? If you aren't familiar with it, then you
either need a SQL Server DBA to set it up for you, or else you need to study
up on it before you'll be able to successfully create a SQL Server database
and secure it, then migrate (essentially upsize) the data to it.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Nando]

Rick/Joan, I'm curious about this too. What about the encryption feature? I
have never used it, but I believe is additional to the ULS. Please let me
know.

Also I think the main issue here is to disallow a user from opening the RAW
data of the tables, since the permissions of a user are assigned as per
table and not per row. Using secured RWOP queries in the font-end seems to
be a better approach, but I don't think that will satisfy every programmer's
need. What do you think?

 

[Guest]

thanks rick- all these comments from everyone have been VERY helpfull.
What about the access 2007 with encryption- would this alow multi users and
protect the data from both users and non-users??

thanks

 

[Rick Brandt]

However I'm concerned that Rick hasn't mentioned this - but its still
worth a look.

IanO

I have no idea what salmonella is talking about. Access 2007 uses the same
ULS as previous versions if you stay with the MDB file format and has no
security at all if you use the new AccDB file format.

Encrytpion might be different in Access 2007, I don't know. All I know is
that in previous versions encryption was about preventing people from seeing
your data with Notepad or a hex editor. It provides no protection from
viewing the data with Access which is what you are talking about.

 

[David W. Fenton]

I have no idea what salmonella is talking about. Access 2007 uses
the same ULS as previous versions if you stay with the MDB file
format and has no security at all if you use the new AccDB file
format.

Well, it has the database password, but supposedly with strengthened
encryption.

Encrytpion might be different in Access 2007, I don't know.

It is said to be much stronger than in past versions.

All I know is
that in previous versions encryption was about preventing people
from seeing your data with Notepad or a hex editor. It provides
no protection from viewing the data with Access which is what you
are talking about.

And it does nothing for protecting data from people with the
password, something that Jet ULS *does* allow.

I don't know what the Access team was thinking about when they
implemented this. It is no security at all -- it makes ULS look
safe.

Why they couldn't have simply upped the encryption level for the
data in the workgroup file is beyond me.

 

[David W. Fenton]

all these comments from everyone have been VERY helpfull.
What about the access 2007 with encryption- would this alow multi
users and protect the data from both users and non-users??

No. It doesn't do anything except protect you from someone who could
steal the data file but doesn't know the password. It offers no help
at all in protecting the data from people who need to use it.

 

[David W. Fenton]

_All_ file-based databases cannot be secured, so don't blame
Access.

I've been doing a little spiel for clients for many years about
security. The main issue is that you *can* protect yourself from
outsiders, but you can't protect yourself from employees who have to
have access to your data. I always tell the clients that this is a
management issue, that you have to have employees you can trust, and
if you don't trust them, then you can't give them access to your
database application.

This is true regardless of what kind of data store you use and what
kind of security you implement.

And when looked at in that manner, Jet security is very often
completely sufficient -- it is very seldom that I'd see security
alone as a reason to use a server back end. I'd never suggest it
except where there are other issues (24/7 availability, and/or no
tolerance for the loss of even one record, and/or high concurrency
needs).

 

[Guest]

David,
The problem is that this is a database that interphases between a student
researcher and their supervisor. It has information that other students
should not view. ULS is fine for this as long as they 'promise' not to
override the ULS. For this reason I am not alowed to use it by the
university. So you see it is not a matter of whether I trust someone-
security regs state that certain information must be secured and not
entrusted. It would be nice if somehow they could encrypt it or something
and where a simple recovery program would not be able to give access to the
data. It just seems strange that in todays world of security concerns that
the access group does not address this (if technically possible) because
having a person that you trust just does not work anymore and probably opens
you up to a big lawsuit if someone YOU chose to trust caused damage through
theft of information. Too bad 2007 did not fix this problem

 

[Guest]

I've been doing a little spiel for clients for many years about
security. The main issue is that you *can* protect yourself from
outsiders, but you can't protect yourself from employees who have to
have access to your data. I always tell the clients that this is a
management issue, that you have to have employees you can trust, and
if you don't trust them, then you can't give them access to your
database application.

This is true regardless of what kind of data store you use and what
kind of security you implement.

And when looked at in that manner, Jet security is very often
completely sufficient -- it is very seldom that I'd see security
alone as a reason to use a server back end. I'd never suggest it
except where there are other issues (24/7 availability, and/or no
tolerance for the loss of even one record, and/or high concurrency
needs).

It should be possible for the FE application to open the password protected,
encrypted backend (if it's a Access 2007 .accde file), where the password is
stored in the application (i.e.the user doesn't know it). That way the user
can't open the BE file directly - he (or she) doesn't have the password.

However this assumes that it is possible to open a password protected
encrypted .accdb or .accde file using Visual basic, something that should be
possible according to Access help.

I've tried and failed to do this - see post "How to Open a Password
encrypted .accdb file in Access 2007 using in VB?".

IanO

 

[David W. Fenton]

The problem is that this is a database that interphases between a
student researcher and their supervisor. It has information that
other students should not view. ULS is fine for this as long as
they 'promise' not to override the ULS.

If properly implemented (which very often doesn't happen) ULS cannot
be overridden except by someone who has decided to crack Jet's ULS.
That should be a dismissable offense, in my opinion, as it shows a
desire to circumvent in-place security precautions.

I would say the same about someone who gets a password cracker to
get into an Excel spreadsheet -- and that's much weaker security
than Jet ULS.

For this reason I am not alowed to use it by the
university. So you see it is not a matter of whether I trust
someone- security regs state that certain information must be
secured and not entrusted. It would be nice if somehow they could
encrypt it or something and where a simple recovery program
would not be able to give access to the data. It just seems
strange that in todays world of security concerns that the access
group does not address this (if technically possible) because
having a person that you trust just does not work anymore and
probably opens you up to a big lawsuit if someone YOU chose to
trust caused damage through theft of information. Too bad 2007 did
not fix this problem

I think 2007 made it worse, by introducing a single-password point
of failure. And I'm not sure exactly how you would protect the
password in a front end (it would have to be encrypted so the VBA
code with the password in it is not shown, and you couldn't use
linked tables, since the password has to be stored in the CONNECT
string, and that will be visible to anyone who knows where to look).

It is the way ot is.

Sounds to me like your university's rules require that you use a
server back end with security (e.g., MySQL wouldn't really be
sufficient). Since the university's rules prevent a cheaper solution
they should subsidize this development cost by providing support in
implementing and administering it, at the very least.

 

[Chris Mills]

If the password is stored in the application, and the application (ie FE) is
Access, then most anyone here could discover it. It could completely undo any
advantages of a "more secure BE"! I believe Gunny alluded to this
"transgression of security", i.e. SQL Server etc is only as secure as NOT
knowing (or being able to find) a password.

OTOH, if a user has a valid password, then in principle, and if they have a
bit of IT experience, they can copy out any data they have access to. I
believe David alluded to this (not being able to protect from valid
employees).

If you want, say, employee salaries to be in a table, but accessible only to
management, well you'd better hope that no employees have adequate IT
knowledge! One way with only limited security, is RWOP queries, another is to
link tables to a physical mdb/table which holds that data and only management
has physical access to? (haven't tried it)
-----
I was once asked to provide look-up data but nothing else (e.g. no printouts
or tabular views). I could sort-of do it, by disabling all unsuitable keys
etc, and more importantly by individually encrypting the tables so that only
my "form" could interpret it, and not, say, just a Table view.

Well, I experimented but did not proceed. The fact remains, someone of the
experience level to be found in this newsgroup, could decrypt my mde/form and
therefore discover how it encrypts/decrypts the table! It becomes no-win,
unless one establishes who one wishes to protect against. End Users? I
probably used overkill. The Gunny's of this world? I would not even pretend I
can hide anything from them

I've used up my 2 cents. So...stop reading!

However, I don't believe it's as simple as slagging "file-based" vs
"client-server", since both are "file-based" to an administrator with
appropriate access. Of course, I think Gunny alluded to this too! It is
necessary to define who you wish to protect against, and it can't really be
your most experienced IT person. This is why, I believe, ULS still has a place
for practical protection in a business environment full of, umm, "clerks" let
us say. A "student researcher" is probably, shall we say, relatively
inquisitive!

Neither is there any reason to express surprise. After all, the USA didn't
hold the bomb secret for very long. And in a way, this ng has taught me a lot
more than is good for me! What can you expect if we get a bunch of experts
together on how to break into your house?

Chris