Kintana Script : Remove Security Groups From Disabled User Accounts

Purpose

This script should also be on your pre SOX audit checklist.
Over the year you've set the end date on user records for users who have left to disable access.

However this does NOT automatically remove them from the security groups. You'll have to do that manually.
This script will locate them for you.

Issues

Deleting the security groups on a disabled user record also removes history information from the system

Before doing this, you should archive the listing from this script in your version control system.
This should satisfy any audit requirements while still allowing you to declutter your security groups.

A useful enhancement to the Workbench User manager when the end date is set would be to grey out
the security groups in the user record and remove the entries in the security groups, and vice versa.

Tested

With HP PPM (Project and Portfolio Management) Kintana v6.0.

Usage

Paste the script into SQL Runner

Omit the comment, spool, and linesize statements unless you're using a PL-SQL editor. SQL Runner only supports select statements and you'll get a KNTA-10648 error. If using PL-SQL change 'yyyymmdd' in the output file name to today's date.

spool c:\disabled_users_by_security_group_yyyymmdd.txt
set linesize 1000

select distinct
       nu.end_date,
       nu.username,
       nu.first_name,
       nu.last_name,
       nsg.security_group_name
from KNTA_USER_SECURITY nus,
     KNTA_USERS nu,
     KNTA_SECURITY_GROUPS nsg
where nsg.enabled_flag = 'Y'
  and nu.user_id = nus.user_id
  and nsg.security_group_id = nus.security_group_id
  and nu.end_date is not NULL    /* List disabled users only */
  order by
    nu.last_name, nu.first_name, nsg.security_group_name
    -- or
    -- nsg.security_group_name, nu.last_name, nu.first_name

spool off

END_DATE                  USERNAME      FIRST_NAME  LAST_NAME     SECURITY_GROUP_NAME
3/1/2005 12:00:00.000 AM  fharvey       Frank       Harvey        Inspection DOD
3/1/2005 12:00:00.000 AM  fharvey       Frank       Harvey        Inspection Inwards
10/2/2004 8:16:24.000 AM  jfaulks       Johann      Faulks        Stock Managers
...

To extract the output, click on the [Open as Text] button.

This opens a text window from which you can copy and paste.

Download

KintanaScripts.zip (64 Kb)