topic Re: RE: How to track who accessed what table and columns in Administration and Deployment

RE: How to track who accessed what table and columns

Ram4sas — Sun, 21 Jun 2015 20:59:10 GMT

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

Re: RE: How to track who accessed what table and columns

MichelleHomes — Sun, 21 Jun 2015 22:25:33 GMT

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

Re: RE: How to track who accessed what table and columns

Ram4sas — Sun, 21 Jun 2015 23:53:22 GMT

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

Re: RE: How to track who accessed what table and columns

MichelleHomes — Mon, 22 Jun 2015 00:57:33 GMT

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

Re: RE: How to track who accessed what table and columns

Ram4sas — Mon, 22 Jun 2015 01:31:21 GMT

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.

Re: RE: How to track who accessed what table and columns

Ram4sas — Mon, 22 Jun 2015 01:35:58 GMT

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

Re: RE: How to track who accessed what table and columns

MichelleHomes — Mon, 22 Jun 2015 02:07:12 GMT

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

Re: RE: How to track who accessed what table and columns

jakarman — Mon, 22 Jun 2015 08:03:29 GMT

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.

Re: RE: How to track who accessed what table and columns

Ram4sas — Wed, 24 Jun 2015 14:52:49 GMT

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

Re: RE: How to track who accessed what table and columns

Ram4sas — Wed, 24 Jun 2015 14:56:27 GMT

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

Re: RE: How to track who accessed what table and columns

jakarman — Wed, 24 Jun 2015 15:16:17 GMT

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.

Re: RE: How to track who accessed what table and columns

Ram4sas — Wed, 24 Jun 2015 16:52:27 GMT

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

Re: RE: How to track who accessed what table and columns

jakarman — Thu, 25 Jun 2015 05:36:18 GMT

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.

Re: RE: How to track who accessed what table and columns

Kurt_Bremser — Thu, 25 Jun 2015 05:50:42 GMT

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?

Re: RE: How to track who accessed what table and columns

Kurt_Bremser — Thu, 25 Jun 2015 05:57:19 GMT

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).

Re: RE: How to track who accessed what table and columns

jakarman — Thu, 25 Jun 2015 06:24:33 GMT

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.

Re: RE: How to track who accessed what table and columns

PaulHomes — Thu, 25 Jun 2015 09:53:09 GMT

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:

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

Re: RE: How to track who accessed what table and columns

Kurt_Bremser — Thu, 25 Jun 2015 10:15:01 GMT

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

Re: RE: How to track who accessed what table and columns

jakarman — Thu, 25 Jun 2015 12:27:33 GMT

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.

Re: RE: How to track who accessed what table and columns

PaulHomes — Fri, 26 Jun 2015 23:22:58 GMT

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).