Protect MDB any MVP

[Andy]

Hi;

This quest started with a post: InputBox Mask ****** & Call it 3 times.

Added a password to my dbase, (A2K), and instead of adding another frm, w/a
module and code wanted a simple InputBox asking for a password and the text
would be masked.

Thanks to the help of some good people learned quickly that an InputBox
could not have a mask.

And also learned this from Rob: "You know that any of these methods (and
that also
includes the MS one) can be overridden by just holding down the Shift key?
Sure enough Launch Access, Click on File/Open. Select the dbase's name.
Hold the Shift key down and click the Open button."

You didn't have to knock me over with a feather because I was already on the
floor. All of my tbls/qrys/frm/rpts and macros were displayed and
accessible for design changes.

As Charlie Brown would say "RATS" or for us older folks as "Major Hoople"
would say "EGAD".

Started searching for an answer. Including but not limited to User-Level
Security.
Since the dbase is on scattered stand alone PCs that wasn't the answer.

Put a call out to programmers I know and also called/e-mailed friends and
clients.
Got an answer from an old friend. Old in the sense that he is in his 80s.
He told me that through the years he had different "Kids" working on his
computers and that he had a "Sophisticated" program that tracks his
inventory and that it did run in Access. "It says so everytime he starts
the program"

Went to his office yesterday to see this "Sophisticated" program. My friend
has A2K. The database has an MDB extenstion, not MDE, not ADP, MDB.

Launched Access, Clicked on File/Open. Selected the dbase's name. Held the
Shift key down and clicked the Open button.

The dbase opened but all of the qrys/frms/rpts were dimmed and when I right
clicked on the frms names the "Design" option was grayed and un-usable.
Even when the frms were imported into a new dbase You still couldn't get
into design view for those frms.

Asked for the "Kids" name and number. He didn't remember who made the
Database for him, it was so long ago. Through the years he has had a lot of
kids helping him. Some paid/Some free.

This morning began searching for an answer. Msft, MVPS, Google and a few
hundred others.
Did find a reference about adding "USYS" to the begining of the tbl's name
at http://www.mvps.org/access/tables/tbl0004.htm. but then it goes on to
say "To see this table later, choose view, options, show system objects".

This couldn't be the answer because all of the objects were visible but
dimmed and none started w/"USYS".

Perhaps this "Kid" is the only person that knows the answer, however I
thought that this information was important enough to share with others.

Thank You for reading this and if You know of a solution would You be kind
enough to steer us in the correct direction.

Andy

 

[Arvin Meyer [MVP]]

Access Security is not absolute because it relies on a password. Practically
every password can be cracked, so, in the end, there is no security to a
determined hacker. That said, there are ways to disable the ByPassKey (the
Shift key) and make your database resonably secure, even without a password.
Read the Security FAQ carefully, several times. The answers are in there:

http://support.microsoft.com/default.aspx?scid=/support/access/content/secfaq.asp
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Rick B]

Just a thought, did he modify the settings for the ADMIN user in the default
system.mdw workgroup on the user's pc? If you take it home and open it with
your workgroup file, do you have the ability to modify the items? If you
replace your old friend's workgroup file, what happens? If you go into the
security settings, what do you see there?

My bet is that he did not assign a password, but he changed permissions.

 

[Marshall Barton]

Andy wrote:
[snip]

Went to his office yesterday to see this "Sophisticated" program. My friend
has A2K. The database has an MDB extenstion, not MDE, not ADP, MDB.

Launched Access, Clicked on File/Open. Selected the dbase's name. Held the
Shift key down and clicked the Open button.

The dbase opened but all of the qrys/frms/rpts were dimmed and when I right
clicked on the frms names the "Design" option was grayed and un-usable.
Even when the frms were imported into a new dbase You still couldn't get
into design view for those frms.

I'm not sure, but Isn't that the effect you would see if the
file was an MDE that was renamed to MDB?

 

[Tim]

Andy,

Have a look at this to disable the shift-bypass key.

http://www.databasedev.co.uk/disable_shift_bypass.html

I've implemented this in an MDB, and so long as you protect your code with a
password you can be sure that short of cracking your protect-password, users
are not going to see the password for enabling the Shift-Bypass.

 

[Tim Ferguson]

I've implemented this in an MDB, and so long as you protect your code
with a password you can be sure that short of cracking your
protect-password, users are not going to see the password for enabling
the Shift-Bypass.

