Find users who are pulling in large datasets from Oracle to SAS

[CharlesYuVA]

Hi, Experts!

I've been asked to determine if there are any users who are pulling in large datasets from Oracle to SAS. Most of our users use EG. I can write code to parse logs all day, so I just need to know where to look. What specific logs, if any, can I dig through for this purpose? I assume that the Workspace Server logs might have something, but is there anywhere else? Maybe I can look for data steps or proc sql steps that are taking a long time to run?

Thanks in advance!

[Kurt_Bremser]

The workspace server logs will only have your information if you set logconfig.xml accordingly. In the default configuration, only startup (autoexec) and shutdown of the server are recorded, everything else goes exclusively to the client.

[Sven111]

@Kurt_Bremser is correct, although you can specify in the global SAS config files that the user log files also be copied to an alternate log file location.  Once all the log files are in one place you could then setup something to manually parse through the logs (although I really wouldn't want to try setting up something like that).  If it's a security issue rather than a system monitoring issue, this may not work as I don't believe this can be locked down so the users can't change it, but if it's a security issue I think this is the wrong approach from the get go.

[SASKiwi]

If most of your users are using EG, then I can almost guarantee that they don't keep copies of their EG SAS logs - these would be pretty much the only source of the data you require that I can think of. Any attempt to collect enterprise-wide EG logs sounds to me like a cure that is worse than the disease.

I'd suggest you talk to your Oracle DBA. If users have individual Oracle accounts then there is probably some useful Oracle logging stats by user that can be matched against a list of known SAS users.

[Kurt_Bremser]

Some additional thoughts:

Since the log is written by the workspace server process, and that process belongs to the individual user, any location for a log must be user-writable. That means that no matter what you do, a user can also remove such a log, as long as (s)he is able to determine the name of the log.

I have tried to throw as much of an obstacle into this by

• creating a logs directory in the home directories of the users
• have this directory owned by root and the primary group of the user
• set the permission to drwx-w----

The logconfig.xml has been added a new appender:

   <!-- Rolling log file with default rollover of midnight -->
   <appender class="RollingFileAppender" name="TimeBasedRollingFile">
      <param name="Append" value="true"/>
      <param name="ImmediateFlush" value="true"/>
      <rollingPolicy class="TimeBasedRollingPolicy">
         <param name="fileNamePattern" value="$HOME/logs/XXXXXXXXXXXXXXX_%d_%S{pid}.log"/>
      </rollingPolicy>
      <layout>
         <param name="HeaderPattern" value="Host: '%S{hostname}', OS: '%S{os_family}', Release: '%S{os_release}', SAS Version: '%S{sup_ver_long2}', Command: '%S
{startup_cmd}'"/>
         <param name="ConversionPattern" value="%d %-5p [%t] %X{Client.ID}:%u -
%m"/>
      </layout>
   </appender>

and two loggers have their level set to Info:

   <!-- Application message logger -->
   <logger name="App" immutability="true">
      <level value="Info"/>
   </logger>

   <!-- IOM protocol message logger -->
   <logger name="IOM" immutability="true">
      <level value="Info"/>
   </logger>

This gives me all actions with individual timestamps in the log; if I want, I can put on my superuser persona and watch a user's actions "live" by repeatedly tailing the log file. The users could only remove (or edit) a log file if they knew the process number and the details of this structure.

Although this is just a case of security by obscurity, it provides at least some level of safely recording all user actions done through SAS.

Note this was implemented before the advent of Environment Manager.