RE: How to track who accessed what table and columns

[jakarman]

Yes Kurt agree on the SAS EBI part all logging and monitoring is set up as part of that.

When you have forced your users into that and not allowing anything with SAS without your knowledge you can monitor the usage. What is achieved is abandoning the SAS usage for self service shadow IT. Knowing them and restrictions the usage makes logging and monitoring less usefull.

When all flexibility of SAS usage is handed over the logging can be bypassed within SAS it cannot bypassed at the OS level..

I do not agree that Unix systems are better. Windows admins are far more cooperative to deliver something and Unix admins are often far more blocking ones. That is the reason I believe SAS started to build something themself. Misalignment of the Unix support stating the application Eg SAS should sole monitoring and auditing like Oracle has done.

[PaulHomes]

You might also want to have a look at the SAS(R) 9.4 Logging: Configuration and Programming Reference, Second Edition and in particular the following sections:

• Audit Messages for SAS Library Access
• Audit Messages for Metadata-Bound Libraries

... it also includes Sample Code to Look for Log Files and Build an Audit Report Data Set

[Kurt_Bremser]

If I interpret this correctly, this is depending on SAS sessions using the right logconfig.xml. Any other SAS sessions will not be audited?

[jakarman]

Yes Kurt, they are part of an appserver setup for each of the SP WS and even a connect an batch server one. Can be frustrating that the batch server with arm log config being activated is disabling the quoting being necessary to allow spaces in filenames at the sysin option. This is at the Unix environment.

Studio is a webbased approach connecting to a WS competition with the DMS and Eguide.

[PaulHomes]

Yes, it needs to be pre-configured in an XML logging configuration file. If required, the standard platform logging configuration can be modified. As the logging config is a Base SAS feature, even old-style, non-metadata driven, batch SAS programs or DMS can have their logging configured this way too - assuming the appropriate sasv9.cfg has a logconfigloc option set. Of course, if someone has access to a command line, or permissions to modify configuration files or server metadata, they could attempt to access the data and bypass the audit logging. For SAS datasets that might require more control, it could be worth considering audit logging with mediated data access or metadata bound libraries and encrypted tables (and limiting the ability to launch SAS sessions with custom configuration).

[jakarman]

Paul, the argumentation for trying to limit SAS usage that way is advocating the usage of R instead of SAS or the lamp stack competition bi usage. Both ends do not make sense.

The full access including shell as wanted for the well high educated dat scientists or the dumb isolated predefined click processing by standard end users.

The os level auditing is a reliable one but needing cooperation with it departments. There is a gap in that alignment.

The business requirements as of regulations is another one.

[Ram4sas]

Hi All,

Thank you very mush for your support.

@Paul- If I create new logcongi.xml file for workspace server  as per - Audit Messages for SAS Library Access   would that generate log file for all table accesst, so that i ca write code to create data set as per SAS(R) 9.4 Logging: Configuration and Programming Reference, Second Edition for the users whoever access the tables using forecast studio, EG and SAS Studio. Like user can access tables using oracle metadata library and want to track if anyone access using direct oracle libname statement. is that possible?

Thanks,

Ram

[PaulHomes]

If you configure it for the workspace server it will be effective for all users of that workspace server (i.e. EG and other metadata driven client/web apps that use that workspace server configuration). Anyone who runs SAS code by other mechanisms like non-metadata driven SAS batch jobs, DIsplay Manager etc will not get logged unless you can change the SAS config file used by those other mechanisms too.

[Ram4sas]

HI Paul,

Thank you for you reply,  Yes, My customer want to have users details which access what sas data set and oracle tables list using SAS forecast studio, EG and SAS Studio clients as these are all metadata driven tools.

I will create new logconfig.xml(Audit.Data.Dataset.Open) and place in for workspace server folder, and check if that create log file for all users who access the sas data sets and oracel library tables(including if user run libanme statement for Oracel table access in EG).

Does above one require SAS Enviroment manager 2.4 with APM 14w41 version as we have old version of SAS Environement Manager(14w32)?

Thank you Paul,

Ram

[Ram4sas]

Hi Paul,

I have been busy with Prod Issues and other stuff hence couldn't able to test earlier this.

After long time i have tested the below process, but its not working.

as per  SAS(R) 9.4 Logging: Configuration and Programming Reference, Second Edition

I have created the new "logconfig.xml"(took Back up default file) and added below code into new logconfig.xml file.

but no luck. it error for worksapce server connection. i got below error when i tried to validate SAS workspace server.

Error:

The launch of the server process failed due to problem with the processing of the SAS Logging Facility configuration file(LOGCONFIGLOC).

Did i miss something in the process to generate log file for users who accessing tables in SAS Servers?

==================================================================

<!-- Audit.Data.Dataset File Appender Definition -->

