I have a set of macros wrapped around username and password management, including: %getID and %getPassword(user_id).
I also have macro and include sets for database access.
[pre]
%macro database;
datasource='a_place' provider=sqloledb user='user_id' password='encoded_password' prompt=no
%mend;
[/pre]
The include file "database.sas" contains:
[pre]
libname db_lib oledb %database;
[/pre]
This makes for a single point of maintenance for the database library definition.
It can be used as a SAS LIBNAME through the include file, or within a proc sql pass-through
[pre]
proc sql noprint;
connect to oledb as db (%database);
create table selected as
select * from connection to db
( select ... );
disconnect from db;
quit;
[/pre]
Now, if I wanted to change the %database macro to use the "%getPWD(user)" macro, it cannot inject a SAS dataset to retrieve the password, or this thing fails.
The relevent example here is
[pre]
filename pwfile 'external-filename';
options symbolgen;
data _null_;
infile pwfile obs=1 length=l;
input @;
input @1 line $varying1024. l;
call symput('dbpass',substr(line,1,l));
run;
libname x odbc dsn=SQLServer user=testuser password="&dbpass";
[/pre]
This cannot be used for ' password="%getPassword(user_id)" '
and neither can
[pre]
%let password=%getPassword(user_id);
%if 1=1 %then
... password="&password" ... ;
[/pre]
inside "connect to oledb as db (%database);"
The desire is to maintain maximum security and portability of the code and minimize the maintenance of users and their passwords.
We have AD, and the SAS MetaData Server, whose user's are refreshed every night from AD. But, AD does not, and will not for some time to come, yet, contain application user ID's -- like, for instance, "Oracle". So, if I create the library in the MetaData Server, I have to hard code in the user and password into the libary definition, which defeats the security and maintainability issues.
So, for unattended batch processing, I have created the macros and language structures. Of course, now there is the chore of having to set/change users and passwords manually in two places: 1) AD, 2) for my group's SAS stuff. But since a user has to update their password at least every 30 days, doing this twice isn't so bad, and application level passwords can likewise be easily maintained.
Message was edited by: Chuck