MSSQL登录：最佳实践

1. MSSQL登录：最佳实践

MSSQL是一款强大的数据库管理系统，但是安全性也是它需要重视的问题之一。在实际开发和使用过程中，我们需要采取一些措施来保证MSSQL的安全性。本文将简要介绍其中的一些最佳实践。

1.1. 使用强密码

在登录MSSQL时，强密码可以很好地保护我们的账户免受黑客攻击。一个强密码应该具备以下要素：

长度大于8位

包含大小写字母、数字和符号

不包含常见的单词或短语

以下是一个符合上述要素的密码示例：

P@d#FTL^tQyZ2e7!

需要注意的是，在使用强密码时也需要注意安全性，避免将密码写在纸条或文本文件中，或者在公共场所使用。

1.2. 勿在公共网络登录MSSQL

在公共网络环境中，我们需要采取特别的措施来保障MSSQL的安全性。这可能是一个开放的Wi-Fi网络、公用的计算机等等。

在这种情况下，建议采取以下措施：

使用加密连接来保护数据

在使用完毕后，及时退出登录

禁用自动登录功能

勿在公共网络环境中泄露敏感信息。

1.3. 限定IP地址范围

为了保障数据库的安全性，我们可以限制访问MSSQL的IP地址范围，只允许来自特定IP地址或地址范围的访问者访问。

以下是一个设置IP地址范围访问MSSQL的命令示例：

USE [master]
CREATE LOGIN [username] WITH PASSWORD=N'password', DEFAULT_DATABASE=[dbname], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [dbname]
CREATE USER [username] FOR LOGIN [username] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [dbname]
ALTER ROLE [db_datareader] ADD MEMBER [username]
GO
USE [dbname]
ALTER ROLE [db_datawriter] ADD MEMBER [username]
GO
USE [dbname]
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE TO [username]
GO
USE [dbname]
ALTER ROLE [db_backupoperator] ADD MEMBER [username]
GO
USE [master]
CREATE ENDPOINT [MSSQLReplication] 
AUTHORIZATION [username] 
STATE=STARTED 
AS TCP (LISTENER_PORT = 1433)
FOR DATA_MIRRORING (ROLE=ALL)
GO
USE [msdb]
EXECUTE sp_add_proxy 
   @proxy_name=N'sqladmin_proxy_credential', 
   @credential_name=N'sqladmin_credential', 
   @enabled=1
GO
USE [msdb]
EXECUTE sp_add_job 
   @job_name=N'Backup_Database', 
   @enabled=1, 
   @description=N'Daily backup of all databases on the server.', 
   @owner_login_name=N'sqladmin', 
   @category_name=N'Database Maintenance', 
   @job_id = @jobId OUTPUT
GO
USE [msdb]
EXECUTE sp_add_jobstep 
   @job_id=@jobId, 
   @step_name=N'Set Backup Path', 
   @step_id=1, 
   @cmdexec_success_code=0, 
   @on_success_action=1, 
   @on_fail_action=2, 
   @retry_attempts=0, 
   @retry_interval=0, 
   @os_run_priority=0, 
   @subsystem=N'TSQL', 
   @command=N'declare @backupPath nvarchar(512) = N''C:\Backup'';'
GO
USE [msdb]
EXECUTE sp_add_jobstep 
   @job_id=@jobId, 
   @step_name=N'Backup Databases', 
   @step_id=2, 
   @cmdexec_success_code=0, 
   @on_success_action=1, 
   @on_fail_action=2, 
   @retry_attempts=0, 
   @retry_interval=0, 
   @os_run_priority=0, 
   @subsystem=N'TSQL', 
   @command=N'SET NOCOUNT ON;
  
DECLARE @db sysname;
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb');
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
   DECLARE @backupFile nvarchar(512) = @backupPath + ''\'' + @db + ''_'' + CONVERT(nvarchar(20), GETDATE(), 112)  + ''_'' + REPLACE(CONVERT(nvarchar(20), GETDATE(), 108), '':'' ,'');
  
   BACKUP DATABASE @db 
      TO DISK = @backupFile WITH NOFORMAT, NOINIT, 
      NAME = @db, SKIP, NOREWIND, NOUNLOAD;
      
   FETCH NEXT FROM db_cursor INTO @db;
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;'
GO

需要注意的是，为了更好的保护数据库，建议将密码、IP地址等信息保存在安全的地方，如加密的文件夹或密码管理软件中。

1.4. 使用多因素身份验证

多因素身份验证是一种更为安全的身份验证方式，可以降低黑客攻击的风险。它需要综合使用密码、指纹、身份证件等多个验证方式，以获得更高的安全性和保障。

以下是一个使用多因素身份验证的命令示例：

USE [master]
CREATE LOGIN [username] WITH PASSWORD=N'password', DEFAULT_DATABASE=[dbname], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [dbname]
CREATE USER [username] FOR LOGIN [username] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [dbname]
ALTER ROLE [db_datareader] ADD MEMBER [username]
GO
USE [dbname]
ALTER ROLE [db_datawriter] ADD MEMBER [username]
GO
USE [dbname]
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE TO [username]
GO
USE [dbname]
ALTER ROLE [db_backupoperator] ADD MEMBER [username]
GO
USE [master]
CREATE ENDPOINT [MSSQLReplication] 
AUTHORIZATION [username] 
STATE=STARTED 
AS TCP (LISTENER_PORT = 1433)
FOR DATA_MIRRORING (ROLE=ALL)
GO
USE [msdb]
EXECUTE sp_add_proxy 
   @proxy_name=N'sqladmin_proxy_credential', 
   @credential_name=N'sqladmin_credential', 
   @enabled=1
