CLR Assembly permission
Job details
| Name: | CLR Assembly permission | |
| Platform: | Sqlserver | |
| Category: | Security | |
| Premium package: | Security and compliance package | |
| Description: | Checks if the CLR Assembly permission_set is set to SAFE access. | |
| Long description: | Checks if the CLR Assembly permission_set is set to SAFE access. This will prevent assemblies from accessing external system resources such as files, the network, environment variables, or the registry | |
| Version: | 1.2 | |
| Default schedule: | 3 1 1 * | |
| Requires engine install: | Yes | |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2005′ & hasengine=‘YES’ & eng_inst_priv = 0 & (engine_edition = ‘Microsoft SQL Server’ | engine_edition = ‘Azure SQL Managed Instance’)] |
Parameters
| Name | Default value | Description |
|---|---|---|
| return status | 1 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when the CLR Assembly permission_set is not set to SAFE access. |
| enable SAFE access | NO | If set to “YES“ the alert will alter assembly and set permission_set to value SAFE. |
| history threshold | 365 | The maximum number of days to keep statistics for in the historic tables. |
Job Summary
- Purpose: The purpose of this job is to monitor and ensure that the CLR Assembly permission_set is set to SAFE in all SQL Server assemblies.
- Why: This job is significant as it helps maintain security within SQL Server environments by making sure that assemblies do not have permissions that allow them to access external system resources such as files, networks, and registry entries, which can lead to vulnerabilities.
- Manual checking: You can check the CLR Assembly permission settings manually in the database by issuing the commands shown below:
Detailed Implementation
This job includes several key processes:
- Creates and updates tables to record current assembly permissions and their history.
- Executes a periodic check of assemblies across all databases, inserting fresh data into the designated tables.
- If configured (through parameters), alters assembly permissions to enforce SAFE access.
- Manages historical data, removing records older than the specified threshold to maintain table size and performance.
- Updates security frameworks tables with verification results to aid in compliance checks.
Tables Involved
The activity involves the manipulation and referencing of the following tables:
- bc. dbw_CLR_assembly_permission_info
- bc. dbw_CLR_assembly_permission_histr
Schedule
- Default Schedule: The task is scheduled to run yearly on the first day of the year at 1:03 AM.
Dependencies
The execution of this job relies on:
- Main procedure: dbw_assembly_permission_proc, which contains the core logic for checking and potentially altering CLR Assembly permissions.
- Supportive information from “CURRENT STATUS TABLE“ dbw_CLR_assembly_permission_info and “HISTORY TABLE“ dbw_CLR_assembly_permission_histr, utilized to log and trace changes over time.
Job Reporting
The reports generated by this job offer insights into:
- Current configuration of CLR settings.
- Historical and current permissions of assemblies across all databases, highlighting databases where certain permissions are set.
- Aggregated statistics about the use of various permission sets over time, providing a timeline perspective to aid in assessing compliance and security standards over periods.
Versioning and Upgrades
Upgrades from an older version (1.1) involve:
- Updating procedures to reflect any changes in the handling and verification of CLR assembly permissions.
- Focused enhancements to ensure continued compatibility with newer versions and operational guidelines.
This job, through its complex and multi-faceted approach, plays a crucial role in the preventative security measures adopted by organizations using SQL Server, ensuring assemblies operate under strict control and within safe parameters.