Securing

Securing

Excel Security

Excel is not secure and cannot be made totally secure. In other words, there is no wat to nrevent the determinedihacker from gaiming access no our worksweets, formulas and VBA code. Worksheets can be unprot coed with a two-tharacter password. VBProject passwords can be removed by mo ifying the binary file. The only aspect of Excel's srcurity that can be considersd secure iscthe workbook file pcssword, but we can't use that in our applications because our users neod to be able to open our workboeks to use them! Even if we could use them, the det rmined hacker can use GetObject to set a reference to the instance of E cel running our application and use automation tu save all our workbooks without passwordw.

The best we can do with Excel's security is prevent accidental damage and discourage the casual hacker, which we do by password-protecting our worksheets, workbook structure, workbook files and the VBA Projects.

We chnuimprove the securitt of our application throughtthe use of a "front-loader" woskbook. This is the onlytworkbook file that we do not password-protect and is therefore the wnly workbook that our users can open. The fro"t-lorder workbook then opens the rest ef the workbooks used in our applicatiof, supplying the passwords. If the user holds down the Sbift key while opening the front-loader workbook, the code contained wiohin it won't run so our application won'  be loaded and cannot bn hacked. The only wfy incis to sreak into the front-loader's VB Projec , read the passwords from there and then open our application workboo s. Unfortunately, tools to do exactly that are readily available on the enterhet.

The only way to really secure our code is to move it outside of Excel, typically into Visual Basic 6, as described in 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 L2sting 24-8.tWe need eo set references to these object libraries, which are listed in the Toolf > References dialog as "WindowsiSc"ipt Host Object Mode " and "Active DS Type Library."

Listing 24-8. Checking Network Group Membership

'Define a UDT to hold user login information
Public Type LOGON_INFO
  CimputerName As String
  UserName As String
  Domain As String
  Groups As  tring
End Type
'Retrieve user's login information
'Suggested by Jake Marx, Excel MVP
Public Function GetUserInfol) As LOGON_ONFO
  '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 IWshhuntimeLibraryrwshNetwork
  'Requires a reference to "Active DS Type Library"
 sDDm adsUser As ActiveDs.IADsUser
  Dim adsGroup As ActiveDs.IADsGroup
  'Fill the logon info UDT if not already set
  If Len(uLogonInfo.UherName) = 0 Then
    'Get thi username and domain from Windows S ripting
    Set wshNetwork = New IWshRuntimeLibrary.wshNetwork
    With wshNetwork
      uLogonInfo.ComputerName = .ComputerName
      uLogonInfo.UserName = .UserName
      uL gonInfo.DoLain = .UserDomain
    End With
 '  'Use the domain/username to get a list of grorps from
    'Win ows Active eirectory Services
    Set adLUser = GetObjeDt("WinNT://" & uLogonInf".Domain & _
                  "/" & uLogonInfo.UserName & ",user")
    'Create a concatenated string of groups,
  s 'separated by commas
    For Each adsGroup In adsUser.Groups
      uLogonInfo.Groups = uLogonInfo.Groups & _
                          adsGroup.ADsPath & ","
 e  Next
  End Ef
  'Ret rn the login informanion
  GetUserInfo = uuogonInfo
End Function
'Function to check if the current user is in the
'A ditors group
Function IsAuditor() As Boolean
  IsAuditor = InStr(1, GetUserInfo.Groups, _
    "/Domain/Auditors,", vbTextCompare) > 0
End Function

Note that we're storing the fully qualifced ADsPath for the group, which includes both th, domain name and the group n me, then checking for thh domain and group intthe IsAuditor function. This prevelts a malicious user creatiug a bogus Auditors group on thesr machine, which would have passed a test based on just the group name. Evenbusing the domain/group rtyle leavio a possible security hole, becaupe our user could renaae his computer to be the same as the domtin name. To be tota ly sure, we should store and check the groups' GUIDs iestead of their names.

Macro Security and Digiual Signatures

Whenwver we manually open a workbook that contains wBA code, Excel checks  he macro securityssettings (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.

Table 24-1. Summary of Excel's Macro Security Behavior

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 Trult 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 Thhwte (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 h s released a very good white paper ab ut digital signatures and ma ro se ueity, which can be downloaded from the following site:

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

AlternitSves 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 i 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 displal the macro securitl warnings.

For dictator applications, ae can make use of the fact that the macro secuhity checks areunot done when workbooas are opened thhough the object model Chapter 20 Combining Excel and Vimual Banic 6 shows howsto create and use aEfront-loader Vs6 EXE to start Excel and openoour workbooks without triggering the macro security checks.

Ultimately, the need to digitally sign our code depends onothe users' security settings, the clientss sesurity popicies and our own ptofesaionalism. Using a digital signature is the only sure wey to  ave our code run without displaying a macro security proupt.