CHRS Knowledge Base

Query Policy Guide

Updated on

REVISION CONTROL

Date  By  Action  Section
Author: CHRS Program Team
02/2021 L. Negrete New Document
03/2021 J. Wenrick Edits and Update
Added section for creating Public Queries		 5.0
11/2021 Program Team Final Edits to publish All
02/07/22 Program Team Revised document title; addressed Wave 2 campus inquiries;
Revised Appendix		 All

Review / Approval History

Review Date  Reviewed By  Action (Reviewed, Recommended or Approved) 
2/08/22
SWHR informed - Informed
3/24/22
Steering Committee – Approved
5/25/2023 J. Wenrick Approved
Table of Contents

1.0 Introduction

1.1 Query Access to Level 1 Data 

1.2 Query / Security Testing Environment 

2.0 CHRS Query Naming Conventions 

2.1 Naming Convention for Queries 

2.2 Query Description 

3.0 CHRS Public Query Process 

Appendix 

Query Trees

Security Roles

1.0 Introduction

This document provides the guidelines to be followed for queries in CHRS.

It is the responsibility of each campus to ensure that all individuals on their campus who are granted access to develop queries are properly trained, understand, and follow these guidelines.

The Common Human Resources System (CHRS) Query Policy Guide documents the security access, naming standards, and environment.

  1. Security access – defines the process of granting security access to campus users
  2. Naming standards – provides naming standards
  3. Environment – documents where queries will be developed and tested before it is deployed into production
  4. Public query process – process to have a query developed and made public for all campuses

Private Queries

For the transition from 9.0 to 9.2, campuses are also expected to review their current inventory to eliminate private queries that can be replaced by reports provided in either CHRS Baseline or in the CHRS Reporting environment. For queries that will not be provided by CHRS baseline or cannot be provided through the reporting system, campuses will need to create those 9.0 queries in CHRS. Queries will not be migrated from 9.0 to 9.2.

It is recognized that campuses may have unique report requirements that cannot be met by CHRS Baseline, prepared reports in CHRS Reporting, or by utilizing the ad hoc capability in CHRS Reporting. As such, each campus may designate power users to be assigned PS Query creation privilege. It is expected that the subset of individuals assigned this privilege will be kept to a minimum.

Campuses can create and save private queries, and the creator can then share the private query with other users on their campus as needed.

Public Queries

If there is a need to add what is believed to be missing public queries, refer to the exception request process in the Appendix.

1. Security Access

  • CHRS will provide each campus security administrator the ability to grant query security access to end-users.
  • CHRS will provide two types of query access:
  1. RUN ONLY ACCESS: Run only query access - Users can run delivered queries
  2. QUERY DEVELOPMENT ACCESS: Access is provided to a control group at each campus that enables access to create private queries. Private queries will be allowed to be shared with other users at the same campus. For queries that need to be made public, campuses can submit a ticket via ServiceNow for the request.

The following are the required procedures campuses shall follow to provide query access to users:

  1. Each query should be reviewed to ensure that CHRS Baseline reports or the CHRS Reporting tool cannot provide the report in question.
  2. Grant query development capabilities only to technical users and select power users that know how to create queries.
  3. All queries need to be scoped out. Do NOT use the query tool to perform complete table dumps. Un-scoped queries are executed against large tables, so there is a considerable risk of having an out-of-control or runaway query which could severely impact system performance.
  4. Validate that end-user query developers have had query training on the Query tool. The training should include warnings about Cartesian products, recommendations on tables that may have all the data they need (to avoid joins), and information on which tables contain commonly sought information.
  5. Users should also be informed about specific query behaviors – i.e., if the query times out, do not resubmit it. Request that a technical resource verify that the query is not still running on the Unix server. If it is, this is a runaway query, report the issue to CMS (Common Management System) via ServiceNow.
  6. The designated Query Administrator needs to determine what restrictions will be placed on the user’s permission list for access to query. Users can be limited to just running queries.
  7. The Query Administrator needs to review and determine which query tree(s) should be assigned to the user.

1.1 Query Access to Level 1 Data

