Leanne Tang had a problem. She has one giant database that houses data from all of the units at National Agricultural Statistics Service. She has hundreds of people who need access, but she wants individual users to have access only to the data that meets their needs.   

In Tang’s presentation at the 2012 SouthEast SAS Users Group conference, she explained how she limited data access. For instance, California users only need data from California; likewise, users from Alabama who want crops data probably aren’t looking for crops or livestock data from other states.

Where to start

“Everybody comes in and executes my SAS program and retrieves data from the database,” said Tang. Tang didn’t want to manually manage the access; she wanted to automate access. Once she has the users’ organizational unit, she can use it in her SAS program to control access.

All she needed to figure out was how to use her SAS program to get the information back to her SAS application to automatically control database access. Tang said she has all of the data on her active directory server (AD). (The AD server is a Microsoft server that is used to manage users, computers, printers, etc. on the network and control file access.) In this presentation, she discussed only the user information.

Ready, set, go

According to Tang, there are many utilities that can be used to retrieve the user information from the server. “You can use DSQuery.exe to query the data on the AD server,” she said. “The DSQuery is installed with the server … you can also go to the Microsoft web site to download that particular command line utility.”

She said that this allows every user with an account in the domain has read access to query AD objects using the DSQuery. Now, every user who uses the SAS program to execute the query can hit the database to get the data and use it in a SAS program.

To use the SAS program to execute a utility and get the information she needs, she first needs to copy the query to a convenient location for both the users and her. She assigned the location to a macro variable and then submitted a file name statement to associate with the external file. This tells the program to go to the file, execute the query and query the database down to SYSUSEID. Then she types everything back to SAS.  

To make it easy to parse the information, she used a data step called a user id.  This gives her info about where users are from so she can permit the users to see data that is relevant.

Download Tang’s SESUG presentation to learn more about the process. All of the papers will be available soon at SESUG.org.

Image provided by Tim Morgan // attribution by creative commons