mssql sqlserver 安全检查—数据库实例配置快照


摘要:
下文讲述sqlserver日常安全检查中,数据库实例配置快照也必须作为数据库安全检查的重要环节,下文讲述”数据库实例”配置记载的方法,如下所示:


在大多数环境中,不止一个人可以访问SQL Server,并且不止一个人拥有更改服务器或数据库选项所需的权限。
如果错误的设置发生变化,性能影响可能很大。
可能某一个同事无意间对数据库选项进行改变,导致异常的发生,我们可以通过日常对配置快照,可以快速了解问题出现的原因,并修正异常。
—————————————————–
快照数据库实例配置的方法

    ---创建属性存放表
	IF OBJECT_ID(N'dbo.database_ConfigData', N'U') IS NULL
BEGIN
  CREATE TABLE [dbo].[database_ConfigData] 
  (
    [ConfigurationID] [int] NOT NULL ,
    [Name] [nvarchar](35) NOT NULL ,
    [Value] [sql_variant] NULL ,
    [ValueInUse] [sql_variant] NULL ,
    [CaptureDate] [datetime] NOT NULL DEFAULT SYSDATETIME()
  )  ;
  end 
GO
 
 
 
IF OBJECT_ID(N'dbo.database_DBData', N'U') IS NULL
BEGIN
  CREATE TABLE [dbo].[database_DBData]
  (
    [name] [sysname] NOT NULL,
    [database_id] [int] NOT NULL,
    [source_database_id] [int] NULL,
    [owner_sid] [varbinary](85) NULL,
    [create_date] [datetime] NOT NULL,
    [compatibility_level] [tinyint] NOT NULL,
    [collation_name] [sysname] NULL,
    [user_access] [tinyint] NULL,
    [user_access_desc] [nvarchar](60) NULL,
    [is_read_only] [bit] NULL,
    [is_auto_close_on] [bit] NOT NULL,
    [is_auto_shrink_on] [bit] NULL,
    [state] [tinyint] NULL,
    [state_desc] [nvarchar](60) NULL,
    [is_in_standby] [bit] NULL,
    [is_cleanly_shutdown] [bit] NULL,
    [is_supplemental_logging_enabled] [bit] NULL,
    [snapshot_isolation_state] [tinyint] NULL,
    [snapshot_isolation_state_desc] [nvarchar](60) NULL,
    [is_read_committed_snapshot_on] [bit] NULL,
    [recovery_model] [tinyint] NULL,
    [recovery_model_desc] [nvarchar](60) NULL,
    [page_verify_option] [tinyint] NULL,
    [page_verify_option_desc] [nvarchar](60) NULL,
    [is_auto_create_stats_on] [bit] NULL,
    [is_auto_update_stats_on] [bit] NULL,
    [is_auto_update_stats_async_on] [bit] NULL,
    [is_ansi_null_default_on] [bit] NULL,
    [is_ansi_nulls_on] [bit] NULL,
    [is_ansi_padding_on] [bit] NULL,
    [is_ansi_warnings_on] [bit] NULL,
    [is_arithabort_on] [bit] NULL,
    [is_concat_null_yields_null_on] [bit] NULL,
    [is_numeric_roundabort_on] [bit] NULL,
    [is_quoted_identifier_on] [bit] NULL,
    [is_recursive_triggers_on] [bit] NULL,
    [is_cursor_close_on_commit_on] [bit] NULL,
    [is_local_cursor_default] [bit] NULL,
    [is_fulltext_enabled] [bit] NULL,
    [is_trustworthy_on] [bit] NULL,
    [is_db_chaining_on] [bit] NULL,
    [is_parameterization_forced] [bit] NULL,
    [is_master_key_encrypted_by_server] [bit] NOT NULL,
    [is_published] [bit] NOT NULL,
    [is_subscribed] [bit] NOT NULL,
    [is_merge_published] [bit] NOT NULL,
    [is_distributor] [bit] NOT NULL,
    [is_sync_with_backup] [bit] NOT NULL,
    [service_broker_guid] [uniqueidentifier] NOT NULL,
    [is_broker_enabled] [bit] NOT NULL,
    [log_reuse_wait] [tinyint] NULL,
    [log_reuse_wait_desc] [nvarchar](60) NULL,
    [is_date_correlation_on] [bit] NOT NULL,
    [is_cdc_enabled] [bit] NOT NULL,
    [is_encrypted] [bit] NULL,
    [is_honor_broker_priority_on] [bit] NULL,
    [replica_id] [uniqueidentifier] NULL,
    [group_database_id] [uniqueidentifier] NULL,
    [default_language_lcid] [smallint] NULL,
    [default_language_name] [nvarchar](128) NULL,
    [default_fulltext_language_lcid] [int] NULL,
    [default_fulltext_language_name] [nvarchar](128) NULL,
    [is_nested_triggers_on] [bit] NULL,
    [is_transform_noise_words_on] [bit] NULL,
    [two_digit_year_cutoff] [smallint] NULL,
    [containment] [tinyint] NULL,
    [containment_desc] [nvarchar](60) NULL,
    [target_recovery_time_in_seconds] [int] NULL,
    [CaptureDate] [datetime] NOT NULL DEFAULT SYSDATETIME()
) ;
end 
GO
  
-------------------插入数据库实例相关配置信息------------------


INSERT INTO [dbo].[database_ConfigData]
(
  [ConfigurationID] ,
  [Name] ,
  [Value] ,
  [ValueInUse]
)
SELECT 
  [configuration_id] ,
  [name] ,
  [value] ,
  [value_in_use]
FROM [sys].[configurations];
GO
 
