SQL Server默认库：保持数据安全-猿码集

什么是SQL Server默认库

SQL Server是一款关系型数据库管理系统，它可以存储和管理大量的数据。在SQL Server中，有一些默认库，这些库在安装SQL Server时就已经存在，它们对于SQL Server的正常运行非常重要。默认库是指在SQL Server创建数据库时，如果没有指定数据库的创建位置，那么它就会被创建在默认库中。

SQL Server默认库有哪些

SQL Server默认库包括以下几种：

master

model

msdb

tempdb

master库

master库是SQL Server中最重要的库之一，它记录了SQL Server的系统级元数据和数据库级元数据，包括所有的系统级对象和所有的用户数据库信息。它还包含了登录信息，聚合函数，数据类型，系统配置设置等重要内容。

当SQL Server启动时，系统会首先加载master库，以确保SQL Server正常运行。


USE master; -- 使用master库
SELECT * FROM sys.tables; -- 查询master库中的表

model库

model库是用来创建新数据库的模板库，当使用CREATE DATABASE语句创建新的数据库时，SQL Server会以model库为模板。如果您需要在多个数据库中创建相同的对象，可以将这些对象定义在model库中，每次创建数据库时这些对象都会被自动复制。


USE model; -- 使用model库
CREATE TABLE [dbo].[NewTable] ( -- 在model库中创建新表
    [Id] INT NOT NULL PRIMARY KEY,
    [Name] VARCHAR(50) NOT NULL
);

msdb库

msdb库主要用于SQL Server代理的管理，包括SQL Server代理作业、运行历史、警报和操作系统任务等。msdb库还包含备份和还原、数据库邮件、数据库镜像、日志传递和分配等重要信息。


USE msdb; -- 使用msdb库
SELECT * FROM dbo.sysjobs; -- 查询SQL Server代理作业信息

tempdb库

tempdb库是SQL Server中重要的临时库，它用于存储不属于任何数据库的临时数据。例如，临时表、表变量、游标、存储过程等都将在tempdb库中创建对应对象。tempdb库是所有用户数据库共享的，每个连接都可以在其中创建自己的临时表和对象，但是这些对象在连接关闭后将会自动删除。

由于tempdb库的重要性，我们需要保证其正常运行。以下是一些提高tempdb性能的常用策略：

设置tempdb文件数量和大小

将tempdb文件放在不同的物理驱动器上，以平衡I/O负载

禁止在tempdb库中创建自增长文件

定期清理不需要的对象和日志


USE tempdb; -- 使用tempdb库
CREATE TABLE #TempTable ( -- 在tempdb库中创建临时表
    [Id] INT NOT NULL PRIMARY KEY,
    [Name] VARCHAR(50) NOT NULL
);

保持数据安全

数据安全对于任何数据库系统都是至关重要的，在SQL Server中，我们可以采取以下措施来保持数据的安全性：

备份和恢复

备份和恢复是SQL Server中最基本的安全措施。通过定期备份，我们可以确保数据的安全性，并在数据遭受损坏时迅速恢复。备份可以分为完整备份、差异备份和日志备份三种类型。完整备份包含整个数据库的所有数据和对象，差异备份包含自上次完整备份以来所发生的所有更改，而日志备份则包含最近事务日志中的所有更改。

BACKUP DATABASE MyDatabase TO DISK='C:\MyDatabase.bak'; -- 执行完整备份 BACKUP DATABASE MyDatabase TO DISK='C:\MyDatabase_differential.bak' WITH DIFFERENTIAL; -- 执行差异备份 BACKUP LOG MyDatabase TO DISK='C:\MyDatabase_log.bak'; -- 执行日志备份

恢复可以分为完整恢复、差异恢复和点恢复三种类型。完整恢复包含整个数据库的所有数据和对象，差异恢复包含自上次完整恢复或差异恢复以来所发生的所有更改，而点恢复则可以只恢复到某个时间点的状态。

RESTORE DATABASE MyDatabase FROM DISK='C:\MyDatabase.bak' WITH RECOVERY; -- 执行完整恢复 RESTORE DATABASE MyDatabase FROM DISK='C:\MyDatabase_differential.bak' WITH NORECOVERY; -- 执行差异恢复 RESTORE DATABASE MyDatabase FROM DISK='C:\MyDatabase.bak' WITH NORECOVERY; -- 执行点恢复 RESTORE LOG MyDatabase FROM DISK='C:\MyDatabase_log.bak' WITH NORECOVERY; -- 恢复日志 RESTORE DATABASE MyDatabase WITH RECOVERY; -- 完成恢复

授权和认证

SQL Server支持基于角色的安全模型，通过授权和认证，我们可以限制用户对数据库的访问权限。授权是指将安全对象（如表、视图和存储过程等）的访问权限授予用户或角色，而认证则是指验证用户的身份以确保其有权限访问所请求的资源。


USE MyDatabase; -- 使用MyDatabase库
CREATE USER [NewUser] WITHOUT LOGIN; -- 创建新用户
ALTER ROLE [db_datareader] ADD MEMBER [NewUser]; -- 授权只读权限

加密

加密可以保护重要的数据库对象和敏感数据，以防止其被非法访问或窃取。SQL Server支持多种加密技术，包括对称和非对称加密、数字证书和身份验证等。加密后的数据只有在解密后才能使用，这样可以有效防止数据泄露。


USE MyDatabase; -- 使用MyDatabase库
CREATE SYMMETRIC KEY [MyKey] WITH ALGORITHM = AES_256 ENCRYPTION BY PASSWORD='password'; -- 创建对称加密密钥
OPEN SYMMETRIC KEY [MyKey] DECRYPTION BY PASSWORD='password'; -- 打开密钥
UPDATE MyTable SET MyColumn = ENCRYPTBYKEY(KEY_GUID('MyKey'), MyColumn); -- 加密数据
SELECT MyColumn, CONVERT(VARCHAR, DECRYPTBYKEY(MyColumn)) AS Decrypted FROM MyTable; -- 查询加密后的数据并解密
CLOSE SYMMETRIC KEY [MyKey]; -- 关闭密钥

审计和跟踪

SQL Server提供了审计和跟踪功能，可以记录数据库操作和访问，以便分析和调查数据库安全事件。审计可以用于记录对敏感数据的访问，而跟踪则可以用于跟踪数据库的活动和性能。


USE master; -- 使用master库
CREATE SERVER AUDIT [MyAudit] TO FILE (FILEPATH='C:\MyAudit'); -- 创建服务器审计
CREATE SERVER AUDIT SPECIFICATION [MyAuditSpec] FOR SERVER AUDIT [MyAudit]
ADD (SCHEMA_OBJECT_ACCESS_GROUP); -- 创建审计规范
ALTER SERVER AUDIT [MyAudit] WITH (STATE=ON); -- 开启审计

总结

默认库在SQL Server中具有非常重要的作用，它们可以确保SQL Server的正常运行并提供了大量的重要信息和功能。同时，保持数据安全也是数据库管理中必不可少的一部分。SQL Server提供了备份和恢复、授权和认证、加密以及审计和跟踪等多种安全措施，管理员可以根据实际情况选择合适的方法来保障数据库的安全性。

SQL Server默认库：保持数据安全