SQL Server 审核（Audit）-- 审核对数据库对象的访问

lfjigu

SQL Server 审核（Audit）-- 审核对数据库对象的访问
任务1：创建登录账户，授予适当的权限
　　
　　步骤1：打开SSMS，输入如下语句，创建登录账户UltraSQL，授予访问AdventureWorks2012数据库的Person.Person和Person.Password权限。
USE master　　
GO
　　
--Create Login UltraSQL
　　
CREATE LOGIN UltraSQL
　　
WITH PASSWORD=N’Mpdfzh7’,
　　
DEFAULT_DATABASE=AdventureWorks2012, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
　　
GO
　　
--Create User UltraSQL In AdventureWorks2012
　　
USE AdventureWorks2012
　　
GO
　　
CREATE USER UltraSQL FOR LOGIN UltraSQL
　　
GO
　　
--Grant Select Privilege to Person.Person & Person.Password
　　
GRANT SELECT ON Person.Person TO UltraSQL
　　
GRANT SELECT ON Person.Password TO UltraSQL
　　
GO
任务2：创建审核
　　
　　步骤1：打开SSMS，登录到指定的实例，展开“Security”，“Audits”节点。
　　步骤2：在“Audits”节点上，右键选择“New Audit…”选项。
　　步骤3：在“Create Audit”窗口，输入以下的参数。
　　l 在“Audit name”输入Audit-AdventureWorks2012-AccessTable。
　　l 在“Audit destination”选择“File”
　　l 在“File path”输入D:\MSSQL\DATA\Audit_logs。
　　l 取消勾选位于“Maximum rollover files”框下的“Unlimited”。
　　l 在“Number of files”输入100。

　　l 取消勾选位于“Maximum file>
　　l 在“Maximum file>
　　设置“Maximum file>

　　步骤4：单击“OK”完成设置。
　　步骤5：右键点击刚刚创建的审核“Audit-AdventureWorks2012-AccessTable”，选择“Enable Audit”选项。
任务3：创建访问数据表的审核
　　
　　步骤1：展开“Database”，“AdventureWorks2012”，“Security”，“Database Audit Specifications”节点。

　　步骤2：在“Database Audit Specifications”节点，单击鼠标右键选择“New Database Audit Specification…”选项。
　　步骤3：在“Create Database Audit Specification”窗口输入以下的参数。
　　l 在“Name”输入DatabaseAuditSpecification-UltraSQLAccessPerson.Password。
　　l 在“Audit”下拉框选择之前创建的审核对象Audit-AdventureWorks2012-AccessTable。
　　l 在“Audit Action Type”中选择“SELECT”。

　　l 在“Object>　　l 在“Object Name”中单击图表，选择“Person.Password”选项。
　　l 在“Principal Name”中单击图表，选择“UltraSQL”选项。

　　步骤4：参照步骤3，在“Action”区域的第二行部分，添加对“Audit Action Type”是“UPDATE”的监视。。以针对用户UltraSQL查询或是更新数据表Person.Password进行审核。

　　步骤5：右键点击刚刚创建的数据库审核规范“DatabaseAuditSpecification-UltraSQLAccessPerson.Password”，选择“Enable Database Audit Specification”选项。
任务4：测试审核功能
　　
　　步骤1：以UltraSQL的身份，通过SSMS连接到AdventureWorks2012。

　　步骤2：使用UltraSQL账号，对数据表Person.Password执行查询与更新等操作。
USE AdventureWorks2012　　
GO
　　
SELECT * FROM Person.Password
　　
SELECT * FROM Person.Person
　　
SELECT * FROM Person.Password
　　
UPDATE Person.Password SET ModifiedDate=GETDATE() WHERE BusinessEntityID=1
　　
/*
　　
Msg 229, Level 14, State 5, Line 1
　　
The UPDATE permission was denied on the object 'Password', database 'AdventureWorks2012', schema 'Person'.
　　
*/
　　
SELECT * FROM Person.Password WHERE BusinessEntityID=1
　　
SELECT a.PasswordHash,b.FirstName,b.LastName
　　
FROM Person.Password a INNER JOIN Person.Person b
　　
ON a.BusinessEntityID = b.BusinessEntityID
　　
SELECT TOP 1 * FROM Person.Password WHERE BusinessEntityID=1
任务5：使用“Log File Viewer”，阅读审核日志
　　
　　步骤1：以sysadmin身份登录实例，展开“Object Explorer”，“Security”，“Audits”节点。
　　步骤2：在审核“Audit-AdventureWorks2012-AccessTable”上单击右键，选择“View Audit Logs”。
　　步骤3：在“Log File Viewer”窗口的左侧，选择“Audit Collection”，“Audit-AdventureWorks2012-AccessTable”，并利用以下的方式来阅读所记录的审核信息。

　　l 在右边的“Log file summary”区域内，在“Action>　　l 在下方的“Database Principal Name”区域，可以看到所记录的数据库用户。在“Statement”区域可以看到所记录的程序代码。

　　有个特别的现象，整理如下。

　　l 以执行UPDATE语句为例，将会引发SELECT与UPDATE等两个“Action>

