topic protecting the password in proc sql in SAS Procedures

protecting the password in proc sql

deleted_user — Thu, 05 Jun 2008 11:04:53 GMT

Hi,
While using the proc sql statement to connect the sas to oracle,it is needed to enter the user id and password.My question is i don't want to display the password in the code, is there any way to do that.

eg: proc sql;
connect to oracle (user= password= path=);
quit;

Thanks in advance

Re: protecting the password in proc sql

deleted_user — Thu, 05 Jun 2008 13:34:42 GMT

I am new in SAS
what does this statement do?

connect to oracle (user= password= path=);

Does it ask for the prompt to enter the user name and password?

Re: protecting the password in proc sql

rab24 — Thu, 05 Jun 2008 14:39:42 GMT

Are you using EG4? You can make your password a macro variable and use the parameter manager to prompt you for a password everytime you run the code. This will result in the password showing up in your log, but that is easier to clear than your code.

Rkmalkani, it is a code to connect to a secured server. You need to enter a username and password to the code before it will work. Keeping it blank does not make it prompt for me. Message was edited by: rab24

Re: protecting the password in proc sql

HoustonGSC — Thu, 05 Jun 2008 15:16:48 GMT

> Hi,
> While using the proc sql statement to connect the
> sas to oracle,it is needed to enter the user id and
> password.My question is i don't want to display the
> password in the code, is there any way to do that.
>
> eg: proc sql;
> connect to oracle (user= password= path=);
> quit;
>
> Thanks in advance

Is the PWENCODE proc available to you to encrypt your password and then it can be read in by your PROC SQL code?

Best Regards,
Gil.

Re: protecting the password in proc sql

LAP — Thu, 05 Jun 2008 15:28:28 GMT

There are two different methods that come to mind

1) Define a macro variable with %let statements in an external file and %include the file in my batch code. (security through obscurity)

IE In an external file...

%let pw = mypassword;
%let uid = myuserid;

In batch code
proc sql;
connect to oracle (user=&uid password=&pw path=);
quit;

2) Use Proc PWENCODE to encode the password and substitute the encoded password into the procedure. See the base SAS procedures documentation.
Basically, you can use proc pwencode to write an encoded password to an external file. You can then use a data _null_ step to read the password and create a macro variable the you use in a similar way in method 1.

Linda

Re: protecting the password in proc sql

deleted_user — Mon, 02 Mar 2009 15:34:29 GMT

I collected the encoded pw using PROC PWENCODE but hoped to hide my password from the stored log by using the NOSOURCE option.
"Lazy" is not clever in SAS programming.[pre]
option nosource ; proc pwencode in='my password' ; run; option source ;
[/pre]
still revealed the procedure syntax.

It seems like the NOSOURCE option only takes affect after the line in which it is found, and not immediately after its statement. Here is a log snippet showing the effect of puting all the code on one line like the above [pre]
454 option nosource ;
454! proc pwencode in='my password' ; run;

{sas001}bXkgcGFzc3dvcmQ=

NOTE: PROCEDURE PWENCODE used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

454! o

454! ption source ;
[/pre]

When the option NOSOURCE is on a separate line, the syntax of the following lines are not displayed (until the line with option SOURCE).

I won't make that mistake again 😉

PeterC