Creating obfuscated views for Synergetic data integration

Customers that aren't using all of Intellischool's data warehousing capabilities may wish to redact information being stored on Intellischool's platform in accordance with data management best practices.

The DDL script below can be run by schools that wish to obfuscate fields that they would prefer not be visible to Intellischool's sync process.

 

Overview

This process will:

• Create a shallow-copy database on your Synergetic SQL server that will make data accessible to the Intellischool agent without duplicating data storage (i.e. it won't take up double the disk space of your Synergetic database); and
• Create a series of views from existing Synergetic tables/views, and give you the option to provide NULL values for fields you do not want exposed.

Permissions

This process does not specify a permissions structure. As schools configure Synergetic differently, you will need to create an appropriate permissions such that the Intellischool sync process can read what is required.

We recommend that an intellischool user be created on your SQL server that has:

• CONNECT to the shallow-copy database
• SELECT on all objects in the dbo schema of the shallow-copy database
• REVOKE CONNECT to your production Synergetic database
• SELECT on the source objects in your Synergetic database

Because the shallow-copy database is acting as a proxy to your production Synergetic database, the intellischool user will still need SELECT permissions on your Synergetic database - however by revoking CONNECT permissions the user will never be able to connect to your Synergetic database directly (and cannot circumvent the obfuscation of fields in your shallow-copy database).

 

---

1. Create the shallow-copy database

Create a new database on your Synergetic server. By default, we call the database "Intellischool", but you are welcome to call it something different.

Take care to ensure that you set appropriate paths for your database and log files.

2. Build your custom DDL

As there are many variants of Synergetic out there, we can't provide a static script that can be universally run across any school. Instead, you will create your own version - customised to your Synergetic environment - using the script below.

Prior to executing the script:

• ensure that you update the source database name to match the name of your Synergetic production database;
• if you used a database name other than "Intellischool" in step 1, ensure that you update line 2 of the script; and
• remove any views that you don't want accessible to Intellischool - if you're not sure what is required for your particular implementation, refer to our Synergetic Schema Map.

declare @source_db_name varchar(100) = 'Synergetic_LOC_SCL_PRD'
declare @target_db_name varchar(100) = 'Intellischool'


select
    1,
    'use ' + @target_db_name + ';'

select
    2,
    cast(
      cast('create view ' as varchar(max)) + 
      cast(@target_db_name as varchar(max)) + 
      cast('.dbo.' as varchar(max)) +
      cast(t.table_name as varchar(max)) + 
      cast(' as select ' as varchar(max)) +
      (
        select
          cast(
            string_agg(
             case
               when c.data_type in( 'int', 'varchar', 'nvarchar', 'nchar', 'char', 'decimal', 'numeric')
                 then 'cast('
               else ''
             end + 
             cast('[' as varchar(max)) +
             cast(c.column_name as varchar(max)) +
             cast(']' as varchar(max)) +
             case
               when c.data_type in( 'int', 'varchar', 'nvarchar', 'nchar', 'char', 'decimal', 'numeric')
                 then ' as '
               else ''
             end + 
             case
               when data_type in( 'varchar', 'nvarchar', 'nchar', 'char') and character_maximum_length is not null 
                 then DATA_TYPE + '(' + 
                 case
                   when character_maximum_length = -1 
                     then 'max'
                   else cast(character_maximum_length as varchar(max))
                 end + '))'
               when data_type in('decimal', 'numeric') 
                 then DATA_TYPE + '(' + cast(NUMERIC_PRECISION_RADIX as varchar(max)) + cast(',' AS VARCHAR(MAX))+ cast(NUMERIC_SCALE as varchar(max)) + '))'
               when data_type in('int') 
                 then DATA_TYPE + ')'
               else ''
             end + 
             ' as ' + 
             cast('[' as varchar(max)) +
             cast(c.column_name as varchar(max)) +
             cast(']' as varchar(max))
           ,
             '\n,'
           ) as varchar(max)
         )
        from information_schema.COLUMNS c
        where
          t.TABLE_CATALOG = c.TABLE_CATALOG and 
          t.TABLE_NAME = c.TABLE_NAME and 
          t.TABLE_SCHEMA = c.TABLE_SCHEMA
      ) as varchar(max)
    )
    + cast(' from ' as varchar(max))
    + cast('[' + @source_db_name + ']' as varchar(max))
    + cast('.dbo.' as varchar(max))
    + cast(t.TABLE_NAME as varchar(max))
    + ';'
    sql_view
from
    information_schema.tables t
where
    t.table_catalog = @source_db_name
    and t.table_schema = 'dbo'
    and t.table_name in (
      'AbsenceEvents'                       -- potentially sensitive
    , 'Absences'                            -- potentially sensitive
    , 'AttendanceMaster'
    , 'Attendances'                         -- potentially sensitive
    , 'FileSemesters'
    , 'FileSemesterYearLevels'
    , 'LearningAreas'
    , 'luAbsencePeriod'
    , 'luAbsenceReason'
    , 'luAbsenceType'
    , 'luCampus'
    , 'luFileType'
    , 'luForm'
    , 'luGender'
    , 'luHouse'
    , 'luTimetableGroup'
    , 'luReportResultGroup'
    , 'luReportResultType'
    , 'luReportResultTypeMaskExpanded'
    , 'luYearLevel'
    , 'PastStudentClasses'
    , 'pvStaffJobPositions'                 -- personal
    , 'pvStudentAssessmentResultAreas'
    , 'pvStudentNamesAll'                   -- personal
    , 'pvTimetableDefinitionAll'
    , 'StudentClasses'
    , 'Students'                            -- sensitive
    , 'StudentSemester'
    , 'StudentYears'
    , 'SubjectAssessmentAreas'
    , 'SubjectAssessmentCurriculum'
    , 'SubjectAssessmentVELSDomains'
    , 'SubjectAssessmentVELSStrands'
    , 'SubjectAssessmentVELSStrands'
    , 'SubjectClasses'
    , 'SubjectClassesStaff'
    , 'tAttendances'                        -- potentially sensitive
    , 'Timetable'
    , 'TimetableConfig'
    , 'TimetableDefinition'
    , 'vStaff'                              -- sensitive
    , 'vStaffJobPositions'                  -- personal
    , 'vStudentClasses'
    , 'vStudents'                           -- sensitive
    , 'vStudentTimetableClasses'
    , 'vStudentTimetablePeriods'
    , 'vSubjectClassesStaff'
    )

3. Modify your customised DDL as required

Upon completion of step 2, you should have a series of SQL statements that will create the shallow-copy views of your Synergetic database.

Depending on your obfuscation requirements, you may change the values of fields to NULL to prevent the Intellischool sync process from being able to view data.

Once your DDL has been modified to your satisfaction, you can then execute it to create the shallow-copy views on your SQL server.

4. Provide your connection details to Intellischool

Once completed, please provide Intellischool with the:

• Synergetic shallow-copy database name;
• SQL credentials; and
  
• Hostname / IP address and port (and SQL server instance name if necessary).

It's best to use a secure means to provide these details. Our team recommends using OneTimeSecret, but you can use any other secure credential-sharing service that you feel comfortable with.