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.
39 Comments
Hi Chris,
I'm looking for an approach of Encrypting a Text File directly in SAS E-G, without using any code.
I went under Options --> Security, and this only gave options for checking to not put the password in generated code. However, how to be able to extend this to a Text File?
Do you also have a phone #?
Thanks!
Shane, EG doesn't support encryption directly in this way. I'm curious about the use case though. If you want to follow up with me, you can send me direct e-mail: chris.hemedinger@sas.com
Hi Leonid,
I'd be interested in seeing how to go about on solving this:
Say I have an encryption portion of passing a SAS dataset which lies here at my company to Acxiom’s Oracle DB.
I’ve tested encrypting a SAS Dataset and opening the SAS Dataset with a key. That works within the company's SAS environment.
But not successful passing an encrypted SAS Dataset to an Oracle database.
Please advise with any syntax. Thanks!
Shane, I'm guessing that you're using the ENCRYPT= data set option. See the cautions/limitations of this in the documentation.
In particular, note that "To copy an encrypted AES data file, the output engine must support AES encryption." If you're copying an encrypted SAS data set to Oracle, that might not work. You might need to decrypt the records you want to copy and then add them to the database. Something like this might work:
Very useful post and comments!
Thanks, Mike, I am glad you found it useful.
I’m thinking the better method for Enterprise Guide is to store passwords in Metadata (EGUIDE > TOOLS > SAS ENTERPRISE GUIDE EXPLORER > FILE > MANAGE LOGINS) and then use the AUTHDOMAIN= option on the LIBNAME, etc.
When the encrypted password is used from Unix shell executed SAS programs, we tell users to put the code that sets a password variable in a file that has Unix permissions of 600 and do an include of that file, so only they can read the encrypted password and use it.
Kevin, thank you for your comment. I agree that storing passwords in Metadata is a preferred way. However, not all SAS installations have metadata. In many cases, SAS code is run interactively or in batch mode without touching metadata. And unfortunately, I have seen way too many cases when SAS programmers carelessly place unencrypted passwords into their code.
For the SAS BI installations, I wrote in this blog post: "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."
For the Unix shell scenario you are doing it exactly right: you encode the passwords and protect the files where the passwords are stored. Here is another example of storing an encoded password in an external file.
Hi,
As Nina L. Werner wrote (see previous), if visible, everyone can use the encrypted password in another SAS code. Of course, encrypted is better but not safer at all. A better solution should be to encrypt the password and that the encryption is linked to a unique (or not unique) userid.
Something like this :
PROC PWENCODE IN="mypassword" USER="USER1 USER2 USER3"; RUN;
=> The resulting encrypted password should be usable in a SAS code but only when that SAS code is executed under one of the specified users (USER1, USER2 or USER3). The encrypted password should not be valid if used in a SAS program submitted by USER4.
Unfortunately, the parameter "USER" is not available... Perhaps an improvement for a future release ?
Thank you, Francois, for your comment and suggestion. In its current form proc pwencode encodes passwords irrespective of users. Using the encoded passwords instead of “naked” passwords is just a single little step in a grand scheme of data security. Let me reiterate the last paragraph of this blog post:
"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."
Hello Leonid,
This is a very insightful article, thank you for putting this up.
Question: I am running Base SAS in a Unix environment (executing SAS scripts at the command line via PuTTY). Essentially I'm running in the old "batch" mode where the instance of SAS ends once the program is completed. Does the encrypted password persist after the session has ended (so I could use my encrypted password in another session, in a different SAS program) or is it lost? I have many scripts which are setup to run automatically (executing via wrapping a Korn Shell Script around them, and then using Crontab to call the script) so I would like to know could I propagate my encrypted password across all of my code.
Thank you!
Matthew, thank you for your question. Encrypted passwords as well as unencrypted passwords do not propagate by themselves - they are just strings of characters. Unless you assign that string of characters to some global macro variable. If you assign your encoded password to a macro variable in an autoexec file, then every time you initialize a SAS session your macro variable value (the password) will be available within that SAS session.
I am hoping to benefit from this thread and the numerous SAS experts in the community with specific regard to an approach for having shared code which will include any user specific credentials needed to authenticate and to have the code shared across all team members without the need to edit first. The overarching aim is to enable cross-functional teams and eliminate the problem where specific users are the only ones enabled to run the specific code more often than not because they have their credentials tied to a macro variable, not encrypted or encoded, I feel this is a huge bottleneck.
I may use the wrong terms as I have zero SAS experience.
My thought was to use %userprofile% variable, for windows based PC's, that will hopefully allow specific local paths for output, to simply bypass network latency and to point to specific local paths, but most importantly, and for which this thread is discussing, to enable the same local file to have each user's various connection strings, encoded or encrypted, which then get included upon initializing global variables, to authenticate for the respective user and allow the code to be shared and dynamic. Do environmental variables come into play?
I would be very pleased if I could hear back with regard to best security practice whilst considering the shared user approach.
Thanks in advance
Paul Jones
Paul, thank you for your feedback. However, I think you are asking for too broad a discussion which is way outside of the topic of this blog post. I would suggest going to the basics of the operating system security for the shared users authorization control. This blog post only touches on the password encryption within SAS.
Hi Leonid
You are absolutely right, thank you for your feedback and I will be happy follow your advice.
Leonid,
Great blog as always! And, I easily understood that it was aimed at _ONLY_ONE_ specific issue in the great landscape of data security.
I forwarded the link to this blog to a couple of people that I know would be interested. And, after writing this, I am going to print multiple copies of the blog on a color printer, cut out the yellow sticker and give it to a few colleagues.
Thank you, Michael. Great comment, as always. And, after reading it, I thought of producing and giving out to SAS users packs of the yellow stickers with pre-printed proc syntax. That might save them some printer paper. Yes, some people need to start thinking about data security, and I thought that this _ONE_ is a good point to turn them 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.
Thank you, Gregory, for your feedback and for sharing your experience.
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.
Thank you, Clifton, for sharing your experience. You are a tough cookie, and obviously a cyber security leader at your organization.
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;
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?
Of course my passwords are encrypted, then locked away in password protected SAS datasets. NOSOURCE2 option prevents displaying the passwords as they are retrieved, used, then blanked out. Further, the %INCLUDE macro call is stored in my Windows user virtual drive, so no one else can call it. I would NEVER put any clear password anywhere ever. I'm sorry for the flippant tone of my reply. I was trying to convey that no one would ever save their password in the code; I must be wrong about that!
Nina, thank you for your clarification. Obviously, you are doing great with your password protection. However, and unfortunately I saw way too many instances where people placed naked passwords in their SAS code.
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.
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.
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
Jaap, of course there are 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 way too many cases when SAS programmers just don't do it, and I've decided to bring this to their attention.
Agree! Important to have encrypted passwords. To assist SAS administrators in creating encoded/encrypted passwords directly from the SAS® Management Console, Metacoda has a free Password Encoder plug-in. https://www.metacoda.com/en/products/utility-plug-ins/password-encoder/
Thank you, Michelle, for your constructive feedback. It is very thoughtful of Metacoda to have created the Password Encoder plug-in. Very handy for the SAS admins. It is also very generous of you to distribute it for free. Am I correct in my assumption that this plug-in runs proc pwencode in the background?
Yes it does run proc pwencode with options for the 4 encoding methods. We have a number of free plug-ins as described at https://www.metacoda.com/en/2015/07/free-sas-metadata-tools/
Thank you, Michelle.
Thanks, Leonid! This is wonderful. This would make a great tip of the day on sasCommunity.org...
Thank you, Paul. Please feel free to share / propagate it to the SAS community. I would appreciate your link back / reference to this blog post.
I am more than happy to link back. More people should know about this. See the tip here, which will hopefully be featured on the front page at some point!
Thank you, Paul!
Thank you, Leonid,
It is a very handy tool for those who need to protect password in their SAS codes.
Thank you, Anna. It is just one layer of protection, very easy to use, and it masks the password from plain view.