How to Secure An Excel Application

AutoSoft Systems

2 Round Hill Court

East Greenwich, RI 02818

401.885.3631

401.884.5653 Fax

401.996.3631 Cell

AMDG

Decision Ready Information!

autosoft@aol.com

Commercial & custom multi-user computer software for a variety of applications including performance metrics, statistical analysis, data extraction and merger from multiple large databases, computer simulation and management information systems. Founded in 1982.

We know Excel! Whenever possible, Excel is the user interface, VBA is the programming language, ODBC connects to the database of your choice.

Pharmaceutical & Biotech

Construction Management

AMDG

How To Secure An Excel Application

Excel is an incredibly flexible and powerful tool. There are some legitamite concerns about securing and validating Excel documents which can be addressed simply and quickly. Here are features I have employed in the past to do so:

Two-factor authentication

●

Two-factor authentication (2FA), sometimes referred to as two-step verification or dual-factor authentication, is a security process in which users provide two different authentication factors to verify themselves.

●

In the case of a Cloud-based Excel XLSM application, AutoSoft implements this by checking both the Excel User Name and Computer Name using the standard ENVIRON functions.

●

Upon startup of the read-only XLSM App which is stored locally, the Cloud database is presented with the User Name and the Computer Name that is running the App. The User table is queried to see if this User Name - Computer Name combination exists.

●

If the User Name - Computer Name combination does not exist in the database, the App is shut down on the User's computer, the System Administrator is emailed reporting the security breach and a record is added to the activity log.

●

If the User Name - Computer Name combination does exist in the database, the appropriate permissions are granted to the user. Those permissions could include "read-only" or "read-write" access and limited or unlimited use of App functions, as well as customized Ribbons designed for different access levels.

Password Protection - one can protect an Excel document at 3 different levels:

- Workbook level protection

- Worksheet level protection

- Visual Basic Code protection

As a further safeguard, once an Excel application has been fully validated or verified, one can make a copy of the workbook, then create a random password, protect the copy with the random password, then discard the password. By doing this, the document is fully protected, and this working copy, which is then distributed, cannot be unprotected for unauthorized modifications because NOBODY knows the password. Of course, the master copy, which is not password protected, or protected with a password known to authorized individuals, is kept in a secure location.

Remove Default Commands from the Excel Ribbon

A programmer has the ability to edit the XML code of an Excel workbook and strip off the default Excel ribbons, replacing it with a Ribbon that only has the desired commands. Here are two examples of such a practice:

- Project Portfolio Manager

- MATRIX - Database of Databases

Lock or Hide cells that contain equations, only allowing users to edit certain cells

One has the ability to include in the Cell's formatting properties a LOCK, which restricts users from editing or optionally even selecting a locked cell.

Hide some of the worksheets in a workbook:

One has the ability to hide worksheets at two different levels (Hidden and Very Hidden). Here is the screen shot from the VBA Shell indicating the 3 options for a worksheet.

Is the "Very Hidden" option sort of like "Double Secret Probation"?

Use Data Validation to assure the user only enters valid choices on text entry fields.

Illustration of a Drop Down Box in a cell:

Illustration of contraints that can be placed on a cell:

The Data Validation Control:

Use a VBA Form with a text box that has as Masked Password

Data Encryption

I have many ways of encrypting data at all levels. Here is a screen shot of a simple password encryptor:

In this example, the password "Welcome@123" is encrypted in the long string of numbers.

Payroll is a particularly sensitive topic at many companies. This screen shot, from a back end Access database used by a multi-user application, illustrates how Payroll information is encrypted at the field level inside the Payroll table of the database, so that even if a nefarious person obtained a copy of the Access database, confidential information is not compromised.

Validated Systems

Some applications require that software be validated. General principals of software validation can be found at:

https://www.fda.gov/media/73141/download

Some Validation "Rules of Thumb" that can be simply applied to any Excel workbook are quite simple:

- Lock all cells except those cells where data entry is required.

- Use data validation on all data entry cells.

- After the file has been checked, doubled check and you are assured it does exactly what is desired:

- make a copy of the file

- password protect that copy at the workbook, worksheets and VBA code level USING A RANDOM PASSWORD

- Throw away the password (remember to always save a copy of the file with no password for future work).

- Only use the copy of the file with the unknown password

- Take on any auditor with supreme confidence.

AutoSoft Systems | 2 Round Hill Court, East Greenwich, Rhode Island, USA 02818 | 401.885.3631 | Fax: 401.884.5653 | Mobile: 401.996.3631

This web page was last updated at 01/09/2024 10:50 AM and is written in EXCEL!

AXIOM is a Trademark of Consona - USYS is a Trademark of Zumbach - AutoSoft Systems is not affiliated with Consona nor Zumbach