Is there any sensible way to prevent a user creating a new file and
importing the objects from it?

Or reading the data by linking from a new mdb. Or from Excel?

IMV, if you need to lock up the data use Workgroup/ User-level security.
As pointed out earlier, this is not infallible, so if you really, really,
really need to lock them up, then use a proper DBMS with full security.
If you don't need it that badly, then get a life. In the end, educating
your users will pay off far better dividends than any code-based jiggery-
pokery: the latter will also merely present a challenge for any half-able
user with an interest.

Just a thought....


Tim F

 

[Albert D.Kallal]

A few things:

As for that mdb database that does not allow modifications, two things could
have been done:

1) the security workgroup file IS setup, and design privileges are removed
from the admin user, but no password

2) as a few others pointed out, if your renamed a mde to a mdb, you would
get this results

Both the above seem quite reasonable concepts. Not a big secret.

This quest started with a post: InputBox Mask ****** & Call it 3 times.

Well, the problem here is that you are hand coding your own security, and is
the start of this problem

Thanks to the help of some good people learned quickly that an InputBox
could not have a mask.

Sure, but noting stops you from making a custom dialog form, and that does
allow you this.
So, why not use a custom dialog form that you make?

And also learned this from Rob: "You know that any of these methods (and
that also
includes the MS one) can be overridden by just holding down the Shift key?
Sure enough Launch Access, Click on File/Open. Select the dbase's name.
Hold the Shift key down and click the Open button."

Well, I have to use the shift key about 75% of the time during the
development process. I mean, if you disable things via the tools->startup,
then how are earth could YOU get in to develop the application?

During the day, I will shift key into the application. Make some changes,
and then go alt-f4 to close the application. The cursor is now sitting on
the mdb file, and then I whack the enter key (and do not hold down the
shift) to run the application in "end user mode".

As a developer, you can't possibly open a database with the tools->startup
stuff set, as that would prevent you from working on the application!
(this begs the question --- on your system, if you hide everything via the
tools ->startup..then how did YOU get into modify those forms etc??).

So, you will go in and out of a application 100's of times in a day during
development to switch from developer mode, and then to switch back to "user"
mode to "test" things.

So, I accept that the shift key thing may be new to you, but it really will
become part of your standard development process.


Now, lets get back to the security issues:

First, just like learning to using the shift-key by-pass all day long, you
will quickly learn that you need to split your database if you plan to
"update" your users applications. So, please read the following article as
to why you split, and in this article I also talk about the development
process here.

The simple solutions here is to do two things:

1) ALWAYS distribute a mde to your users. This is exactly what this is
for. You don't even have to set security, or nothing, but a mde means that
users cannot modify your reports, forms, and code. It just like using shift
key during the day 100's of times is STANDARD FAIR

2) Consider implementing security. Considering the about of time you
spent on this already, security might be a solution here.


As for hiding all of the ms-access interface, you don't actually need any
code.


You most certainly can, and should hide all of the ms-access interface. The
options to complete hide and keep people out of the ms-access interface can
easily be done using the tools->start-up options. Using those options allows
you to complete hide the ms-access interface (tool bars, database window
etc). Also, using these options means you
do not have to bother setting up security.

Try downloading and running the 3rd example at my following web site that
shows a hidden ms-access interface, and NO CODE is required to do
this....but just some settings in the start-up.

Check out:

http://www.members.shaw.ca/AlbertKallal/msaccess/DownLoad.htm

After you try the application, you can exit, and then re-load the
application, but hold down the shift key to by-pass the start-up options. If
want, you can even disable the shift key by pass. I have a sample mdb file
that will let you "set" the shift key bypass on any application you want.
You can get this at:

http://www.members.shaw.ca/AlbertKallal/

 

[Andy]

Gentlemen;

Thank You for Your answers.

The delayed reply was caused by me having to "Put Out Fires".

All of You have taught me amazing things.

Thank You again.

Andy

 

[Arvin Meyer [MVP]]

It really depends upon what you need to lock up. If it's the forms,
reports, and code, an MDE should do the job nicely. The data is only safe if
protected by a server. If you create a new database, you cannot import or
export objects from a database front-end that is properly secured or is an
MDE. As with anything, a really determined hacker can probably get in, but
the average user is kept out.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Andy]

Hi;

Been "Absorbing" the information all of You have provided.

