topic Re: Problem with SYMGET in the CONNECT Statement in SAS Programming

Problem with SYMGET in the CONNECT Statement

hari1109 — Tue, 27 Sep 2011 22:08:42 GMT

Hi All,

I have an issue with Symget fucntion i am using the following code to mask my password from displaying in log. I have attached both code and error log to this post. please suggest a way out of this problem. Thank you very much.

Re: Problem with SYMGET in the CONNECT Statement

Tom — Tue, 27 Sep 2011 22:21:58 GMT

Remove the quotes from around the name of the symbol. When using %SYSFUNC() (and macro functions in general) you do not need to put quotes around strings. To the macro processor everything is a string.

Re: Problem with SYMGET in the CONNECT Statement

hari1109 — Wed, 28 Sep 2011 14:32:14 GMT

Hi Tom,

Thank you for the reply , i tried without quotes as you mentioned but it is not working and i am getting the same error as i have mentioned in the error_log file. quoted were removed for all strings and am geeting the same error.

Re: Problem with SYMGET in the CONNECT Statement

Tom — Wed, 28 Sep 2011 16:05:56 GMT

You do not need to use SYMGET. Just reference the macro variable.

Replace:

WHERE uid eq %sysfunc(symget('abc_username')) AND pwd eq %sysfunc(symget('abc_password'))

with

WHERE uid eq "&abc_username" AND pwd eq "&abc_password"

Note the quotes so that the compiler will see that the terms on the right hand side of the EQ operator are strings and not variable names or constants.

Symget is only useful when running in a data step and you want to get the current value for a macro variable that you might have updated since the data step was compiled by using a CALL SYMPUT statement.

Re: Problem with SYMGET in the CONNECT Statement

vrmkool — Wed, 28 Sep 2011 16:35:37 GMT

This paper describes about masking your password.

support.sas.com/resources/papers/proceedings09/013-2009.pdf

Re: Problem with SYMGET in the CONNECT Statement

hari1109 — Wed, 28 Sep 2011 18:58:50 GMT

Hi Tom,

Thank you for the suggestions yeah if i mean if i replace the where statement as you mentioned the macro variables will get resolved but when you use some options like symbolgen mprint and mlogic in your code these password values will get exspodes in the sas log and that is i why i am trying to mask those variables using SYMGET function. i came across this in the paper mentioned below

http://support.sas.com/resources/papers/proceedings09/013-2009.pdf

Thank you.

Re: Problem with SYMGET in the CONNECT Statement

Tom — Wed, 28 Sep 2011 19:20:05 GMT

The example in the program is calling the symget function directly in the select statement of the SQL. Does not need to wrap it in a SYSFUNC call. Did you try just removing the %SYSFUNC() wrapper around the SYMGET function calls so that it looks like in the paper you found?

Re: Problem with SYMGET in the CONNECT Statement

hari1109 — Wed, 28 Sep 2011 19:42:09 GMT

Yes Tom i tried without the %SYSFUNC fucntion but it didn't work. The reason why i used is i looked into this link

http://www.sascommunity.org/wiki/Talk:Secret_Sequel:_Keeping_Your_Password_Away_from_the_LOG and i thought it would work.

Thank you.

Problem with SYMGET in the CONNECT Statement

FriedEgg — Wed, 28 Sep 2011 20:02:51 GMT

What are you connecting to when running the query? For example, if you are connecting to oracle the query would not work becuase the character strings you are trying to pass the where clause are not encased in single quotes. Try doing this:

%let user=user;

%let pass=pass;

data _null_;

call symput('user',"'"||"&user"||"'");

call symput('pass',"'"||"&pass"||"'");

run;

proc sql;

connect to oracle user=&user pass=&pass path=mypath;

create table want as select * from connection to oracle

( select *

from mytable

where uid=&user and pwd=&pass );

disconnect from oracle;

quit;

Re: Problem with SYMGET in the CONNECT Statement

hari1109 — Wed, 28 Sep 2011 20:14:48 GMT

Hi FriedEgg,

Thank you I can use what you have told but i am trying to hide the password away from the SAS log. For example if is use the above code with options such as Symbolgen, mprint and mlogic then what ever the password i am using in the connect statement will get exsposed. So to avoid that i am using Symget fuction which i have seen in a document

http://support.sas.com/resources/papers/proceedings09/013-2009.pdf

THank you.

Problem with SYMGET in the CONNECT Statement

SASJedi — Wed, 28 Sep 2011 20:48:33 GMT

