Download
managing passwords in the sas system n.
Skip this Video
Loading SlideShow in 5 Seconds..
Managing Passwords in the SAS System PowerPoint Presentation
Download Presentation
Managing Passwords in the SAS System

Managing Passwords in the SAS System

71 Views Download Presentation
Download Presentation

Managing Passwords in the SAS System

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Managing Passwords in the SAS System Allen Malone Senior Analyst/ProgrammerKaiser Permanente

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

  3. 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

  4. 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.

  5. 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

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

  7. 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!

  8. HowDoes it Work? LIBNAME HTP odbc dsn='HealthTRAC_Prod' user=B468357 password=%pw(htrac); DATA patients(pw=%pw(dspw) encrypt=YES); SET HTP.members; ... RUN;

  9. How Does it Work? (cont.) PROC SQL; CONNECT TO teradata AS tera (user=B468357 pw=%pw(clar) db=massiveDB 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, 59, 519,109,991222,999408) ); DISCONNECT FROM TERA; QUIT;

  10. SAS Macro -- Basic Implementation %MACRO pw( sys_code ); %LOCAL CLAR DB2 HTRAC DSPW; %LET CLAR=secret1; /* clarity password */ %LET DB2=secret2; /* db2 password */ %LET HTRAC=secret3; /* healthTRAC Password*/ %LET DSPW=secret4; /* data set password */ &&&sys_code %MEND;

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

  12. Macro Debugging Options • SYMBOLGEN • MLOGIC • MPRINT • MACROGEN

  13. Managing Macro Debugging Options %MACRO pw( sys_code ); %IF %sysfunc(getoption(SYMBOLGEN))= SYMBOLGEN OR %sysfunc(getoption(MLOGIC)) = MLOGIC OR %sysfunc(getoption(MPRINT)) = MPRINT OR %sysfunc(getoption(MACROGEN)) = MACROGEN %THEN %DO; %PUT ERROR: PW.SAS failed! Turn off Macro Debug Options; %GOTO quit; %END; %LOCAL CLAR DB2 HTRAC DSPW; %LET TSO=secret1; /* Z/OS password */ %LET DB2=secret2; /* db2 password */ %LET HTRAC=secret3; /* SQL Server Password*/ %LET DSPW=secret4; /* data set password */ &&&sys_code %quit: %MEND;

  14. 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 code\My Macro Directory‘; OPTIONS MAUTOSOURCE SASAUTOS=(sasautos mymacs);

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

  16. 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; ...

  17. 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; %optsOff; /* Check Macro options; Turn off if necessary */ DATA work.secure_patient_recs2( pw=%pw(DSPW)); SET work.secure_patient_recs( pw=%pw(DSPW)); RUN; %optsOn; /* If previously turned on, then turn options back on */

  18. 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 inUseFlag to “yes”. • Set back to “no” when Mainframe connection is finished. *No sample code available for this topic.

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