topic Re: Monitoring users direct connectoin on databases in SAS Enterprise Guide

Monitoring users direct connectoin on databases

Kaladhan — Thu, 05 May 2016 19:11:01 GMT

Hello fellow SAS users,

We have users who execute SAS code in Enterprise Guide 5.1 to connect directly to databases, instead of using librairies and profiles setup for them in management console.

We would like to know which databases are used this way and which accounts are used to connect.

We do not have a repository of SAS source code, meaning that our users are free to store them wherever they want (even their PC). I can't just scan SAS code for the information.

We also have people who manually execute code that they didn't write. So asking users will not give us a complete answer to this question (although it could answer part of it).

I've looked online for a solution, but can't find anything. Anybody has a lead that could help me?

FYI, our EG is on UNIX, if that matters.

Re: Monitoring users direct connectoin on databases

SASKiwi — Thu, 05 May 2016 20:04:40 GMT

Unless you have SAS activity logging installed and running I think you are out of luck from a SAS perspective. Your database administrators should have monitoring stats on users connecting so I suggest you check with them.

Re: Monitoring users direct connectoin on databases

Kaladhan — Thu, 05 May 2016 20:08:42 GMT

With 200+ users and 40+ databases in various technologies, monitoring the databases seems like a lot of work to put in place.

I'm looking into SAS logging at the moment. I found this of interest:
http://support.sas.com/kb/17/730.html

I'm thinking of centralizing all of the user logs on the network so that we could dig into it for an answer.

Re: Monitoring users direct connectoin on databases

SASKiwi — Thu, 05 May 2016 21:31:06 GMT

That's EG logging normally only used for troubleshooting EG problems.

The sort of logging I was talking about is SAS server logging and it's a lot of work to set up if you don't have it already. Here is a link to get you started:

http://support.sas.com/documentation/cdl/en/biov/69018/HTML/default/viewer.htm#p0n6mp9320vobun1x0pw1kqy7ee5.htm

Really I think you should talk to your DBAs as databases collect stats like this as a standard activity. You would probably need to identify all of the SAS user accounts to filter the database connections but I wouldn't think that would be a lot of work.

Re: Monitoring users direct connectoin on databases

TomKari — Thu, 05 May 2016 22:03:49 GMT

I'm afraid I have to agree with Kiwi on this. Regulating access to data in the databases is a DBA job. Let me give you a quick example; even if you could find the SAS users accessing them, and manage them, I could simply use Excel or raw SQL to pull data out of the databases, and then access those results with SAS.

It's the database accounts and passwords that are the only effective choke point.

Tom

Re: Monitoring users direct connectoin on databases

Kaladhan — Fri, 06 May 2016 12:15:36 GMT

In our case, we don't care if the users access the data outside of SAS, for example with TOAD, Excel or the like.

The issue with asking DBAs is that it will have to include a lot of red tapes as it involves an oversea team. It will take months to be put in place. I'm not sure the amount of effort required is worth it.

Regarding the logs, it looks promising, but I'm not sure we want to dabble with our users PC. There could be consequences to doing that.