PLM Tech Tips

PLM Tech Tips: A Better Mousetrap (Agile PLM)

Awhile back I wrote in this space about the need to properly extract and save system passwords for the newer Agile versions when doing refreshes or upgrades. At that time I provided a very simple SQL script that would output the keystore value in one text file and the encrypted passwords for the admin, superadmin, ifsuser and agile user accounts in another. It was then up to you to fill in the blanks in another SQL script used to update the passwords in the database.

Wouldn’t it be much more effective and easier to just have the query extract the passwords and keystore, then write the SQL script needed to restore them with the values in place? This would save writing yet another script and using copy/paste to insert the values. In this installment I will show you how I accomplished this to make the single extraction script a better mousetrap.

A quick review is in order to set the ground work for what we are going to do. The original script looked like this:

<code>set echo off feedback off heading off linesize 256 pagesize 50 serveroutput on size 1000000 termout on timing off

column loginid FORMAT A11

column login_pwd FORMAT A62

spool 932Keystore.txt

SELECT 'KeyStore',value FROM propertytable WHERE parentid=5004 AND propertyid=1008;

spool off

spool 932Passwords.csv

select loginid ||','|| login_pwd FROM agileuser where loginid IN ('admin','superadmin','ifsuser','agileuser');</code>

[clear-line]

<code>set echo on feedback on heading on termout off timing on;

set feedback on

set serveroutput off

spool off</code>

Not elegant but useful. I knew at the time that it could be improved but I was busy and needed it in a hurry, so I left the enhancements for another day. The encrypted password values had to be copied from two output files (932Keystore.txt and 932Passwords.csv) in to yet another file I had called RestorePasswords.sql. Here is that simple script.

[clear-line]

Keystore update:

<code>UPDATE propertytable SET value='&lt;INSERT YOUR ENCRYPTED PASSWORD HERE&gt;' WHERE parentid=5004 AND propertyid=1008;

commit;</code>

For each encrypted user password that was saved:

<code>UPDATE agileuser SET login_pwd ='&lt;INSERT YOUR ENCRYPTED PASSWORD HERE&gt;' WHERE loginid = 'admin';

UPDATE agileuser SET login_pwd ='&lt;INSERT YOUR ENCRYPTED PASSWORD HERE&gt;' WHERE loginid = 'superadmin';

UPDATE agileuser SET login_pwd ='&lt;INSERT YOUR ENCRYPTED PASSWORD HERE&gt;' WHERE loginid = 'ifsuser';

UPDATE agileuser SET login_pwd ='&lt;ENCRYPTED USER PASSWORD&gt;' WHERE loginid = 'agileuser';

commit;</code>

Although the file is simple and effective, it did require the user to copy/paste the actual values from two other files and this occasionally caused some confusion and required extra effort that could be put to better use. So I set out to unify the functionality and make a script that produced the Restore script with the appropriate values.

[clear-line]

Here is that script:

<code>column loginid FORMAT A11

column login_pwd FORMAT A62

set heading off

set echo off

set termout off

set feedback off

spool RestorePasswordsNew.sql

select 'UPDATE propertytable SET value= '''||value||''' WHERE parentid=5004 AND propertyid=1008 FROMpropertytable WHERE parentid=5004 AND propertyid=1008;' FROM propertytable WHERE parentid=5004 AND propertyid=1008;

select 'UPDATE agileuser SET login_pwd = '''||login_pwd||''' WHERE loginid = ','''admin''' FROM agileuser where loginid = 'admin';

select 'UPDATE agileuser SET login_pwd = '''||login_pwd||''' WHERE loginid = ','''superadmin''' FROM agileuser where loginid = 'superadmin';

select 'UPDATE agileuser SET login_pwd = '''||login_pwd||''' WHERE loginid = ','''ifsuser''' FROM agileuser where loginid = 'ifsuser';

select 'UPDATE agileuser SET login_pwd = '''||login_pwd||''' WHERE loginid = ','''agileuser''' FROM agileuser where loginid = 'agileuser';

spool off

set heading on

set echo on

set termout on

set feedback on

[clear-line]
</code>

The output of this script looks like this:

<code>UPDATE propertytable SET value = `{AES}UsbCzPtZBYdzoF/zVNCxizAkN8Mcil9ZjCJluiNxmEg=` WHERE parentid=5004 AND propertyid=1008 FROM propertytable WHERE parentid=5004 AND propertyid=1008;                                                                                                                                                    UPDATE agileuser SET login_pwd = `{SHA-256}3KQ7AVA5AJB3NR9V1S0HEMNJ3IH4SK6FKRFQATB8MJ9N5QMNKEI0 ` WHERE loginid = `admin`;                                                                                                     UPDATE agileuser SET login_pwd = `{SHA-256}P5G27ARNU48R5FI3OF92441G3CVVB1QDPFC8H4F2FOLQPLAL20Q0` WHERE loginid = `agileuser`;                                                                                                                                                                                                              UPDATE agileuser SET login_pwd = `{SHA-256}31C6M400I9FA6LU5C5JD27QGCT0UKVH06P40EAQ20LI73NUTD0U0 ` WHERE loginid = `ifsuser`;                                                                                                   UPDATE agileuser SET login_pwd = `{SHA-256}SLAC7DINFRIQLDKFMQ2NRRJHV4ERUP9LKDUPJ7UN6N6NRUF7G0AG` WHERE loginid = `superadmin`;</code>

This script can be further improved upun by combining the four separate SELECT statements for account passwords into one statement as below:

<code>SELECT 'UPDATE agileuser SET login_pwd = '''||login_pwd||''' WHERE loginid = ','''admin''' FROM agileuser where loginid IN ('admin','superadmin','ifsuser','agileuser');</code>

The script still requires that you first connect to your Agile database since putting a plain text password in the script would be a security issue.

[clear-line]

Here is the completed script:

  • SavePasswords.sql
  • Extracts encrypted passwords and keystore value
  • Writes the values in a new script RestorePasswordsNew.sql
  • To be used when restoring Agile system passwords
<code>set linesize 356 pagesize 128 timing off

set heading off

set echo off

set termout off

set feedback off

set serveroutput off

spool RestorePasswordsNew.sql

select 'UPDATE propertytable SET value = ' || value || ' WHERE parentid=5004 AND propertyid=1008 FROM propertytable WHERE parentid=5004 AND propertyid=1008;' FROM propertytable WHERE parentid=5004 AND propertyid=1008;

select 'UPDATE agileuser SET login_pwd = ''' || login_pwd , ''' WHERE loginid = ''' ||loginid||''';'   FROM agileuser where loginid IN ('admin','superadmin','ifsuser','agileuser');

set heading on

set echo on

set termout on

set feedback on

set serveroutput on

spool off </code>

Well there you have it, the new and improved version of my SQL script to extract and save passwords from Agile 9.3.2 and 9.3.3 systems. I am certain that many of you can build pun this make an even more elegant solution.

 

Subscribe to the ZWS Blog

Recent Posts