Auditing and recording a user-id

[stephenpyle]

I have Auditing up and running in my test environment running OpenEdge 10.1C0203

After making a schema change from the Data Administration screen and I pull data from _aud-audit-data, the _aud-audit-data._user-id is all blank.

I am logged in using a user/pass...
I have "Use Application User ID for Auditing" checked (tried both ways)

Does anyone know what I need to do to populate this field? Has anyone else seen this?

Thanks

 

[MaximMonin]

I noticed the following:

SETUSERID ( login, pword, "dbalias" ) required, if you want that AUDIT-CONTROL command writes any user information to _aud-audit-data._user-id.

But there are 2 potential problems with this approach:
1. If your application uses Application server, any connect to appserver is new db session with its own context. So you have to call SETUSERID again and again to write User to audit tables. Else user-id will be blank.
2. My web application operates in State free-mode (no real connection to DB while user idle). It connects while calling web service and disconnects at end. My audit tables is registering that web operations always have empty User-id. I wanted to eliminate this. There are two ways.
First is supply password information at any web service call. But it is not good for security.
Second is save user and (password) in my context managment db and read it every call. It is better cause i can save it at login procedure and never trasfer password through internet again. But admin user can read context managment db and it is not good again.

I dont have solution yet.

 

[RealHeavyDude]

Before OpenEdge 10.1a there was only one way to authenticate against the database. You had to use the SETUSERID function and thus utilizing the _User table. From OpenEdge 10.1a you alternatively you can use an application registry domain to authenticate against the database.

The latter works like this: You have a third-party or some other module in the application (something like LDAP for example) that does the authentication for you. In a single-sign-on environment you don't want to force the user to have a different set of credentials to log into your application. Therefore, after having the user successfully authenticated by "something" else, you build the CLIENT-PRINCIPAL object and seal it with a shared secret password that only the application and the database knows. Then you can use the SET-CLIENT function in conjunction with the sealed CLIENT-PRINCIPAL object to authenticate against the database and the database will use the user identification stored in the CLIENT-PRINCIPAL object. This way the database looks at the application as a trusted source for authentication and that's where "Use application User Id for Auditing" comes into play ...

Regards, RealHeavyDude.

 

[MaximMonin]

RHD: thanks for reply.

But I do not understand what is lifetime of CLIENT-PRINCIPAL Object and where is it stored (in memory or DB?). If my application/web service call ends how can i reuse it?

 

[RealHeavyDude]

The CLIENT-PRINCIPAL object is a 4GL handle-based object, therefore it primarily exists in the memory of a Progress session. But, you can export it to and import if from a raw data type ( variable or database / temp-table field ) which gives you the opportunity to store it wherever you want. You need to have some context management that allows you in a state free environment to identify your client as a returning client. Usually you would use some token that is exchanged between client and server after a successful authentication and is then part of each request.

So, you could export the CLIENT-PRINCIPAL object into a raw data type and store it together with some token that identifies the client uniquely in the database and then retrieve it on the client's next request to use the SET-CLIENT so that the database knows the user and the USERID function returns a user identification that you could use for your own auditing solution or the one that is built-in into the Progress database since OpenEdge 10.1a.

Regards, RealHeavyDude.

 

[MaximMonin]

My web apps works like this:
User logins, I call setuserid function to identify it. If login successful I create new Session record in ContextDB, and create new Context record with Context Parameters like UserLogin/UserName/Language selected etc... and return ContextKey to client. Every Web service call requires correct ContextKey. It solves security problems and I Can extract from ContextDB any ContextKey related info, like UserName/global vars settings.

As I inderstood I have to change my first login DB procedure, create CLIENT-PRINCIPAL Object and store it in Session record in ContextDB/instead storing password given by User.
Then use it at every WS calls which changes data to make sure audit records will be filled with user-id information.

Thanks for help.

 

[RealHeavyDude]

Correct.

But to be secure you need to make sure that nobody is able to steal this ContextKey on the client or the transport between the client and your server back-end. As soon as somebody with malicious intentions is able to steal that ContextKey he is able to authenticate and your server back-end will never know ... but that's another discussion.

Regards, RealHeavyDude.

 

[MaximMonin]

Correct.

But to be secure you need to make sure that nobody is able to steal this ContextKey on the client or the transport between the client and your server back-end. As soon as somebody with malicious intentions is able to steal that ContextKey he is able to authenticate and your server back-end will never know ... but that's another discussion.

