Trustworthy
Job details
| Name: | Trustworthy | |
| Platform: | Sqlserver | |
| Category: | Security | |
| Premium package: | Security and compliance package | |
| Description: | Check if the TRUSTWORTHY database option allows database objects to access objects in other databases. | |
| Long description: | Check if the TRUSTWORTHY database option allows database objects to access objects in other databases. | |
| Version: | 1.2 | |
| Default schedule: | 31 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 “Trustworthy” for a database is enabled. |
| disable Trustworthy | NO | If set to “YES“ the alert will disable “Trustworthy” (if it is enabled) by running ALTER DATABASE [<database_name>] SET TRUSTWORTHY OFF. |
| history threshold | 365 | The maximum number of days to keep statistics for in the historic tables. |
| ignore databases | msdb | The list of databases to be ignored (separated by comma). |
| ignore READ ONLY databases | YES | Ignores READ ONLY databases. Databases where DATABASEPROPERTYEX Updateability is set to READ_ONLY. |
Job Summary
- Purpose: The purpose of this job is to monitor the TRUSTWORTHY setting in SQL Server databases, which when enabled, allows database objects to access resources outside the database. This job ensures security compliance by detecting and addressing improper configurations.
- Why: This job is important because the TRUSTWORTHY setting, if improperly configured, can pose a security risk by allowing escalation of privileges and unauthorized access across databases. Identifying databases with this setting enabled can help in maintaining security integrity.
- Manual checking: You can check this manually in SQL Server by issuing the following command:
SELECT name, is_trustworthy_on FROM sys.databases WHERE is_trustworthy_on = 1;Implementation Details
The job implements a series of SQL operations encapsulated within stored procedures to assess the TRUSTWORTHY status, log history, and possibly disable the setting:
- A procedure dbw_trustworthy_proc is created to dynamically fetch the current status of the TRUSTWORTHY setting across databases and insert them into logging tables: dbw_trustworthy_status_info and dbw_trustworthy_status_histr.
- The job monitors all databases except those specified as ignored, including handling READ ONLY databases based on configuration.
- Depending on the configuration, the job can automatically disable the TRUSTWORTHY setting if it is found enabled to comply with security policies.
Configuration and Dependencies
The job involves the use of several dependencies crucial for its execution:
- dbw_trustworthy_status_info: A table designed to temporarily hold the current TRUSTWORTHY status of databases.
- dbw_trustworthy_status_histr: A historical table to track changes over time and maintain records up to a configurable threshold of days.
- dbw_trustworthy_proc: The main procedure that handles the execution logic described, including checks and modifications of database settings when required.
Tables and Reports
- Current Database Configuration: Displays databases with TRUSTWORTHY turned on and the last check date.
- Historical View: Provides a timeline showing when the TRUSTWORTHY setting was enabled across databases, allowing for trend analysis and review of past configurations.
Job Maintenance and Upgrading
- An upgrade implementation ensures revisions in the procedure to refine checks or accommodate newer SQL Server versions or administrative requirements.
- Modifications include updates to logging mechanisms and conditional checks which are revised to ensure compatibility and enhanced security checks.
In conclusion, this dbWatch Control Center job is integral for organizations aiming to uphold strong database security practices by monitoring and managing the TRUSTWORTHY setting in SQL Server environments.