تمرين - دفتر الأستاذ
تخيل أنك مدقق لشركة Contoso. يستخدم قسم الموارد البشرية في Contoso تطبيق ويب بسيط لإدارة رواتب الموظفين. يطلب منك تدقيق البيانات في قاعدة البيانات للتأكد من صحة البيانات وأنه لم يعبث بها أحد.
في هذا التمرين، نرى كيف يمكنك استخدام دفتر الأستاذ في سيناريو العالم الحقيقي لتدقيق البيانات باستخدام جداول دفتر الأستاذ. نقوم بتنفيذ المهام التالية:
- إنشاء قاعدة بيانات تسمى
ContosoHR
. - إنشاء جدول دفتر الأستاذ القابل للتحديث يسمى
Employees
. - قم بإجراء تحديثات على البيانات.
- تحقق من أن البيانات يمكن الوثوق بها بالكامل.
- استعلم عن طرق عرض جدول المحفوظات ودفتر الأستاذ لمشاهدة التعقب الذي يحدث والعلاقة بين الجداول.
المتطلبات الأساسية
- تم تثبيت SQL Server 2022.
- أحدث إصدار من SQL Server Management Studio (SSMS).
إنشاء قاعدة البيانات وجدول دفتر الأستاذ
سجل الدخول إلى مثيل SQL Server 2022 باستخدام SSMS مع
sysadmin
الأذونات. إنشاء قاعدة بيانات تسمىContosoHR
والتبديل إلى سياق استخدام قاعدة البيانات هذه.USE master; GO -- Create the ContosoHR database DROP DATABASE IF EXISTS ContosoHR; GO CREATE DATABASE ContosoHR; GO USE ContosoHR; GO
إنشاء جدول دفتر الأستاذ القابل للتحديث من
Employees
أجل عن طريق تنفيذ البرنامج النصي التالي:USE ContosoHR; GO -- Create the Employees table and make it an updatable ledger table DROP TABLE IF EXISTS [dbo].[Employees]; GO CREATE TABLE [dbo].[Employees]( [EmployeeID] [int] IDENTITY(1,1) NOT NULL, [SSN] [char](11) NOT NULL, [FirstName] [nvarchar](50) NOT NULL, [LastName] [nvarchar](50) NOT NULL, [Salary] [money] NOT NULL ) WITH ( SYSTEM_VERSIONING = ON, LEDGER = ON ); GO
إذا قمت بفحص الجدول الذي تم إنشاؤه حديثا في مستكشف عناصر SSMS، فستلاحظ أن الجدول يحتوي على أيقونة جديدة مع علامة اختيار تشير إلى أنه جدول دفتر الأستاذ.
قم بتعبئة
Employees
الجدول ببعض بيانات العينة عن طريق تنفيذ البرنامج النصي التالي:USE ContosoHR; GO -- Clear Employees table DELETE FROM [dbo].[Employees]; GO -- Insert 10 employees. The names and SSN are completely fictional and not associated with any person DECLARE @SSN1 char(11) = '795-73-9833'; DECLARE @Salary1 Money = 61692.00; INSERT INTO [dbo].[Employees] ([SSN], [FirstName], [LastName], [Salary]) VALUES (@SSN1, 'Catherine', 'Abel', @Salary1); DECLARE @SSN2 char(11) = '990-00-6818'; DECLARE @Salary2 Money = 990.00; INSERT INTO [dbo].[Employees] ([SSN], [FirstName], [LastName], [Salary]) VALUES (@SSN2, 'Kim', 'Abercrombie', @Salary2); DECLARE @SSN3 char(11) = '009-37-3952'; DECLARE @Salary3 Money = 5684.00; INSERT INTO [dbo].[Employees] ([SSN], [FirstName], [LastName], [Salary]) VALUES (@SSN3, 'Frances', 'Adams', @Salary3); DECLARE @SSN4 char(11) = '708-44-3627'; DECLARE @Salary4 Money = 55415.00; INSERT INTO [dbo].[Employees] ([SSN], [FirstName], [LastName], [Salary]) VALUES (@SSN4, 'Jay', 'Adams', @Salary4); DECLARE @SSN5 char(11) = '447-62-6279'; DECLARE @Salary5 Money = 49744.00; INSERT INTO [dbo].[Employees] ([SSN], [FirstName], [LastName], [Salary]) VALUES (@SSN5, 'Robert', 'Ahlering', @Salary5); DECLARE @SSN6 char(11) = '872-78-4732'; DECLARE @Salary6 Money = 38584.00; INSERT INTO [dbo].[Employees] ([SSN], [FirstName], [LastName], [Salary]) VALUES (@SSN6, 'Stanley', 'Alan', @Salary6); DECLARE @SSN7 char(11) = '898-79-8701'; DECLARE @Salary7 Money = 11918.00; INSERT INTO [dbo].[Employees] ([SSN], [FirstName], [LastName], [Salary]) VALUES (@SSN7, 'Paul', 'Alcorn', @Salary7); DECLARE @SSN8 char(11) = '561-88-3757'; DECLARE @Salary8 Money = 17349.00; INSERT INTO [dbo].[Employees] ([SSN], [FirstName], [LastName], [Salary]) VALUES (@SSN8, 'Mary', 'Alexander', @Salary8); DECLARE @SSN9 char(11) = '904-55-0991'; DECLARE @Salary9 Money = 70796.00; INSERT INTO [dbo].[Employees] ([SSN], [FirstName], [LastName], [Salary]) VALUES (@SSN9, 'Michelle', 'Alexander', @Salary9); DECLARE @SSN10 char(11) = '293-95-6617'; DECLARE @Salary10 Money = 96956.00; INSERT INTO [dbo].[Employees] ([SSN], [FirstName], [LastName], [Salary]) VALUES (@SSN10, 'Marvin', 'Allen', @Salary10); GO
استكشاف البيانات باستخدام طرق عرض دفتر الأستاذ
فحص الأعمدة باستخدام
SELECT
استعلام. يحتوي البرنامج النصي التالي على مثالين لكيفية الاستعلام عن البيانات. يستخدم*
المثال الأول حرف البدل لإرجاع كافة الأعمدة. يسرد المثال الثاني جميع الأعمدة. لاحظ كيف يحتوي الجدول على أعمدة أخرى ليست جزءا من تعريف الجدول الأصلي. يتم استخدام هذه الأعمدة بواسطة ميزة دفتر الأستاذ لتعقب التغييرات على البيانات.USE ContosoHR; GO -- Use * for all columns SELECT * FROM dbo.Employees; GO -- List out all the columns SELECT EmployeeID, SSN, FirstName, LastName, Salary, ledger_start_transaction_id, ledger_end_transaction_id, ledger_start_sequence_number, ledger_end_sequence_number FROM dbo.Employees; GO
انظر إلى
Employees_Ledger
طريقة العرض عن طريق تنفيذ الاستعلام التاليSELECT
. طريقة العرض هذه منEmployees
الجدول وجدول محفوظات دفتر الأستاذ. يحتوي دفتر الأستاذ على معلومات المعاملة من أعمدة مخفية في الجدول، بالإضافة إلى إشارة إلى نوع العملية التي تم تنفيذها على دفتر الأستاذ لصف معين.USE ContosoHR; GO SELECT * FROM dbo.Employees_Ledger; GO
للحصول على فهم أفضل لتعريف طريقة عرض دفتر الأستاذ، يمكنك تنفيذ الاستعلام التالي:
USE [ContosoHR]; GO sp_helptext 'Employees_ledger'; GO
CREATE VIEW [dbo].[Employees_Ledger] AS SELECT [EmployeeID], [SSN], [FirstName], [LastName], [Salary], [ledger_start_transaction_id] AS [ledger_transaction_id], [ledger_start_sequence_number] AS [ledger_sequence_number], 1 AS [ledger_operation_type], N'INSERT' AS [ledger_operation_type_desc] FROM [dbo].[Employees] UNION ALL SELECT [EmployeeID], [SSN], [FirstName], [LastName], [Salary], [ledger_start_transaction_id] AS [ledger_transaction_id], [ledger_start_sequence_number] AS [ledger_sequence_number], 1 AS [ledger_operation_type], N'INSERT' AS [ledger_operation_type_desc] FROM [dbo].[MSSQL_LedgerHistoryFor_901578250] UNION ALL SELECT [EmployeeID], [SSN], [FirstName], [LastName], [Salary], [ledger_end_transaction_id] AS [ledger_transaction_id], [ledger_end_sequence_number] AS [ledger_sequence_number], 2 AS [ledger_operation_type], N'DELETE' AS [ledger_operation_type_desc] FROM [dbo].[MSSQL_LedgerHistoryFor_901578250]
يمكنك أن ترى أن طريقة العرض عبارة عن اتحاد للجدول الأصلي (للإدراجات الجديدة)، والتحديثات من جدول المحفوظات (للإدراج والحذف).
يمكنك دمج طريقة عرض دفتر الأستاذ مع جدول نظام للحصول على مزيد من معلومات التدقيق، مثل من أجرى المعاملة على قاعدة البيانات، وفي أي تاريخ ووقت. لمشاهدة مثال، قم بتنفيذ الاستعلام التالي.
USE ContosoHR; GO SELECT e.EmployeeID, e.FirstName, e.LastName, e.Salary, dlt.transaction_id, dlt.commit_time, dlt.principal_name, e.ledger_operation_type_desc, dlt.table_hashes FROM sys.database_ledger_transactions dlt JOIN dbo.Employees_Ledger e ON e.ledger_transaction_id = dlt.transaction_id ORDER BY dlt.commit_time DESC; GO
للتحقق من تكامل دفتر الأستاذ، دعنا ننشئ ملخصا عن طريق تنفيذ الإجراء
sp_generate_database_ledger_digest
المخزن . احفظ قيمة الإخراج (بما في ذلك الأقواس) لاستخدامها للتحقق من دفتر الأستاذ.USE ContosoHR; GO EXEC sp_generate_database_ledger_digest; GO
يمكنك الآن رؤية الكتل التي تم إنشاؤها لجدول دفتر الأستاذ من خلال النظر في
sys.database_ledger_blocks
.USE ContosoHR; GO SELECT * FROM sys.database_ledger_blocks; GO
إجراء تغييرات على البيانات
بعد النظر إلى بعض وظائف دفتر الأستاذ في SQL Server 2022، دعنا نقم بإجراء بعض التغييرات على البيانات ونرى كيف يتعقب دفتر الأستاذ التغييرات. يساعدك هذا التمرين في مهمتك المتمثلة في تدقيق البيانات في ContosoHR
قاعدة البيانات، وتحديدا الجدول Employees
.
تظاهر بأن جاي، الموظف في الشركة، سجل الدخول إلى قاعدة البيانات، ونفذ الاستعلام التالي لإضافة 50000 إلى راتبه. لسوء الحظ بالنسبة لجاي،
Employees
الجدول هو جدول دفتر الأستاذ القابل للتحديث. وهذا يعني أن تغييرهم، جنبا إلى جنب مع هويتهم والطوابع الزمنية، مستمر في بنية بيانات دفتر الأستاذ الظاهر للعبث.UPDATE [dbo].[Employees] SET [Salary] = [Salary] + 50000 WHERE [FirstName] = N'Jay' AND [LastName] = N'Adams'
إذا كنت تستخدم استعلاما
SELECT
علىEmployees
الجدول، يمكنك أن ترى أن راتب جاي قد تم تحديثه من وقت سابق.SELECT * FROM [dbo].[Employees]
تدقيق البيانات
لنفترض أنه بعد بضعة أسابيع، تقوم بمراجعة روتينية للتغييرات في ContosoHR
قاعدة البيانات. كخطوة أولى، يمكنك تشغيل التحقق من دفتر الأستاذ للتأكد من أنه يمكنك الوثوق بالبيانات التي ستقوم بفحصها.
نفذ الاستعلام التالي لتشغيل التحقق من دفتر الأستاذ. تحتوي النتائج على أحدث ملخص لقاعدة البيانات وتمثل مزيجًا لقاعدة البيانات في الوقت الحالي. انسخ محتويات النتائج لاستخدامها في الخطوة التالية.
USE ContosoHR; GO EXEC sp_generate_database_ledger_digest; GO
sp_verify_database_ledger
استخدم الإجراء المخزن للتحقق من عدم العبث بالبيانات التاريخية في قاعدة البيانات الخاصة بك. استبدله<YOUR DATABASE DIGEST>
بالملخص الذي نسخته في الخطوة السابقة.EXECUTE sp_verify_database_ledger N' <YOUR DATABASE DIGEST> ';
إذا نجح التحقق ولم يتم العبث ببياناتك، فسترى الرسالة التالية:
باستخدام هذا الملخص، نعرف ما يلي:
- البيانات صالحة، استنادا إلى وقت التقاط الملخص.
- تتطابق الكتل الداخلية مع تغييرات البيانات الحالية للتحديث إلى راتب جاي. إذا اضطر شخص ما إلى تزوير البيانات للجدول
Employees
دون القيام بأمر T-SQLUPDATE
لجعل النظام يعتقد أن راتب جاي الحالي كان 50,000 أكثر مما هو عليه حقا، فإن النظام يثير خطأ أن تجزئة التغييرات لا تتطابق مع البيانات الحالية. سترى رسالةLedger verification failed
.
الآن بعد أن تحققت من عدم العبث بالبيانات، يمكنك استعراض محتوى طريقة عرض دفتر الأستاذ للجدول
Employees
. لاحظت عملية تحديث مشبوهة قام بها Jay، والذي لا يمكنه رفض تحديث الراتب بشكل فعال. يتم التحقق من البيانات الموجودة في جدول دفتر الأستاذ بشكل مشفر على أنها أصلية وتظهر بوضوح اسم المستخدم الخاص ب Jay كحساب قام بتحديث الراتب. لإجراء هذا التحقق، قم بتشغيل الاستعلام التالي.SET NOCOUNT ON SELECT t.[commit_time] AS [CommitTime] , t.[principal_name] AS [UserName] , l.EmployeeId , l.[SSN] , l.[FirstName] , l.[LastName] , l.[Salary] , l.[ledger_operation_type_desc] AS Operation FROM [dbo].[Employees_Ledger] l JOIN sys.database_ledger_transactions t ON t.transaction_id = l.ledger_transaction_id WHERE t.[commit_time] > DATEADD(MONTH, -1, SYSDATETIMEOFFSET()) ORDER BY t.commit_time DESC;