Went back and tried the shift key to open that "Sophisticated" database
mentioned in the first post..

The AllowByPassKey was disabled.

The curious part, at least to me is this:
When any of the forms were imported into a new DBase the "Design View" was
"Grayed out".

Couldn't get into design view of any of the imported Forms. As if the
"Allow Design Changes" property has been set to "False", not an option in
the list.

The computer was not set for User Level Permissions, at least in the general
sense of having to log on, the PC always starts as administrator, (WindowsXP
w/Access 2002).

One more item:
Function faq_DisableShiftKeyBypass(strDBName as String, fAllow as Boolean)
As Boolean
From:
http://support.microsoft.com/default.aspx?scid=/support/access/content/secfaq.asp

It says it can be run from the Immediate Window.

Presume I place the function in a module and then type:
faq_DisableShiftKeyBypass
Then hit the Enter key.

It causes an error. Believe that is because I need to change a line
somewhere to the name of the Dbase.

After all these years and all the thousand of support pages that have been
read I feel like a "Newbie" again. AND ITS NOT A PLEASANT FEELING!!!

There must be a way to block design changes in forms and reports, know that
because I've seen it. Have yet to find a way to repeat it. Maybe this
"Kid" created his own code.
Don't know that. Do know this; I will learn how to do it again.

Thank You for Your kindness and help.

Andy

 

[Douglas J Steele]

It would help to know the exact error message, but I'm betting the problem
is that that routine wasn't updated to reflect the fact that ADO has a
higher precedence than DAO in newer versions of Access (In fact, DAO isn't
even enable by default in Access 2000 or 2002).

Try changing

Dim ws As Workspace
Dim db As DATABASE
Dim prop As Property

to

Dim ws As DAO.Workspace
Dim db As DAO.DATABASE
Dim prop As DAO.Property

