Blog | Level Data

State Data Validation Suite Security Explained

Written by Joel Lepp | October 8, 2019 at 1:18 PM

Level Data applications integrate tightly with PowerSchool. These include page enhancement applications like RealTime Validation, and full page applications like State Data Validation and Validation Reporting.

Each of these applications has options available to help District Administrators apply the appropriate security options, as per their district policies and following PowerSchool security guidelines.

This document outline how Level Data’s PowerSchool applications integrate with PowerSchool’s security mechanisms in order to properly secure the applications.

Plugins

Level Data applications are accessed through the use of three plugins:

  • Level Data Core: Provides access to Level Data’s services and an interface to PowerSchool districts to allow access to Level Data applications.
  • Level Data Core Schema: Contains the database schemas for the new tables being used by Level Data applications.
  • Level Data Core Schema Permissions: Maps API level access to Level Data application specific endpoints, to apply access rights to tables installed by Level Data Core Schema plugin.

The preferred method of installing the three plugins is via the Plugin Management Dashboard, which can be found under System Settings, within the Systems menu in PowerSchool.

Manual installation is still possible, and a previous version of the plugin may have been installed manually on your PowerSchool System (via the custom/web_root directory on the PowerSchool server or Custom Page Management). For information on how to manually remove old plugin versions and installing the latest plugin, please see the “Important Links” section of this document. However, contact Level Data if you require assistance with plugin removal or installation.

Security

Level Data’s applications respect PowerSchool’s two main security mechanisms: Page Level Security and Field Level Security. Since applications might use one or a mix of both security mechanisms, once installed all Level Data applications should be considered insecure by default.

It is highly recommended that once installed, PowerSchool System Administrator’s go through the set up for each of these security mechanisms, to ensure that access to the applications and data, is appropriately set throughout the system.

Level Data’s recommended approach is to implement User Access Role based security across the PowerSchool system, while in conjunction, locking down Groups. Allowing the System Administrator to open access to pages or fields as needed, and ensuring that no one is starting out with more access than necessary.

PowerSchool Configuration

To review your current Groups, from the PS start page:

  1. Click on System within the main navigation menu
  2. Under the Security click on Groups
  3. Click on any of the groups available to access its Edit Group page.

To review what users belong to specific Groups, from the PS start page:

  1. Click on System within the main navigation menu
  2. Under the Security click on User by Group

To view and modify the Group/Role a user is assigned to, from the PS start page:

  1. Click on the Staff tab
  2. Select A Staff Member
  3. Click on Security Settings within the main navigation menu
  4. Click on the Admin Access and Roles tab
  5. Modify the groups that the user is assigned from the Default Group dropdown and/or set the roles for the user (per school if desired) through the Roles and Schools section

To view and modify Field Level Security, from the PS start page:

  1. Click on System within the main navigation menu
  2. Under the Security click on Field Level Security
  3. Find the fields that need to have their security set and edit them
    • If you cannot find the field that you are looking to set security for, you can click on the Add button, which is available at the top-right of the Field Level Security page and add the field if available within PowerSchool’s list of managed fields.

How It Works

State Data Validation (SDV)

State Data Validation works by injecting Level Data SQL queries, that have been stored on the PowerSchool database, through the PowerSchool “tlist_sql” HTML tag being used on the Core.HMTL file.

Field Level Security is respected by utilizing the list of managed fields and their assigned User Access Roles to determine what rights a user has to a field. This is accomplished via the “security.canviewfield” HTML tag, which verifies that the current user has, at the very least, view access to a field.

If a user does not have access to at least one of the fields being returned in a rule, they will be denied access. For information on how to configure Field Level Security and the User Access Roles assigned to fields, see the PowerSchool Configuration section of this document.

This will not stop a rule from displaying the total count of records that might be getting flagged by a rule:

However, it will stop the actual rule from displaying any data, and will just display an error message:

 

In addition, Page Level Security is respected by managing who has write access to the Level Data SQL queries stored within the PowerSchool database, see Queries section for more information.

Page Level Security is also being used to map API page level access to the SDV specific endpoint, /admin/leveldata/validate.html, to determine if a user should be able to see the corresponding application link within the “Level Data Apps” menu.

NOTE: This does not mean that the actual application is inaccessible to the user, just that they will not be able to see/use the direct link to the application.

This can be set by:

  1. Clicking on the Plugin Details link on the Level Data Apps menu
  2. Selecting the link corresponding to the State Validation application, under the Page Level Security section
  3. Setting the appropriate level of access for the district’s managed Groups

For information on how to configure configure Groups and User Access Roles, see the PowerSchool Configuration section of this document.

Validation Reporting (Beta)

Validation Reporting (Beta) works by injecting Level Data SQL queries, that have been stored on the PowerSchool database, through the PowerSchool “tlist_sql” HTML tag being used on the Core.HMTL file.