GO
USE [msdb]
EXECUTE sp_add_job 
   @job_name=N'Backup_Database', 
   @enabled=1, 
   @description=N'Daily backup of all databases on the server.', 
   @owner_login_name=N'sqladmin', 
   @category_name=N'Database Maintenance', 
   @job_id = @jobId OUTPUT
GO
USE [msdb]
EXECUTE sp_add_jobstep 
   @job_id=@jobId, 
   @step_name=N'Set Backup Path', 
   @step_id=1, 
   @cmdexec_success_code=0, 
   @on_success_action=1, 
   @on_fail_action=2, 
   @retry_attempts=0, 
   @retry_interval=0, 
   @os_run_priority=0, 
   @subsystem=N'TSQL', 
   @command=N'declare @backupPath nvarchar(512) = N''C:\Backup'';'
GO
USE [msdb]
EXECUTE sp_add_jobstep 
   @job_id=@jobId, 
   @step_name=N'Backup Databases', 
   @step_id=2, 
   @cmdexec_success_code=0, 
   @on_success_action=1, 
   @on_fail_action=2, 
   @retry_attempts=0, 
   @retry_interval=0, 
   @os_run_priority=0, 
   @subsystem=N'TSQL', 
   @command=N'SET NOCOUNT ON;
  
DECLARE @db sysname;
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb');
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
   DECLARE @backupFile nvarchar(512) = @backupPath + ''\'' + @db + ''_'' + CONVERT(nvarchar(20), GETDATE(), 112)  + ''_'' + REPLACE(CONVERT(nvarchar(20), GETDATE(), 108), '':'' ,'');
  
   BACKUP DATABASE @db 
      TO DISK = @backupFile WITH NOFORMAT, NOINIT, 
      NAME = @db, SKIP, NOREWIND, NOUNLOAD;
      
   FETCH NEXT FROM db_cursor INTO @db;
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;'
GO

需要注意的是，多因素身份验证需要重视隐私保护，如指纹、身份证件等敏感信息的收集与保管。

1.5. 定期备份

定期备份是保护数据安全的重要手段，在数据灾变等情况下可以帮助我们迅速恢复数据。定期备份通常可分为完整备份、增量备份和差异备份等多种形式。

以下是一个设置定期备份的脚本示例：

USE [master]
CREATE LOGIN [username] WITH PASSWORD=N'password', DEFAULT_DATABASE=[dbname], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [dbname]
CREATE USER [username] FOR LOGIN [username] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [dbname]
ALTER ROLE [db_datareader] ADD MEMBER [username]
GO
USE [dbname]
ALTER ROLE [db_datawriter] ADD MEMBER [username]
GO
USE [dbname]
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE TO [username]
GO
USE [dbname]
ALTER ROLE [db_backupoperator] ADD MEMBER [username]
GO
USE [master]
CREATE ENDPOINT [MSSQLReplication] 
AUTHORIZATION [username] 
STATE=STARTED 
AS TCP (LISTENER_PORT = 1433)
FOR DATA_MIRRORING (ROLE=ALL)
GO
USE [msdb]
EXECUTE sp_add_proxy 
   @proxy_name=N'sqladmin_proxy_credential', 
   @credential_name=N'sqladmin_credential', 
   @enabled=1
GO
USE [msdb]
EXECUTE sp_add_job 
   @job_name=N'Backup_Database', 
   @enabled=1, 
   @description=N'Daily backup of all databases on the server.', 
   @owner_login_name=N'sqladmin', 
   @category_name=N'Database Maintenance', 
   @job_id = @jobId OUTPUT
GO
USE [msdb]
EXECUTE sp_add_jobstep 
   @job_id=@jobId, 
   @step_name=N'Set Backup Path', 
   @step_id=1, 
   @cmdexec_success_code=0, 
   @on_success_action=1, 
   @on_fail_action=2, 
   @retry_attempts=0, 
   @retry_interval=0, 
   @os_run_priority=0, 
   @subsystem=N'TSQL', 
   @command=N'declare @backupPath nvarchar(512) = N''C:\Backup'';'
GO
USE [msdb]
EXECUTE sp_add_jobstep 
   @job_id=@jobId, 
   @step_name=N'Backup Databases', 
   @step_id=2, 
   @cmdexec_success_code=0, 
   @on_success_action=1, 
   @on_fail_action=2, 
   @retry_attempts=0, 
   @retry_interval=0, 
   @os_run_priority=0, 
   @subsystem=N'TSQL', 
   @command=N'SET NOCOUNT ON;
  
DECLARE @db sysname;
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb');
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
   DECLARE @backupFile nvarchar(512) = @backupPath + ''\'' + @db + ''_'' + CONVERT(nvarchar(20), GETDATE(), 112)  + ''_'' + REPLACE(CONVERT(nvarchar(20), GETDATE(), 108), '':'' ,'');
  
   BACKUP DATABASE @db 
      TO DISK = @backupFile WITH NOFORMAT, NOINIT, 
      NAME = @db, SKIP, NOREWIND, NOUNLOAD;
      
   FETCH NEXT FROM db_cursor INTO @db;
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;'
GO

需要注意的是，在备份数据时，需要注意安全性和可靠性，以确保备份的数据可正常恢复。

2. 总结

MSSQL数据库的安全性是企业和个人需要认真关注的问题。采用安全的登录方式、密保措施、定期备份等方式能够保障数据库的安全性。希望本文介绍的一些最佳实践能够对开发者和用户有所帮助。