(Actually, only the 3rd is essential: there's a Property object in both the
ADO and DAO models, but Workspace and Database are unique to DAO. Still,
it's good practice to disambiguate)

As well, the function requires 2 arguments be passed to it. The first
argument is the full path to the database, and the second argument is
whether or not you want to disable the bypass key.

In the Immediate window, you need to type:

?faq_DisableShiftKeyBypass("C:\MyDatabase.mdb", True)

(including the question mark: replace C:\MyDatabase.mdb with the appropriate
path). It should return True if successful, False if not.

 

[Tim Ferguson]

It really depends upon what you need to lock up. If it's the forms,
reports, and code, an MDE should do the job nicely. The data is only
safe if protected by a server. If you create a new database, you
cannot import or export objects from a database front-end that is
properly secured or is an MDE.

I don't really understand the protectionism about hiding VBA code; but
then again, I don't earn my living from it either. Securing data, though,
is clearly a priority in many situations and that can be done by nothing
less than user-level workgroup security -- which is where the response to
this thread came in.

As with anything, a really determined
hacker can probably get in, but the average user is kept out.

Natch.


B Wishes

Tim F

 

[Albert D.Kallal]

The curious part, at least to me is this:

When any of the forms were imported into a new DBase the "Design View" was
"Grayed out".

Since you can import, then likely security was indeed setup.

The computer was not set for User Level Permissions, at least in the
general
sense of having to log on, the PC always starts as administrator,
(WindowsXP
w/Access 2002).

Don't confuse windows security with ms-access security, they are SEPARATE
issues. ms-access security (workgroup) does NOT use, or have ANY relation to
windows security. It is *still* possible (in fact likely) that workgroup
security was used. When you run ms-access, you ALWAYS MUST HAVE a workgroup
file. (ms-access WILL NOT START if you don't). Further, when you run
ms-access you ALWAYS MUST HAVE a user name. That default user name is
"admin". If the workgroup user password for admin is blank, then that is the
reason WHY you are not getting a logon prompt.

One more item:

Function faq_DisableShiftKeyBypass(strDBName as String, fAllow as Boolean)
As Boolean
From:
http://support.microsoft.com/default.aspx?scid=/support/access/content/secfaq.asp

It says it can be run from the Immediate Window.

Presume I place the function in a module and then type:
faq_DisableShiftKeyBypass
Then hit the Enter key.

Yes, the function obviously requites you to enter the name. Note

Function faq_DisableShiftKeyBypass(strDBName as String, fAllow as Boolean)

Look at the above....it needs two parameters.

Note that if you put the code in side of a database, then obviously it is
only going to be of "use" ONLY the next time launch the database. (since
you are already in). So, if you lock yourself out, then you need some "door"
(a special key, or hidden button, or something that you do to un-shift the
application). If you don't want to use the trap door, then grab my sample,
as it lets you "browse" to a given file, and change the setting. It uses the
above code.

There must be a way to block design changes in forms and reports, know
that
because I've seen it. Have yet to find a way to repeat it. Maybe this
"Kid" created his own code.
Don't know that. Do know this; I will learn how to do it again.

Since you *can* import forms, then it can't be a mde file. (if you make a
mde, then forms + reports don't import). So, logically, then it means that
security was used on this file..........

If you read the security document (found here)

http://www.mvps.org/access/resources/techpapers.htm

It does explain how to setup security, and not requite a user to logon....

Read the above.

If you open up user security settings, you should be able to see the
permsisons set for those forms.

Regardless, read the secrty faq. there is both a on-line, and downloadable
links can be found in the above...

 

[Andy]

Hi;

Tried a suggestion from Marshall, renamed the extension MDE to MDB and was
still able to import the data from my "MDB" into a new DBase.

Andy

 

[Andy]

Albert;

Thank You. That was something I didn't know. I was confusing Windows Users
with Access Users.

The picture keeps getting clearer.

Andy

 

[Douglas J. Steele]

It's not even necessary to rename the file: you can import data from an .MDE

 

[Andy]

Hi;

THANK ALL OF YOU!!!

Learned alot and was able to secure the database.

Still in "Quick Sand" re:
Function faq_DisableShiftKeyBypass(strDBName As String, fAllow As Boolean)
As Boolean
(From MSFT's Security FAQ)

Learned from Albert that the code had 2 parameters.
1 it needed the Dbase name in the strDBName. Did that.
The 2nd parameter isn't "Revealing" itself. What is it?

Also Getting Compile errors in Debug.
The Function includes these:
Dim ws As Workspace
Dim db As DATABASE

Both Workspace and DATABASE are returning "User Defined type Not Defined".
Highlighted each in turn and hit the F1 key, result for both "Keyword Not
Found".

Seemed to me that: Dim ws As Workspace was referrig to an Excel worksheet.
Re-read the section in FAQ and it only refers to Access and not Excel.
Added referrence: Microsoft DAO 3.6 Object Library
And the Compile worked.

Ran:
faq_DisableShiftKeyBypass
From the Immediate Window and got: "Compile Error Argument not Optional."

Moved strDBName As String, fAllow As Boolean from Function's name and
created:
Dim strDBName As String
Dim fAllow As Boolean

The code runs and returns ERR msg:
"Function DisableShiftKeyBypass did not complete successfully."
That is normal. Comment states it will occur the first time the code is
run.
Ran it twice and even 3 times still get same ERR.

Went to: http://www.mvps.org/access/general/gen0040.htm
And tried to use a version of Michael Kaplan's example.
It runs without errors but still can open using the ShiftKey.

Also tried opening the Dbase exclusively and running the code. Same
results.

Learned a lot. THANK YOU.

Please be so kind and provide a little more guidance.

Andy

 

[Andy]

Hi again.

Read through all these posts and the primary thing that stuck with me was
that the shift key would not be able ot open an MDE exposing the database
window.

Yet it does on my computer. Is that because it is the same Access2k
package?

And it really is protected as an MDE.

Andy

 

[Rick Brandt]

Hi again.

Read through all these posts and the primary thing that stuck with me
was that the shift key would not be able ot open an MDE exposing the
database window.

Sorry, but an MDE has absolutely nothing to do with the shift key. An MDE
locks all code-based objects (Forms, Reports, and Modules) so their design
can neither be viewed nor changed. It does nothing to hide the db window
and provides zero protection for queries, macros, or tables.

You CAN hide the db window on startup and disable the shift bypass key, but
that is completely separate from making an MDE as you can also do those on
an MDB.

In addition if you disable the shift bypass key and don't also secure the
database then anyone who knows how can re-enable the bypass key by running
code from another file.

In any case all queries and tables can be imported or viewed from other
files unless User Level Security is set up.

 

[Andy]

Hi;

A question about an MDE believe that any Object in an MDE should not be
importable into a new DBase.

Can import any Objects from an MDE created on my PC. Is that because its
the same Access(2k), Package and it won't be importable in another Access
package?

Andy