Access 2003 Macro Security Modification

[Beth]

WinXP, Office 2003

Anyone who has had an 'opportunity' to work with Access 03 has surely
run across the 'macro' security issue. M$ publishes (at
http://office.microsoft.com/assista...ID=HA011071651033&CTT=3&Origin=HA011071331033)
that you can modify the macro security settings through code by using
the following:

Application.AutomationSecurity = msoAutomationSecurityLow

Ok, this was the answer to my problems, but then, when I compile, it
highlights msoAutomationSecurityLow and tells me that the variable is
not defined. Go figure!

Does anyone know which reference I need for using this, or at least
how to find out which one is needed?

I am aware that the security can be changed through a registry key
modification, but this is not an option. I also understand about the
certs, which again, is not an option at this point. I just want to
use the code that was provided by M$ and it actually work!

TIA,

Beth

 

[Marshall Barton]

WinXP, Office 2003

Anyone who has had an 'opportunity' to work with Access 03 has surely
run across the 'macro' security issue. M$ publishes (at
http://office.microsoft.com/assista...ID=HA011071651033&CTT=3&Origin=HA011071331033)
that you can modify the macro security settings through code by using
the following:

Application.AutomationSecurity = msoAutomationSecurityLow

Ok, this was the answer to my problems, but then, when I compile, it
highlights msoAutomationSecurityLow and tells me that the variable is
not defined. Go figure!

Does anyone know which reference I need for using this, or at least
how to find out which one is needed?

I don't know the answer, but until someone that does comes
along . . .

However, the "mso" prefix in the constant's name indicates
that it's in the Microsoft Office Runtime library.

 

[Jamie]

Beth,

msoAutomationSecurityLow is an integer constant and has a value of 1 so
changing the line to:

Application.AutomationSecurity = 1 ' msoAutomationSecurityLow

will also work.

Jamie
www.jamiessoftware.tk

 

[M.L. Sco Scofield]

You probably need to set a reference to Microsoft Office. In the VBA editor,
Tools/References, Scroll down to Microsoft Office 11.0 Object Library and
check the box.

--

Sco

M.L. "Sco" Scofield, MCSD, MCP, MSS, Access MVP, A+
Useful Metric Conversion #16 of 19: 2 monograms = 1 diagram
Miscellaneous Access and VB "stuff" at www.ScoBiz.com

 

[Beth]

Thanks for the replies. I did have a reference to the correct
library, but it didn't work. I changed to the integer value and still
didn't work. So, I did what every good developer does... Rearched
until I need a new perscription in eyeglasses! This is what I have
found so far. Maybe it will keep someone else from reading till they
go blind while looking for a resolution!

Access installs with Security Level default of Medium. Users are
prompted when opening an Access database and receive the message "This
file may not be safe if it contains code that was intended to harm
your computer. Do you want to open this file or cancel the
operation?" They are then prompted with Cancel, Open or More Info.
If they choose Cancel, the app will not open. To quote Microsoft,
"This message is a result of new security features that are part of
Access 2003. For more information on these features, see Migrating to
Access 2003, on Office Online." Microsoft also publishes the
following:

<snip>
To hide this message, try one of the following:
• When the preferred security level is High or Medium, make sure you
work only with files signed by trusted publishers.
For more information on how to keep the list of trusted publishers
up-to-date, see the following Access 2003 Help topic: • Modify the
list of trusted publishers for macros
• When the security level is set to Low, the warning will not be
displayed. Setting the security level to Low is not recommended.
A scenario where you might want to lower the security level is when
you programmatically open a database. A High or Medium macro security
setting might interrupt your program execution, if the database is not
signed by a trusted publisher. You can avoid this interruption by
turning off macro security in the new instance of Access by setting
Application.AutomationSecurity = msoAutomationSecurityLow. Lowering
the security level will suppress the macro security warning. Make sure
your program sets the level back to the original value as soon as
possible.
Warning  Setting the security level to Low is not recommended. You
should do this only if you are absolutely sure that the database you
are opening is safe and does not contain malicious code. </snip>

Any one who is using SP-8 for Jet 4.0 will experience these issues.

The line of code supplied by MS does not work. With modifications, it
could be utilized, however, once the settings have been changed, it is
my understanding that is it necessary to log out and back in again
before the settings are recognized. This would not allow you to
modify a client's settings and then reset to original settings after
logging out of the database, thereby leaving them exposed to possible
malicious code.

Registry Key modifications would also solve the issue, but then again,
you would be affecting their security settings for all Access
databases, not just yours.

Digital Certs are available from VeriSign, Inc for a cost of $400 and
would allow applications to be opened without any warning messages
being received by the client if you aren't able to modify the registry
key.

Again, I hope this saves someone else some undo frustration in the
future!

Beth

 

[Mike Wachal]

Hi Beth,

I'm sorry to hear that you are strugling with the Automation Security
property. It might help if you copy the code that is failing for you into
the newsgroup so we can take a look at it. If you are trying to use this
property from VBA inside an Office application, you will need to have a
reference to the Office 11 TypeLib.

I've written two pieces of sample code, both are working successfully in my
test environment (WinXP Pro, Office 2003 Pro). Here is my code for
reference.

This is VBA code that I run from within Access to use automation to open a
separate database.

Sub OpenAutomationLow(strPath As String)

Dim oAcc As Access.Application

Set oAcc = CreateObject("Access.Application.11")
oAcc.AutomationSecurity = msoAutomationSecurityLow
oAcc.OpenCurrentDatabase strPath, True
oAcc.Visible = True
oAcc.UserControl = True

End Sub

I just call:

OpenAutomationLow "D:\Program Files\Microsoft
Office\Office11\Samples\Northwind.mdb"

from the immediate window to test this. Obviously, you'll need to change
the path to a valid one for your computer. It is worth pointing out that
this only works for opening a different database than the one you are in.
This code only changes the security setting for the Access instance that is
being opened through automation, oAcc in this case. This doesn't make any
kind of setting on the database or change a setting permanently in Access.

The second sample code I actually pulled from the following help topic:

http://office.microsoft.com/assistance/preview.aspx?AssetID=HP010397921033
&CTT=98

This code is in VBScript and is a great way to open your development
database on a computer that has High or Medium security. The idea here is
that while it makes sense that any production database on you computer
would be signed, the database your are currently developing might not be.
You can open them by making a shortcut to the script rather than to the
database itself.

Const cDatabaseToOpen = "D:\Program Files\Microsoft
Office\Office11\Samples\Northwind.mdb"

On Error Resume Next
Dim AcApp
Set AcApp = CreateObject("Access.Application")
If AcApp.Version >= 10 Then
AcApp.AutomationSecurity = 1 ' msoAutomationSecurityLow
End If
AcApp.Visible = True
AcApp.OpenCurrentDatabase cDatabaseToOpen
If AcApp.CurrentProject.FullName <> "" Then
AcApp.UserControl = True
Else
AcApp.Quit
MsgBox "Failed to open '" & cDatabaseToOpen & "'."
End If

Needless to say, a little tweaking and you could make this script more
generalized to take a parameter and thus use the same script to open
different database based on the parameter passed to it.

In my tests, both of these scripts opened Northwind in Access without
showing the Macro Security warning. Northwind otherwise shows me a warning
prompt.

Let me know if these samples don't work for you.

--
Regards,
Mike Wachal
Microsoft Corporation

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Beth]

Hello Mike and thanks for your reply. However, you either didn't read
or understand what I was saying in my post. I am a software developer
for a retail product. I'm not worried about security settings in
development as I don't think I am going to write anything that is
going to wipe out my own hard drive. :/

My issue is when we sell this app to clients and they have medium or
high security settings. No, we don't have digitally signed
applications, but since this latest 'feature' of M$'s, it looks like
we will have to go down this road since we won't be modifying the
clients' registry key.

Thanks again for your time.

Beth Moffitt

Hi Beth,

I'm sorry to hear that you are strugling with the Automation Security
property. It might help if you copy the code that is failing for you into
the newsgroup so we can take a look at it. If you are trying to use this
property from VBA inside an Office application, you will need to have a
reference to the Office 11 TypeLib.

I've written two pieces of sample code, both are working successfully in my
test environment (WinXP Pro, Office 2003 Pro). Here is my code for
reference.

This is VBA code that I run from within Access to use automation to open a
separate database.

 

[Peter Russell]

I'm with you on this Beth.
Macro security warnings in Excel have always been counterproductive in my
view and now we're going to have the same ineffective approach for Access.

The security settings for Excel mean that I run my Excel config with low
security which of course means that when/if I get something from somebody
else, I have no security in place. I don't think I am alone in working
this way.

This is exactly what is going to happen with Access.

But of course the issue for developers is that their clients are going to
get the warning and you can't tell a client that they should operate with
low security. So it ends up being a gravy train for the certification
agencies, who are competing so hard(!) for this business that there's even
a 1 dollar price difference between highest and lowest prices.

It's a rip-off!

Peter Russell

 

[Mike Wachal]

Hi Beth,

It is certainly possible that I didn't fully understand you question,
although I question your motive in suggesting that I would respond to your
post without actually reading it first.

My answer was directed specifically to your comment:

The line of code supplied by MS does not work.

in reference to the AutomationSecurity property. The code samples I
included in my response were specifically geared towards overcoming the
macro security prompt on your clients computers. In your comentary you did
not give any specifics on exactly how you were tyring to use the
AutomationSecurity property or describe in what way the code "does not
work."

Obviously, the desired way to prevent the macro security warning is to
digitally sign your database. In cases where you do not want to (or can't)
sign your database, one mechanisim to "bypass" the warning for those
clients who feel that Macro Security is important is to launch your
applciation using Automation rather than by opening it directly in Access
or by pointing a shortcut directly to the .mdb file.

When implemented on a client computer, it would look like this:

1. Your database file(s) on the client computer.
2. A script similar to the example I gave you that opens your database file
using the AutomationSecurity property.
3. A shortcut pointing to the script file.

When the client clicks on the shortcut, it launches the script, which in
turn creates an automation instance of Access and sets the security to Low
before opening your database. Setting the UserControl property give your
client the ability to interact with the database as if it was launch
normally rather than using Automation. You don't have to restart Access
when you do this and the setting only affects the specific instance of
Access that is launched by the shortcut. You could replace the script with
some other stub launcher, for example you could write a small VB app that
launches databases using this mechanisim.

If you write deployment packages for your applications, you would need to
modify your deployments so that the shortcut points to the launcher
application rather than your database file.

Another alternative to bypass the warning would be to sign the database
with a SelfCert created using the cetificate creation tool that is included
with Office 2003. While these certificates are not usually trustable on a
client computer, you can export the Public Key of this type of a signature
and then have your client install the Public Key on thier computer, which
then allows them to trust the certificate, thus eliminating the warning.

The primary risk of the first method (script workaround) is that there is
nothing to prevent your database from being modified by a macro virus in a
way that it could be used to harm your clients computer. Remember, Macro
Security is not about protecting your code, it's about protecting your
clients computer. Macro Viruses are typcially designed to move from one
file to another for a period of time, and then do something on a specific
day. By signing a file, you make it possible to determine if the signed
objects have been modifed, and thus make it possible to not run the
modified code that is potentially harmfull.

The primary risk of the second method (SelfCert workaround) is that there
is no chain of authority to validate the certificate. While installing the
Public Key allows your client to "trust" the signature, there is nothing to
prevent some other person from creating a SelfCert using your name and
sending it to your client. The value of a Certificaiton Authority is to
validate that you are you, so when your client sees your signature that is
authenticated by some third party CA, they can be more confident that it is
actually from you.

Digital signing is nothing new, and it certainly isn't unique to Microsoft
or Office. Many companies digitally sign thier code in this same way. This
is just a new thing in Access 2003 and an important step in making
computers more trustworthy for your clients.

--
Regards,
Mike Wachal
Microsoft Corporation

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Beth]

Hello Mike,

First I apologize for my delay in responding to posts. I do not
access the newsgroups on a regular basis but via Google. Secondly, I
didn't mean to suggest that you hadn't 'read' my post, but that you
hadn't 'read my concerns' within the post. A difference not worth
discussing right now, but I don't doubt that you did read the lines
within the post. Enough said on that issue.

My point in the posts has been, and still is, that as a developer for
a software product which has been a 'great' product through the use of
M$ Access has been a wonderful experience. Access is a powerhouse of
a tool which a lot of people do not understand or recognize the
potential they have with this product, especially when used in
conjunction with a backend such as M$ SQL Server.

My issue is that now, due to 'malicious code', my method of
development and deployment is supposed to be modified, instead of M$
devising a better way of addressing the issue at hand.

Even using the method you describe, it would still set the security
level to Low, thereby leaving the 'client' pc open to potential risk
factors. Is this not an accurate statement? Or is the security level
only modified for that particular instance?

Quote:

If you write deployment packages for your applications, you would need to
modify your deployments so that the shortcut points to the launcher
application rather than your database file.

Another alternative to bypass the warning would be to sign the database
with a SelfCert created using the cetificate creation tool that is included
with Office 2003. While these certificates are not usually trustable on a
client computer, you can export the Public Key of this type of a signature
and then have your client install the Public Key on thier computer, which
then allows them to trust the certificate, thus eliminating the warning.

I don't deploy this to 'a' client. This product is sold world wide to
fortune 100 companies as an enterprise wide solution. Their IT isn't
particulary fond of loading 'public' anything from outside sources on
all of their company computers.

Quote:

Digital signing is nothing new, and it certainly isn't unique to Microsoft
or Office. Many companies digitally sign thier code in this same way. This
is just a new thing in Access 2003 and an important step in making
computers more trustworthy for your clients.

It may not be anything new, but it appears that it is becoming
necessary. And not everyone used to use Digital Signatures.

Just my $.02 worth, but I personally think its ridiculous.

Respectfully,

Beth

 

[Mike Wachal]

Hi Beth,

My issue is that now, due to 'malicious code', my method of
development and deployment is supposed to be modified, instead of M$
devising a better way of addressing the issue at hand.

Some would say that moving to managed code is a "better way" of addressing
the issue. I'm not much of .NET developer (OK, I'm not a .NET developer at
all) since Access has always met my needs. In a file based database, there
are limited options available for preventing hackers from affecting your
application.