<appender name="AuditLibraryFile" class="FileAppender">

   <param name="Append" value="true"/>

   <param name="ImmediateFlush" value="true"/>

   <param name="fileNamePattern" value=

               "/opt/sas/Config/Lev1/Audit/

                Audit.Library_server_%d_%S{hostname}_%S{pid}.log"/>

   <layout>

      <param name="ConversionPattern"

       value="DateTime=%d Userid=%u Action=%E{Audit.Dataset.Action}

              Status=%E{Audit.Dataset.Status} Libref=%E{Audit.Dataset.Libref}

              Engine=%E{Audit.Dataset.Engine} Member=%E{Audit.Dataset.Member}

              NewMember=%E{Audit.Dataset.NewMember} MemberType=%E{Audit.Dataset.Memtype}

              Openmode=%E{Audit.Dataset.Openmode} Path=%E{Audit.Dataset.Path}

              Sysrc=%E{Audit.Dataset.Sysrc} Sysmsg=  %E{Audit.Dataset.Sysmsg}"

   </layout>

</appender>

<!-- Audit.Data.Dataset.Open logger definition -->

<logger name="Audit.Data.Dataset.Open" additivity="false">

   <appender-ref ref="AuditLibraryFile"/>

   <level value="Trace"/>

</logger>

==========================================================================

Please suggest how can i generate log file of users accessing table details.

Thanks,

Ram

[Kurt_Bremser]

You try to create the log file in the configuration tree, where (as should be) only the install user has write permissions. Have the logs written in

- the current user's home directory tree

- a shared location where all SAS users have write permission.

[PaulHomes]

That error can indicate a problem with the logconfig.xml file (as pointed to by the logconfigloc system option in sasv9.cfg). I would open the logconfig.xml in Internet Explorer or a good XML editor to make sure it is well-formed and there are no obvious errors. Then check all the references to directory paths and files are valid. Check any available SAS log files for further clues (there is often a fail-safe log created somewhere when the logging facility fails).

If that doesn't help, you may want to consider contacting SAS Professional Services or a SAS partner in your local area to see if they can provide some expertise for your project.

[BrunoMueller · Accepted solution]

Hi Ram

Find below as sample logconfig that I have used sucessfully. Please make sure, that all users starting a Workspace server do have write access to the location where the log file are written.

If something is wrong with the XML then one get an entry in the system log giving the name of a file that indicates the type of error. The sample blow was made for Windows, so you have to change the location of the log file, also make sure, that you get unique filename for each process.

<?xml version="1.0" encoding="UTF-8"?>

<logging:configuration xmlns:logging="http://www.sas.com/xml/logging/1.0/" debug="true">  

<!-- Audit.Data.Dataset File Appender Definition -->

<!--

<appender name="Normal" class="FileAppender">

   <param name="Append" value="true"/>

   <param name="ImmediateFlush" value="true"/>

   <param name="fileNamePattern" value="c:\temp\normal_%d_%S{hostname}_%S{pid}.log"/>

   <layout>

      <param name="ConversionPattern"

       value="DateTime=%d Logger=%c Msg=%m" />

   </layout>

</appender>

-->

<!-- Audit.Data.Dataset File Appender Definition -->

<appender name="AuditLibraryFile" class="FileAppender">

   <param name="Append" value="true"/>

   <param name="ImmediateFlush" value="true"/>

   <param name="fileNamePattern" value="c:\temp\audit_dataset\audit.dataset_%d_%S{hostname}_%S{pid}.log"/>

   <layout>

      <param name="ConversionPattern"

       value="%d!%c!%u!jobid=%S{jobid} Action=%E{Audit.Dataset.Action} Status=%E{Audit.Dataset.Status} Libref=%E{Audit.Dataset.Libref} Engine=%E{Audit.Dataset.Engine} Member=%E{Audit.Dataset.Member} NewMember=%E{Audit.Dataset.NewMember} MemberType=%E{Audit.Dataset.Memtype} Openmode=%E{Audit.Dataset.Openmode} Path=%E{Audit.Dataset.Path} Sysrc=%E{Audit.Dataset.Sysrc} Sysmsg=%E{Audit.Dataset.Sysmsg} username=%S{user_name} startup_cmd=%S{startup_cmd} " />

   </layout>

   <filter class="StringMatchFilter">

        <param name="StringToMatch" value="Libref=SASHELP"/>

        <param name="AcceptOnMatch" value="FALSE"/>

   </filter>

</appender>

<!-- Audit.Data.Dataset.Open logger definition -->

<logger name="Audit.Data.Dataset.Open" additivity="false">

   <appender-ref ref="AuditLibraryFile"/>

   <level value="Trace"/>

</logger>

<!-- Audit.Data.Dataset.Delete logger definition -->

<logger name="Audit.Data.Dataset.Delete" additivity="false">

   <appender-ref ref="AuditLibraryFile"/>

   <level value="Trace"/>

</logger>

<!-- Audit.Data.Dataset.Rename logger definition -->

<logger name="Audit.Data.Dataset.Rename" additivity="false">

   <appender-ref ref="AuditLibraryFile"/>

   <level value="Trace"/>

</logger>

<!-- Root logger -->

   <root>

      <!--

      <level value="ERROR"/>

      <appender-ref ref="Normal"/>     

      -->

   </root>

</logging:configuration>

[Ram4sas]

Hi Bruno,

thank you very much, its working.

[Ram4sas]

Hi Bruno,

Can i pull the code what user execute to access data from table like "select * from EMP;" into log file?

as grabed user details into log file using above code, is there any code to pull the code what user using to read table?