Introduction
Kepion versions 6.1.23000.0 and above have eliminated the need for granting VIEW_SERVER_STATE permission to the Kepion SI during SQL server setup. This permission was required in earlier versions of Kepion to cache database assets on the Web Server. This article will discuss the security risks of granting VIEW_SERVER_STATE permission and how Kepion has addressed these risks.
Security Risks
The "principle of least privilege" states that users should have access only to the information required to perform their duties. Granting VIEW_SERVER_STATE permission violates this principle by providing read access to SQL Server state information. Although the permission does not grant direct read or write access to the data in your databases, it enables Kepion SI to view information it does not require.
Granting VIEW_SERVER_STATE permission allows Kepion SI to access potentially sensitive data across your databases. Your databases contain views and functions with data about execution contexts and plans, such as sys.dm_exec_cached_plans. If these contexts and plans are combined with sys.dm_exec_sql_text() or sys.dm_exec_query_plan(), Kepion SI can obtain information about code and objects in databases. Additionally, DMVs like sys.dm_os_sys_info contain information about the host machine, including hardware and services.
Solution
To address the security risks associated with granting VIEW_SERVER_STATE permission, Kepion has leveraged SQL Server's Change Tracking infrastructure to replace our index-based approach. Any DML statement to your tables is recorded as change tracking information with the primary key column values. By joining the source table with the tracked table using the primary key column values, your Applications can obtain the latest data for those rows.
Conclusion
Granting VIEW_SERVER_STATE permission creates security risks by violating the principle of least privilege, exposing potentially sensitive data across your databases. Kepion has eliminated the need for this permission by leveraging SQL Server's Change Tracking infrastructure, which records any DML statements made to your tables as change tracking information. This approach mitigating the security risks associated with granting VIEW_SERVER_STATE permission.