Notify users of long running query

[Dreamer]

Hi,

I have a SAS library mapped to a SAP database server. All SAS user uses this SAS library to submit their query against this database.

Since query is submitted through this library which uses a mapped common SAS id, how can I figure out which SAS user has submitted that query and can be notified in case of any crisis.

Thanks!

[jakarman]

Well as you did mapped  to a common SAP-id in the SAS  environment what would you think on the auditability in SAP? No way.
The only option is to analyze all SAS loggings (having activated that) and find the SAS session with SAS user in those logs that gets connected to SAP.  When the SAP  connection would tell something more....   Other options, get back to SAS and ise personal logins to SAP (authdomain user/[pw settings).

Within in an fully domain controlled security that should be no problem (RBAC user/pw propagation). AD - IWA is having that by standard and RACF mainframe is having that. Just Unix alikes and RDBMS systems ore often failing and of course SAS architects not aware of regulations. 

[Dreamer]

Thanks Jaap, it was helpful!!

I don't want users to keep their login credential in side the code.

That's why they connects to SAP via SAS library.

Is there a better way to manage this scenario?

[jakarman]

See: Five strategies to eliminate passwords from your SAS programs - The SAS Dummy  I made there remarks for several reasons.

The Pwencode offers some protection on eye-leaves dropping seeing code. When it is in the code you can copy and paste that what ever  SAS code you have and it will work. It is still readable and copy/pasteable.

It is not that SAS hashed password that SAP will understand. What is happening is that SAS will recover the original password and use that for the SAP interface client. The pwencoded hash is not only a hash but there is unhash algorithme also.

I menioned  the authdomain as approach. This will work but you need a "due dillgence" investigation whether it is compliant. Thre reverse hash algorithme is mentioned. Now adding metadata security constraints that can get surprising.

With batchprocessing there is something strange seen at 9.3. It will connect to the metadata using the OS-key as metadata-key without doing a login.

The last way out is using a sas-macro that wil read a file on a OS-layer secured location. You can used an encrypted sas-dataset. In the old syntax (not using AES) the read-password is used as the salt for the encryption.

[Dreamer]

Yes Jaap, pwencode is not a solution here. I'm using Authdomain approach.

I have defined a Sybase library at SAS Server and users access sybase tables via this library using authdomain option.

LIBNAME MyLib ODBC DSN=DSNName AUTHDOMAIN=AssignedAuthDomain;

Of-course Sybase can only identify id from which any sql has triggered. In this case id defined in the SAS library.

So only option I have to audit at SAS end.

I thought log can tell me something but I couldn't fine any log under workspace server logs folder (E:\SAS\Config\Lev1\SASApp\WorkspaceServer\Logs).

I can see a "PerfLogs" folder but I believe its regarding performance traceability of the server.

I'm not sure if I have to do any setting here. Please suggest.

[jakarman]

Dreamer, I like the name of AUTHDomain but not how it is abused by SAS people.

It is something like this anecdote: A Romanov Passion for Flowers - Blog - Alexander Palace Time Machine

"Catherine the Great treasured her roses in Tsarskoe Selo and had special guards assigned to protect them year-round from the harsh climate and accidents.  Once an Imperial order had been given it was followed until rescinded.  Over a century after Catherine died Nicholas and Alexandra discovered a guard still stationed to protect a long vanished rose of Catherine's."

Let me try to explain.

1/ With a RBAC approach Role-based access control - Wikipedia, the free encyclopedia you are defining who is allowed to access what data. Every data object has an owner and a classification according some Confidentiality Integrity Availability risk assessment. It is the fundamental of ITSM with security (standard of good practice).

2/ part of this is verifying auditing  everything is running as should be. Operational monitoring and security monitoring Security information and event management - Wikipedia, the free encyclopedia

Your question is just one detailed of SIEM. As the SAP environment is having sensitive data you have got the question to solve this.

For some reason SAS institute is not accepting this like not accepting a flower is something for passing. They are even putting their guards on their old habits as the single truth.

The authdomain is a good name as you can see a Windows domain (controlled by AD) and external DBMS controlled by some local admin eg SAP.      
When the technical implementations change those domains can get joined (single signon) or segregated (external data-source other repsonsability).

For your question with Sybase. When every user would have his own user-id in Sybase you do not need to worry about thinking on access rights and monitoring.

The Sybase administrator can see somebody coming in, he can control the accessrights (group level) for everybody and deliver the audit logs on what anybody has done.

That is his work and his responsibility for the data is responsible for. Sounds easy and simple only he probably does not like the impact on doing his work.

When you could convince him you are ready by haven every used defined his keys/password in that AssigendAuthDomain at his personal user settings. 

This also frees up access from other tools as it becomes all auditable at the Sybase side.

For the workspace logs. They are by default not set not to be active but can be changed.

Some of it is part of the APM Audit&performance tool. SAS Support Downloads Browse (9.3 9.4) some things are getting combined with the eventmanager (9.4)

The details for changing that yourself:

- SAS(R) 9.3 Logging: Configuration and Programming Reference it is complete manual as you can really log a tremendous lot 

- SAS(R) 9.3 Interface to Application Response Measurement (ARM): Reference  (SAS Logging Facility Configuration File)

In the case you are needing an audit log with the workspace server there is an conceptual issue. All those files are created with the ownership of his credentials.
That can be very useful for debugging and analyzing SAS application behavior. It is his proof that something happened that way.

He can delete and change that without being audited. When needing real auditing for some sensitive data the OS toolings has to solve that by monitoring on OS level.

Whatever you do you cannot control micromanage everything. There must be a point that the user of the information becomes responsible. Seeing all this as helpful for having the proves he is integer and ethical correct. Not seeing ICT and those guys as some nerds of an other world being blocking for doing work at the business.