RE: How to track who accessed what table and columns

[Ram4sas]

Hi SAS Experts,

I got a task how to track the users accessed waht tables in SAS 9.4 EBI.

Example: User- X accessed table 'Y' on 06/15/2015 09:30:45 using SAS forecast studio or EG  or SAS Studio.

I am sure that APM can produce reports for Most heavily used SAS Procedure No. of times each User accessed Metadata, top ten user WS server..etc..

Is there a way to track the user list who accessed what table and columns and present using APM portal?

Thanks,

Ram

[MichelleHomes]

Hi Ram,

For SAS 9.4 the supported audit, performance and measurement package is with SAS Environment Manager. Information can be found at SAS Audit, Performance and Measurement.

If you look at the list of out-of-the box reports, you'll see there is a Data Usage report. You many need to tailor a custom report using this as a basis. http://support.sas.com/rnd/emi/SASEnvMgr/EVSAF/Report_Center_Report_Listings.pdf

I wrote a paper at SAS Global Forum 2014 about using APM to build a SAS Visual Analytics dashboard. It may give you some other ideas... http://support.sas.com/resources/papers/proceedings14/1247-2014.pdf

Kind Regards,

Michelle

[Ram4sas]

Hi Michelle,

Thank you very much for your reply.

The system is already configured with Environement Manager, but no APM configureation done.

No SAS BI Dashboard and Visual Analytics products configured as well.

Can I still generate the Data Usage report with environment manager if i initiate APM component?

Thanks,

Ram

[MichelleHomes]

Hi Ram,

Yes the Data Usage reports are part of APM and ARM logging and rendered via Environment Manager. You don't need BI dashboard or SAS Visual Analytics.

Kind Regards,

Michelle

[Ram4sas]

Hi Michelle,

The Data usage report is for most heavily used data table.

The same I could see on " SAS Admins Need a Dashboard, Too" document page 5, right pic under performance Reports section.

if I enable APM component in the system, would i be able to see the data table usage by users like which user accessed what table?

Thanks,

Ram.

[Ram4sas]

Hi Michelle,

My customer dont want to see most heavily used table, customer would like to see always what are the tables being access by user.

For Audit purpose need the users list who are all accessing the list of tables with datetime stamp.

Please guide me how to implement this. can you please share if any sample report you have like data usage.html report? any link for that?

Thanks,

Ram

[MichelleHomes]

Hi Ram,

The links above provide you access to how you implement it and the documentation. Sample reports are in my SAS Global Forum paper and other papers I'm sure. I'd suggest looking at the ARM tables created as this will contain the data to build the custom report you are after.

Kind Regards,

Michelle

[Kurt_Bremser]

Hi!

How is the data usage report compiled? Is the data taken from analysing all logs, so that access to datasets by "manually" entering libnames and using them in data/sql steps is also caught? What happens when users simply start a DMS SAS, or run a batch job?

[jakarman]

Logging can be done and configured with arm for the resources inside SAS. With external resources like a dbms or at the os level you have those resources. A complete audit and logging requirement for all data is needing a holistic view.

That requirement is coming as of regulations.

It is rather pittyfull SAS is missing those basic principles for the why's of these guidelines and regulations.

[Ram4sas]

Hi Jaap,

Do you mean that we can not get the report for data table usage reports using SAS APM with Environment Manager. yes i agree like for DBMS those are having this audit access monitor tools. my customer wanna to see the reports what users accessing which reports.

I have check with SAS Techsupport as well, i could see the below one

"The ARTIFACT.AUDIT_ACCESSC table contains audit records for object access events. If the feed to SAS Visual Analytics is enabled, this table is copied to the drop zone."

But we dont have SAS VA configured. I am wondering either i can generate report for data table usage in SAS or not.

Thanks,

Ram

[Ram4sas]

Hi Jaap,

there was typo in my previous reply like..

my customer wanna to see the reports what users accessing which all tables (not report).

thanks,

Ram

[jakarman]

Your question is getting more complicated.

Apm better classified arm logging can be configured to log all kind of events with using SAS in a controlled environment.

You can tailor that to any kind  of access event to tables using SAS on the server. It can be tailored to see any SAS code usage as long that is run on your server. It would be weird for me to have the SAS code (is that what you men by report?) being logged with accessing tables as that combination looks to me meaningless. That combination could be a result of reverse engineering when the design of code is missing.

SAS will never be able to log the access to tables using a rdbms like oracle using something like TOAD. That access of data is not having anything of SAS involved. The same could be for downloaded SAS datasets to another machine that is not in control of access monitoring.

[Ram4sas]

Hi Jaap,

Let me put like as simplest...System is SAS 9.4 already configured with environement manager its working fine for environement web reports.

I would like to see the report for data tables accessed users list with datetime stamp like who accessed tables using sas metadata library or using EG quering libanem statement. the data set can be from Oracle or internal data set to SASApp server.

The report can be a environment manager web reprot or other.

If i enable SAS APM, would i be able to get that report what i am looking for?

Thanks,

Ram

[jakarman]

Ram4sas  the environment manager is the successor of apm. Both are based on arm logging. The environment manager is adding some things of vfabric vm tools and another dbms postgres as of that. It comes with a lot of sample reports and starters first access screens as of operational support.

When the operational support is an segregated department as is normal ib bigger professional organizations there is business challenge to alignment.

Those sample reports can be added to new ones when having access to the collected data. That is what Michelle posted with admins are needing a dashboard too. Building can be done in any tool and as SAS support staff it makes sense to use the SAS tools. But that is not really a hard one.

As you are mentioning eguide Libnames with oracle, that is a bad description. The sas sessions being in control at the server with monitoring can arm tailored. Using eguide with a local sas session on a desktop will not. Both are eguide and sas usage.

Eguide is more a terminal aaproach your question on monitoring and auditing is about data governance and structuring environments.

[Kurt_Bremser]

Serious operating systems (and I an NOT talking about Windows here!) provide auditing mechanisms at the OS level, so you can record every simple open/close/read/write event. This is the only method of reliably catching ALL accesses to resources, as there cannot be access outside of the system calls provided by the kernel.

I seriously doubt that the Environment Manager will be able to report about dataset access that is being done in a SAS session that does not connect to the metadata sever and the EBI structure at all (batch, DMS).