Field Level Security is respected by utilizing the list of managed fields and their assigned User Access Roles to determine what rights a user has to a field. This is accomplished via the “security.canviewfield” HTML tag, which verifies that the current user has, at the very least, view access to a field. If a user does not have access to at least one of the fields being returned in a report, they will be denied access and the KPI report will fail completely. For information on how to configure Field Level Security and the User Access Roles assigned to fields, see the PowerSchool Configuration section of this document.

 

In addition, Page Level Security is respected by managing who has write access to the Level Data SQL queries stored within the PowerSchool database, see Queries section for more information.

Page Level Security is also being used to map API page level access to the KPI specific endpoint, /admin/leveldata/kpi.html, to determine if a user should be able to see the corresponding application link within the “Level Data Apps” menu.

NOTE: This does not mean that the actual application is inaccessible to the user, just that they will not be able to see/use the direct link to the application.

This can be set by:

  1. Clicking on the Plugin Details link on the Level Data Apps menu
  2. Selecting the link corresponding to the KPI application, under the Page Level Security section
  3. Setting the appropriate level of access for the district’s managed Groups

For information on how to configure configure Groups and User Access Roles, see the PowerSchool Configuration section of this document.

Additional Settings

For Level Data applications to fully comply with Page Level Security, the additional endpoints must also be reviewed and added/removed from Groups/User Access Roles in PowerSchool:

/admin/home.html

  • This is used to map who can view the Settings link within the Level Data Apps menu and the U_SQL table in PowerSchool.
  • This can be set by:
    1. Going to the Groups page found within the System Administrator page, under the Security section
    2. Selecting a group
    3. Clicking on Overridden Page Permissions
    4. Setting the appropriate permission level for the needed page

/admin/leveldata/settings.html

  • This is used to determine what users should be able to make changes within the Settings link within the Level Data Apps menu
  • This can be set by:
    1. Clicking on the Plugin Details link on the Level Data Apps menu
    2. Selecting the link corresponding to the Settings application, under the Page Level Security section
    3. Setting the appropriate level of access for the district’s managed Groups

/admin/leveldata/core.html

  • This will control if queries in general should be able to be run against PowerSchool.
  • NOTE: Setting this endpoint to an access level of “None” will disable all Level Data applications for the users within that group. This is due to the fact that the Core.HTML file is used to run queries through the use of tlist_sql tags, and would not be accessible.
  • This can be set by:
    1. Clicking on the Plugin Details link on the Level Data Apps menu
    2. Selecting the link corresponding to the Query Execution link, under the Page Level Security section
    3. Setting the appropriate level of access for the district’s managed Groups

For information on how to configure configure Groups and User Access Roles, see the PowerSchool Configuration section of this document.

Queries

For State Validation and KPI, an additional validation check is verifying that queries sent to PowerSchool for processing, match the corresponding query in Level Data’s code base before any actual processing is done.

To do this, all relevant Level Data queries will be stored within the U_SQL table. These queries will be wrapped in SELECT statements with an additional WHERE clause added to the end, that lists all of the table and column pairings that are being returned by the query. After which, the query will be hashed and both the query and hashed value will be added to the table.

Since rules will be stored within a district’s instance of PowerSchool, it is imperative that only the correct individuals have write access to the U_SQL endpoint (see steps listed below). Otherwise, any user will have access to the U_SQL table and be able to overwrite any of Level Data’s rules and run arbitrary queries.

When a user logs into PowerSchool, a check will be done to verify that the list of hashed query values in U_SQL match up with the hashed query values from the Level Data’s code base.

If a mismatch is found between PowerSchool and Level Data, whether due to a change having been done within the U_SQL table or the Level Data code base itself, then none of the rules/reports will be loaded:

And the user will get a “Updates Available” message within the “Level Data Apps” menu bar in PowerSchool.

 

If Page Level Security has been used to place constraints on the table, then only users that have write access to the “U_SQL” table will be able to re-sync the queries and hashes between systems.

The following is what users, who do not have access to make modifications to U_SQL, will see if they try to download the updates:

 

In order to set Page Level Security for the U_SQL endpoint, /admin/leveldata/db/u_sql.html:

  1. Click on the Plugin Details link on the Level Data Apps menu
  2. Select the link corresponding to the Query Modification link, under the Page Level Security section
  3. Setting the appropriate level of access for the district’s managed Groups

For information on how to configure configure Groups and User Access Roles, see the PowerSchool Configuration section of this document.

Appendix

  • Queries - A request made to the PowerSchool Oracle database, written in the SQL language
  • PowerSchool HTML Tag - The language used to write traditional PowerSchool pages, made up of standard HTML and PowerSchool tags.
  • Field Level Security - PowerSchool’s method of implementing access at the field level across the system.
  • Page Level Security - PowerSchool’s method of implementing access page level access across the system.

Useful Links
View full post