Authenticating the user of our application is a common problem, with common pitfalls – some innocuous, some fatal. It’s also a solved problem, with a fairly standard solution. Unfortunately, it’s also a problem that’s too often solved with naive, “good-enough” solutions that make any security expert twitch.
The vast majority of scenarios don’t need any custom authentication. Accessing a SQL Server database? Use Windows Authentication! Windows Auth not possible? Use SQL Authentication over a secure network! App authentication isn’t for authenticating a user with a server. More like, the application itself needs a concept of users and privileges granted to certain groups of users, and so we need to prompt the user for a user name and a password. What could possibly go wrong?
Security First: Threat Model Assessment
The first question we need to ask ourselves, is literally “what could possibly go wrong?” — as in, what are we trying to do? If the answer is along the lines of:
- Enhance user experience with tailored functionality
- Grouping users into “roles” for easier management
- Prevent accidental misuse of features
…then you’re on the right track. However if you’re thinking more in terms of…
- Prevent intentional misuse of features
- Securely prevent groups of users from accessing functionalities
…then you’re going to need another kind of approach. VBA code is not secure, period. Even if the VBA project is password-protected, the VBE can be tricked into unlocking it with some clever Win32 API calls. So, the threat model should take into account that a power user that wants to see your code… will likely succeed …pretty easily, too.
That doesn’t mean VBA code gets a pass to do everything wrong! If you’re going to do password authentication, you might as well do it right.
Where to store users’ passwords?
We’ve all done this:
Private Const ADMIN_PWD As String = "@Dm!n"
…without realizing that the code of a VBA project – even locked – is compressed into a binary file that’s zipped with the rest of the Excel host document. But nothing prevents anyone from peeking at it, say, with Notepad++
Obviously, hard-coding passwords is the worst possible idea: we need somewhere safe, right?
Truth is, not really. You could have everything you need in a hidden worksheet that anyone can see if they like; a database server is ideal, of course, but not necessary, if parts of your host document can be used as one (looking at you too, Microsoft Access).
The reason it doesn’t matter if the “passwords table” is compromised, is because you do not store passwords, period. Not even encrypted: the “passwords table” contains nothing that can be processed (decrypted) and then used as a password.
What you store is a hash of the users’ passwords, never the passwords themselves. For example, if a user’s password was
password and we hashed it with the SHA256 hashing algorithm, we would be storing the following value:
Contrary to encryption and encoding, there is by definition no way to revert a hash value back to the original string
password. It’s possible that some random string that’s not
password might produce the same hash value (i.e. a hash collision) – but very (very very) unlikely, at least with SHA256 or higher.
There are many different hashing algorithms, producing values of varying length, at varying speeds: with cryptographically secure requirements, using slow algorithms that produce values with a low risk of collision will be preferred (harder/longer to brute-force). Other applications might use a faster MD5 hash that’s “good enough” if not very secure, for many things but a password.
Now obviously, if any two users have the same password, their SHA256 hash would be the same. If that’s a concern (it should be), then the solution is to use a salt: prepend a random string to the password, and hash the salted password string – assuming all users use a different salt value (it can be safely stored alongside the user record), then it becomes impossible to tell whether any two users have the same password just by looking at the table contents… and this is why a hidden worksheet is a perfectly fine place to store your user passwords if you can’t use a database for whatever reason.
Storing a salted password hash prevents “translating” the hash values wholesale, using a lookup/”rainbow” table that contains common passwords and their corresponding hash representation. Even if one password is compromised, other users with the same password wouldn’t be, because their hash is different, thanks to the “salt” bytes.
Hashing with VBA
There’s… no built-in support whatsoever for hashing in VBA… but nothing says we can’t make explicit late binding and the .NET Framework work for us! Note that we’re invoking the
ComputeHash_2 method, because it’s an overload of the
ComputeHash method that takes the byte array we want to give it. COM/VBA doesn’t support method overloading, so when .NET exposes overloads to COM, it appends
_2 to the method name,
_4, and so on for each overload. The order depends on… the order they were written to the IDL, which means you could… just trust Stack Overflow on that one, and go with
Public Function ComputeHash(ByVal value As String) As String Dim bytes() As Byte bytes = StrConv(value, vbFromUnicode) Dim algo As Object Set algo = CreateObject("System.Security.Cryptography.SHA256Managed") Dim buffer() As Byte buffer = algo.ComputeHash_2(bytes) ComputeHash = ToHexString(buffer) End Function Private Function ToHexString(ByRef buffer() As Byte) As String Dim result As String Dim i As Long For i = LBound(buffer) To UBound(buffer) result = result & Hex(buffer(i)) Next ToHexString = result End Function
This code would feel right at home in a
SHA256Managed standard module, or it could be a class that implements some
IHashAlgorithm interface with a
ComputeHash method – and with it we have everything we need to start handling password-based authentication in VBA …by today’s best practices.
What follows is an object-oriented approach to leveraging this function in a VBA project that needs to authenticate a user. An online copy of this code can be downloaded from GitHub: https://github.com/rubberduck-vba/examples/tree/master/Authentication
I like having functionality neatly abstracted, so instead of just having a public
ComputeHash function that computes the SHA256 hash for a given string, I’ll have a class module formalizing what a hash algorithm does:
'@Folder("Authentication.Hashing") '@ModuleDescription("An interface representing a hashing algorithm.") '@Interface Option Explicit '@Description("Computes a hash for the given string value.") Public Function ComputeHash(ByVal value As String) As String End Function
One implementation would be this
SHA256Managed class module:
'@Folder("Authentication.Hashing") '@PredeclaredId Option Explicit Implements IHashAlgorithm Private base As HashAlgorithmBase '@Description("Factory method creates and returns a new instance of this class.") Public Function Create() As IHashAlgorithm Set Create = New SHA256Managed End Function Private Sub Class_Initialize() Set base = New HashAlgorithmBase End Sub Private Function IHashAlgorithm_ComputeHash(ByVal value As String) As String Dim bytes() As Byte bytes = StrConv(value, vbFromUnicode) Dim algo As Object Set algo = CreateObject("System.Security.Cryptography.SHA256Managed") Dim buffer() As Byte buffer = algo.ComputeHash_2(bytes) IHashAlgorithm_ComputeHash = base.ToHexString(buffer) End Function
By coding against an interface (i.e. by invoking
ComputeHash off the
IHashAlgorithm interface), we are making the code easier to modify later without breaking things: if a functionality needs a MD5 hash algorithm instead of SHA256, we can implement a
MD5Managed class and inject that, and no client code needs to be modified, because the code doesn’t care what specific algorithm it’s working with, as long as it implements the
HashAlgorithmBase class is intended to be used by all implementations of
IHashAlgorithm, so we’re using composition to simulate inheritance here (the coupling is intended, there’s no need to inject that object as a dependency). The class simply exposes the
ToHexString function, so that any hashing algorithm can get a hex string out of a byte array:
'@Folder("Authentication.Hashing") '@ModuleDescription("Provides common functionality used by IHashAlgorithm implementations.") Option Explicit '@Description("Converts a byte array to a string representation.") Public Function ToHexString(ByRef buffer() As Byte) As String Dim result As String Dim i As Long For i = LBound(buffer) To UBound(buffer) result = result & Hex(buffer(i)) Next ToHexString = result End Function
At this point we can already test the hashing algorithm in the immediate pane:
The next step is to create an object that’s able to take user credentials, and tell its caller whether or not the credentials are good. This is much simpler than it sounds like.
The first thing we need to address, is the data we’re going to be dealing with – the model. In the case of a dialog that’s prompting for a user name and a password, our model is going to be a simple class exposing
Password read/write properties, and here an
IsValid property returns
True if the
Password values aren’t empty:
'@Folder("Authentication") Option Explicit Private Type TAuthModel Name As String Password As String IsValid As Boolean End Type Private this As TAuthModel Public Property Get Name() As String Name = this.Name End Property Public Property Let Name(ByVal value As String) this.Name = value Validate End Property Public Property Get Password() As String Password = this.Password End Property Public Property Let Password(ByVal value As String) this.Password = value Validate End Property Public Property Get IsValid() As Boolean IsValid = this.IsValid End Property Private Sub Validate() this.IsValid = Len(this.Name) > 0 And Len(this.Password) > 0 End Sub
Since this isn’t a model for changing a password, the validation logic doesn’t need to worry about the password’s length and/or content – only that a non-empty value was provided; your mileage may vary!
If we wanted the UI to provide a
ComboBox dropdown to pick a user name, then the model class would need to encapsulate an array or collection that contains the user names, and that array or collection would be provided by another component.
When my object-oriented brain thinks “authentication”, what shapes up in my mind is a simple interface that exposes a single Boolean-returning function that takes user credentials, and returns
True when authentication succeeds with the provided credentials.
Something like this:
'@Folder("Authentication") '@ModuleDescription("An interface representing an authentication mechanism.") '@Interface Option Explicit '@Description("True if the supplied credentials are valid, False otherwise.") Public Function Authenticate(ByVal model As UserAuthModel) As Boolean End Function
If we have a hidden worksheet with a table containing the user names, salt values, and hashed passwords for all users, then we could implement this interface with some
WorksheetAuthService class that might look like this:
'@Folder("Authentication") '@ModuleDescription("A service responsible for authentication.") '@PredeclaredId Option Explicit Implements IAuthService Private Type TAuthService Algorithm As IHashAlgorithm End Type Private Type TUserAuthInfo Salt As String Hash As String End Type Private this As TAuthService Public Function Create(ByVal hashAlgorithm As IHashAlgorithm) With New WorksheetAuthService Set .Algorithm = hashAlgorithm Set Create = .Self End With End Function Public Property Get Self() As IHashAlgorithm Set Self = Me End Property Public Property Get Algorithm() As IHashAlgorithm Set Algorithm = this.Algorithm End Property Public Property Set Algorithm(ByVal value As IHashAlgorithm) Set this.Algorithm = value End Property Private Function GetUserAuthInfo(ByVal user As String, ByRef outInfo As TUserAuthInfo) As Boolean 'gets the salt value & password hash for the specified user; returns false if user can't be retrieved. On Error GoTo CleanFail With PasswordsSheet.Table Dim nameColumnIndex As Long nameColumnIndex = .ListColumns("Name").Index Dim saltColumnIndex As Long saltColumnIndex = .ListColumns("Salt").Index Dim hashColumnIndex As Long hashColumnIndex = .ListColumns("PasswordHash").Index Dim userRowIndex As Long userRowIndex = Application.WorksheetFunction.Match(user, .ListColumns(nameColumnIndex).DataBodyRange, 0) outInfo.Salt = Application.WorksheetFunction.Index(.ListColumns(saltColumnIndex).DataBodyRange, userRowIndex) outInfo.Hash = Application.WorksheetFunction.Index(.ListColumns(hashColumnIndex).DataBodyRange, userRowIndex) End With GetUserAuthInfo = True CleanExit: Exit Function CleanFail: Debug.Print Err.Description Debug.Print "Unable to retrieve authentication info for user '" & user & "'." outInfo.Salt = vbNullString outInfo.Hash = vbNullString GetUserAuthInfo = False Resume CleanExit End Function Private Function IAuthService_Authenticate(ByVal model As UserAuthModel) As Boolean Dim info As TUserAuthInfo If Not model.IsValid Or Not GetUserAuthInfo(model.Name, outInfo:=info) Then Exit Function Dim pwdHash As String pwdHash = this.Algorithm.ComputeHash(info.Salt & model.Password) IAuthService_Authenticate = (pwdHash = info.Hash) End Function
If we only look at the
IAuthService_Authenticate implementation, we can easily tell what’s going on:
- If for any reason we can’t identify the specified user / get its authentication info, we bail
- Using the user’s
Saltstring, we use the hashing algorithm’s
ComputeHashmethod to get a hash string for the specified password.
- Authentication succeeds if the hashed salted password matches the stored hash string for that user.
Note how the provided
model.Password string isn’t being copied anywhere, or compared against anything.
GetUserAuthInfo function is being considered an implementation detail here, but could easily be promoted to its own
IUserAuthInfoProvider interface+implementation: the role of that function is to get the
PasswordHash values for a given user, and here we’re pulling that from a table on a worksheet, but other implementations could be pulling it from a database: this is a concern in its own right, and could very easily be argued to belong in its own class, abstracted behind its own interface.
If we’re going to have a dialog for the user to enter their credentials into, then everything we’ve seen about the Model-View-Presenter UI design pattern is applicable here – we already have our model, and now we need an abstraction for a view.
'@Folder("Authentication") '@Interface Option Explicit '@Description("Shows the view as a modal dialog. Returns True unless the dialog is cancelled.") Public Function ShowDialog() As Boolean End Function Public Property Get UserAuthModel() As UserAuthModel End Property
From an abstract standpoint, the view is nothing more than a function that displays the dialog and returns
False if the dialog was cancelled,
The concrete implementation will be a
UserForm that includes two textboxes, two command buttons, and a few labels – like this:
The code-behind for the form is very simple:
Changehandlers for the textboxes assign the corresponding model property
Clickhandlers for the command buttons simply
Createfactory method takes a
- Model is exposed for property injection (only the factory method uses this property)
'@Folder("Authentication") '@PredeclaredId Option Explicit Implements IAuthView Private Type TAuthDialog UserAuthModel As UserAuthModel IsCancelled As Boolean End Type Private this As TAuthDialog Public Function Create(ByVal model As UserAuthModel) As IAuthView If model Is Nothing Then Err.Raise 5, TypeName(Me), "Model cannot be a null reference" Dim result As AuthDialogView Set result = New AuthDialogView Set result.UserAuthModel = model Set Create = result End Function Public Property Get UserAuthModel() As UserAuthModel Set UserAuthModel = this.UserAuthModel End Property Public Property Set UserAuthModel(ByVal value As UserAuthModel) Set this.UserAuthModel = value End Property Private Sub OnCancel() this.IsCancelled = True Me.Hide End Sub Private Sub Validate() OkButton.Enabled = this.UserAuthModel.IsValid End Sub Private Sub CancelButton_Click() OnCancel End Sub Private Sub OkButton_Click() Me.Hide End Sub Private Sub NameBox_Change() this.UserAuthModel.Name = NameBox.Text Validate End Sub Private Sub PasswordBox_Change() this.UserAuthModel.Password = PasswordBox.Text Validate End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = VbQueryClose.vbFormControlMenu Then Cancel = True OnCancel End If End Sub Private Function IAuthView_ShowDialog() As Boolean Me.Show vbModal IAuthView_ShowDialog = Not this.IsCancelled End Function Private Property Get IAuthView_UserAuthModel() As UserAuthModel Set IAuthView_UserAuthModel = this.UserAuthModel End Property
The important thing to note, is that the form itself doesn’t do anything: it’s just an I/O device your code uses to interface with the user – nothing more, nothing less. It collects user-provided data into a model, and ensures the dialog validates that model.
The form knows about the
UserAuthModel and its properties (
IsValid), and nothing else. It doesn’t know how to get a list of user names to populate a dropdown so that the user can pick a name from a list (that could be done, but then the model would need a
UserNames property). It doesn’t know how to verify whether the provided password string is correct. It’s …just not its job to do anything other than relay messages to & from the user.
We have a
UserAuthModel that holds the user-supplied credentials. We have a
WorksheetAuthService that can take these credentials and tell us if they’re good, using any
IHashAlgorithm implementation. We’re missing an object that pieces it all together, and that’s the job of a presenter.
What we want is for the code that needs an authenticated user, to be able to consume a simple interface, like this:
'@Folder("Authentication") '@ModuleDescription("Represents an object that can authenticate the current user.") '@Interface Option Explicit '@Description("True if user is authenticated") Public Property Get IsAuthenticated() As Boolean End Property '@Description("Prompts for user credentials") Public Sub Authenticate() End Sub
Now, any class that encapsulates functionality that involves authenticating the current user can be injected with an
IAuthPresenter interface, and when
True we know our user is who they say they are. And if we inject the same instance everywhere, then the user only needs to enter their credentials once for the authentication state to be propagated everywhere – without using any globals!
'@Folder("Authentication") '@PredeclaredId '@ModuleDescription("Represents an object responsible for authenticating the current user.") Option Explicit Implements IAuthPresenter Private Type TPresenter View As IAuthView AuthService As IAuthService IsAuthenticated As Boolean End Type Private this As TPresenter Public Function Create(ByVal service As IAuthService, ByVal dialogView As IAuthView) As IAuthPresenter Dim result As AuthPresenter Set result = New AuthPresenter Set result.AuthService = service Set result.View = dialogView Set Create = result End Function Public Property Get AuthService() As IAuthService Set AuthService = this.AuthService End Property Public Property Set AuthService(ByVal value As IAuthService) Set this.AuthService = value End Property Public Property Get View() As IAuthView Set View = this.View End Property Public Property Set View(ByVal value As IAuthView) Set this.View = value End Property Private Sub IAuthPresenter_Authenticate() If Not this.View.ShowDialog Then Exit Sub this.IsAuthenticated = this.AuthService.Authenticate(this.View.UserAuthModel) End Sub Private Property Get IAuthPresenter_IsAuthenticated() As Boolean IAuthPresenter_IsAuthenticated = this.IsAuthenticated End Property
At this point any standard module macro (aka entry point) can create the presenter and its dependencies:
Public Sub DoSomething() Dim model As UserAuthModel Set model = New UserAuthModel Dim dialog As IAuthView Set dialog = AuthDialogView.Create(model) Dim algo As IHashAlgorithm Set algo = SHA256Managed.Create() Dim service As IAuthService Set service = WorksheetAuthService.Create(algo) Dim presenter As IAuthPresenter Set presenter = AuthPresenter.Create(service, dialog) presenter.Authenticate If presenter.IsAuthenticated Then MsgBox "Welcome!", vbInformation Else MsgBox "Access denied", vbExclamation End If End Sub
If this were real application code, instead of consuming the
presenter it would be injecting it into some class instance, and invoking a method on that class. This composition root (where we compose the application / instantiate and inject all the dependencies) would probably be in the
Workbook_Open handler, so that the authentication state can be shared between components.
Up to this point, we only cared for authentication, i.e. identifying the current user. While very useful, it doesn’t tell us who’s authorized to do what. Without some pretty ugly code that special-cases specific users (e.g. “Admin”), we’re pretty limited here.
One proven solution, is to use role-based authorisations. Users belong to a “group” of users, and it’s the “group” of users that’s authorized to do things, not users themselves.
In order to do this, the
WorksheetAuthService implementation needs to be modified to add a
RoleId member to the
TUserAuthInfo, and the
IAuthService.Authenticate method could return a
Long instead of a
0 would still mean a failed authentication, but any non-zero value would be the authenticated user’s
Roles could be defined by an enum (note the default /
Public Enum AuthRole Unauthorized = 0 Admin Maintenance Auditing End Enum
Or, role membership could be controlled in Active Directory (AD), using security groups – in that case you’ll want your
IAuthService implementation to query AD instead of a worksheet, and the
IAuthPresenter implementation to hold the current user’s role ID along with its authentication status.
There are many ways to go about implementing authentication, and many implementation-specific concerns. For example, if you’re querying a database for this, you’ll want to use commands and proper parameterization to avoid the problems associated with SQL Injection vulnerabilities: maybe a user named
Robert');DROP TABLE USERS;-- isn’t part of your threat model, but can
Tom O'Neil log onto your system without breaking anything?
Regardless of the approach, if you’re comparing the user’s plain-text password input with the plain-text password stored in
$(storage_medium), you’re doing it wrong – whether that’s in VBA or not.