SAS 中的 ODBC 密码安全性 [英] ODBC Password Security in SAS
问题描述
我们希望从 SAS 代码中的 ODBC 连接字符串中删除硬编码密码，同时防止任何密码出现在 SAS 日志文件中.
We want to remove hardcoded passwords from ODBC connection strings in our SAS code, and also prevent any of the passwords from appearing in the SAS log files.
似乎有很多白皮书讨论如何解决这个问题，但我要么发现它们存在问题，要么无法让它们发挥作用.
There seems to be plenty of whitepapers discussing how to go about this but I either find problems with them, or can't get them working.
每次都提示用户输入密码不是一个可行的选择.此外，将密码存储在宏变量中是一种可接受的方法，只要您有办法在启用 MACROGEN 和 SYMBOLGEN 选项的情况下禁止将其打印到日志中.
Prompting the user each time for the PW is not a viable alternative. Also, storing the password in a macro variable is an acceptable approach, as long as you have a way to suppress it from printing to the log with MACROGEN and SYMBOLGEN options turned on.
尝试 1 - 编码(此处为白皮书链接)
proc pwencode in='mypassword' method=sasenc;
run;
给予:
{sasenc}ACFD24061BF77D7D5362EE7C2D00D08B
如果我将明文密码替换为代码中的编码值，则 ODBC 直通语句运行良好.
If I replace my plaintext password with the encoded value in my code then the ODBC passthrough statement runs fine.
proc sql noprint;
connect to odbc as remote (datasrc=cmg_report user=myuser password='{sasenc}68B279564BD2695538CDCDB301E8A357563480B0');
create table sqlo as
select *
from connection to remote
(
select top 1 * from application
)
;
disconnect from remote;
quit;
并且日志正确地掩盖了带有 XXXXXXX 的值.
And the log correctly masks out the values with XXXXXXXs.
961 proc sql noprint;
962 connect to odbc as remote (datasrc=cmg_report user=&user_cmg password=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX);
963 create table sqlo as
964 select *
965 from connection to remote
966 (
967 select top 1 * from application
968 )
969 ;
971 quit;
NOTE: Table WORK.SQLO created, with 1 rows and 29 columns.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.34 seconds
cpu time 0.01 seconds
上述方法的问题在于，如果有人可以访问代码，他们可以使用加密密码登录，而无需知道明文密码.因此，虽然它隐藏了实际密码，但它不提供安全性.对我来说似乎有点傻还是我错过了什么?如果您的 ODBC 密码碰巧在其他地方使用，这可以提供一些安全性，但仅此而已.
The problem with the above approach is that if someone has access to the code, they can login using the encrypted password, without needing to know the plain text password. So while it hides the actual password it doesn't provide security. Seems kind of silly to me or am I missing something? This provides some security if your ODBC password happens to be used elsewhere, that's about it though.
尝试 2 - 使用 SYMGET(链接在此处查看白皮书)
问题在于我根本无法让所描述的技术在 SAS 中工作.我在 XP 上运行 SAS 9.2，试图连接到 SQL Server 数据库.
The problem with this is that I simply can't get the technique described to work in SAS. I'm running SAS 9.2 on XP, trying to connect to an SQL Server DB.
%let my_password = password;
proc sql noprint;
connect to odbc (dsn=cmg_report uid=myuser pwd=symget('my_password'));
create table sqlo as
select *
from connection to remote
(
select top 1 * from application
)
;
quit;
我收到以下消息说登录失败:
I get the below message saying that the login failed:
1034 proc sql noprint;
1035 connect to odbc (dsn=cmg_report uid=myuser pwd=XXXXXX('my_password'));
ERROR: CLI error trying to establish connection: [Microsoft][SQL Server Native Client 10.0][SQL
Server]Login failed for user 'myuser'.
看起来它正在尝试使用symget"作为实际密码(因为它已在日志中被屏蔽).对此白皮书有一些回应说将 symget 包装在 %sysfunc 调用中，但 symget() 函数是 SAS 在 %sysfunc 调用中不允许使用的少数函数之一，所以我看不出这怎么可能.
It looks like it is trying to use "symget" as the actual password (as it has been masked out in the log). There are some responses to this whitepaper saying to wrap the symget in a %sysfunc call but the symget() function is one of the few functions that SAS does not allow within a %sysfunc call so I don't see how that could be possible.
任何其他提示/建议/想法将不胜感激.
Any other tips/suggestions/ideas would be much appreciated.
谢谢
如果有一种技术可以在打开 options symbolgen macrogen 的情况下执行此操作，那就太好了.
It would be especially good if there was a technique to do this that worked with options symbolgen macrogen turned on.
推荐答案
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;
这篇关于SAS 中的 ODBC 密码安全性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
