Kintana Script : Security Groups and Users

Purpose

The yearly audit visitation is imminent and suddenly the zero base review of
who should be in your security groups that you've been putting off for some
time has acquired a certain urgency.
This script will ease the process.

And, a bonus, a small script to pull the members of a security group.
Useful if you're planning on deleting or retiring a security group.

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.

-- List Users in Security Groups
spool c:\users_by_security_group_yyyymmdd.txt
set linesize 1000

select nsg.security_group_name,
       nu.username, nu.first_name, nu.last_name
       -- nu.email_address,
from KNTA_USER_SECURITY nus, KNTA_USERS nu, KNTA_SECURITY_GROUPS nsg
where nus.user_id = nu.user_id
  and nus.security_group_id = nsg.security_group_id
  and nu.end_date is NULL                    /* List active users only */
order by
  nsg.security_group_name, nu.last_name
  -- or
  --nu.last_name, nu.first_name, nsg.security_group_name

spool off

SECURITY_GROUP_NAME, USERNAME      FIRST_NAME  LAST_NAME
Inspection DOD       johnford      John        Ford
Inspection DOD       megparker     Margaret    Parker
Stock Managers       jamesarden    James       Arden
Stock Managers       annettegould  Annnette    Gould
Stock Managers       harveyfrank   Harvey      Frank
...

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

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

-- List Users in specified Security Group
spool c:\users_for_security_group_yyyymmdd.txt
set linesize 1000

select nsg.security_group_name,
       nu.username, nu.first_name, nu.last_name
       -- ,nu.email_address
       ,nu.end_date                  /* [NULL] indicates active user */
from KNTA_USER_SECURITY nus, KNTA_USERS nu, KNTA_SECURITY_GROUPS nsg
where
       nsg.security_group_name = 'Stock Managers' /* Specify your sg */
   and nus.user_id = nu.user_id
   and nus.security_group_id = nsg.security_group_id
 --and nu.end_date is NULL                /* List active users only */
 order by
   nu.last_name, nu.first_name, nsg.security_group_name

spool off

SECURITY_GROUP_NAME, USERNAME      FIRST_NAME  LAST_NAME END_DATE
Stock Managers       jamesarden    James       Arden     [NULL]
Stock Managers       harveyfrank   Harvey      Frank     [NULL]
Stock Managers       annettegould  Annnette    Gould     [NULL]
Stock Managers       arleneMante   Arlene      Mante     Jun 6, 2004
...

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)