Solved: Hiding a password in a code

[Amanda1]

Hi,

I know that Bill is not reknowned for the level of security on his applications, but is there a way to "hide" the password in a VBA script?

My script unprotects the worksheet before it runs a macro, (the macro has to sort hidden rows which doesn't work if the sheet is protected). But I have to put in the actual password to unprotect the sheet in the script. Problem then of course is, that although I protect the script with a password - if someone does get into it they can just look at the code and see the password for the sheet.

I'm not bothered about the people who could easily get into the code; because hopefully they should know better than to change formulaes etc in the sheet anyway - I'm more concerned about those users who enter incorrect data etc and then change a sum formula to make it balance. With my luck they are just the sort of users who, if they did somehow get into the code, would quite happily change links/formulaes etc on the sheet as soon as they knew the password to unprotect it.

I've searched the KB, so if I've missed the answer, forgive me.

Thanks for any advice.

Amanda:help

[johnske]

It's not 100% secure, but most ppl will be stopped if you go into the VBE window and select Tools > VBAProject Properties > Protection > Password

HTH,
John :)

[Amanda1]

Hi,

No, that isn't what I meant - I have done that, but it is the password within the actual code that I want to hide, i.e.

ActiveSheet.Unprotect Password:="1234"
Rows("70:130").Select

