topic Re: Prevent a macro from being 'put'? in SAS Programming

Prevent a macro from being 'put'?

paulsonalec — Fri, 17 May 2019 15:22:42 GMT

Is there a way to set up a macro variable such that its contents cannot be '%put' into the SAS log?

For example, I have a password SAS program saved on my local drive that when '%include'd in my program, sets up my password for Teradata access:

options nosource;
%let dbpass = Password123;

In this way, I do not explicitly state my password in any analysis code, which is desired because I do not want other people on my team to know my Teradata password. Unfortunately, those ill-intended individuals who want to know my password can easily do so with this code:

%put &dbpass. ;

Is there a way to set up my password such that it cannot be revealed with a '%put' statement?

Thanks in advance!

Re: Prevent a macro from being 'put'?

Kurt_Bremser — Fri, 17 May 2019 15:45:03 GMT

The most simple method for protecting passwords is protecting the user, meaning that everyone has their own user for access. Sharing user-ID's is always a VERY BAD idea, securitywise.

I know of no method to keep an existing macro variable from being used in a %put or a symget().

Re: Prevent a macro from being 'put'?

Astounding — Fri, 17 May 2019 16:24:37 GMT

A few ideas to play with ...

It's only a partial solution, but take a look at PROC PWENCODE. You can use an encoded version of your password.

Depending on how smart the users are, you can set up your password in a way that it erases after using it one time. For example, consider the code that you %INCLUDE:

options nosource;

%let dbpass = Password123;

Note that you may need to add NOSOURCE2 to your list of options, to suppress writing %INCLUDEd code to the log. Here is tricky way (modified after I first thought about it) so you don't need a macro variable (&DBPASS) at all:

%macro passw;

%global k;

%let k = %eval(&k+1);

%if &k=1 %then Password123;

%mend passw;

Currently when you use SAS to access Teradata, you need to specify something like:

password = "&dbpass"

Instead, use:

password = "%passw"

The users won't even have a macro to examine. If they try to execute %PASSW a second time in the same program, it generates nothing.

Re: Prevent a macro from being 'put'?

paulsonalec — Fri, 17 May 2019 16:24:23 GMT

Thanks for the reply! This is clever, but limits the ability of the user to re-run the code in case that's desired. I'll add the options you suggested. It sounds like there's really no good answers to this problem

Re: Prevent a macro from being 'put'?

Astounding — Fri, 17 May 2019 16:26:32 GMT

Yes, no ideal solution. Note that I updated the code to make it simpler, and to prevent the user from gaining information by trying something like this:

%put _user_;

There isn't even a macro variable to examine.

Re: Prevent a macro from being 'put'?

VDD — Fri, 17 May 2019 17:39:34 GMT

You have to put trust into the users you assign roles to.

then if logging is required you need to use one of the suggestions above to remove those macro results or reset them to null after there need has been compleated.

Re: Prevent a macro from being 'put'?

Tom — Sat, 18 May 2019 00:14:05 GMT

The best method is to use authentication methods that do not use passwords.

If you have SAS/BI (or whatever they call it now) where you can store credentials in metadata then use that.

If you really must access systems where you have to use passwords then store the information in files. Make sure the files are properly secure. For example by placing them in your .ssh directory that should be readable only by you. Then you can use a macro to pull from the file without ever putting the value into a SAS macro variables. (Such as this one https://github.com/sasutils/macros/blob/master/fread.sas)

Just make sure that MPRINT and other options that might display the value are off.

Example:

%let opt_save=%sysfunc(getoption(mprint)) %sysfunc(getoption(macrogen));
%let opt_save=&opt_save %sysfunc(getoption(symbolgen));
options nomprint nomacrogen nosymbolgen;
libname mydb oracle path=mydb user=myuser pass="%fread(~/.ssh/mydb.txt,mode=3)" ;
options &opt_save;

For this usage you would probably want to make a version of FREAD() that only supported the MODE=3 option of returning the file results as the output of the macro. You could also have that macro test that that the options are set properly to prevent displaying the password and fail when they are not.

Re: Prevent a macro from being 'put'?

Patrick — Sat, 18 May 2019 01:43:00 GMT

@paulsonalec

Use authentication domains http://support.sas.com/documentation/cdl/en/mcsecug/69854/HTML/default/viewer.htm#aboutum.htm#p18zm6n7c8eketn1ggs2opidsuzk

Or as another option found here: https://go.documentation.sas.com/?docsetId=acreldb&docsetTarget=n020g4ewg9ioe6n1rr3ihchwny4v.htm&docsetVersion=9.4&locale=en

One way to protect connection information is by storing user name, password, and other connection options in a local environment variable. Access to the DBMS is denied unless the correct user and password information is stored in a local environment variable. See the documentation for your DBMS to determine whether this alternative is supported.