Help using Base SAS procedures

protecting the password in proc sql

Reply
N/A
Posts: 0

protecting the password in proc sql

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
N/A
Posts: 0

Re: protecting the password in proc sql

Posted in reply to deleted_user
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?
Contributor
Posts: 35

Re: protecting the password in proc sql

Posted in reply to deleted_user
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
Occasional Contributor
Posts: 19

Re: protecting the password in proc sql

Posted in reply to deleted_user
> 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.
Frequent Contributor
Frequent Contributor
Posts: 77

Re: protecting the password in proc sql

Posted in reply to deleted_user
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
N/A
Posts: 0

Re: protecting the password in proc sql

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
Ask a Question
Discussion stats
  • 5 replies
  • 864 views
  • 0 likes
  • 4 in conversation