This article describes patterns and SQL objects for limiting end-user access to student data in applications that use the Analytics Middle Tier.

Overview

The Family Educational Rights and Privacy Act (FERPA) outlines certain data privacy rights for students plus the rules by which student data can be shared to anyone other than the student or parent. Systems that utilize the Analytics Middle Tier must provide appropriate data security so that school officials, parents, and so forth are only authorized to view "need-to-know" records. What is appropriate may vary from state to state and district to district.

The Analytics Middle Tier provides two options for managing authorization to row-level (i.e., student-level) data sourced from the ODS. The provided options are certainly not the only approaches; careful evaluation will be needed to determine what is most appropriate for any given business intelligence solution and implementation.

Roles / Scopes of Data Access

In K–12 scenarios, several common roles clearly require different degrees of authorization to view student data:

• Superintendents see data for all students in their district.
• Principals see data for all students in their school.
• Teachers see data for all students in their classes.
• Parents see data for all their children.
• Students see only data for themselves.

Real-world usage might not map job titles to data authorization levels in such a simple manner. There may be district employees other than superintendents who need access to all students. An Assistant Principal might take the lead on checking an Early Warning system. Rather than speaking about roles, the Analytics Middle Tier views define these access scopes:

• District
• School
• Section

Other scopes can be imagined but are not currently supported in the as-shipped solution (e.g., counselors assigned to cohorts of students, students viewing their own information).

Option 1. Dynamic Authorization Model

This model is particularly tuned to the capabilities of Microsoft's Tabular Data Model technology and may be useful elsewhere. Here we will have a User view representing all the staff listed in the ODS. It will be assumed that the current user name will be the user's e-mail address; thus, the analytics engine determines the scope of permissions starting from the current user's username.

The rls_UserAuthorization View maps each user to a scope of access. The "StudentPermission" shown in the view above is a place holder for future Student scope; the as-shipped implementation only supports Section, School, and District scopes. A teacher will have one record for every section requiring access; a school administrator will have one record for every school; and a district administrator will have a single record for the district. Sample rows for two teachers with two and one section respectively, two school administrators, and a district administrator:

The rls_StudentDataAuthorization View brings together a student with her school and section enrollments by date. When the data modeler builds a security mode with these views, an individual user can thus access only student data relevant to the time period in which the student is enrolled in the section or school.

Option 2. Static Authorization Model

In this model a teacher can access a student's data in any section and at any date. Likewise, a school administrator is able to access a student's information for dates prior to enrollment at the administrator's school (if such data are commingled in the same ODS).

The rls_UserStudentDataAuthorization View will return significantly more rows than the dynamic views, though this will present only a moderate storage burden for the analytics database. For data modelers who do not require the more detailed restrictions of the dynamic authorization model described above, this model may be simpler to use.