Leanne Tang has learned a lot about encoding in her work of providing relevant user data and securing the database from accidental changes. There are hundreds of SAS users at the National Agricultural Statistics Service. Tang didn’t want to create thousands of accounts and passwords to allow those users to access relevant data.
Tang presented two back-to-back Coder’s Corner presentations at the SouthEast SAS Users Group conference (SESUG). According to Tang, her presentations are more about a problem-solving strategy and less about SAS code. The first presentation provided a simple step for identifying users.
“I’ve got a database. I’ve got a SAS program,” said Tang. “I need to connect from my SAS program to the database. In the traditional way, you would create an account for every user who needs to access the database.” Her problem is the hundreds or users who access multiple databases.
One thing that Tang needed to know is what kind of database she has. According to Tang, hers is an analytical database, which is like a data warehouse. She doesn’t need to keep track of the transactions, or login every user who hits the database because it is more of an information exchange. “It’s designed to store the business intelligence information; it’s designed to be used with analytical applications,” she said. “It’s more for analysis.”
Instead of creating and maintaining thousands of accounts and passwords, Tang decided to create one password. So she did three things:
- Requested to create an application ID and password. The application ID, embedded in her program, is used by every user. The users are not given the application ID and password. Tang says that users don’t need to know the password – they don’t care what database she is hitting. They only want to get the data that is appropriate for their analysis.
- Encoded the password and saved it. She used the PROC PWENCODE to encode the application ID and password.
- Updated the SAS program to use the application ID and password.
Tang gave three examples of encoding the application ID and password. In example 1, she used the method 00sas002. In the log file, the tag lets SAS server know there is an encoded password and the method that she used to encode the password.
Example 2 is to output to a file. The password doesn’t show in the log because she used the OUT option to put the password in a file where no one can see it. In this example, she doesn’t put in an option of method. “sas002 is the default, so if you don’t specify that will mean that you will get sas002,” explained Tang. “
(She used the sas001 method in example 3. sas001 requires a special license that Tang doesn’t have, so she couldn’t run it or show the code.)
Using the encoded password
“The most straight forward way to use it is cut and paste, put in the LIB Name and you’re good to go,” says Tang. But she used the OUT option that saves the password to a file. To retrieve the password, she said all that she needs to do is submit a file name statement and a DATA step, read in the file and pull in the macro variable key (MYKEYW).
Why encode?
According to Tang, encoding is the simpler way to go, but it really only disguises the data. She explained that encoding is translating one set of characters to another set of characters, a form of lookup table. Encryption, the transformation of data from one form to another, is for securing the data.
By encoding the application ID and password, she has eliminated the need for individual user accounts in the database and the need for users to login – everything is taken care of behind the scenes.
“Bottom line – PROC PWENCODE is used to encode any text string,” said Tang.
Read Tang’s paper and all other SESUG 2012 proceedings at SESUG.org, when the papers are available on the site.