Per the CSU System-wide Information Security Policy access to information assets containing protected data must be based on the principles of need-to-know and least privilege.

  • Examples of Level 1 – Confidential information include but are not limited to:
    1. Passwords or credentials that grant access to Level 1 and Level 2 data
    2. PINs (Personal Identification Numbers)
    3. Birth date combined with last four digits of SSN and name
    4. Credit card numbers with cardholder name
    5. Tax ID with name
    6. Driver's license number, state identification card, and other forms of national or international identification (such as passports, visas, etc.) in combination with name
    7. Social Security number and name
    8. Health insurance information
    9. Medical records related to an individual
    10. Psychological Counseling records related to an individual
    11. Bank account or debit card information in combination with any required security code, access code, or password that would permit access to an individual's financial account
    12. Biometric information
    13. Electronic or digitized signatures
    14. Private key (digital certificate)
    15. Law enforcement personnel records
    16. Criminal background check results
  • Examples of Level 2 – Internal Use information include but are not limited to:
    • Identity Validation Keys (name with)
      • Birth date (full: mm-dd-yy)
      • Birth date (partial: mm-dd only)
    • Photo (taken for identification purposes)
    • Student Information-Educational Records not defined as "directory" information, typically:
      • Grades
      • Courses taken
      • Schedule
      • Test Scores
      • Advising records
      • Educational services received
      • Disciplinary actions
      • Student photo
    • Library circulation information.
    • Trade secrets or intellectual property such as research activities
    • Location of critical assets
    • Location of Level 1 or Level 2 Data
    • Licensed software
    • Vulnerability/security information related to a campus or system
    • Campus attorney-client communications
    • Employee Information
      • Employee net salary
      • Home address
      • Personal telephone numbers
      • Personal email address
      • Payment History
      • Employee evaluations
      • Pre-employment background investigations
      • Mother's maiden name
      • Race and ethnicity
      • Parents' and other family members' names
      • Birthplace (City, State, Country)
      • Gender
      • Marital Status
      • Physical description
      • Other

If the request is for access to Level 1 or Level 2 records, the Campus Security Administrator must review and grant the specific security role.

1.2 Query / Security Testing Environment

HACHRSBX will be available for query and security development. This environment will be masked. It is recommended for campuses to use this environment for complex query development and tested for performance. Campuses will have the same security access as they do in production. If additional security is required, a ServiceNow ticket must be submitted to Chancellor’s Office Information Security team.

2.0 CHRS Query Naming Conventions

2.1 Naming Convention for Queries

The PeopleSoft Query Designer allows up to 30 bytes for Query names. The following naming conventions apply to all CHRS custom private and public queries:

Name Component  R  Description  Valid Values 
CHRS CO Code R ‘CSU’ for CHRS System-wide or 3 Letter
Campus Abbreviation as defined by the
CHRS Standardization group		 CSU or Campus specific abbreviation
(i.e., FRE = Fresno)  HR 
Standardization CHRS Company Code
UNDERSCORE R Separation _
HR PRODUCT R Two/Three-character Product Abbreviation See HR Product Abbreviations table below
UNDERSCORE R Separation between words _
QUERY NAME R Descriptive name for the query Developer Defined

HR Product Abbreviations

HR Product Abbreviations
AM Absence Management LCD Labor Cost Distribution TAE Temporary Academic Employment
BB Base Benefits PM Position Management TL Time and Labor
BA Benefits Administration RS Recruiting Solutions WA Workforce Administration

Examples:

CSU_BB_???_???_???? CSU Baseline Public for Base Benefits

FRE_AM_?????_?????_????? Fresno Private for Absence Management

Additional Naming Considerations

Following are the required procedures when creating the query name:

  • Include the area that owns the query (AM, LCD, WA, etc.)
  • Create an abbreviated description of the query with underscores between words should follow.
  • Be as descriptive as possible

When queries are named with the above prescribed naming convention, it allows them to be easily sorted by campus and module, which is helpful when searching for an existing query when the query name is unknown. It also assists functional areas during an upgrade or patch when questions arise regarding campus queries. In some cases, the information a user needs may already exist in an existing query, which can be located on this list. This also helps to prevent duplicate queries and duplicate work by the end-users.

2.2 Query Description

When saving queries, use the Properties hyperlink on the bottom of the screen to record details about the query in the Query Definition box; it is very important to provide a concise, but informative description of the query. This will help users understand the purpose of the query and prevent the creation of duplicate queries. Include the creator’s name and any subsequent changes in the area for reference. For example, [11/16/21 LJones, query captures a list of all active employees in Academic Affairs.]

Query Description Screenshot

3.0 CHRS Public Query Process

After following the above steps to test and create a private query in production and it is determined that a query should be made public for all CSU campuses, below is the process to make this request (NOTE: Only Chancellor’s Office support will have the ability to create and save public queries):

  • Determine if this query can be run in CHRS Reporting
    • Are all the data elements available?
    • Does this need to be run in real-time, or can it be delayed one day so the data would exist in CHRS Reporting after the nightly refresh?
    • Will multiple campuses benefit from this query? Or is this a campus specific query?
  • If the query is needed by all campuses and cannot be replicated or run in CHRS Reporting for any reason, a ServiceNow ticket should be opened to request the query to be moved to the public folder
  • The ticket should include:
    • The name of the private query that has been developed and tested should be included in the ticket

Appendix

Query Trees

The PeopleSoft Tree Manager allows 18 bytes for tree names. Query trees delivered by PeopleSoft follow the naming convention QUERY_<name>. CSU query trees will follow the same naming convention except they will include the CSU_QRY_TREE_ prefix followed by category. All records containing SSN field have been moved to Level 1 Query Trees.

