Methods to protect database passwords in macros? 1

[wgeorgia]

Can anyone suggest methods of protecting a database password, which changes monthly, when using a macro to run an impromptu report? Know of the mcx vs. mac being readable but have many macros which would have to be updated and compiled monthly? Thanks.

 

[griffindm]

wgeorgia,

I wrote an encrypt/decrypt function, details below. It's a fairly simple algorithm, but enough to keep out casual prying eyes. It lets you include the password in encrypted clear text, but translates it while the macro runs.

Check my profile for my email for further details / explanations.

HTH,

Dave Griffin ;-)

DECLARE FUNCTION Mix(string1$,action$)

FUNCTION Mix (string1$,action$)
Dim Res$
Dim NxtChrVal%
Dim NxtChr$
Dim Stepper%
Res$ = ""
Select Case action$
Case "E"
Stepper = 1
Case "D"
Stepper = -1
End Select
cnt = len(string1$)
for x = 1 to cnt
NxtChrVal% = Asc(Mid(string1$,x,1))+(x*Stepper)
IF NxtChrVal% > 128 THEN NxtChrVal% = NxtChrVal% -128
Res$ = Res$+Chr(NxtChrVal%)
next x
Mix = Res$
End function

 

[griffindm]

wgeorgia,

I missed the part about the password changing monthly. Use the function I listed above with an encrypted password within a text file that you open and read into the macro using the Open and either the Get or Line Input functions to read the file content into the macro. That way you will not have to open, change and recompile the macros each time the password changes. Let me know if you need an example, but the Macro help for File Input/Output should be a good starting point.

Just a question - Why does the password have to change monthly? We use a restricted reporting ID that NO end user knows the password for. Our IT shop is satisfied enough by Impromptu security that it does not have to be changed on a regular schedule. We embed that password into our catalogs for all users, and use the catalog filters and governors to restrict individual users, without changing the ID the user class accesses the database with.

I only use the Mix function to encrypt the database password when using ODBC calls within macros myself.

HTH,

Dave Griffin

 

[wgeorgia]

Thanks. I am going to try this. The monthly password changing is just due to a corp. policy.

 

[DrSimon]

Dave

I've just come across this. I'm sure I'm being thick but I don't understand how to use this in a macro. Please explain how I would use your function to encrypt "OurPwd" in the statement below.

ImpApp.OpenCatalog "MyCat","UserClassID",,"DB_ID","OurPwd"

I'm extremely grateful to you both as it hadn't occurred to me to check how much gets compiled.

Regards

Simon Rouse

 

[griffindm]

Simon,

You would run the function on an encrypted password string to get the true password into a variable before it is used in the OpenCatalog method. As in:

pwd$ = Mix(encrypted string,"D&quot

Then open the catalog with:

ImpApp.OpenCatalog "MyCat","UserClassID",,"DB_ID",pwd$

The function takes two arguments. The first is the string to process, and the second is either an "E" or "D" (for encrypt/decrypt). In the macro you would usually use a "D", but you can put the password in in clear text, use the "E" option, and put a breakpoint in after it is called to see what the encrypted string would be. Then replace the clear text with the encrypted string, change the option back to "D", and check that the returned string is properly decrypted.

This example is one of the few times I have posted complete macro code, just because so many of Impromptu users are novices to this area, and because I don't want Cognos to get a black eye on security complaints because of clear text passwords.

Send me an email (check my profile for my address) if you need further help, as I'm hesitant to put too much in the open forum on this topic, just as a precaution.

Hope this helps,

Dave Griffin

The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"

http://www.decisionsupportgroup.com