When SYMGET is executing on the remote host and the macro variable was created in the local SAS session, the macro variable you are attempting to retrieve probably does not exist in the remote host symbol table.

Check out %SYSLPUT and %SYSRPUT. These statments allow passing macro values between local and remote host, and may help resolve your problem.

Re: Problem with SYMGET in the CONNECT Statement

FriedEgg — Wed, 28 Sep 2011 20:50:28 GMT

Sorry I forgot that one piece. During the call of &user or &pass you can replace with %sysfunc(symget(user)) etc. However all methods will still show masked data in the log if certain suboptions of sastrace are used.

options mlogic mprint macrogen symbolgen;

data _null_;

input (uid pwd) ($);

call symput('user',"'"||uid||"'");

call symput('pass',"'"||pwd||"'");

cards;

a1 abcde

;

run;

options nosymbolgen;

proc sql;

connect to oracle user=&user /* or %sysfunc(symget(user)) */ pass=&pass /* or %sysfunc(symget(pass)) */ path=mypath;

create table want as select * from connection to oracle

( select *

from mytable

where uid=&user /* or %sysfunc(symget(user)) */ and pwd=&pass /* or %sysfunc(symget(pass)) */);

disconnect from oracle;

quit;

options symbolgen;

I do believe that any method of masking will be foiled by utilizing the sastrace option for any of it's routines that return submitted SQL statements.

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a000433982.htm

Problem with SYMGET in the CONNECT Statement

FriedEgg — Wed, 28 Sep 2011 20:59:15 GMT

The %syslput and %sysrput are for sharing variables between mp-connect processes. Which I do not think the op is using?

Problem with SYMGET in the CONNECT Statement

SASKiwi — Wed, 28 Sep 2011 21:20:28 GMT

A better way to hide passwords is to encrypt them like so:

proc pwencode in='My password';

run ;

This produces an encrypted string like this: {sas001}TXkgcGFzc3dvcmQ=

Which you then put into your macro variable or supply directly in a database connect statement:

connect to oracle (user = myuser password = '{sas001}TXkgcGFzc3dvcmQ=' path='Mypath' );

The advantage is that is doesn't matter if the password appears masked or not, it can't be easily unencrypted.

Problem with SYMGET in the CONNECT Statement

FriedEgg — Wed, 28 Sep 2011 21:38:57 GMT

This suggesttion will also work well, however if you are going to use it inside a select statement to dbs like oracle you will need another one where the in="'My password'" Also I am pretty sure that certain conditions of the sastrace option will still show the unmasked password. This is just an assumption though.

Problem with SYMGET in the CONNECT Statement

hari1109 — Wed, 28 Sep 2011 21:52:46 GMT

Hi SASkiwi,

This could be good for one but in my case there are mutiple people who have to connect to the database using pass through facility. That is why am looking for other methods to hide the password from not appearing in the sas log. Thank you.

Re: Problem with SYMGET in the CONNECT Statement

Tom — Wed, 28 Sep 2011 21:54:43 GMT

We tried using this, but it does not really solve the problem.

Anyone with access to SAS can use that "encrypted" password to access the database.

Re: Problem with SYMGET in the CONNECT Statement

Tom — Wed, 28 Sep 2011 22:03:16 GMT

To solve this issue I usually encapsulate the step that uses the password inside of a macro.

That way you can turn off SYMBOLGEN, MLOGIC, MPRINT, MACROGEN while you are expanding the password.

You can store the passwords in a dataset or file and restrict access to the file using your operating system.

Something like this:

%macro oracle(passwordfile);

%local userid password optstore;

%let optstore = %sysfunc(getoption(symbolgen)) ;

options nosymbolgen ;

data _null_;

infile "&passwordfile" ;

input userid &$20. password &$20. ;

call symputx('user',userid);

call symputx('password',password);

run;

proc sql ;

connect to oracle (user="&user" password="&password" path="mydatabase");

options &optstore;

%mend oracle;

Problem with SYMGET in the CONNECT Statement

FriedEgg — Wed, 28 Sep 2011 22:06:56 GMT

At the OS level just ensure that the password file is only readable by the user or group of users who are expected to utilize it, and you have as close to a secure facility as I know how to achieve for a connect statement.

Problem with SYMGET in the CONNECT Statement

SASKiwi — Thu, 29 Sep 2011 21:06:23 GMT

Agreed Tom, but it does block any use of that password in other non-SAS database tools like TOAD, Oracle client etc. By using encrypted passwords in combination with other security measures, as discussed, you can make it hard enough for all but the most expert of users to crack.