Samples SAS     hardening SAS
Concepts    Solutions    top  bottom

Hiding securing storing business keys - passwords

Signed me
Problems Solution
Define standard ways of solving the problem.



Concepts using SAS base code


Background Information:

SAS base language has the options of: The running project should already have: The only problem to be solved is extending the business data environment to a third party component like Oracle Netezza Teradata having stored much more of the business data.(to same level secured).

Detail design


Implementations:
Running well in combination with all other default startup settings, all SAS versions and environments.


Macro usage Note
Instead of coding user/passwor into code like:
libname dmorp101 oracle PATH="PMIB" SCHEMA=P_DRS defer=yes user="P_RSI_CI" pw="Z75AftgTb" buffsize=12000 dbindex=yes ;

Make a macro call with the positon in the code.
libname dmorp102 oracle PATH="PMIB" SCHEMA=P_DRS defer=yes %xkeypsw(PMIB) buffsize=12000 dbindex=yes ;

Remark: not using a ; after the macro-call.

Sample to create SAS dataset
Data lib.keypswd (read=P1jkU7r write=secret-me alter=secret-me) ;
   length id $ 16 keypsw $ 32 ;
   id="orion" ; keypsw="test app" ; output;
   id="PMIB" ; keypsw='user="P_RSI_CI" pw="Z75AftgTb"' ;output;
run;



Sample SAS macro
/* * * xkeypswd * * * */
/* * * read personal key-pswd to acess external dbms * * * */
/* * * written by:jakarman * * * */
/* * * designed 2007 (8.2) converted 2012 (9.-) * * * */

%macro xkeypsw ( mainarg,opt,readvl=P1jkU7r,keypswfl=lib.keypswd) ;
%local larg lind xkeypswdret ;
%let opt =%lowcase(&opt) ;
%let larg=%length(&mainarg) ;
%let lopt=%length(&opt) ;

%let xkeypswdret=;
%if ( %index(&opt,HELP) >0 | %index(&opt,?) >0 | %index(&mainarg,?) >0 ) %THEN %do ;
   %* (insert your comment as copied this sample source) ;    %let xkeypswdret=help text given ;
%end ;

%if ( %length(&xkeypswdret) = 0) %then %do ;
   %let keydsid=%sysfunc(open(&keypswfl ( read=&readvl where=( id = "&mainarg") ) ,i ));
   %if (&keydsid = 0) %then %put %sysfunc(sysmsg());
   %else %do ;
      %let rc=%sysfunc(fetch(&keydsid ));
      %if ( &rc ne 0) %then %put %sysfunc(sysmsg()) ;
      %let pw=%sysfunc(getvarc(&keydsid, %sysfunc(varnum(&keydsid,keypsw)))) ;
&pw
   %end ;
   %if ( &keydsid > 0 ) %then %let rc=%sysfunc(close(&keydsid)) ;
%end;

%mend ;


New Operational problems like: Not Solved problems:
Conclusion:
This approach is able to fullfill all requirements for using in secure environments.



SAS metadata

Background Information:
There is a concept delivered with SAS metadata. It relies on the metadata registration and the metalib engine.
The keywords of this concept are:
Detail design:
Not available, customer dependent.
Using Eguide prompting
There must be a lot of questions arround to this.
New Operational problems like:


Not Solved problems:



Conclusion:
This approach is nice but not ready yet using in secure environments.
Implementations:
(not available)



Concepts    Solutions    top  bottom

Solutions

SAS base code

SAS base code (new 9 functions)
With SAS 9 al lot of fucntionality has been added. Data-access is optional in macro without having coding a data-step.
The technical design should be adjusted for this.


SAS base code (old appraoch)
This solution was designed working with SAS Vv8. It will run also in higher SAS versions
Needed is a SAS-datastep an the usage of global macro variables.
The dataset is expected to have the fields:
Usage:
%xkeypsw( prj3prt.keys , Ora21 SAS00 , geekps);

Will create "ora21_u" and "sas00_u" with the corresponding fields extracted from the dataset "prj3prt.keys" with read password "geekps".
Thes macro-vars can be used in a libname (oracle) or SAS-connect or other SAS statement.

%xkeypsw( , Ora21 SAS00 , );

Will clean up "ora21_u" and "sas00_u"


Macro Code:
%macro Xkeypsw ( dsndef , appopt, readpw ) / STMT ;
    %local larg lind ;
    %let dsndef=%upcase(&dsndef) ; %let appopt=%upcase(&appopt) ;
    %let ldsndef=%length(&dsndef) ; %let lappopt=%length(&appopt) ; %let lreadpw=%length(&readpw) ;
   
    %if ( &ldsndef > 2 ) %then %do; /* define macro-variables */
   
    /* Defining the target sas-macrovars as global. The macrovar name is extended with _u */
    %local wrdh wrdo ;
    %let wrdh = 1 ;
    %let wrdo = %scan(&appopt,&wrdh) ;
    %do %while( %length(&wrdo) > 1 & &wrdh < 99 ) ;
    %global &wrdo._U ;
    %let wrdh = %eval(&wrdh + 1) ;
    %let wrdo = %scan(&appopt,&wrdh) ;
    %end;
   
    /* Putting the content into the sas-macrovars. The read-psw of the dataset is used at this moent */
    data work._NULL_ ;
        set &dsndef ( read=&readpw where = ( indexw("&appopt" , UPCASE(appldef) ) ) ) ;
        SELECT ;
        WHEN ( contyp =: 'SAS' ) DO ;
            call symput( TRIM(appldef)||'_U', TRIM(conopt)||' NOPOP' ) ;
            put ' - key SAS-connect ' appldef ' defined';
        END;
        WHEN ( contyp =: "ORA" ) DO ;
            call symput( TRIM(appldef)||'_U' ,TRIM(conopt) ) ;
            put ' - key Oracle-connect ' appldef ' defined';
        END;
        OTHERWISE Put ' Contype:' contyp ' not supported' ;
        END ;
    run;quit;
    %end;
   
    %else %do;
   
    /* Cleanup the content of the sas-macrovars. A symdel function is at sas 9 available */
    %local wrdh wrdo ;
    %let wrdh = 1 ;
    %let wrdo = %scan(&appopt,&wrdh) ;
        %do %while( %length(&wrdo) > 1 & &wrdh < 99 ) ;
        %let &wrdo._U = ; /* cleanup */
        %let wrdh = %eval(&wrdh + 1) ;
        %let wrdo = %scan(&appopt,&wrdh) ;
        %end;
    %end;
   
%MEND ;





SAS metadata

Will become an administrator task. (future)
Not worked out (see concepts)




Concepts    Solutions    top  bottom
Samples SAS     hardening SAS

© 2006 J.A.Karman (02 may 2012)