Tree Name  Category  Effective Date 
CSU_QRY_TREE_ACA DEFAULT 1/1/2001
CSU_QRY_TREE_AM GLOBAL_PAYROLL 1/1/1900
CSU_QRY_TREE_BEN BENEFITS 1/1/1900
CSU_QRY_TREE_BN_L1 BENEFITS 1/1/1900
CSU_QRY_TREE_ECM HR 1/1/1900
CSU_QRY_TREE_EO TOOLS 1/1/1900
CSU_QRY_TREE_EPERF HR 1/1/1900
CSU_QRY_TREE_FSA FSA 1/1/1900
CSU_QRY_TREE_GP GLOBAL_PAYROLL 1/1/1900
CSU_QRY_TREE_GPADS GLOBAL_PAYROLL 1/1/1900
CSU_QRY_TREE_GPPG GLOBAL_PAYROLL 1/1/1900
CSU_QRY_TREE_GPUS GLOBAL PAYROLL 1/1/2004
CSU_QRY_TREE_HCSC DEFAULT 1/1/1900
CSU_QRY_TREE_HR HR 1/1/1900
CSU_QRY_TREE_HR_L1 HR 1/1/1900
CSU_QRY_TREE_JPM HR 1/1/1900
CSU_QRY_TREE_PAY PAYROLL 1/1/1900
CSU_QRY_TREE_PI PAYROLL_INTERFACE 1/1/1900
CSU_QRY_TREE_PIP DEFAULT 1/1/1900
CSU_QRY_TREE_PM POSITION 1/1/1900
CSU_QRY_TREE_PT TOOLS 1/1/1900
CSU_QRY_TREE_RECRT HR 1/1/1900
CSU_QRY_TREE_SCH SCHEDULING 1/1/1900
CSU_QRY_TREE_TF DEFAULT 1/1/1901
CSU_QRY_TREE_TL TIME_AND_LABOR 1/1/1900
CSU_QRY_TREE_WF TOOLS 1/1/1990
CSU_QRY_TRE_PAY_L1 PAYROLL 1/1/1900
CSU_QRY_TRE_PIP_L1 DEFAULT 1/1/1900
CSU_QRY_TR_GPPG_L1 GLOBAL_PAYROLL 1/1/1900
CSU_QRY_TR_GPUS_L1 GLOBAL PAYROLL 1/1/2004
CSU_QRY_T_GPADS_L1 GLOBAL_PAYROLL 1/1/1900
CSU_QRY_T_RECRT_L1 HR 1/1/1900

Security Roles that Grant Access to Query Tree Access Groups

Access to the CSU Query Trees is delivered on the roles listed below. Users will need to be assigned one or more roles to query the tables.

Role Name  Descr  Descr 
CHR_Query_Tree_All Access to All Query Access Grp This role is typically given to someone that has a business need to see all query trees.
CHR_Query_Tree_Ben Benefits Query Access Groups This role is typically given to someone that has a business need to see the Benefits tables minus tables containing SSN.
CHR_Query_Tree_Ben_L1 Benefits Query Access Grp SSN This role is typically given to someone that has a business need to see the Benefits records including records with SSN.
CHR_Query_Tree_HR HR Query Access Groups This role is typically given to someone that has a business need to see HR Query Tree Information.
This role is given to a select few groups of users.
CHR_Query_Tree_HR_L1 HR Query Access Groups SSN This role is typically given to someone that has a business need to see HR Query Tree Information with SSN data.
This role is given to a select few groups of users.
CHR_Query_Tree_LCD LCD Query Access Groups This role is typically given to someone that has a business need to see Time and Labor Query Trees.
CHR_Query_Tree_No_PayBen QueryAccessGrp w/o Payroll/Ben This role is typically given to someone that has a business need to see all query trees with the exception of Payroll Trees and Benefits Trees.
CHR_Query_Tree_PM Query Access Grp Position Mgt This role is typically given to someone that has a business need to see Position Management Query Tree.
CHR_Query_Tree_REC Query Access Grp Recruitment This role is typically given to someone that has a business need to see the Recruitment Query Tree.
CHR_Query_Tree_REC_L1 Query Access Grp Rec w/ SSN This role is typically given to someone that has a business need to see the Recruitment Query Tree.
CHR_Query_Tree_TF Query Access Temp
Faculty		 This role is typically given to someone that has a business need to see Temp Faculty tables.
CHR_Query_Tree_TL_AM Query Access Grp TL /
AM		 This role is typically given to someone that has a business need to see both Time and Labor and Absence Management Query Trees.

End of Article

0 Comments

Add your comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

Previous Article (job aid) Public Queries
Next Article (job aid) Solution Design Scope Summary

Application Development

9.2 Configuration Guides 0
Public Queries 1
Functionality 2
Enhancements/System Application Support 3

Recently Updated Articles

More Recently Updated Articles
Do you need an article? Contact Us