How Can I validate if a user is authorized to view certain data or not?

I'm in

25th May is approaching fast and so is GDPR. For those of you who are not yet aware – GDPR is a EU-wide regulation on the handling of privacy data

A potential implication for all data analysts is that they may have to take extra precautions as to who can access the data they provide for further analysis within the company.

Of course, you can add password protection to your workbook but:

a) this does not prevent unauthorized people from finding out your password

b) it’s easily hack-able

A clever way in which you can add an extra protection layer would be to make sure that your spreadsheet always checks that the person trying to open a spreadsheet is actually authorized to view it.

Here are a few simple steps in which you can implement this:

  1. Set up a list of the computer aliases that are allowed to view your data in a tab on your spreadsheet
  2. Add the following code as an “upon open” event in your workbook

(Get it Right in Excel Workshop)

Thus, the code will trigger every time the workbook is opened, ensuring that the “data” tab is hidden if the person opening the workbook is not authorized to view it.

Sub AuthUser()
Dim Cell As Range
Dim person As String
Dim authperson As String
person = Application.UserName
'written by Angelina Teneva, 2013
For Each Cell In ActiveWorkbook.Worksheets("Names").Range("E1:E100")
authperson = Cell.Value
If person = authperson Then
Worksheets("data").Visible = xlSheetVisible
Worksheets("data").Visible = xlSheetVeryHidden
MsgBox ("Sorry, you are not authorized to view this data")
End If
Exit For
Next Cell
End Sub

As you’ll see above, the code makes use of the “Application.UserName” property

Below you can find the the most common approaches in which you can check the name, path or both of a given workbook. These can be pretty handy if you need your code to be opening and closing various workbooks

Sub ShowUser()
ActiveCell.Value = Environ("UserName") 'returns the domain name
ActiveCell.Offset(1, 0).Value = Application.UserName 'the user registered name
ActiveCell.Offset(2, 0).Value = Application.UserLibraryPath 'location where COM-Addins are installed
ActiveCell.Offset(3, 0).Value = "C:\Users\" & UCase(Environ("UserName")) & "\Desktop" 'returns a custom directory
ActiveCell.Offset(4, 0).Value = ActiveWorkbook.FullName 'the path + the name of the file <– assimes file has been saved
ActiveCell.Offset(5, 0).Value = 'returns the name of the file alone
ActiveCell.Offset(6, 0).Value = ActiveWorkbook.path 'returns the path where it is saved
ActiveCell.Offset(7, 0).Value = Application.PathSeparator 'returns the dash
'C:\Users\hp\Google Drive\Analytics\Blogging\Excel Spreadsheets\Get it Right in Excel Workshop.xlsb
'Get it Right in Excel Workshop.xlsb
'C:\Users\hp\Google Drive\Analytics\Blogging\Excel Spreadsheets
End Sub

P.S. Don’t forget to hide and protect the worksheet in which you put your authorized names

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.