Passwords should never be visible in clear text. Period. However, with Automic up to Version 9, there was a problem: Accessing backend systems (like databases) requires a username and a password. There was no possibility of encrypting a password for usage in the Process-Tab. All users could read it. Luckily, that has changed now.

Access to Database with JOBS

First, I want to show you what I mean by an excample. Let’s assume I want to access an Oracle Database with a Unix Job and conduct a simple query:

SELECT * FROM dept ORDER BY deptno;

The Job is to conduct the query as user “Scott”. That is a standard Oracle DB User that can be used for a demo. The Table EMP, which we will access, is also a standard demo table in Oracle.

If I use an sqlplus command in JOBS, most of the time I will also use a here document for that. I will also add a few settings. The most important point: In case of errors, the return code should not be 0 but 1. That’s the only way a failed JOB results in ENDED_NOT_OK.

The complete query looks like this:

sqlplus -s scott/tiger << EOSQL
whenever sqlerror exit 1;
whenever oserror exit 1;
set pagesize 0;
set linesize 230;
set Feedback off;
set colsep ";";
SELECT * FROM dept ORDER BY deptno;
EOSQL

I’m sure you can already see the problem: The password “tiger” is visible in the text.

Solution 1: LOGIN Objects

We can solve this problem via LOGIN Objects.

1. Add the Login Type

Add to the variable UC_LOGIN_TYPES at Client 0000 the desired type (for example, SQLPLUS). The type can be freely chosen.

Creating the Login-Type

2. Create a LOGIN Object

Create a LOGIN Object for the user (in this instance “scott”) and save the password there. Of course, it will show up here encrypted. Under “Type“, select the previously created login type (here: “SQLPLUS“).

Creating a LOGIN-Object

3. Read the Information within the JOBS-Object

In the JOBS script you can now read the information from the LOGIN Object. For that you will use the function GET_LOGIN:

:SET &USER# = GET_LOGIN("LOGIN.SCOTT",scott,SQLPLUS,LOGIN_INFO)
:SET &PASS# = GET_LOGIN("LOGIN.SCOTT",scott,SQLPLUS,PASSWORD)

The encrypted password is now saved in the variable &PASS#.

4. Using it with the Job-Messenger

From our JOBS we are now activating the Job-Messenger. To do that, we use the variable &UC_JOBMD, in which the path and the name of the Jobmelder are stored. The Jobmelder knows the parameter CMD in order to execute any commands on the agent.

The commands are entered as a string, the encrypted passwords are marked at the beginning with a soft hyphen. The Jobmelder recognizes them and decrypts them as it executes the commands.

Therefore, the query looks like this:

&UC_JOBMD CMD='
sqlplus -s &USER#/&PASS# << EOSQL
whenever sqlerror exit 1;
whenever oserror exit 1;
set pagesize 0;
set linesize 230;
set Feedback off;
set colsep ";";
SELECT * FROM dept ORDER BY deptno;
EOSQL
'

You can view the completed JOBS in the following screenshot.

The password still does not show up in clear text in the generated Job. It is decrypted only during execution by the Jobmelder.

Complete Script for solution with Login-Object

Solution 2: PRPT Objectse

Alternatively, we can use a PromptSet Object instead of a LOGIN Object. The procedure is similar to Solution 1.

1. Create a PRPT Object

In this Object you create a text field for the username. You use USER# as a variable.

Then you create a second text field with the property “show password”. That is the password field. You use PASS# as a variable.

I have used the already established “scott” and “tiger” as default values.

Creating a PRPT-Object

2. Using PRPT Object in JOBS

The sqlplus commands are again accessed via the Jobmelder.

The completed JOBS now looks like this:

Complete Script for solution with LOGIN-Object

Keine perfekte Lösung

Both solutions prevent a password from showing up anywhere in clear text. Nevertheless, it is not 100% secure because these are reversible passwords.

Whoever knows the encrypted password, can decrypt it with the Job-Messenger. It’s done by simply entering an echo command with the Job-Messenger.

Attention: Password can be made visible with the Job-Messengerf

Furthermore, it’s possible to read the password on OS Level from the running command, e.g. with Unix via “ps -ef”. You can solve that by passing the password with a Pipe. This way, it’s only visible in the process list for a split second.

Anyway, keep control over your naming convention and pay attention to who has access to these LOGIN and PRPT Objects.

So, have fun fiddling around with the two solutions. As always, I’m happy about every feedback.