Intralink SQL: Monitoring User Passwords Part 1

PTC provides very little control over passwords, and unfortunately expiration dates cannot be set from within the GUI. However, it can be accomplished in the oracle backend using triggers. Not with C based Intralink triggers (you can't trigger on a password change), but with PL/SQL based oracle triggers.

The basic concept is to setup a trigger that monitors changes to the password column of the user table (PDM_USER). Generally, the trigger writes the username, date, and optionally the "encrypted" password to a separate tracking table.

In the simplest application, an sql query on the tracking table would report users who have not changed their password within the required period. A cron job or scheduled task can use this query to send nag messages to users. If desired, this process could update the user's password field in the PDM_USER table to a value that would effectively disable the account.

A more complex trigger can prohibit password reuse, disallow known bad passwords, purge old "good" passwords from the database, and potentially send emails itself.

In this two part example, I will show you how to setup a trigger that tracks passwords, prohibits "known bad passwords", and prohibits some password reuse. 180 days is used here as a threshold, but any numeric values can be used, just be consistent.


Tracking Table:

You need a table to store the user/password/date information. The following code will create a table using datatypes from the PDM_USER table itself in the form of a query.

create table pdm.custom_pwchange_track unrecoverable as
select
userid as pwcid,
userid,
userpassword,
modifiedby,
modifiedon
from
pdm.PDM_USER
where
userid=-1
;

Even though the query doesn't return any values, it does provide a table structure with which to build a table. By matching datatypes with the PDM_USER table, we don't need to worry about the data size of the columns in the new table because they will be the same.


The table columns and sizes can be verified with 'describe':

describe pdm.custom_pwchange_track;

Here are a few examples of queries to get useful info:

-- set column widths (for two queries below)
--
column UserName format a15
column UserPassword format a15
column ModifiedBy format a12
column ModDateTime format a20
column lastchange format a20


-- report password history, all users
--
select PWCID, a.userid, username, a.userpassword, a.MODIFIEDBY,
to_char(a.modifiedon,'DD-MM-YY HH:MI:SS') moddatetime
from pdm.custom_pwchange_track a, pdm.pdm_user b
where a.userid=b.userid;


-- report users who have not changed their password in 180 days
--
select username,
to_char(max(a.modifiedon),'YY-MM-DD HH:MI:SS') lastchange
from pdm.custom_pwchange_track a, pdm.pdm_user b
where a.userid=b.userid
group by username having max(a.modifiedon)<sysdate-180;

Bad Password Table:

The bad password table concept is a little tricky to implement. Since the password is encrypted, the bad encrypted passwords would need to be stored for each user. A "new user" script or process, could go through the motions of changing the new user's password to a list of bad passwords, before changing to the password given to the user.

With the tracking table and trigger in place, the bad passwords can be pulled from the tracking table and inserted into the bad password table. As I said, it's a little tricky, but it can be done without too much effort. Might make for a big table if you get too strict about "bad" passwords.


Code to create the table, again, based on a query:

create table pdm.custom_bad_passwords unrecoverable as
select
userid as bpid,
userid,
userpassword
from
pdm.PDM_USER
where
userid=-1
;

Commands to verify the table:

describe pdm.custom_bad_passwords;

select * from pdm.custom_bad_passwords;


-- Take values from old password table for user 'fred' and insert
-- into bad password table
--
insert into pdm.custom_bad_passwords (bpid, userid, userpassword)
select pwcid, userid, userpassword from pdm.custom_pwchange_track
where userid=(
select userid from pdm.pdm_user where username='fred'
)
;

Sequence for unique IDs:

Typically a well designed database uses sequences to generate unique integer ids. This allows differentiation between each record in a table. In this case, it is not absolutely necessary, because we are not linking multiple tables together, but it's generally a good thing and very easy to do.

Code to create the sequence:

create sequence pdm.custom_pwchange_track_seq
increment by 1
start with 1
;

Commands to verify the sequence:

select SEQUENCE_OWNER,SEQUENCE_NAME,
MIN_VALUE,MAX_VALUE,INCREMENT_BY,LAST_NUMBER
from all_sequences
where SEQUENCE_NAME='custom_PWCHANGE_TRACK_SEQ';

Next time, implementing the trigger in Intralink SQL: Monitoring User Passwords Part 2.