/* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -  
Macro's usage Note: 

Instead of coding user/password 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 at that user/password  positon in the code. 
   -->   libname dmorp102 oracle PATH="PMIB" SCHEMA=P_DRS defer=yes %xkeypsw(PMIB) buffsize=12000 dbindex=yes ; 


Notes:
 
 - do not use a ; (semicolon) after the macro-call. 
 - The pwencode hash password can be copied from the source code and is not safe.
   That hash is reversable to a readable password. That logic is hidden by SAS institute.
   The hash of SAS will not work in any DBMS client, still it can connect. There must a be a reversable routine.
 - The default location should be the persons local home-directory (~ at Unix, mydocuments at Windows).

   Using the vanilla installation of SAS this is a failure. 
   The default locations is left to the sasroot of the configuration location.
   That location should be read-only.
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */

/* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */
/* * * db_get_password                                                                     * * * */ 
/* * * use DatabaseAuth with real valid local name ;                                       * * * */ 
/* * * _PWencode macrovar will contain the password hased valued                           * * * */ 
/* * * For reovery purposes the old value is stored in the operating system user location  * * * */ 


%macro db_get_password( metauser= ) ;
  %global DBrc_getpw _pwencode ;  
  %let DBrc_getpw = 0 ;  
  %let metaserver_sv = %sysfunc( getoption( metaserver ) );
  %let metauser_sv = %sysfunc( getoption( metauser ) );
  %if ( %length( &metauser_sv ) = 0 ) %then %let metauser_sv = &sysuserid ;
  %if ( %length( &metauser ) = 0 ) %then %let metauser = &metauser_sv ;
  data _null_;
    file print ;
    uri_user = "omsobj:Login?@UserID='&metauser'";
    length uri_login _pwencode login_name uri_domain domain_name $60;
    call missing( uri_login, login_name, uri_domain, domain_name );
    nlogins = metadata_getnobj( uri_user, 1, uri_login );
    rcpass = -2 ;
    do n = 1 to nlogins;
      rc = metadata_getnobj( uri_user, n, uri_login );
      rc = metadata_getnasn( uri_login, "Domain", 1, uri_domain );
      rc = metadata_getattr( uri_domain, "Name", domain_name );
      if domain_name = "DatabaseAuth" then do;
        rcpass = metadata_getattr( uri_login, "Password", _pwencode );
      end;
    end;
    if ( nlogins < 0 ) then do;
        Put "GETD: &syshostname Connection to &metauser code:" nlogins " Autorisation definition failed" ;     
    end;
    else do;
      if ( rcpass = 0 ) Then do;
        put "GETD: &syshostname Password succesfully retreived &metauser:" _pwencode ; 
        call symputx( "_pwencode", _pwencode, 'G' ) ;   
        %let TDrc_getpw = 1  ;  
      end;
      else do;
        Put "GETD: &syshostname Password is not able tot retreive DatabaseAuth is missing for user: &metauser"  ; 
      end;
    end;
  run;
%mend;


/* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -  
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */

/* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */
/* * * db_get_password                                                                     * * * */ 
/* * * use DatabaseAuth with real valid local name ;                                       * * * */ 
/* * * _PWencode macrovar will contain the password hased valued                           * * * */ 
/* * * For reovery purposes the old value is stored in the operating system user location  * * * */ 


%macro db_set_password(db_new_PASSWORD=,metauser=&sysuserid) ;
%global _pwencode TDrc_setpw;
%let DBrc_setpw=0;                      %* local macro status code ;

  filename oscmdX pipe "rm TD_encoded.old ; mv TD_encoded TD_encoded.old ";
data _null_ ; infile  oscmdX ; input; run;
  filename DBencd "DB_encoded";
proc pwencode in="&db_new_PASSWORD" OUT=DBencd; run;
  filename oscmdX pipe  "chmod 0700 DB_encoded ";
data _null_ ; infile oscmdX; input; run;

data _null_;
   File print ;
   uri_user="omsobj:Login?@UserID='&metauser'";
   length uri_login login_name uri_domain domain_name $60;
   call missing(uri_login,login_name,uri_domain,domain_name);
   nlogins=metadata_getnobj(uri_user,1,uri_login);
   rcpass = -2 ;
   do n=1 to nlogins;
      rc=metadata_getnobj(uri_user,n,uri_login);
      rc=metadata_getnasn(uri_login,"Domain",1,uri_domain);
      rc=metadata_getattr(uri_domain,"Name",domain_name);
      if domain_name="DatabaseAuth" then do;                         
         rcpass=metadata_setattr(uri_login,"Password","&_pwencode");
    end;
   end;
   if ( nlogins < 0 ) then do;
        Put "SETD: &syshostname Connection to &metauser code:" nlogins " Autorisation definition failed" ;     
   end;
   else do;
     if ( rcpass = 0 ) Then do;
        Put "SETD: &syshostname DB Password succefully changed for &metauser"  ; 
        call symputx("DBrc_setpw",1,'G') ;   
     end;
     else do;
        Put "SETD: &syshostname Password is not able to change. DatabaseAuth is missing for user: &metauser"  ; 
     end;
   end;
