Managing Passwords in the SAS System Allen Malone

Managing Passwords in the SAS System Allen Malone Senior Analyst/Programmer Kaiser Permanente

How do you Manage Passwords? • • Hard Code? Macro variables? Manual entry? Something Else?

Data Security Is Important • Survey by Ponemon Institute: 19% people ended relationship with business when notified of data security breach. • Lawsuits and settlements. • Lose Customers. • No bonus

What is a Good Approach? • • Easy to use Simple to Understand Easy to manage, (add, update) Passwords Programmers need to buy into it. p. s. The solution does not have to be a perfect.

Easy to Use • Same method works with in all SAS code – Data Step – Proc Step – SAS/CONNECT – SCL – SQL Pass Thru • Does not interfere with program logic

Easy to Manage • One file to add or update password information. Simple to Understand • No Complex Logic

Does not have to be Perfect • Most data security laws require reasonable security precautions, not impenetrable methods. • Too complex and Difficult… No one will used it!

How Does it Work? LIBNAME HTP odbc dsn='Health. TRAC_Prod' user=B 468357 password=%pw(htrac); DATA patients(pw=%pw(dspw) encrypt=YES); SET HTP. members; . . . RUN;

How Does it Work? (cont. ) PROC SQL; CONNECT TO teradata AS tera (user=B 468357 pw=%pw(clar) db=massive. DB tdpid=prod); EXECUTE ( DIAGNOSTIC NOPRODJOIN ON FOR SESSION ) BY TERA; CREATE TABLE new_visits AS SELECT * from connection to tera ( SELECT PE. PAT_ID FROM HCCLCO. PAT_ENC PE WHERE PE. ENC_CLOSE_DATE > DATE&SYM_BEG AND PE. ENC_TYPE_C IN (9, 519, 109, 991222, 999408) ); DISCONNECT FROM TERA; QUIT;

SAS Macro -- Basic Implementation %MACRO pw( sys_code ); %LOCAL CLAR DB 2 HTRAC DSPW; %LET CLAR=secret 1; /* clarity password */ %LET DB 2=secret 2; /* db 2 password */ %LET HTRAC=secret 3; /* health. TRAC Password*/ %LET DSPW=secret 4; /* data set password */ &&&sys_code %MEND;

Vulnerabilities of The Basic Implementation • Macro Debugging options • Macro Code Accessibility • Trace Command – SAS/CONNECT

Macro Debugging Options • • SYMBOLGEN MLOGIC MPRINT MACROGEN

Managing Macro Debugging Options %MACRO pw( sys_code ); %IF %sysfunc(getoption(SYMBOLGEN))= %sysfunc(getoption(MLOGIC)) = %sysfunc(getoption(MPRINT)) = %sysfunc(getoption(MACROGEN)) = %PUT ERROR: PW. SAS failed! %GOTO quit; %END; SYMBOLGEN OR MLOGIC OR MPRINT OR MACROGEN %THEN %DO; Turn off Macro Debug Options; %LOCAL CLAR DB 2 HTRAC DSPW; %LET TSO=secret 1; DB 2=secret 2; HTRAC=secret 3; DSPW=secret 4; &&&sys_code %quit: %MEND; /* /* Z/OS password */ db 2 password */ SQL Server Password*/ data set password */

Managing Macro Code Accessability • Do not store the userid with the password • Store files in a secure directory • Use Macro Autocall Library /* Setting up Autocall Macros in your SAS code. /* /* Macro names must match the file name in which */ they are stored for autocalls to work! */ FILENAME mymacs ‘c: SAS codeMy Macro Directory‘; OPTIONS MAUTOSOURCE SASAUTOS=(sasautos mymacs); */

Advanced Password Management Topics • Using %pw() with SAS/CONNECT • Programmatically turning Debugging Options off and on. • Userid/Password Pooling

SAS/Connect • SAS/CONNECT connect scripts are macro enabled. • Use double quotes around macro. /* A snippet of a SAS/CONNECT signon Script using %pw() */. . . /*---------MVS LOGON------------*/ /* /* input 'Userid? '; */ type ENTER; */ type ‘AMALONE' ENTER; /* /* input nodisplay 'Password? '; type ENTER; type "%pw(TSO)" ENTER; waitfor 20 seconds; type "&TSOTYP" ENTER; . . . */ */

Programmatically Turning Off Macro Debug Options • Can’t turn off Macro Debug Options inside %pw() code. • Must use separate macros to turn options off and on. • Macros must be invoked outside the data step and PROC step code. OPTIONS SYMBOLGEN; %opts. Off; /* Check Macro options; Turn off if necessary */ DATA work. secure_patient_recs 2( pw=%pw(DSPW)); SET work. secure_patient_recs( pw=%pw(DSPW)); RUN; %opts. On; /* If previously turned on, then turn options back on */

Userid/Password Pooling • Used for simultaneous, multiple connections to IBM mainframe. • Userid and Passwords pairs stored in dataset. • Suite of macros control/manage pairs in dataset. • When program uses a userid, set in. Use. Flag to “yes”. • Set back to “no” when Mainframe connection is finished. *No sample code available for this topic.

Conclusion • • Looked at simplementation Reviewed vulnerabilities Addressed vulnerabilities Discussed advanced ways to use this concept. • Questions or Comments?