While we recognize that there are situations where direct SQL Access to the Data Warehouse for sophisticated campus power users for data analytics, the majority of use cases are well handled by our current adhoc query tools. The primary reasons OIT restricts access to data warehouse with direct SQL include:
- Far less agility on the part of the DWH team to fix/change tables and data schema since SQL is hired wired to the database so user reports will break if the data schema is changed.
- Lack of row level security and the OIT expense of creating it at the database layer. No role based access.
- No KSAMS integration to track who grants authorization to view what data to whom
- Inability to use DUO second factor
- Lack of auditing
- Potential performance impact of incorrectly formed queries and cartesian products
- Lack of metadata integration or any kind of built-in help.
- The fear that many 1000s of campus users will start asking questions about joins and data of the tiny DWH Team.
- Proliferation of tools that we won’t be able to support or manage (Excel, PowerBI, etc)
- Most users do not know SQL well enough to form reliable and trustworthy data reports. These errors can be extremely costly from the business perspective.
- Bad actor / intruder controls are reduced. Even read only access provides a door into our databases that are currently behind firewalls and well protected. Once in, malware or other means in the database or OS layer can allow intruders to get to our crown jewels, or use the database machines as pivot points to more critical assets behind firewalls.
- The campus conducts an annual inventory of all data access to databases as part of compliance with SAS 112. Users having direct access to databases are very difficult to track and inventory annually. Managers must approve any OIT internal staff on this inventory and to extend it to the campus requires building better inventorying processes and tools.