(I hope I've got the code tags? right - if not, sorry).

Do you see what I mean?

Cheers
Amanda

[johnske]

Hi,

No, that isn't what I meant - I have done that, but it is the password within the actual code that I want to hide, i.e.

ActiveSheet.Unprotect Password:="1234"
Rows("70:130").Select

(I hope I've got the code tags? right - if not, sorry).

Do you see what I mean?

Cheers
AmandaYep, you got em right. Sorry, can't see any secure way of doing that.

Could maybe put the password in a cell on a hidden sheet, or, in a text doc hidden on your hard drive and recover it each time it's required, but it'd only slow someone down - not stop them.

[Amanda1]

Unfortunately that won't work cause the sheet is a master that will then be reused by the staff on a daily basis for them to add data.

Oh well!:boohoo

[mvidas]

Amanda,

While you can't hide it, you can completely confuse people. Change the line that has the password to one that calls a function:' ActiveSheet.Unprotect Password:="1234"
ActiveSheet.Unprotect Password:=qwpoqwpoThen stick the follow functions randomly around your project:Function qwpoqwpo$()
Dim erty$
erty = aopareeatlk(asdfjhear(iwwyor(lkjh("1234"))))
qwpoqwpo = erty
End Function
Function wpthwalerkj3&(slkbjwspet$)
wpthwalerkj3 = Asc(slkbjwspet)
End Function
Public Function asdfjhear$(ByVal seaewfjawn$)
Dim i&, adlkgjaer67$
adlkgjaer67 = ""
For i = 1 To aefpogiqe3r(seaewfjawn) Step 2
adlkgjaer67 = adlkgjaer67 & lakseor$(seaewfjawn, i, 1)
Next i
For i = 2 To aefpogiqe3r(seaewfjawn) Step 2
adlkgjaer67 = adlkgjaer67 & lakseor$(seaewfjawn, i, 1)
Next i
asdfjhear = iwwyor(adlkgjaer67)
End Function
Public Function aopareeatlk$(ByVal oStr$)
Dim i&, j&, letr$
For i = 1 To aefpogiqe3r(oStr)
letr = lakseor(oStr, i, 1)
If letr = " " Then
aopareeatlk = aopareeatlk & " "
Else
Select Case i Mod 2
Case 0: j = wpthwalerkj3(letr) + i
Case 1: j = wpthwalerkj3(letr) - i
End Select
Do Until j < 202 And j > 32
If j < 33 Then j = 202 - (33 - j)
If j > 201 Then j = 32 + (j - 201)
Loop
aopareeatlk = aopareeatlk & aoeioru3w(j)
End If
Next i
aopareeatlk = iwwyor(aopareeatlk)
End Function
Function aoeioru3w$(lkaergwer&)
aoeioru3w = Chr$(lkaergwer)
End Function
Function lakseor$(ByVal sdflsertgisw$, ByVal aeklgwer&, Optional ByVal efkjw3e43& = 0)
If efkjw3e43 = 0 Then efkjw3e43 = aefpogiqe3r(sdflsertgisw)
lakseor = Mid$(sdflsertgisw, aeklgwer, efkjw3e43)
End Function
Public Function iwwyor$(ByVal oStr$)
Dim i&
For i = aefpogiqe3r(oStr) To 1 Step -1
iwwyor = iwwyor + lakseor(oStr, i, 1)
Next i
End Function
Function lkjh$(ByVal poiuy$)
Dim i&, srpwe4lq3a
ReDim srpwe4lq3a(aefpogiqe3r(poiuy) - 1)
For i = aefpogiqe3r(poiuy) To 1 Step -1
srpwe4lq3a(aefpogiqe3r(poiuy) - i) = aoeioru3w(255 - wpthwalerkj3(lakseor$(poiuy, i, 1)))
Next
lkjh = Join(srpwe4lq3a, "")
End Function
Function aefpogiqe3r&(ByVal apaw$)
aefpogiqe3r = Len(apaw)
End FunctionAnyone trying to trace that will probably just get confused by the whole thing. Anyone that wouldn't get confused by it probably wouldn't bother, and would instead just crack the password themselves.
Just an idea though. The actual pre-garbled password is located in the function qwpoqwpo, currently "1234" (pw actually used: ")?&!")

While I've got you here, I should tell you about the UserInterfaceOnly argument for the .Protect method of a worksheet. It is an argument that, when set = True, protects the sheet from the user but allows vba to work unhindered. That way you only have to unprotect/reprotect once per session, instead of doing it frequently in your code (based on your example I can only assume you're doing it multiple times). It has a quirk of needing to be set = true once per workbook session, but its understandable. You may want to take a look in the help system for more details about it.

Matt

[Amanda1]

Hi Matt,

You are dead right - it is confusing me and I know what it is - any of our users see this they will run screaming for the hills (hopefullY). I'll try it!!!!!

Thanks for the hint about the other function too - I'm going to look at that.

Just to let you know, my novice modifications to the script you originally gave me have met with approval by two staff members today - running the scripts has just made their life a lot easier - my thanks again.

Cheers

Amanda:beerchug:

[tonyrosen]

Matt,

That's AWESOME

[Steiner]

Hi Matt, it really seems confusing, but what if the attacker simply calls your function (Debug.Print qwpoqwpo)?

Steiner

[brettdj]

ROFL!

[mvidas]

Hi Matt, it really seems confusing, but what if the attacker simply calls your function (Debug.Print qwpoqwpo)?

Steiner
Hey Daniel,

Thats true, there isn't much you could do there. It really is just banking on the hopes that whoever looks at it says "WHOA!" before actually realizing how simple it is. I thought about using a cell's ID property to store the PW (hoping it wouldnt be published), but I've had those clear on me. There really isn't a way of hiding the PW that I can think of for a workbook used by multiple people (cant use a file on the HD or anything), though I'd love to hear of one! The code above was more for fun and confusion than anything else :)

Matt

[Ken Puls]

Matt... I think that you have just proven that knowledge can be a dangerous thing! That is just crazy! LOL!

Could you not just go a little simpler and use the ANSI characters to hide it though?

Dim pass As String
pass = InputBox("Please enter you password")
If pass = Chr(49) & Chr(50) & Chr(51) & Chr(52) Then
MsgBox "success"
Else
MsgBox "wrong password! The police have been dispatched!"
End If

Password is still 1234

[Killian]

I like it, Matt ROFL
While there isn't easy way of stopping someone who knows what they're doing from cracking passwords, as a means of frightening the curious, it's outstanding!

[Aaron Blood]

My script unprotects the worksheet before it runs a macro, (the macro has to sort hidden rows which doesn't work if the sheet is protected). But I have to put in the actual password to unprotect the sheet in the script. Problem then of course is, that although I protect the script with a password - if someone does get into it they can just look at the code and see the password for the sheet.

Amanda:help

:doh: If you PW protect the VBA project... and you're sayin they can break into that! It's pretty much a moot point is it not?

Your best bet is to PW protect the VBA project, use the UserInterfaceOnly property to PW protect every sheet in the app at startup, and don't ever bother toggling the PW protection in your code. It's not very efficient or safe...

[johnske]

:doh: If you PW protect the VBA project... and you're sayin they can break into that! It's pretty much a moot point is it not? ... Exactly, they've cracked your project password and are in the VBE window reading your code - Game over!

[Aaron Blood]

I'm more concerned about those users who enter incorrect data etc and then change a sum formula to make it balance. With my luck they are just the sort of users who, if they did somehow get into the code, would quite happily change links/formulaes etc on the sheet as soon as they knew the password to unprotect it.


...oh, and BTW, I don't need to know your password to make those kinds of changes. :whistle:

[Ken Puls]

...oh, and BTW, I don't need to know your password to make those kinds of changes. :whistle:

FYI, Aaron, in Excel 2003, the trick of overwriting the existing sheet password with a new one doesn't work anymore.

Regardless, hacking a sheet password, removing the VBE password, or just getting access to the code are all very easy tasks once you know how.

Security in Excel (or office) is a non-entity. The best you can do is lock it down against the novice user. Maybe a routine to log the unprotect issue could prove intent, but even that could be VERY easily thwarted. Unfortunately, policy is your best weapon in this battle. :(

[Aaron Blood]

FYI, Aaron, in Excel 2003, the trick of overwriting the existing sheet password with a new one doesn't work anymore.

Gee, thanks for the FYI. I've never bothered with that approach...

If you've got an Excel 2003 WB with a worksheet/workbook level password, send it to me, and I'll modify any formula, in any cell you like and send it back with your original PW intact.

...and I'll do it without the assistance of any 3rd party cracking utilities.

If a user is saavy enough to break into the VBA project it's an exercise in futility to assume that they can't EASILY change any protected cell in any sheet of the workbook.

If I wanted to change something on a worksheet I wouldn't even bother to open the VBA module; that would just slow me down. :dunno

I dare not say anything more...

[Aaron Blood]

I've had the "Unbreakable!" challenge on my site for almost a year now... still no winners...

Maybe one of you will trip me up?

http://www.xl-logic.com/pages/challenge.html

[Zack Barresse]

I dare not say anything more...
Good, because this conversation is borderline with our policies. If this discussion would like to continue between any parties involved, let it continue in pm's please.

Have a great Thanksgiving all! :yes

[Amanda1]

Hi

I really wanted to "hide" it in the code, cause if the macro gives an error, then the user gets in to VB by clicking the debug - even if the project is protected. Hence they will immediately see the password & hey presto they are off changing formula etc. (Its also a comedy of errors that results in the loss).

Matts confusion technique looks my best solution.

Thanks and take care

Amanda:2jump: By the way - someone pls translate for me - what does ROFL stand for?

[mvidas]

ROFL = Roll On Floor Laughing

I'm surprised that isn't already in the dictionary addon to hover over some words, admins?

[Amanda1]

Thanks for the translation Matt:wavey:

[Zack Barresse]

Voila! ROFL!

[Zack Barresse]

Btw, if you password protect your VBA Project, try closing it and reopening it. See if your Debug will take you to the VBE then.

[Killian]

... cause if the macro gives an error, then the user gets in to VB by clicking the debug - even if the project is protected...My understanding was that if the VBA project is locked for viewing, only the "End" button on the error dialog is enabled..?

[johnske]

My understanding was that if the VBA project is locked for viewing, only the "End" button on the error dialog is enabled..?That's correct, but (as Zack suggests) you have to close and re-open the workbook for this to take effect, otherwise the debug button is still enabled.

[Zack Barresse]

That's correct, but (as Zack suggests) you have to close and re-open the workbook for this to take effect, otherwise the debug button is still enabled.
Suggests? I thought I came right out and said it? ;)

[mgh_mgharish]

MsgBox qwpoqwpo

:dunno

[mvidas]

MsgBox qwpoqwpo:dunno



Thats true, there isn't much you could do there. It really is just banking on the hopes that whoever looks at it says "WHOA!" before actually realizing how simple it is. I thought about using a cell's ID property to store the PW (hoping it wouldnt be published), but I've had those clear on me. There really isn't a way of hiding the PW that I can think of for a workbook used by multiple people (cant use a file on the HD or anything), though I'd love to hear of one! The code above was more for fun and confusion than anything else :)

Matt:)

[russkie]

Hey,

What you really can do is have an Input box popup when the workbook is loaded and allow that input box to store the password.

You would need to make the closing of thw workbook perform a similiar action because you need to store the password somewhere for it to call it back when it loads, SOooo, what you can do is upon closing the workbook change the password to (example:) "OpenMe", this must never change, THEN when the workbook opens, automatically it enters the password "OpenMe" and immidiatly pops up with the input box requesting the new password and changing it to that. Like this it is impossible to find the password in the code becuase its not there!

[Skittels]

A Pop-up storing the password invisible for the user sounds good, i am struggling with the same issue, it's not that big an issue that they can see the macro, but it is that user can find the password and unlock the document itselve.

Do you have an example of this?