Few things cause a database administrator to lose more sleep than coming across this within a SAS program:
libname ora10 oracle
1. Use the AUTHDOMAIN= option to access SAS libraries
The AUTHDOMAIN option allows you to delegate the authentication by allowing SAS to "look up" credentials as needed using the SAS metadata environment. For each domain that has a unique set of credentials, your SAS administrator can create an "auth domain" in metadata and associate it with a database server (or other resource). Every SAS/ACCESS database engine supports AUTHDOMAIN=, in LIBNAME statements as well as in PROC SQL CONNECT statements.
With AUTHDOMAIN, the above LIBNAME example becomes:
libname ora10 oracle
Bonus: AUTHDOMAIN works for other resources too, such as FTP connections.
2. Use the META engine or INFOMAPS engine to access data sources
The META engine provides a layer of indirection in front of a SAS library, so that not only do you not need credentials to access it, you don't even need to know the details of how the data tables are stored. A LIBNAME statement can be as simple as:
libname MYLIB Library="My SAS Library";
Sometimes you might see an example that looks like this:
libname MYLIB Library="My SAS Library"
metaserv="host.company.com" /* don't want this */
metaport=8561 /* don't want this */
user="userid" /* don't want this */
pw="mySecret"; /* don't want this */
The INFOMAPS engine provides an administered view of data, further abstracted from the physical structure of the data tables. Check out my previous post for a detailed example of programming with Information Maps.
3. Use SAS Token Authentication
SAS Token Authentication uses your established SAS metadata connection to generate and validate single-use tokens for every other SAS-related resource that you might need access to. To put this another way, once the SAS metadata server knows who you are, it "vouches for you" and facilitates connections to anything else you might need, including SAS workspace servers, OLAP servers, database servers (using AUTHDOMAIN) and more.
A big advantage of SAS Token Authentication is that you don't actually need a host account for all of the resources that you might connect to. This cuts down on the sys admin tasks required to get a group of users up and running. It's a best-practice alternative to using group host accounts on a SAS workspace server; configure the SAS workspace server to use SAS token authentication instead.
4. Use Integrated Windows Authentication
The best way to hide passwords? Don't have them in the first place. That's what many customers do when they implement fingerprint readers or retina scanners in their corporate workstations. The users of these workstations wouldn't know how to supply a password if you asked for one.
Even if your users need a password to log in to your workstation, you can use Integrated Windows Authentication to prevent them from seeing another password challenge as they connect to their SAS environment.
SAS Token Authentication and Integrated Windows Authentication are examples of authentication mechanisms that SAS 9.2 supports. Using these authentication mechanisms can reduce the "authentication friction" that results when your SAS applications must hop among different protected resources that would traditionally require a user ID and password to access.
5. If you must, use PROC PWENCODE to obscure passwords
Sometimes, despite your best efforts, you cannot avoid the odd password in your programs. For example, if you've got to access password-protected SAS data sets, you need to specify the password. But there is no need to have the clear-text password appear in your code. You can use the PWENCODE procedure to encode the password so that prying eyes cannot guess at it. For example:
proc pwencode in="ItzaSecret"
18 proc pwencode in=XXXXXXXXXXXX
NOTE: PROCEDURE PWENCODE used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds