DSXchange

NOTE: Split off from this topic after a change in subject.

qt_ky wrote:The security group is pushing to revoke any database privilege that smells like it could be elevated, as well as pushing changes that would force us to change database passwords on application/system IDs that were already exempted to be non-expiring, which would force us to recompile thousands of jobs within an impossible timeframe, etc.

Why do you need to recompile jobs just because the user/password changes? Just pass them as parameters. It's a good usage for parameter sets or project variables.

We use parameter sets. Within those it would not matter if a user name parameter value changed; a scheduled job would pick up the new value fine, but we have found time and time again that with an encrypted password parameter, if you change the value then the next job run will not pick up the change. Not sure if that's a software defect or not but we found that recompiling would force the job to pick up the new password.

Maybe it is really a bug...at our site, it works totally flawlessly. We did it like that:
- In Administrator, define separate project variables for database user, password, schema etc.
- Create a "DB" parameter set out of those parameters and set them to value=$PROJDEF
- Add the "DB" parameter set to the jobs that have database connections.
We don't pass the parameter set from the sequence to the job because we always use the technical user that is saved in the project variables.

I am glad to discuss this, although it could probably be its own separate topic.

We are using a slightly different approach which dates back 7+ years to version 8.5, then later version 8.7, where we had found that using environment variables worked fine for database names, user names, schema names, and so on, but they were unacceptable to use for passwords because the values were clearly displayed as clear text in the job logs.

Because this violated the organization's security policies, we had to move all password parameters away from using environment variables and instead define each one as a local parameter within each project's parameter set.

Then, we discovered through a series of unfortunate events with database accounts getting locked and requiring numerous rounds of resets and password changes, that we had to recompile each job upon changing the database account password. It seemed like it should not be necessary (seemed like a bug) to recompile but we found that it was. I do not recall if we had a PMR about it or not.

At some point along the journey, whether it was from applying a version 8.x fix pack or migrating to 11.3, I noticed that the encrypted environment variable values had become masked in the job logs, displayed as ********, which was a nice corrective action taken by IBM. So perhaps we could go back to using encrypted environment variables as password parameters, if someone were willing to make all the changes... in their spare time!

Do others do it in a different way? What is the most secure way?

You are correct that this really should be a separate discussion, we'll see where it goes and then I'll do the surgical honors.

We do it that way. I can't comment if its the most secure way or not.

I just checked 11.5 and you can extract the password with the simplest imaginable attack. Nothing even interesting, just pure datastage jobs with the right settings will print the password in the logs. So I would offer that encrypted env variables in param sets is very insecure. First all your programmers will have full access if they think for 10 seconds on how to get it out, and second, its vulnerable to just having bad settings that accidentally reveal it.

externally (outside your datastage devs) it may be sufficient protection.

I don't think it CAN be protected from developers, to be honest. Datastage may be flawed but I think a determined programmer is going to find a way if the tool has to decrypt it locally to send to the database engine, its going to be in memory or a packet or something.

As UCDI says, you can't prevent someone with the DataStage Developer role from hacking your passwords.

In a project where there is no DataStage Developer role allowed (i.e. you can't create and compile jobs) and you also prevent read access to the DSParams file on the server (i.e. you can't copy the encrypted string beginning with {iisenc}), then I don't know of a way to hack the password.

Even then I wouldn't be all that confident that you could deter a determined hacker.

Mike

I agree with those comments. I showed a developer once a simple way and he was surprised to learn about it.

Even in a locked down production environment a sneaky developer could potentially include such a debug hack among dozens or hundreds of job changes and slip it in to a promotion.

DataStage may be secure from outsiders or operators, but not so much from developers. If anyone knows of better (or best) DataStage security practices, please do share.

Okay, as predicted, this has been split out to its own topic. Had to make up a new subject, not my finest work but should suffice.

Perfect. Thanks!