Technically, you don't have to change anything about how you develop and
deploy; you can continue doing things that way you already have. As long as
this meets the needs of your customers you have no compleling reason to
change.

Even using the method you describe, it would still set the security
level to Low, thereby leaving the 'client' pc open to potential risk
factors. Is this not an accurate statement? Or is the security level
only modified for that particular instance?

The security level is only lowered for the single instance of Access that
is launched by the script code. All other instances launched would use the
default security level.

I don't deploy this to 'a' client. This product is sold world wide to
fortune 100 companies as an enterprise wide solution. Their IT isn't
particulary fond of loading 'public' anything from outside sources on
all of their company computers.

"Public" in this instance does not refer to THE public, as in the public
domain, rather it referese to a specific piece of a digital certificate.
Every certificate has both a Public and Private key. These two keys
together are used to allow developers to sign code (Private Key) and users
to determine if the code has been altered (Public Key). Every signed file,
whether Access or others, contains a Public Key. This little workaround
just forces a specific type of Public Key onto the computer. For various
reasons, this particular workaround is not particularly trustworthy, but it
is available as an option if you have applications deployed to companies
who are going to run Access in Medium or High security and you don't want
to use a comercial certificate.

It may not be anything new, but it appears that it is becoming
necessary. And not everyone used to use Digital Signatures.

Just my $.02 worth, but I personally think its ridiculous.

Unfortunately I think it is becoming more necessary to use digital
certificates as a way to protect computers from maliceous individuals who
have too much time on thier hands. I know that not everyone used to use
Ditital Signatures, but then again, not everyone used to lock thier houses
either; the times they are a changing.

What's rediculous is that every time I get an e-mail I have to wonder if
opening it will erase my hard drive. All software companies can do it
attempt to mediate the threat, someone else is going to have to stop the
individuals that are threatening us.

I do understand your concerns and the challenges this causes. Making
security better and easier is a top priority for a lot of people. In the
mean time, I think the security we have is better than no security at all.

--
Regards,
Mike Wachal
Microsoft Corporation

This posting is provided "AS IS" with no warranties, and confers no rights.