This questions comes from a DBA:

We have a db2 client instance ( on a stand along AIX ) box , which we use to run some ETL scripts which acess two tables in the remote database server . Let's say there is no data movements involved , we just want to run few SQL statements. When connecting to the remote database from this client instance , we have to provide a password as part of the connect ( CONNECT TO XXX USER YYYY USING zzzzzz) . Since we run a script , this password is in the plain view. This is a security issue. Is there a way in DB2 to connect from the client to the remote database without specifying a password .

So far my ( simple minded ) suggestions were :

1. Define a target instance with authentication client ( Security said no).
2. Replace local "client" instance with the local "server" instance .Create a database containing only federated links to the two tables on the remote server . This way , the scripts will connect to the "local" database without password requirement . ( ETL said no - extra work for them ) .

I think there should be a way ( perhaps some sort of client based DB2 security plug-in or some other method ) to accomplish this . Can you make a suggestion ?

I believe that setting both AUTHENTICATION and TRUST_CLNTAUTH to CLIENT should do the trick. The downside would be that the configuration is instance-wide, obviously, so all other clients will also authenticate locally.

They can't use authentication client. Any other option?

Sure. Use AUTHENTICATION SERVER, but then you have to send the username and password to the server, no?

If you catalog a type 2 conenction and then define an ODBC data source for it on a Windows machine, a userid and password can be defined as part of the ODBC data source. Obviously this requires that an ODBC connection be used, and note that the password is not encyrpted when stored on the local Windows client.

Thank you, I forwarded your suggestions.


Some other suggestions provided:

- use something similar to "connect to <db> user <userid> using $1" and then pass it when calling the script

- encrypt script at the OS level

- if using java appl running from WAS then the userid and password (as well as other properties for the appl) could be encrypted: PropFilePasswordEncoder - script that allows to encrypt property file



DBA's feedback:
---------------
Actually , in order not to complicate the situation I had given you a simplified version of the script. We do have a password in the file in the encrypted form. Then we have a home-grown program ( a script ) which decrypts the password and passes it to the script in the manner you had suggested . However, it turns out this is not adequate for the security folks' purposes. One can still dump the scripts output file or rerun the decrypt script against file containing the encrypted passwords to gain access to production.

Your other suggestion regarding WAS is good . We're using it in our WAS application to protect the password . However, the ETL approach is ksh only - so we're looking for an alternate solution.