SAS中的ODBC密码安全性

Rob，我们遇到了一个类似的问题，并提出了一种不同的方法，该方法允许我们所有的团队成员在没有ID/密码的情况下运行同一程序存储在程序中.它要求每个团队成员都有一个安全存储的文本文件(SAS不能访问，除了所有者以外没有其他权限).

Rob, we ran into a similar issue and came up with a different method that allows all of our team members to run the same program without having our id/passwords stored in the programs. It requires that each team member have a text file stored safely (no permissions except for owner) that SAS can access.

以下是ID/PW文件内容的示例:

Here is an example of contents of an ID/PW file:

machine odbc login XX_odbc_id_XX password XXodbc_pw_XX
machine oracle login XX_oracle_id_XX password XX_oracle_pw_XX

我们在UNIX服务器上运行，因此我们将锁定的个人id/pw文件存储在我们的主目录中，因此没有其他人可以访问它，在这种情况下，该文件名为".netrc".该线程末尾的宏应存储在某个位置，然后程序将如下所示:

We operate on a UNIX server, so we store our indivual id/pw files locked up in our home directory so no one else can access it, in this case it is named ".netrc". The macros at the end of this thread should be stored somewhere, then the program would look like the following:

%let id_pw_text_file = ~/.netrc;

%ODBC_Acct;

proc sql;
   %ODBC_Connect
   create table sqlo as
      select * from connection to odbc
      (
          /*  [ Insert ODBC query here ]  */ 
      );
   %ODBC_Disconnect
   quit;
run;

我试图修改宏以使其在您的环境中工作，并删除了许多特定于我们系统的代码，但是显然我无法对其进行测试以确保其正常工作.如果您有问题，请告诉我，我们将尽力解决.希望这会有所帮助.

I tried to revise the macros to work in your environment and to remove a lot of code specific to our systems, but obviously I wasn't able to test it to make sure it works. Let me know if you have an issue and I'll try to help fix it. Hope this helps.

/*********************************************************************
*  Name:  ODBC_Acct                                                  *
*  Desc:  Set global macro vars containing a users ODBC username     *
*         and password. Retrieves this information from a users      *
*         specific ID/PW file.                                       *
*********************************************************************/
%macro ODBC_Acct( mprint );
   %local __mprint __symbolgen __mlogic;
   %if ( %length( &mprint ) = 0 ) %then %let mprint = NO;
   %if ( %upcase( &mprint ) = NO ) %then %do;
      %let __mprint = %sysfunc( getoption( mprint ));
      %let __symbolgen = %sysfunc( getoption( symbolgen ));
      %let __mlogic = %sysfunc( getoption( mlogic ));
      options nomprint nosymbolgen nomlogic;
   %end;
   %global  odbc_user  odbc_pw;
   %Get_ID_PW( &id_pw_text_file , odbc , odbc_user , odbc_pw )
   %if ( %upcase(&__mprint) ne NOMPRINT ) %then %do;
      options &__mprint &__symbolgen &__mlogic;
   %end;
%mend;

/*********************************************************************
*  Name:  ODBC_Connect, ODBC_Disconnect                              *
*  Desc:  Returns SAS/Access connect or disconnect statements        *
*         for accessing ODBC.                                        *
*********************************************************************/
%macro ODBC_Connect( mprint=no );
   %local __mprint __symbolgen;
   %if ( %upcase(&mprint) = NO ) %then %do;
      %let __mprint = %sysfunc( getoption( mprint ));
      %let __symbolgen = %sysfunc( getoption( symbolgen ));
      options nomprint nosymbolgen;
   %end;
   connect to odbc as remote (
      datasrc=cmg_report
          user = "&odbc_user"
      password = "&odbc_pw"
      );
   %if ( %upcase(&__mprint) ne NOMPRINT ) %then %do;
      options &__mprint &__symbolgen;
   %end;
%mend;
%macro ODBC_Disconnect;
   disconnect from odbc;
%mend;

/*******************************************************************************
*  Name:  GetID_PW                                                             *
*  Desc:  Get loginid and password from a secured file                         *
*------------------------------------------------------------------------------*
*  Arguments:                                                                  *
*    1st   Required. Source file containing IDs and passwords.                 *
*    2nd   Required. Host id.                                                  *
*    3rd   Required. Specify the macro variable to put the loginid.            *
*    4th   Required. Specify the macro variable to put the password.           *
*------------------------------------------------------------------------------*
*******************************************************************************/
%macro Get_ID_PW( source , rhost , usrvar , pw_var );
   %let source_file = &source
   %if ( %sysfunc( fileexist( &source_file ) ) ) %then %do;
      %let rc  = %sysfunc( filename( dummy , &source_file ) );
      %let fid = %sysfunc( fopen( &dummy ) );
      %do %while( %sysfunc( fread( &fid ) ) = 0 );
         %let rc = %sysfunc( fget( &fid , inrec , 500 ) );
         %let machine = %scan( &inrec , 2 , %str( ) );
         %if ( %upcase( &machine ) = %upcase( &rhost ) ) %then %do;
            %let &usrvar = %scan( &inrec , 4 , %str( ) );
            %let &pw_var = %scan( &inrec , 6 , %str( ) );
            %goto Break;
         %end;
      %end;
      %Break: %*;
      %let rc = %sysfunc( fclose( &fid ) );
      %let rc = %sysfunc( filename( dummy ) );
   %end;
   %else %do;
       %put ::: ID/PW file "&source_file" not found;
   %end;
%mend;