任务6：使用T-SQL函数来分析、筛选审核的日志数据
　　
　　步骤1：利用sysadmin身份，执行SSMS，输入如下查询语句，使用sys.dm_server_audit_status动态管理视图来查看各个审核对象的当前状态。
USE master　　
GO
　　
SELECT audit_id N'Audit ID', name N'Audit Name', status_desc N'Server Audit Status',
　　
Status_time N'Last Status Changed Timestamp', audit_file_size N'Audit File Size',
　　
Audit_file_path N'Audit File Full Path'
　　
FROM sys.dm_server_audit_status;

　　l 数据行status_desc（服务器审核状态）：查看各个审核对象是否已经启用。
　　l 数据行audit_file_size（审核文件大小（KB））：查看以二进制文件为目标的审核文件，其所使用的空间，以KB为单位；若非二进制文件，例如：以“Windows应用程序事件日志文件”来存放的审核日志，则会显示NULL。
　　l 数据行audit_file_path（审核文件目标的完整路径名称）：查看以二进制文件为目标的审核文件，其所在的完整路径与文件名。
　　步骤2：单击“New Query”，执行如下代码，使用函数fn_get_audit_file分析审核文件
--EX1 Query the Audit File　　
/*
　　
Fn_get_audit_file (Transact-SQL)
　　
fn_get_audit_file(file_pattern, {default | initial_file_name | NULL },{default | audit_file_offset | NULL})
　　
Param:file_pattern
　　
Set the Audit File Full Path.
　　
*/
　　
SELECT * FROM sys.fn_get_audit_file(N’ D:\MSSQL\DATA\Audit_logs\Audit-Login-Create%9Alter%9Drop_AF3AAECB-30CC-4476-9395-8754E60E356C_0_130639659785910000.sqlaudit’,default,default);
　　
--EX2 Using * Query some Audit Files created by the same audit object
　　
SELECT * FROM sys.fn_get_audit_file(N’ ’ D:\MSSQL\DATA\Audit_logs\Audit-Login-Create%9Alter%9Drop_*.sqlaudit’,default,default);
　　
--EX3 attention: event_time type is datetime2,stored GMT
　　
SELECT event_time N’Audit caused Date & Time(GMT)’, server_principal_name N’Login’,
　　
Database_principal_name N’User’, database_name N’Database’, object_name N’Object Name’, statement N’TSQL’
　　
FROM sys.fn_get_audit_file(N’ ’ D:\MSSQL\DATA\Audit_logs\Audit-Login-Create%9Alter%9Drop_*.sqlaudit’,default,default);
　　
--EX4 Convert to Timezone Bejing(GMT+08:00) using data type datetimeoffset & Function SWITCHOFFSET)
　　
SELECT SWITCHOFFSET(CAST(event_time AS datetimeoffset),’+08:00’) N’Audit Action caused date & time(Timezone Bejing GMT+08:00)’,
　　
Server_principal_name N’Login’, database_principal_name N’User’,
　　
Database_name N’Database’, object_name N’Object Name’, statement N’TSQL’
　　
FROM sys.fn_get_audit_file(N’ ’ D:\MSSQL\DATA\Audit_logs\Audit-Login-Create%9Alter%9Drop_*.sqlaudit’,default,default);
　　
--EX5 Import all audit files in the directory into system
　　
SELECT *
　　
FROM sys.fn_get_audit_file(N’ ’ D:\MSSQL\DATA\Audit_logs\*’,default,default);
　　
--EX6 Convert to Timezone Bejing(GMT+08:00) using data type datetimeoffset & Function SWITCHOFFSET)
　　
SELECT SWITCHOFFSET(CAST(event_time AS datetimeoffset),’+08:00’) N’Audit Action caused date & time(Timezone Bejing GMT+08:00)’,
　　
Server_principal_name N’Login’, database_principal_name N’User’,
　　
Database_name N’Database’, object_name N’Object Name’, statement N’TSQL’
　　
FROM sys.fn_get_audit_file(N’ ’ D:\MSSQL\DATA\Audit_logs\*’,default,default);
　　说明：
　　l 在EX1部分中函数fn_get_audit_file的第一个变量，更换为执行sys.dm_server_audit_status后，取得字段audit_file_path的值部分，或是填入完整的审核文件的文件名。在第二个和第三个变量，填入default，采取默认值即可。
　　l 若要将同一个审核对象所产生的多个审核文件，都加载到系统内进行分析，可以在文件名上，搭配使用通配符*，参考EX2代码。
　　l 审核所记录的日期时间是格林威治时间（GMT），数据类型为datatime2，可以转换成数据类型datetimeoffset，并利用函数SWITCHOFFSET，将此数据改以北京时区（GMT+08:00）的格式来显示，参考EX3和EX4。
　　l 若需要将指定文件夹内的各个审核文件，都加载到系统内进行分析，可以搭配通配符*，参考EX5和EX6。
任务7：审核文件的归档存放
　　
　　步骤1：在D:\MSSQL\DATA\Audit_logs内创建新的文件夹Archive_Audit。
　　步骤2：打开文件夹D:\MSSQL\DATA\Audit_logs，复制此文件夹内的审核文件（*.sqlaudit）到文件夹D:\MSSQL\DATA\Audit_logs内，此为简易备份审核文件的方式。
　　步骤3：若要删除已经备份过的审核文件，请先确认此审核对象已经禁用。否则，正在使用的审核文件将将无法删除。