INSERT INTO [dbo].[database_DBData]
(
  [name],
  [database_id],
  [source_database_id],
  [owner_sid],
  [create_date],
  [compatibility_level],
  [collation_name],
  [user_access],
  [user_access_desc],
  [is_read_only],
  [is_auto_close_on],
  [is_auto_shrink_on],
  [state],
  [state_desc],
  [is_in_standby],
  [is_cleanly_shutdown],
  [is_supplemental_logging_enabled],
  [snapshot_isolation_state],
  [snapshot_isolation_state_desc],
  [is_read_committed_snapshot_on],
  [recovery_model],
  [recovery_model_desc],
  [page_verify_option],
  [page_verify_option_desc],
  [is_auto_create_stats_on],
  [is_auto_update_stats_on],
  [is_auto_update_stats_async_on],
  [is_ansi_null_default_on],
  [is_ansi_nulls_on],
  [is_ansi_padding_on],
  [is_ansi_warnings_on],
  [is_arithabort_on],
  [is_concat_null_yields_null_on],
  [is_numeric_roundabort_on],
  [is_quoted_identifier_on],
  [is_recursive_triggers_on],
  [is_cursor_close_on_commit_on],
  [is_local_cursor_default],
  [is_fulltext_enabled],
  [is_trustworthy_on],
  [is_db_chaining_on],
  [is_parameterization_forced],
  [is_master_key_encrypted_by_server],
  [is_published],
  [is_subscribed],
  [is_merge_published],
  [is_distributor],
  [is_sync_with_backup],
  [service_broker_guid],
  [is_broker_enabled],
  [log_reuse_wait],
  [log_reuse_wait_desc],
  [is_date_correlation_on],
  [is_cdc_enabled],
  [is_encrypted],
  [is_honor_broker_priority_on],
  [replica_id],
  [group_database_id],
  [default_language_lcid],
  [default_language_name],
  [default_fulltext_language_lcid],
  [default_fulltext_language_name],
  [is_nested_triggers_on],
  [is_transform_noise_words_on],
  [two_digit_year_cutoff],
  [containment],
  [containment_desc],
  [target_recovery_time_in_seconds]
)
SELECT
  [name],
  [database_id],
  [source_database_id],
  [owner_sid],
  [create_date],
  [compatibility_level],
  [collation_name],
  [user_access],
  [user_access_desc],
  [is_read_only],
  [is_auto_close_on],
  [is_auto_shrink_on],
  [state],
  [state_desc],
  [is_in_standby],
  [is_cleanly_shutdown],
  [is_supplemental_logging_enabled],
  [snapshot_isolation_state],
  [snapshot_isolation_state_desc],
  [is_read_committed_snapshot_on],
  [recovery_model],
  [recovery_model_desc],
  [page_verify_option],
  [page_verify_option_desc],
  [is_auto_create_stats_on],
  [is_auto_update_stats_on],
  [is_auto_update_stats_async_on],
  [is_ansi_null_default_on],
  [is_ansi_nulls_on],
  [is_ansi_padding_on],
  [is_ansi_warnings_on],
  [is_arithabort_on],
  [is_concat_null_yields_null_on],
  [is_numeric_roundabort_on],
  [is_quoted_identifier_on],
  [is_recursive_triggers_on],
  [is_cursor_close_on_commit_on],
  [is_local_cursor_default],
  [is_fulltext_enabled],
  [is_trustworthy_on],
  [is_db_chaining_on],
  [is_parameterization_forced],
  [is_master_key_encrypted_by_server],
  [is_published],
  [is_subscribed],
  [is_merge_published],
  [is_distributor],
  [is_sync_with_backup],
  [service_broker_guid],
  [is_broker_enabled],
  [log_reuse_wait],
  [log_reuse_wait_desc],
  [is_date_correlation_on],
  [is_cdc_enabled],
  [is_encrypted],
  [is_honor_broker_priority_on],
  [replica_id],
  [group_database_id],
  [default_language_lcid],
  [default_language_name],
  [default_fulltext_language_lcid],
  [default_fulltext_language_name],
  [is_nested_triggers_on],
  [is_transform_noise_words_on],
  [two_digit_year_cutoff],
  [containment],
  [containment_desc],
  [target_recovery_time_in_seconds]
FROM [sys].[databases];
GO

----查询相关表,获取配置变化的方法分享------------------
;WITH [tmp] AS
( 
  SELECT
    ROW_NUMBER() OVER (PARTITION BY [ConfigurationID] ORDER BY [CaptureDate] ASC) AS [RowNumber],
    [ConfigurationID] AS [ConfigurationID],
    [Name] AS [Name],
    [Value] AS [Value],
    [ValueInUse] AS [ValueInUse],
    [CaptureDate] AS [CaptureDate]
  FROM  [dbo].[database_ConfigData]
)
SELECT 
  [tmp].[Name] AS [设置项], 
  [tmp].[CaptureDate] AS [写入时间], 
  [tmp].[Value] AS [Previous Value], 
  [tmp].[ValueInUse] AS [Previous Value In Use],
  [tmp2].[CaptureDate] AS [Date Changed], 
  [tmp2].[Value] AS [New Value], 
  [tmp2].[ValueInUse] AS [New Value In Use]
FROM [tmp]
LEFT OUTER JOIN [tmp] AS [tmp2]
ON [tmp].[ConfigurationID] = [tmp2].[ConfigurationID]
AND [tmp].[RowNumber] + 1 = [tmp2].[RowNumber]
WHERE ([tmp].[Value] <> [tmp2].[Value] OR [tmp].[ValueInUse] <> [tmp2].[ValueInUse]);
GO