Using Lenel Data To Identify Compromised University Network

Using Lenel Data To Identify Compromised University Network IDs HELUG Conference 2017 Steve Cramer Columbia University Information Technology

Columbia University Network Security • Responsible for protecting: – Campus network – 80, 000+ user accounts • This includes identifying University Network IDs (UNI) that have been compromised. • Compromised = UNI used by a user other than its assigned owner. 2

How Are UNIs Compromised? • Keylogging viruses • Phishing attacks • Hacks of non-University application databases containing same credentials as University accounts. 3

What Do Bad Guys Want With UNIs? • Access to valuable University e-resources – Legal, scientific and business research services – Online books, newspapers and journals • Send spam using University email system • Steal personal information 4

Monitoring Is Important • Detecting UNI misuse requires constant vigilance by University Network Security Team. • After identifying, Security Team scrambles password and notifies individual’s school or department. 5

GULP Monitoring System • Processes logs from 14+ different campus services that require UNI authentication. • Records UNI, IP address and login location. • UNI probably compromised if same UNI logs within 72 hours from either: – 6+ locations – 2+ countries • About 300 UNIs compromised per year. 6

Next Step: Adding ID Card Data • Even with GULP, determining validity of UNI logins remained an imperfect science. • The solution: ID card transactions. • Card transactions provide strong evidence of a person’s actual location. • Matching them with network logins, we can better deduce which net logins are truly valid. 7

Project Approval • Public Safety and General Council approved after privacy concerns were met. – Only Panel IP to be stored in GULP, not exact reader location. – Data to be destroyed after 28 days. 8

Example 1: Without Lenel Data UNI Login IP Address Location op 2168 12/13/2016 14: 36 188. 4. 161. 236 Greece op 2168 12/13/2016 12: 17 188. 4. 161. 236 Greece op 2168 12/13/2016 10: 19 191. 101. 30. 254 Chile op 2168 12/13/2016 7: 06 138. 68. 105. 232 Germany • This is a pretty clear-cut case. Not possible to visit to Greece, Germany and Chile all within 5 hours. 9

Example 1: With Lenel Data UNI Login IP Address Location op 2168 12/13/2016 14: 36 188. 4. 161. 236 Greece op 2168 12/13/2016 14: 20 10. 241. 7. 254 Lenel NYC op 2168 12/13/2016 12: 17 188. 4. 161. 236 Greece op 2168 12/13/2016 10: 19 191. 101. 30. 254 Chile op 2168 12/13/2016 7: 06 138. 68. 105. 232 Germany op 2168 12/13/2016 0: 17 10. 244. 19. 252 Lenel NYC • Still, Lenel data makes it that much clearer. • Increased certainty is important when deciding to scramble a user’s password. 10

Example 2: Without Lenel Data UNI Login Date IP Address Location hj 2328 5/22/2017 18: 59 123. 144. 3. 204 China hj 2328 5/22/2017 10: 39 139. 59. 126. 234 Singapore • Possible to travel from Singapore to China between login times. • Are none, one or both valid? Can’t be sure. 11

Example 2: With Lenel Data UNI Login IP Address Location hj 2328 5/22/2017 19: 08 10. 112. 40. 10 Lenel NYC hj 2328 5/22/2017 18: 59 123. 144. 3. 204 China hj 2328 5/22/2017 14: 38 10. 112. 40. 10 Lenel NYC hj 2328 5/22/2017 10: 48 10. 112. 40. 10 Lenel NYC hj 2328 5/22/2017 10: 39 139. 59. 126. 234 Singapore hj 2328 5/22/2017 10: 24 10. 115. 226. 47 Lenel NYC • Lenel data provides the answer – both are invalid. 12

Solution Specifications • Goal: export Lenel access events to GULP database. • Requirements – – – Near real-time Only general access events Data destroyed after 28 days No negative impact on Lenel System Not too complicated • Export Columns – – – UNI BADGEID EVENTIME SERIALNUM PANELIP 13

Solution Options - Lenel • Lenel Data. Exchange – Analysis: Too simple. Not ideal for multi-step solutions. • Lenel Data. Conduit – Analysis: Complex, problematic and requires programming resource. 14

Solution Options – SQL Server • Scheduled SQL Insert – Using SQL Agent job. – OPENQUERY INSERT statement directly exports records into remote table on GULP database via Linked Server. – Analysis: Too intensive to run a query every five minutes against EVENTS table with millions of records. 15

Solution Options – SQL Server • Remote Trigger – EVENTS table trigger directly inserts access events into remote table on GULP Server via Linked Server and OPENQUERY. – Analysis: Too risky. EVENTS table could freeze if trigger failed due to problems accessing remote server. 16

Solution Options – SQL Server • Local Insert Trigger + Agent Job: – EVENTS table trigger inserts access events into local table in ACCESSCONTROL database. – Scheduled SQL Agent job exports records from local table to remote table on GULP server via Linked Server and OPENQUERY. – Analysis: • The winner. Local trigger low-risk and Lenel system not impacted if Agent export job fails. • More maintainable. Our expertise is SQL Server, not Data. Conduit nor Data Exchange. 17

Solutions Steps • Create Linked Server to GULP database. • Create insert trigger on EVENTS table to populate local table UDO_EVENTS_GULP. • Create SQL Agent job to run every 5 minutes to: – Insert unsent events into message queue table. – Export events in message queue to table on GULP server via Linked Server and OPENQUERY. – Update SENT column in UDO_EVENTS_GULP to “Y”. – Clear all events from message queue table. – Delete events > 30 days from UDO_EVENTS_GULP. 18

Solution Summary • Major success. Network Security Team very happy with having additional monitoring tool. • Very few other schools combine logical and physical data in log analysis. • Now thinking of other ways we can leverage Lenel data. 19

Trigger Syntax CREATE TRIGGER [dbo]. [UDO_SEND_EVENTS_TO_GULP_SERVER] ON [dbo]. [EVENTS] AFTER INSERT AS BEGIN *DECLARE AND SET VARIABLES USING INSERTED* IF @EVENTYPE IN (0, 1, 2, 3) AND @UNI IS NOT NULL BEGIN INSERT INTO UDO_EVENTS_GULP VALUES(@EVENTIME, @SERIALNUM, @UNI, @BADGEID, @EVENTYPE, @EVENTID, @PANELIP, 0, NULL) END 20

OPENQUERY Syntax To Update GULP INSERT OPENQUERY(GULP 1 P, 'SELECT ID, EVENTIME, SERIALNUM, UNI, BADGEID, EVENTYPE, EVENTID, PANEL_IPADDRESS FROM GULP_PROD. LENEL_DATA') SELECT ID, EVENTIME, SERIALNUM, UNI, BADGEID, EVENTYPE, EVENTID, PANELIP, FROM UDO_EVENTS_GULP_SEND 21

SQL Server Agent Job Steps 22

Questions 23

For Further Information Steve Cramer Columbia University Information Technology s. cramer@columbia. edu (212) 854 -3140 24