One deadly sin SAS programmers should stop committing

20
The Penitent Magdalene

Titian (Tiziano Vecellio) (Italian, about 1487 - 1576) The Penitent Magdalene, 1555 - 1565, Oil on canvas 108.3 × 94.3 cm (42 5/8 × 37 1/8 in.) The J. Paul Getty Museum, Los Angeles; Digital image courtesy of the Getty's Open Content Program.

Even if you are a traditional SAS programmer and have nothing to do with cybersecurity, you still probably have to deal with this issue in your day-to-day work.

The world has changed, and what you do as a SAS programmer is not just between you and your computer anymore. However, I have found that many of us are still careless, negligent or reckless enough to be dangerous.

Would you scotch-tape your house key to the front door next to the lock and go on vacation? Does anybody do that? Still, some of us have no problem explicitly embedding passwords in our code.

That single deadly sin, the thing that SAS programmers (or any programmers) must not do under any circumstances, is placing unmasked passwords into their code. I must confess, I too have sinned, but I have seen the light and hope you will too.

Password usage examples

Even if SAS syntax calls for a password, never type it or paste it into your SAS programs. Ever.

If you connect to a database using a SAS/ACCESS LIBNAME statement, your libname statement might look like:

libname mydblib oracle path=airdb_remote schema=hrdept
	user=myusr1 password=mypwd1;

If you specify the LIBNAME statement options for the metadata engine to connect to the metadata server, it may look like:

libname myeng meta library=mylib
	repname=temp metaserver='a123.us.company.com' port=8561 
 		user=idxyz pw=abcdefg;

If you use LIBNAME statement options for the metadata engine to connect to a database, it may look like:

libname oralib meta library=oralib dbuser=orauser dbpassword=orapw;

In all of the above examples, some password is “required” to be embedded in the SAS code. But it does not mean you should put it there “as is,” unmasked. SAS program code is usually saved as a text file, which is stored on your laptop or somewhere on a server. Anyone who can get access to such a file would immediately get access to those passwords, which are key to accessing databases that might contain sensitive information. It is your obligation and duty to protect this sensitive data.

Hiding passwords in a macro variable or a macro?

I’ve seen some shrewd SAS programmers who do not openly put passwords in their SAS code. Instead of placing the passwords directly where the SAS syntax calls for, they assign them to a macro variable in AUTOEXEC.SAS, an external SAS macro file, a compiled macro or some other SAS program file included in their code, and then use a macro reference, e.g.

/* in autoexec.sas or external macro */
%let opw = mypwd1;
 
/* or */
 
%macro opw;
	mypwd1
%mend opw;
/* in SAS program */
libname mydblib oracle user=myusr1 password=&opw
        path=airdb_remote schema=hrdept;
 
/* or */
 
libname mydblib oracle user=myusr1 password=%opw
        path=airdb_remote schema=hrdept;

Clever! But it’s no more secure than leaving your house key under the door mat. In fact it is much less secure. One who wants to look up your password does not even need to look under the door mat, oh, I mean look at your program file where the password is assigned to a macro variable or a macro. For a macro variable, a simple %put &opw; statement will print the password’s actual value in the SAS log. In case of a macro, one can use %put %opw; with the same result.

In other words, hiding the passwords do not actually protect them.

What to do

What should you do instead of openly placing or concealing those required passwords into your SAS code? At least encode passwords.

SAS software provides a special procedure to disguise passwords to prevent them from viewing.

This is PROC PWENCODE, and it is very easy to use. In its simplest form, in order to encode your password abc123 you would need to submit just the following two lines of code:

proc pwencode in="abc123";
run;

The encoded password is printed in the SAS log:

1 proc pwencode in=XXXXXXXX;
2 run;

{SAS002}3CD4EA1E5C9B75D91A73A37F

Now, you can use this password {SAS002}3CD4EA1E5C9B75D91A73A37F in your SAS programs. The SAS System will seamlessly take care of decoding the password during compilation.

The above code examples can be re-written as follows:

libname mydblib oracle path=airdb_remote schema=hrdept
	user=myusr1 password="{SAS002}9746E819255A1D2F154A26B7";
 
libname myeng meta library=mylib
	repname=temp metaserver='a123.us.company.com' port=8561 
 		user=idxyz pw="{SAS002}9FFC53315A1596D92F13B4CA";
 
libname oralib meta library=oralib dbuser=orauser
dbpassword="{SAS002}9FFC53315A1596D92F13B4CA";

You need to realize that while encoding a password impedes its usage outside of SAS, it does not preclude from using it freely elsewhere within the SAS system.

It’s a common practice to place SAS library definitions into a separate SAS Autoexec file or some other external files that can be brought into your program using %include, autocall or stored compiled macro facility. Having encoded your passwords within those files, you still have to make extra efforts by protecting the files using filesystem permissions or/and other access control measures.

Encoding methods

The {SAS002} prefix indicates encoding method. This SAS-proprietary encoding method which uses 32-bit key encoding is the default, so you don’t have to specify it in the PROC PWENCODE.

There are other, stronger encoding methods supported in SAS/SECURE:

{SAS003} – uses a 256-bit key plus 16-bit salt to encode passwords,

{SAS004} – uses a 256-bit key plus 64-bit salt to encode passwords.

If you want to encode your password with one of these stronger encoding methods you must specify it in PROC PWENCODE:

proc pwencode in="abc123" method=SAS003;
run;

SAS Log: {SAS003}50374C8380F6CDB3C91281FF2EF57DED10E6

proc pwencode in="abc123" method=SAS004;
run;

SAS Log: {SAS004}1630D14353923B5940F3B0C91F83430E27DA19164FC003A1

Beyond encoding

