Kintana Script : Restrict User Access to Request Type

Purpose

If you want to restrict user access to a request type this script can help ensure that only the users you intend get to use the request.

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:\user_acces_to_a_request_type_yyyymmdd.txt
set linesize 1000

select distinct
     -- nsg.security_group_name, nsg.description, nsg.enabled_flag,
      nu.username, nu.first_name, nu.last_name
from KNTA_ENTITY_RESTRICTIONS ner, KCRT_REQUEST_TYPES crt,
     KNTA_SECURITY_GROUPS nsg,
     KNTA_USERS nu,
     KNTA_USER_SECURITY nus
where ner.restriction_type = 'SG_RQT'
  and ner.child_value = crt.request_type_id
  and crt.request_type_name = 'My Request Type'   -- Specify your request type
  and ner.parent_value = nsg.security_group_id
  and nu.user_id = nus.user_id
  and nus.security_group_id = nsg.security_group_id
  and nu.end_date is NULL
  and nsg.enabled_flag = 'Y'
  order by nu.last_name -- ,nsg.security_group_name

spool off

USERNAME      FIRST_NAME  LAST_NAME
jamesarden    James       Arden
johnford      John        Ford
harveyfrank   Harvey      Frank
annettegould  Annnette    Gould
megparker     Margaret    Parker
...

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

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

spool c:\security_groups_for_request_type_yyyymmdd.txt
set linesize 1000

select nsg.security_group_name, nsg.description, nsg.enabled_flag
from KNTA_ENTITY_RESTRICTIONS ner, KCRT_REQUEST_TYPES crt,
KNTA_SECURITY_GROUPS nsg
where ner.restriction_type = 'SG_RQT'
  and ner.child_value = crt.request_type_id
  and crt.request_type_name = 'My Request Type'   -- Specify your request type
  and ner.parent_value = nsg.security_group_id
order by nsg.security_group_name

spool off

SECURITY_GROUP_NAME, DESCRIPTION                                    ENABLED_FLAG
Inspection DOD       DOD Certified Inspections                      Y
Inspection Group     General non-military inspectors                Y
Stock Managers       Level 1 authority                              Y
Wire Cage            Authorized for secure stock inventory points   Y
...

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)