SAS 中的 ODBC 密码安全
我们希望从 SAS 代码中的 ODBC 连接字符串中删除硬编码密码,并防止任何密码出现在 SAS 日志文件中。
似乎有很多白皮书讨论如何解决这个问题,但我要么发现它们存在问题,要么无法让它们工作。
每次都提示用户输入密码并不是一个可行的选择。 此外,将密码存储在宏变量中也是一种可接受的方法,只要您有办法在打开 MACROGEN 和 SYMBOLGEN 选项的情况下禁止将密码打印到日志中。
尝试 1 - 编码 (链接到此处的白皮书)
proc pwencode in='mypassword' method=sasenc;
run;
给出:
{sasenc}ACFD24061BF77D7D5362EE7C2D00D08B
如果我用代码中的编码值替换明文密码,则 ODBC 直通语句运行正常。
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 的值。
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 密码碰巧在其他地方使用,这会提供一定的安全性,仅此而已。
尝试 2 - 使用 SYMGET(此处链接到白皮书)
问题是我根本无法让所描述的技术在 SAS 中发挥作用。我在 XP 上运行 SAS 9.2,尝试连接到 SQL Server 数据库。
%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;
我收到以下消息,表示登录失败:
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 调用中使用的少数函数之一,因此我不明白这怎么可能。
任何其他提示/建议/想法将不胜感激。
谢谢
编辑:如果有一种技术可以在打开options symbolgen Macrogen
的情况下执行此操作,那就特别好。
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.
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.
ATTEMPT 1 - ENCODING (link to whitepaper here)
proc pwencode in='mypassword' method=sasenc;
run;
gives:
{sasenc}ACFD24061BF77D7D5362EE7C2D00D08B
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;
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
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? EDIT: This provides some security if your ODBC password happens to be used elsewhere, that's about it though.
ATTEMPT 2 - USING SYMGET (link to whitepaper here)
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'.
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.
Thanks
EDIT: It would be especially good if there was a technique to do this that worked with options symbolgen macrogen
turned on.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
Rob,我们遇到了类似的问题,并提出了一种不同的方法,允许我们所有的团队成员运行相同的程序,而无需将我们的 ID/密码存储在程序中。它要求每个团队成员都有一个 SAS 可以访问的安全存储的文本文件(除所有者之外没有任何权限)。
以下是 ID/PW 文件内容的示例:
我们在 UNIX 服务器上运行,因此我们将各个 id/pw 文件锁定在我们的主目录中,这样就没有其他人可以访问它,在本例中它被命名为“ .netrc”。该线程末尾的宏应该存储在某个地方,那么程序将如下所示:
我尝试修改宏以在您的环境中工作并删除大量特定于我们系统的代码,但显然我没有这样做无法测试它以确保它有效。如果您遇到问题,请告诉我,我会尽力帮助解决。希望这有帮助。
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.
Here is an example of contents of an ID/PW file:
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:
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.
因此,我还联系了 SAS,了解他们对此类问题的建议,这是他们(一如既往的及时)回复。不幸的是,如果不禁用符号生成,他们就无法实现这一目标:
So I also contacted SAS to see what they recommend for this type of issue and this was their (timely as always) response. Unfortunately it looks like their is no way to achieve this without disabling symbolgen:
当 SYMGET 在 CONNECT 语句中不起作用时,请尝试使用
%SUPERQ
引用功能。它还解析宏变量,而不将其显示在日志中。when SYMGET does not work in the CONNECT statement, try the
%SUPERQ
quoting function. It also resolves the macro variable without surfacing it in the LOG.抱歉,无法写评论,但是
%superq
-Trick 确实有效。像这样使用它:其中有一个名为
_password
的宏变量。不幸的是,这不能封装到宏中,
因为这样
MPRINT
将再次记录您的密码。Sorry, can't write a comment, but the
%superq
-Trick really works. Use it like this:where you have a macro variable called
_password
.Unfortunately, this can't be wrapped into a macro like
because then
MPRINT
will log your password again.没有安全的方法来定义 ODBC 连接!
每个人都可以使用元数据 API 读取身份验证域、用户名并对密码进行编码。 Linux 管理员或root 用户可以访问任何文件系统,包括主目录及其password.sas 文件。
SAS 还提供 ChangePassPhrase 将存储的密码降级为较旧的 SAS PWENCODE 方法。如果您知道编码后的密码,则可以在线对其进行解码,例如 https://decrypt- password.appspot.com/sas-pwdecode/
有时登录凭据用于不同的服务。 FTP 或 MAIL 的用户名和密码也可用于 ssh。文件和元数据不是安全存储。
There is no secure method to define a ODBC connection!
Everybody can read auth domains, usernames and encode passwords by using the Metadata API. The linux admin or root user can access to any file system, including the home directories and it's password.sas files.
SAS also provide ChangePassPhrase to downgrade stored passwords to older SAS PWENCODE method. If you know the encoded password, than you can decode it for example online at https://decrypt-password.appspot.com/sas-pwdecode/
Sometime login credentials used for different services. A username and password for FTP or MAIL could also use for ssh. Files and metadata are not a secure store.