Regards, RealHeavyDude.

I use SSH for transport, limited contextkey lifetime + contextkey changes sometimes by security system.

Maxim.

 

[RealHeavyDude]

Currently I am working in the most paranoid of paranoid environments - which would the intranet of a big Swiss bank ...

We only consider a environment secure when the user is strongly authenticated. Strong authentication means that the user is not only authenticated via his credentials (something he/she knows) but additionally with a smart card that has a personalized SSL certificate stored on it ( something he/she has ). Since the SSL client certificate is not supported by Progress ( and won't be in the foreseeable future ) it needed some creativity to solve the issue utilizing STunnel and connecting only via the WebServer. Plus - this is really bad and good for more than one headache - cross border access might or might not be issue ( imagine an employee sitting in Singapore accessing client data of a Swiss customer of the bank - that's where legal implications come into play and the nightmare really begins ).

Regards, RealHeavyDude.

 

[MaximMonin]

I understood, I solved this like this:
Entry point of my application is some https link, thus WebServer/Tomcat gives SSL certificate before my application begins to execute.
WSA has setting "Web Service protocol type" = "Use HTTPS Protocol"/"Dublicate WSA URL Value". So trasport between WSA and Client is secured. And application do not use any other methods to access OpenEdge DB except WS Calls.

 

[RealHeavyDude]

Additionally to standard HTTPS utilizing SSL encryption we make use of the SSL client certificate. Not only does the WebServer send his server certificate to the client to authenticate the server on the client, it requests the client certificate ( stored on a smart card which must be in the card reader ) from the client. If the client does not supply this certificate the during the SSL handshake, the connection is rejected by the server.

Therefore, if one would be able to steal the token he/she still would not have access to the personalized SSL certificate stored on the users access badge ( smart card ) at the same time.

In order for this to work you must configure your WebServer accordingly. Plus, most clients do not support the SSL client certificate and therefore you need to use something like STunnel ( which is based on openSSL ) that can be customized to send the client certificate to the server too.


Regards, RealHeavyDude.

 

[stephenpyle]

My actual issue is not even to the application level. SOX auditors are wanting us to track schema changes so the only policy I have in place is for that. All I am doing is - opening the data administration, connecting to the database, and making a schema change.

More data on the actual schema change is recorded than I could want - but the user-id is still blank - just logging in through the "Data Administration"...

 

[RealHeavyDude]

Your issue might be related to the fact that out-of-the box no security restrictions are set in the Progress/OpenEdge database. That means anybody who can connect to your database is allowed to do anything ...

You must disable blank userid access to enforce a login into the database in order to be able to access anything in the database. The user's identity then may be set exploring SETUSERID or the CLIENT-PRINCIPAL object before you are able to make schema changes.

Plus, switching to runtime security checking will prevent rogue r-code to access the database.

Regards, RealHeavyDude.

 

[stephenpyle]

I was afraid of that... our ERP breaks when you change it to not accept blank user id's.

We already changed access to the whole database to just the programmers - so that no one else could compile against the database or run anything against it.

 

[RealHeavyDude]

Out of my experience I can tell you:

When it comes to security Progress gives the application developer every freedom. Therefore it's up to the application developer to introduce security into the application (and thus introducing security to the database too). What I've seen from many Progress partners - even ones selling huge applications - is that they were not concerned all too much by security and auditing. Essential things you need to have in place when you must be compliant with SOX regulations.

While it might be fun to the tell the auditor "I don't know" or "we are not compliant" in the first place, it certainly will put pressure on senior management which then will blame - you guess who ...

Regards, RealHeavyDude.

 

[stephenpyle]

Apparently, I need to not check the "Use Application User ID for Auditing" (like all the docs I read) in the Database Options and then restart the db... I think I tried this before and did not restart.

 

[TomBascom]

our ERP breaks when you change it to not accept blank user id's.

"Breaks" in what way? This might be a very simple thing to fix -- it isn't exactly uncommon to have an initial setup with no _user userids that needs a few tweaks when you do add _users. It often happens, for instance, when people start using ODBC. It is generally a matter of the application having its own security tables and login process. Sometimes the fix is quite simple.

 

[AndyAudit]

Great discussion. Had problem with that issue before.