Intralink SQL: Monitoring User Passwords Part 2

Continued from Intralink SQL: Monitoring User Passwords Part 1

The Trigger:

Finally, we're ready for the trigger itself. It's a bit long and I'll try to explain it in sections. The main three sections define the trigger name and when it is executed, declarations of cursors and variables, and the trigger body.

In this example, 'create or replace' allows us to create a new trigger or replace an existing trigger, without having to first drop the trigger. 'before update of userpassword' indicates that the trigger runs when the userpassword field of pdm.PDM_USER is changing, but not if the email address is changing. We are capturing each row change as it happens using 'for each row'.

In the declare section, two cursors and two variables are defined. The cursors allow looping through (potentially) many values in a table. The cursors here are used for looping through the old passwords less than 180 days old, and all bad passwords. The cursors are limited to rows related to the user undergoing the password change.

In the body of the trigger, virtual tables ":old" and ":new" are often used. As you might suspect, they represent the old values of the table row and the new values of the table row. The trigger can see both and decide upon what action to take.

Much of the trigger below is wrapped in an if-then block. This block determines whether the user being changed is the same as the user doing the change. Only an admin can change another user's password. The 'if' statement figures out whether the user being changed is an admin, and if not, applies the rules of the trigger. If this 'if' statement (and its 'endif') are removed, the trigger applies to all accounts, even admin accounts.

The next if-then block prevents the user from reusing the current password (old password = new password) with 'raise value_error'. This introduces a fictitious error, which stops any further execution. The password change is denied. The user will get a cryptic error message, and there is no way to better inform them why. The error message is semi-obvious for most users that the password did not get changed.

The next two chunks of code open the old password and bad password cursors. The trigger loops through each row in each cursor looking for values in order to 'raise value_error'. The code to process the cursors is nearly identical, even though the cursors are a little different.

The insert statement writes the necessary information to the tracking table. It mostly uses the new table row values, except for the ID which is pulled from the next available value of the sequence. For this trigger, regardless of admin change or not, all successful password changes are recorded to the tracking table.

To create the trigger, copy and paste everything from 'create' down through and including 'run;' into an sqlplus session. 'run' is not actually part if the trigger. It is needed by oracle to compile the trigger. It doesn't actually run the trigger at that time.

create or replace trigger pdm.custom_pwchange_track_trg
before update of userpassword on pdm.PDM_USER
for each row
declare
cursor old_passwords(uid int) is
select userpassword from pdm.custom_pwchange_track
where userid=uid and modifiedon>=(sysdate-180);
old_pw old_passwords%rowtype;
cursor bad_passwords(uid int) is
select userpassword from pdm.custom_bad_passwords
where userid=uid;
bad_pw bad_passwords%rowtype;
begin
--
-- if a non-admin user is changing their own password, apply rules
if (:new.username = :new.modifiedby AND :new.usertype != 1) then
--
-- If new password is old password, raise exception
if (:new.userpassword = :old.userpassword) then
raise value_error;
end if;
--
-- Read bad passwords, make sure they are not used
open bad_passwords(:new.userid);
loop
fetch bad_passwords into bad_pw;
exit when bad_passwords%notfound;
if bad_pw.userpassword = :new.userpassword then
raise value_error;
end if;
end loop;
close bad_passwords;
--
-- Read old password values for this user, make sure
-- they do not reuse a password
open old_passwords(:new.userid);
loop
fetch old_passwords into old_pw;
exit when old_passwords%notfound;
if old_pw.userpassword = :new.userpassword then
raise value_error;
end if;
end loop;
close old_passwords;
--
end if;
--
-- Insert new password into tracking table
insert into pdm.custom_pwchange_track
(pwcid, userid, userpassword, modifiedby, modifiedon)
values
(pdm.custom_pwchange_track_seq.nextval, :new.userid,
:new.userpassword, :new.modifiedby, :new.modifiedon);
end;
.
run;

When the trigger is in place, if there is some form of error, this command will help (though only a little):

show errors trigger pdm.custom_pwchange_track_trg;

These commands can be used to report information about the trigger:

column OWNER format a8
column table_name format a30

select OWNER, TRIGGER_NAME, TRIGGER_TYPE,
TABLE_OWNER'.'TABLE_NAME as table_name, status
from
all_triggers
where
TRIGGER_NAME like 'custom_PWCHANGE%'
;

Should the trigger need to be disabled or re-enabled use these commands:

alter trigger pdm.custom_pwchange_track_trg disable;
alter trigger pdm.custom_pwchange_track_trg enable;

You may want to purge the old password table of old passwords, without dropping the table altogether. This is not absolutely necessary as the trigger will ignore password more than 180 days old.

To delete old passwords use this command:

delete from pdm.custom_pwchange_track where modifiedon<sysdate-180;

Next time: Using Intralink Scripting to Change Passwords