BookmarkSubscribeRSS Feed
hari1109
Fluorite | Level 6

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.

20 REPLIES 20
Tom
Super User Tom
Super User

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.

hari1109
Fluorite | Level 6

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.

Tom
Super User Tom
Super User

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.

hari1109
Fluorite | Level 6

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.

Tom
Super User Tom
Super User

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?

hari1109
Fluorite | Level 6

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.

SASKiwi
PROC Star

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.

FriedEgg
SAS Employee

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.

hari1109
Fluorite | Level 6

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.

Tom
Super User Tom
Super User

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.

SASKiwi
PROC Star

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.

vrmkool
Calcite | Level 5

This paper describes about masking your password.

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

FriedEgg
SAS Employee

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;

hari1109
Fluorite | Level 6

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 20 replies
  • 10014 views
  • 6 likes
  • 7 in conversation