Creating User Security Profiles - Admin, Business User, External

[Richynero]

Hello there fellow Excel Geeks!!

Im trying to create a sort of security profile to allow 3 different types of users different types of access to a very confidential workbook.

1) Admin to be no holds barred, full rwx

2) Business Users should only be allowed to have read only (r--) to all sheets but rwx to 1 or 2 sheets which contain the model variables.

3) External Parties should have a fully locked down version. Read only (r--) for specific sheets

My Idea
As the business users will be a definitive list of users I was thinking of keeping a list of usernames in a hidden sheet and then using the application.username to look up on that list and then fire off a code to lock all but those specific sheets they need access to.

Admin should be myself and another chap. Again, another definitive list.

Then lock down the spreadsheet by default for other users.

Thoughts? Any other ways to do this (with or without VBA) would be gratefully received. No other coders at work so no one to bounce off so hoping you the internets can be my sounding board.

Many thanks in advance for any advice that the community is willing to bestow.

Richard

 

[AndyPandy27]

Hi Richynero,

I've had to do something fairly similar in the past, and your proposed solution is very similar to what I did. As such, I won't be able to wow you with anything ground-breaking in terms of a solution, but I thought I could offer a couple of tips which have proved useful for me:

1. Assuming you are using Windows based PCs, I would go with environ("username"), rather than application.username - as this will ensure that you're using the windows username, which will be unique (some companies will set-up their MSOffice applications on multiple computers each with the same application.username value, so that solution wouldn't be as robust).

2. It's worth wrapping the environ("username") in the LCase function, to avoid any issues with case-sensitivity, so LCase(environ("username"))

3. When maintaining your list of usernames in the hidden sheet, you can add a column containing a number (or any arbitrary value) which corresponds to the level of access that user is allowed (e.g. 1, 2, 3 or "Admin", "Business", "External" etc.). Use a formula in your hidden sheet which returns this corresponding value based on the user who is using the workbook, which you can then use as your permission value in your code, e.g.

PermissionValue = worksheets("Admin").range("A1").value

Select Case PermissionValue
    Case "Admin"
           [Code to unlock everything]
    Case "Business"
           [Code for Business user access]
etc...

4. Make sure that, by default, the workbook starts with everything closed down (the most secure it needs to be), to ensure that a user who shouldn't see things, is not able to accidentally (e.g. because they have not got Macros enabled, therefore your security won't kick in and hide sheets etc, until they do Enable them).

5. When hiding sheets, use xlsheetveryhidden for added "security" (Excel is well-known for not being secure, you can just add measures to avoid an honest person accidentally accessing things they shouldn't; if someone really wants to break in to your workbook and knows what they are doing, they will be able to).

Hopefully some of this is useful as a starter for 10...

Good luck,

AP

 

[Richynero]

So I only just saw your reply after cracking on with the code and it is remarkable how similar your thought process was to mine. You have added some more clever bits around the enivron username instead of the Excel assigned user name and I also like the lower case piece wrapping the username to avoid any potential errors.


I created a function to pull out a username and then set that to a named range called user.


I then created a list of users and assigned them to user groups. I put a little vlookup against the username and then assigned that to a named range called usergroup (it contains either "Admin" or "User") and used this to drive a select case statement precisely like you suggested.

I will add your extra bits to it to make it that bit extra robust. Thank you for such a detailed response. Greatly appreciated!


See my code below


Public Function UserName()
UserName = Application.UserName
End Function



Sub AccessControlList()


Dim UserName As String


UserName = [User].Value


Select Case [UserGroup]
Case "Admin"
Call UnlockAllSheets
Case "User"
Call LockAllSheets
Call UnlockUserSheets
Case Else
Call LockAllSheets

MsgBox "Hello " & UserName & vbCrLf & "Welcome to the Switching Cost Model" _
& vbCrLf & "You are not in the list business users so will enjoy a read only version" _
& vbCrLf & "If you have any questions please contract Bryn Coles"

End Select



End Sub


Sub UnlockUserSheets()


'Just need to define which sheets are unlocked for the users


End Sub


Sub LockAllSheets()


Dim ws As Worksheet


For Each ws In ThisWorkbook.Worksheets

ws.Protect "qwe", DrawingObjects:=True, Contents:=True, Scenarios:=True

Next ws

End Sub


Sub UnlockAllSheets()
Dim ws As Worksheet


For Each ws In ThisWorkbook.Worksheets

ws.Unprotect "qwe"

Next ws

End Sub

 

[Kyle123]

Just bear in mind that excel is not secure and it's trivially easy to remove whatever measures you put in place

 

[Richynero]

Just bear in mind that excel is not secure and it's trivially easy to remove whatever measures you put in place

You are right Kyle. I was aware of the limitations and my stakeholders have been given the necessary caveats. Though I do believe this sort of security is fit for purpose.

Thanks