There are other methods of obscuring passwords to protect access to sensitive information that are available in the SAS Business Intelligence Platform. These are the AUTHDOMAIN= SAS/ACCESS option supported in LIBNAME statements, as well as PROC SQL CONNECT statements, SAS Token Authentication, and Integrated Windows Authentication. For more details, see Five strategies to eliminate passwords from your SAS programs.

Encoding versus Encryption

According to SAS documentation, PROC PWENCODE uses encoding to disguise passwords. With encoding, one character set is translated to another character set through some form of table lookup. Encryption, by contrast, involves the transformation of data from one form to another through the use of mathematical operations and, usually, a “key” value. Encryption is generally more difficult to break than encoding.

PROC PWENCODE is intended to prevent casual, non-malicious viewing of passwords. You should not depend on PROC PWENCODE for all your data security needs; a determined and knowledgeable attacker can decode the encoded passwords. Data should be protected by other security controls such as file system permissions or other access control mechanisms.

Conclusion

Never place unprotected password into your SAS program. At the very least encode it!

Place this sticker in front of you until you memorize it by heart.

PROC PWENCODE sticker

 

Share

About Author

Leonid Batkhan

Leonid Batkhan, Ph.D. in Computer Science and Automatic Control Systems, has been a SAS user for more than twenty years. He came to work for SAS in 1995 and is currently a Senior Consultant with the SAS Federal Data Management and Business Intelligence Practice. During his career, Leonid has successfully implemented dozens of SAS applications and projects in various industries.

20 Comments

  1. Proc pwencode? You can copy/paste that string in the same way as the human readable one. Why trying to overrule the generic features delivers as OS ore elsewhere.
    Storing that one in the SAS metadata? It is reversable and sometimes get into readable scripts. The external DBMS doesn't; understand the pwencode hash so it is reversed.

    Why not combining some things.
    - Use a safe personal location on the OS level and
    - use all things like the sas-dataset encryption
    - the pwencode when possible (only sas will reverse that one)
    - a sas macro setting the macro expansion off and to orginal state

    • Leonid Batkhan
      Leonid Batkhan on

      Jaap, of course there many layers to safeguard data. You suggest using "pwencode when possible" and so do I. This blog post is not a comprehensive guide to the data security, it covers one little aspect of it - using PROC PWENCODE to mask passwords in SAS programs. The main reason I wrote this post is that I've seen a way too many cases when SAS programmers just don't do it, and I've decided to bring this to their attention.

  2. To make it more complete why should all libnames and filenames with physical names be placed inside normal business logic code?
    You can isolate them to a generic startup (autoexec) so code will become more maintainable and being promotable/deployable without any code changes in that well tested business logic.

    • Leonid Batkhan
      Leonid Batkhan on

      Jaap, thank you for your comment. I am not saying you should't place your passwords in some files aside from your main program logic. For maintainability and portability pusrpose that is quite welcome. What I am saying is that wherever you place your passwords, you should not save them unmasked, - always use encrypted/encoded versions of the passwords.

  3. Nina L. Werner on

    Who cares what the unencrypted password looks like? I don't use PWENCODE because anyone can copy the encoded string password into their evil SAS code and get access same as using the clear text. Twenty years ago, I wrote a couple of macros to encrypt and store and retrieve your Oracle passwords in a password-protected SAS dataset for Windows or Unix Server, which I presented to the Madison WIsconsin SAS users group. While my actual encryption process is very basic, the multiple password layers make it safer. Would you like me to resurrect that old code? [Easter joke] After my LIBNAME, I code a password "reset" statement: LET pw = 999;

    • Leonid Batkhan
      Leonid Batkhan on

      Nina, thank you for your feedback. Here are my thoughts.

      "Who cares what the unencrypted password looks like? ... because anyone can copy the encoded string password into their evil SAS code and get access same as using the clear text".

      First, I am sure, IT department would not appreciate if you place a "naked" password in your code file.

      Secondly, wouldn't you agree that much smaller population is capable of writing "SAS code" than "non-SAS code"? Therefore, by using PWENCODE you hinder the majority of "evil" and "non-evil" coder population"?

      The reason for PWENCODE is to make it more difficult to use your database password outside of SAS. As for your "reset" statement %let pw=999, if any SAS coder get access to your program file what would prevent them from inserting %put &pw; right before your "reset"? You are welcome to resurrect and share your old code.

      Of course, there have to be multilayer file access protection as well. Analogy: you might be smart enough not to scotch-tape your house key to the front door next to your lock and go on vacation, but would it really matter if you leave all of your windows wide open?

  4. Clifton Denning on

    Great tips! I had a to fight tooth and nail at my organization to prevent people from doing this. They wanted to store the passwords in home directories... which were unlocked (our IT presence was disinterested at best when it came to SAS). I finally had to convince them by standing at my directors desk and logging into her account for her.

    Fortunately I found an easy solution in Enterprise Guide, using the default authorization stored there. Of course that only works if you also have EG, but we have had success with it.

    • Leonid Batkhan
      Leonid Batkhan on

      Thank you, Clifton, for sharing your experience. You are a tough cookie, and obviously a cyber security leader at your organization.

  5. Gregory Steffens on

    Excellent point about security!

    I've implemented passwords, following the unix .netrc approach. I store encrypted passwords in a file with a standard format, have a SAS macro read that and put the passwords in a local macro variable (not a global one). Other macros call this authentication macro to issue libname statements and other statements with userid/password values, turning the log off and setting the macro variable to null after using it. The authentication macro even prompts for the userid/password if it is not found in the netrc file.

    Another advantage is that when passwords change, there is just one file you need to change and the 1000 programs that use the password do not need to be changed.

Leave A Reply

Back to Top