run;

%mend;

/* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -  
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */

/* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */
/* * * db_sync_password                                                                    * * * */ 
/* * * use DatabaseAuth with real valid local name ;                                       * * * */ 
/* * * _PWencode macrovar will contain the password hased valued                           * * * */ 
/* * * For reovery purposes the old value is stored in the operating system user location  * * * */ 
/* * * A list of metdataservers to synchronise is expected.                                * * * */ 
/* * * The userid is expected to be te same as the on one the operatin system              * * * */ 
/* * * That password to be given for accesssing the othere metadataserver (rmt_password)   * * * */ 

%macro db_sync_password( metauser=, meta_PASSWORD= ) ;
  %local meta_serverlst;
  %global DBrc_synpw DBrc_newpw _pwencode ;  
  %let TDrc_synpw=0  ;   

  %let metaserver_sv=%sysfunc(getoption(metaserver)) ;
  %let metauser_sv=%sysfunc(getoption(metauser)) ;
  %if (%length(&metauser_sv) =0) 
    %then %let metauser=&sysuserid ;
    %else %let metauser=&metauser_sv ;

  %if ( "&sysuserid" ~= "&metauser" )  %then %do;
    %put SYNC ERROR: Operating system user not equal to SAS metadataserver user-id ;
    %let TDrc_synpw=-4 ;
  %end;

  %if ( %length(&rmt_password) = 0 ) %then %do;
    %put SYNC NOTE: Other possible Metdataservers will not be synchronised ;
    %let TDrc_synpw=-1 ;
  %end;
  %else %do;
    options metauser="&metauser" metapass="&rmt_PASSWORD" ;
  %end;


  %if (%length(&_pwencode) =0 & &DBrc_synpw =0 ) %then %do;
    %td_get_password()
    %if (%length(&_pwencode) =0 & &DBrc_synpw =0 ) %then %do;
      %put SYNC ERROR: Retrieved DB password from:&metaserver_sv  is empty? ;
      %let TDrc_synpw=-4 ;
    %end ;
  %end ;

  %if ( &DBrc_synpw =0 ) %then %do;
    %let metaserver_list=%srv_metalist ;

    %let itmsrv=1;
    %let metaserver=%scan(&metaserver_list,&itmsrv,%nrstr( ) );
    %if ( "&metaserver" = "&metaserver_sv") %then %do;
      %let itmsrv=%eval(&itmsrv+1);
      %let metaserver=%scan(&metaserver_list,&itmsrv,%nrstr( ) );
    %end;

    %do %until ( %length(&metaserver) <2 | &itmsrv >99) ;  
    options metaserver="&metaserver";
    data _null_;
      file print ;
      uri_user="omsobj:Login?@UserID='&metauser'";
      length uri_login login_name uri_domain domain_name $60;
      call missing(uri_login,login_name,uri_domain,domain_name);
      nlogins=metadata_getnobj(uri_user,1,uri_login);
      rcpass = -2 ;
      do n=1 to nlogins;
        rc=metadata_getnobj(uri_user,n,uri_login);
        rc=metadata_getnasn(uri_login,"Domain",1,uri_domain);
        rc=metadata_getattr(uri_domain,"Name",domain_name);
        if domain_name="DatabaseAuth" then do;
          rcpass=metadata_setattr(uri_login,"Password","&_pwencode");
        end;
      end;
      if ( nlogins < 0 ) then do;
        put "SYNC Warning: connection to &metaserver_sv -r &metaserver -&metauser- access failed %sysfunc( putn( %sysfunc( datetime() ) , datetime. ) )" ;     
      end;
      else do;
        if ( rcpass = 0 ) Then do;
          put "SYNC password for &metaserver_sv succesfull synchronised with &metaserver -&metauser- %sysfunc( putn( %sysfunc( datetime() ) , datetime. ) ) "  ; 
          call symputx("TDrc_synpw",rcpass,'G') ;   
        end;
        else do;
          put "SYNC Warning: password for &metaserver_sv failed to synchronise. The login to DatabaseAuth is missing: &metaserver &metauser %sysfunc( putn( %sysfunc( datetime() ) , datetime. ) )"  ; 
        end;
      end;
    run;

    %let itmsrv=%eval(&itmsrv+1);
    %let metaserver=%scan(&metaserver_list,&itmsrv,%nrstr( ) );
    %if ( "&metaserver" = "&metaserver_sv") %then %do;
      %let itmsrv=%eval(&itmsrv+1);
      %let metaserver=%scan(&metaserver_list,&itmsrv,%nrstr( ) );
      %end;
    %end;
    options metaserver="&metaserver_sv";
  %end;
%mend;