Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

Securing

Excel Security

Excel is not secure and cannot be made totally secure. In other words, there is no way to prevent the determined hacker from gaining access to our worksheets, formulas and VBA code. Worksheets can be unprotected with a two-character password. VBProject passwords can be removed by modifying the binary file. The only aspect of Excel''''s security that can be considered secure is the workbook file password, but we can''''t use that in our applications because our users need to be able to open our workbooks to use them! Even if we could use them, the determined hacker can use GetObject to get a reference to the instance of Excel running our application and use automation to save all our workbooks without passwords.Chapter 20 Combining Excel and Visual Basic 6. We can use a front-loader VB6 EXE to start Excel and open our password-protected application workbooks, keeping the passwords hidden within the compiled VB6 code. For strongest security, the passwords should not be stored as plain text in the program, but in an encoded form that is run through a decoding function when used. Once started, we can use VB6 DLLs for most of our application''''s features, so our workbooks contain only enough VBA to instantiate the DLL and call its procedures.

Checking Network Groups

It''''s a common requirement for us to restrict access to parts of our applications depending on the user''''s network group membership. For example, we might want to allow only people in the Auditors group to be able to run certain reports. We can find this information from the Windows Script Networking and Active Directory Service Interfaces object libraries, as shown in Listing 24-8. We need to set references to these object libraries, which are listed in the Tools > References dialog as "Windows Script Host Object Model" and "Active DS Type Library."

Listing 24-8. Checking Network Group Membership

''''Define a UDT to hold user login information
Public Type LOGON_INFO
ComputerName As String
UserName As String
Domain As String
Groups As String
End Type
''''Retrieve user''''s login information
''''Suggested by Jake Marx, Excel MVP
Public Function GetUserInfo() As LOGON_INFO
''''Use a static variable, so we only retrieve the 
''''information once
Static uLogonInfo As LOGON_INFO
''''Requires a reference to 
''''"Windows Script Host Object Model"
Dim wshNetwork As IWshRuntimeLibrary.wshNetwork
''''Requires a reference to "Active DS Type Library"
Dim adsUser As ActiveDs.IADsUser
Dim adsGroup As ActiveDs.IADsGroup
''''Fill the logon info UDT if not already set
If Len(uLogonInfo.UserName) = 0 Then
''''Get the username and domain from Windows Scripting
Set wshNetwork = New IWshRuntimeLibrary.wshNetwork
With wshNetwork
uLogonInfo.ComputerName = .ComputerName
uLogonInfo.UserName = .UserName
uLogonInfo.Domain = .UserDomain
End With
''''Use the domain/username to get a list of groups from 
''''Windows Active Directory Services
Set adsUser = GetObject("WinNT://" & uLogonInfo.Domain & _
"/" & uLogonInfo.UserName & ",user")
''''Create a concatenated string of groups, 
''''separated by commas
For Each adsGroup In adsUser.Groups
uLogonInfo.Groups = uLogonInfo.Groups & _
adsGroup.ADsPath & ","
Next
End If
''''Return the login information
GetUserInfo = uLogonInfo
End Function
''''Function to check if the current user is in the 
''''Auditors group
Function IsAuditor() As Boolean
IsAuditor = InStr(1, GetUserInfo.Groups, _
"/Domain/Auditors,", vbTextCompare) > 0
End Function

Note that we''''re storing the fully qualified ADsPath for the group, which includes both the domain name and the group name, then checking for the domain and group in the IsAuditor function. This prevents a malicious user creating a bogus Auditors group on their machine, which would have passed a test based on just the group name. Even using the domain/group style leaves a possible security hole, because our user could rename his computer to be the same as the domain name. To be totally sure, we should store and check the groups'''' GUIDs instead of their names.

Macro Security and Digital Signatures

Whenever we manually open a workbook that contains VBA code, Excel checks the macro security settings (set under the Tools > Macro > Security menu) and either enables or disables any VBA code contained within the workbook, depending on the security level, the way in which the file is opened, whether the VBA code has been digitally signed and whether the signature has been trusted, summarized in Table 24-1.

Some of the details are a little different if the code has been signed, but the signature is invalid or has expired, or if the add-in is being opened using Tools > Add-ins instead of File > Open and the Trust all installed add-ins and templates option in the Macro Security dialog has been ticked. The exact details can be found by searching for "macro security levels" in Excel''''s help.

So if we want our code to run with the strictest settings, we have to sign it with a digital signature, purchased from a certificate authority such as VeriSign ([ www.verisign.com ]) or Thawte ([ www.thawte.com ]). Digital signatures are not cheap and have to be renewed annually. At the time of writing, they cost $200 per annum from Thawte.

If our workbook is opened after the certificate has expired (usually one year), Excel will treat it as if it were unsigned (although the prompts differ slightly). This can be avoided by telling Excel to time stamp the signing of the file. When a signed-and-time-stamped file is opened, Excel can see that the file was signed while the digital signature was valid, so will allow the code to run. We tell Excel to time stamp the signature by adding the following registry entries to HKEY_CURRENT_USER\Software\Microsoft\VBA\Security (creating that key if it doesn''''t exist):

TimeStampURL = http://timestamp.verisign.com/scripts/timstamp.dll
TimeStampRetryCount = 1
TimeStampRetryDelay = 2

Digital signatures also provide a level of assurance to the developer. The signature is applied whenever the VBA code is changed and the file is saved. If the digital signature private key is not installed, the signature will be removed from the file. This gives us a foolproof way of identifying whether our code has been tampered withit won''''t be signed with our digital signature.

Microsoft has released a very good white paper about digital signatures and macro security, which can be downloaded from the following site:

http://www.microsoft.com/downloads/details.aspx?
FamilyID=7e3eab1f-b313-44f4-8900-3399abb2001d

Alternatives to Digital Signatures

In practice, very few Excel developers digitally sign their workbooks. Most users have the Trust all installed add-ins and templates check box ticked in the Macro Security dialog, which means that add-ins opened using the Tools > Add-ins dialog will run without being signed (unless Macro Security is set to Very High in Excel 2003). Workbooks and add-ins opened using File > Open will still display the macro security warnings.

For dictator applications, we can make use of the fact that the macro security checks are not done when workbooks are opened through the object model. Chapter 20 Combining Excel and Visual Basic 6 shows how to create and use a front-loader VB6 EXE to start Excel and open our workbooks without triggering the macro security checks.

Ultimately, the need to digitally sign our code depends on the users'''' security settings, the client''''s security policies and our own professionalism. Using a digital signature is the only sure way to have our code run without displaying a macro security prompt.