kimchidhcn.files.wordpress.com€¦ · Web viewBài Thực Hành Security. Tuần 1-2-3: Login...

Bài Thực Hành SecurityTuần 1-2-3: Login – User – Roles

PHẦN 1: TRẢ LỜI CÁC CÂU HỎI SAU

1. Cho biết các mục tiêu chính của bảo mật? Các mức bảo mật mà SQL Server hỗ trợ.

2. SQL Server hỗ trợ bao nhiêu chế độ chứng thực? Sự khác biệt? Để thay đối chế độ chứng thực của một thể hiện SQL Server, bạn phải thực hiện như thế nào?

3. Cho biết logins, users là gì?

4. Cho biết Roles là gì? Có mấy loại, mức độ như thế nào? Liệt kê các Roles mà SQL Server có hỗ trợ, nếu bạn là thành viên của Roles đó thì bạn có quyền hạn thư thế nào? Cho biết Permissions là gì?

PHẦN 2: THỰC HÀNH

BÀI 11. Tạo cơ sở dữ liệu QLTV tham số tùy ý2. Tạo các users Minh, Huy, Le, Linh, An, và Binh:

a. Password lần lượt là tên username viết hoa.b. Đảm bảo các user này có thể tạo bất kỳ bảng nào trong tablespace với quota 10M.

Hướng dẫn:1. Tạo CSDL QLTV

create database QLTV2. Tạo các users Minh, Huy, Le, Linh, An, và Binh:

a) Password lần lượt là tên username viết hoa.use QLTVgocreate login Minh with password='MINH'gocreate user Minh for login Minhgouse QLTVgo

Trang 1

Mục tiêu: - Tạo, sửa, xóa được các login - Tạo, sửa, xóa được các user- Tạo, sửa, xóa được các roles- Thực hiện được việc cấp quyền, thu hồi quyền cho các user và roles - Áp dụng mô hình DAC, MAC, RBAC vào bài toán thực tế

create login Huy with password='HUY'gocreate user Huy for login Huygouse QLTVgocreate login Le with password='LE'gocreate user Le for login Legouse QLTVgocreate login Linh with password='LINH'gocreate user Linh for login Linhgouse QLTVgocreate login An with password='AN'gocreate user An for login Angouse QLTVgocreate login Binh with password='BINH'gocreate user Binh for login Binh

b) Đảm bảo các user này có thể tạo bất kỳ bảng nào trong tablespace với quota 10M.Ví dụ minh họa:

goCREATE USER Hung IDENTIFIED BY out_standing1 DEFAULT TABLESPACE example QUOTA 10M ON example TEMPORARY TABLESPACE tempQUOTA 10M ON system PROFILE app_user PASSWORD 123456;

3. Cho bảng SachSach(MaSach INT PRIMARY KEY,

Trang 2

TenSach NVARCHAR(40))

Tạo bảng SachCreate table Sach(

ID INT PRIMARY KEY,Name NVARCHAR(2)

)Làm các bước sau:a. Tạo các role sau: DataEntry, Supervisor, và Management.

gouse QLTVgocreate application role DataEntry;gouse QLTVgocreate application role Supervisor;gouse QLTVgocreate application role Management;

b. Gán Minh, Huy, và Linh vào role DataEntry, gán Le vào role Supervisor, và gán An và Binh vào role Management.

goEXEC sp_addrolemember 'DataEntry','Minh'goEXEC sp_addrolemember 'DataEntry','Huy'goEXEC sp_addrolemember 'DataEntry','Lym'--role SupervisorgoEXEC sp_addrolemember 'Supervisor','Le'--role ManagementgoEXEC sp_addrolemember 'Management','An'goEXEC sp_addrolemember 'Management','Binh'

c. Cho role DataEntry các quyền SELECT, INSERT, và UPDATE trên bảng Sach.go

Trang 3

use QLTVgogrant select,insert,update on Sach to DataEntry

d. Cho role Supervisor các quyền SELECT và DELETE trên bảng Sach.gouse QLTVgogrant select,delete on Sach to Supervisor

e. Cho role Management quyền SELECT trên bảng Sach.gouse QLTVgogrant select on Sach to Management

f. Lần lượt kiểm tra kết quả phân quyền đã cấp cho các role--Dang nhap bang user Minh thuc hien lenh select , insert, update cua role DataEntry

gouse QLTVgoselect * from Sachgoinsert into Sach values (1,'df')goselect * from Sachgoupdate Sachset Name='gg'where ID = 1goselect * from Sach--Dang nhap bang user Le thuc hien lenh select, delete cua role Supervisor gouse Sachgoselect * from Sachgodelete from Sach where ID=1goselect * from Sach--Dang nhap bang user An thuc hien lenh select cua role Managementgo

Trang 4

use QLTVgoselect * from Sach

4. Tạo một user mới tên NameManager với password là pc123. Gán quyền update cho user này trên cột TenSach của bảng Sach.

--tao usergouse QLTVgocreate login NameManager with password='pc123'gocreate user NameManager for login NameManager--gan quyengouse QLTVgogrant update on Sach(TenSach) to NameManager--user chay thu quyen dc capgouse QLTVgoupdate Sachset TenSach='TD'where Masach=1

–Lệnh xem các quyềnSELECT [UserName] = CASE memberprinc.[type] WHEN 'S' THEN memberprinc.[name] WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI END, [UserType] = CASE memberprinc.[type] WHEN 'S' THEN 'SQL User' WHEN 'U' THEN 'Windows User' END, [DatabaseUserName] = memberprinc.[name], [Role] = roleprinc.[name], [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = obj.type_desc,--perm.[class_desc], [ObjectName] = OBJECT_NAME(perm.major_id)

Trang 5

FROM --Role/member associations sys.database_role_members membersJOIN --Roles sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]JOIN --Role members (database users) sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]LEFT JOIN --Login accounts sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]LEFT JOIN --Permissions sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]LEFT JOIN --Table columns sys.columns col on col.[object_id] = perm.major_id AND col.[column_id] = perm.[minor_id]LEFT JOIN sys.objects obj ON perm.[major_id] = obj.[object_id]

5. Thực hiện các bước sau:a. Gán password cho role DataEntry ở bài 1 là “mgt”b. Cho phép user Minh quyền cấp quyền cho các user khácc. Gán tất cả các quyền mà Minh có cho Binh. Binh có quyền INSERT và UPDATE trên bảng

QLTV không?--a.Gán password cho role DataEntry ở bài 1 là “mgt”

gouse QLTVgoalter application role DataEntry with password = 'mgt'--b. Cho phép user Minh quyền cấp quyền cho các user khácgouse QLTVgogrant select,insert,update on Sach to Minh with grant option-- Minh gan quyen cho user #go

Trang 6

use QLTVgogrant insert on Sach to An-- An chay thu quyen dc cap

gouse QLTVgoinsert into Sach values (1,'df')goselect * from Sach

--c. Gán tất cả các quyền mà Minh có cho Binh. Binh có quyền INSERT và UPDATE trên bảng Sach không?

--dang nhap bang user Joingouse QLTVgogrant select,insert,update on Sach to Binh --Binh chay thu quyen dc capgo

use QLTVgoselect * from Sachgoinsert into Sach values (4,'ff')goselect * from Sachgoupdate Sachset Tensach='gg'where Masach= 4goselect * from Sach

--=> Binh có quyền INSERT và UPDATE trên bảng QLTV

BÀI 2CSDL mẫu: AdventureWorks2008Hướng dẫn: Attach file AdventureWorks2008_Data.mdf, AdventureWorks2008_Log.ldf để tạo cơ sở dữ liệu mẫu AdventureWorks2008

Giả sử ngoài CSDL do SQL server hỗ trợ, còn có CSDL AdventureWorks2008,…. Bạn tạo login chỉ có quyền được cho (không được có quyền cao hơn), tạo xong bạn phải đăng nhập vào SQL Server

Trang 7

bằng chính login vừa tạo, thực hiện kiểm tra quyền bằng cách thực hiện các câu lệnh ứng với quyền được phép và các câu lệnh ứng với quyền không được phép.

I. Tạo các Roles, Logins, gán các quyền bằng thao tác trong SQL Server Management Studio (SSMS).

1. Tạo login dạng SQL Server Authentication

a. Tạo một login có tên là tên của bạn, login có:

Chế độ chứng thực là SQL Server Authentication, password tùy ý, CSDL mặc định là AdventureWorks2008.

Không thuộc Server Roles nào cả

Chỉ cho truy xuất đến duy nhất CSDL là AdventureWorks2008 và không thuộc Database Roles nào cả ngoại trừ Public

Kiểm tra:

- Ở SSMS, kiểm tra xem tên login của bạn có nằm trong nhánh Security\Login không? kiểm tra xem tên login của bạn có nằm trong nhánh User của CSDL AdventureWorks2008 không? Xem properties của nó.

- Kết nối vào SSMS bằng login vừa tạo

- Trong mục database bạn có thể nhìn thấy được những database nào? Tại sao?

- Dùng câu lệnh SELECT … FROM… để xem các mẫu tin trong bảng Production.Product, bạn xem được không? Tại sao?

b. Hiệu chỉnh login ở trên, cho phép login thuộc database Roles tên là db_DataReader trong CSDL AdventureWorks2008

Kiểm tra:

- Dùng câu lệnh SELECT … FROM… để xem các mẫu tin trong bảng Production.Product, bạn xem được không? Tại sao?

- Dùng câu lệnh INSERT … VALUES để chèn một mẫu tin mới vào bảng Production.Product, bạn có chèn được không? Tại sao? Muốn chèn được bạn phải làm gì? Thực hiện thử xem sao.

c. Tương tự như vậy, lần lược tìm hiểu các database Roles còn lại.

2. Tạo login dạng Windows Authentication

a. Quay về hệ điều hành tạo một local user account hoặc domain user account được phép kết nối đến máy Server của SQL Server. User account này có tên là Nhanvien1.

b. Cho phép Nhanvien1 trở thành login của SQL Server, login này chỉ thuộc vào database Roles là db_datareader của CSDL là AdventureWorks2008. (Lưu ý: phải chọn Windows Authentication)

Trang 8

c. Bạn hãy thử kết nối Server thông qua công cụ SSMS bằng login vừa tạo và kiểm tra quyền của login đối với AdventureWorks2008.

(Hướng dẫn: đóng hết các ứng dụng đang chạy, log off user hiện kết nối đến máy, log on vào máy bằng user account vừa tạo, kết nối vào SSMS bằng login)

3. Login được tạo ở câu 1 và câu 2 có thể thực hiện tạo được Table, view, … trong CSDL AdventureWorks2008 hay không? Muốn tạo được cần có điều gì?

4. Tạo một Database Role có tên là NVHoaDon của CSDL AdventureWorks2008, Role này có quyền hạn như sau:

- Được phép chèn, cập nhật dữ liệu trong hai bảng Purchasing.PurchaseOrderHeader và Purchasing.PurchaseOrderDetail

- Chỉ được phép xem (Select) trên bảng Purchasing.WorkOrder

5. Tạo 3 login dạng SQL Server Authentication, có tên lần lược là NVHD1, NVHD2, NVHD3. Các Login này chỉ thuộc duy nhất DataBase Role là NVHoaDon đã tạo ở trên. Đăng nhập vào từng login NVHD1, NVHD2, NVHD3, ứng với mỗi login thực hiện các công việc sau:

- Xem thông tin các bảng Purchasing.PurchaseOrderHeader Purchasing.PurchaseOrderDetail, Purchasing.WorkOrder

- Chèn vào các bảng Purchasing.PurchaseOrderHeader Purchasing.PurchaseOrderDetail, Purchasing.WorkOrder, mỗi bảng 1 record với dữ liệu tùy ý, chú ý các ràng buộc khóa ngoại

- Xóa một record bất kỳ trong mỗi bảng sau Purchasing.PurchaseOrderHeader Purchasing.PurchaseOrderDetail, Purchasing.WorkOrder.

- Nếu thực hiện lệnh Update cho 3 bảng Purchasing.PurchaseOrderHeader Purchasing.PurchaseOrderDetail, Purchasing.WorkOrder có thực hiện được không? Giải thích và cho ví dụ minh họa trong cả 2 trường hợp được hoặc không được.

6. Tạo 3 login dạng SQL Server Authentication, có tên lần lược là QLKho1, QLKho2, QLKho3. Các login này có cùng một quyền hạn là được phép chèn, xóa dữ liệu trên bảng Production.Product; cập nhật duy nhất cột ListPrice trong bảng Production.Product. Chỉ được phép xem (Select) trên bảng Production.WorkOrder. Cho ví dụ kiểm tra các trường hợp đã cấp quyền cho mỗi login thông qua các lệnh insert, update, delete, select

7. Bạn chọn một giải pháp đơn giản nhất để cho phép các login đã tạo ở trên được phép xem thông tin trong bảng HumanResources.Employee.

8. Tạo hai login thuộc dạng SQL Server Autehtication, có tên lần lược là PTUD1, PTUD. Các login này có các quyền như sau:

- Được phép tạo các đối tượng của database

- Được phép truy xuất và hiệu chỉnh các đối tượng database

Ứng với mỗi login thực hiện các lệnh sau:

a) Tạo Table UngDung(MaUD int primary key, TenUD nvarchar(30))Trang 9

b) Thêm cột TacGia nvarchar(30) vào bảng UngDungc) Tăng độ rộng cho cột TenUD lên 50 ký tựd) Thêm vào UngDung 2 record có dữ liệu tùy ýe) Tạo thủ tục cho phép xem thông tin của một ứng dụng bất kỳf) Xóa dữ liệu có trong bảng UngDungg) Chạy thủ tục đã tạo ở câu eh) Xóa thủ tục câu e

II. Tạo các Roles, Logins, gán các quyền bằng T_SQL thông qua các thủ tục hệ thống.

Chú ý sau mỗi câu bạn thực hiện kiểm tra lại các lệnh bạn vừa thực hiện

1. Tạo một login dạng Windows Authentication có tên là GD1 (vào hệ điều hành Window tạo user GD1 trước khi tạo ).

2. Tạo hai login dạng SQL Server Authentication tên là PGD1 và PGD2 có password tùy ý.

1. Bạn hãy tạo một user-defined role với tên là QLSP có các quyền sau: thêm, xóa, sửa trên bảng Production.Product. Tạo 3 user ứng với 3 login trên, thực hiện thêm 3 user là thành viên của role QLSP.

2. Giả sử bạn muốn cấm 1 cách tường minh quyền thêm, xóa, sửa trên bảng Production.Product đối với user PGD1, cho dù user này là thành viên của role có các quyền trên (quyền thêm, xóa, sửa trên bảng Production.Product) thì user này cũng bị cấm. Các user khác không bị ảnh hưởng. Bạn thực hiện thế nào?

3. Ở câu 4 bạn đã cấm quyền thêm, xóa, sửa trên bảng Production.Product đối với user PGD1. Bạn muốn khôi phục lại quyền thêm, xóa, sửa trên bảng Production.Product đối với user PGD1. Bạn thực hiện thế nào?

4. Ở câu 3 bạn đã cấp quyền cho role QLSP: thêm, xóa, sửa trên bảng Production.Product. Bạn muốn cấm quyền thêm, xóa, sửa trên bảng Production.Product đối với role này. Bạn thực hiện thế nào? Các user là thành viên của role QLSP có các quyền gì ở lúc này?

5. Tạo hai login dạng SQL Server Authentication có tên là NghiepVu1, NghiepVu2. Tạo 2 user NghiepVu1, NghiepVu2 ứng với 2 login trên, 2 user này có các quyền sau: xem và hiệu chỉnh cột ListPrice trong bảng Production.Product ; xem, hiệu chỉnh, xóa dữ liệu trong bảng Production.WorkOrder và Production.Product, chỉ được phép xem (Select) trên bảng Purchasing.WorkOrder.

BÀI 3: ỨNG DỤNG VÀO BÀI TOÁN CỤ THỂ

Bài 1Câu 1: Sử dụng tài khoản với quyền quản trị thực hiện tạo login, user và cấp quyền1.1 Tạo login tên admin1, mật khẩu Abc12345

1.2 Tạo user thuộc cơ sở dữ liệu master (Databases->System Databases->master)

1.3 Cấp quyền tạo cơ sở dữ liệu, tạo bảng và quyền tạo login cho admin1Trang 10

Câu 2: Sử dụng tài khoản admin1 và thực hiện các yêu cầu sau2.1 Tạo CSDL QuanLyNhanSu

File Size MaxSize FileGrowth

Data 100 Không giới hạn 50

Log 300 Không giới hạn 100

2.2 Tạo bảng NhanVien và LuongNV thuộc CSDL QuanLyNhanSu

Bảng NhanVien

Tên cột Kiểu dữ liệu Số kí tự Ghi chú

MaNv Varchar 20 Khóa chính

TenNv Nvarchar 100

NgaySinh Varchar 10

NoiSinh Nvarchar 50

Bảng LuongNV


MaNv varchar 20

Khóa chínhNamThang varchar 7

Luong Float

2.3 Tạo login chứng thực SQL Server (SQL Server Authencation)

Tên login Mật khẩu

LyNT Abc12345

HungNT Abc12345

2.4 Tạo user

Tên user Tên login

LyNT LyNT

HungNT HungNT

2.5 Cấp quyền

Tên user Tên bảng Quyền được cấp

LyNT NhanVien, LuongNV Thêm, xóa dữ liệu

Trang 11

HungNT NhanVien

Chỉ được phép xem MaNv,TenNV và cấp quyền Cập nhật dữ liệu

2.6. Kiểm tra cấp quyềnThực hiện các lệnh sau với user LyNT và HungNT

1) Thêm vào bảng nhân viên dòng dữ liệu (‘A01’, ‘Nguyễn Anh Linh’, ‘1/2/88’,’TPHCM’)2) Xem thông tin bảng nhân viên3) Sửa dữ liệu nơi sinh cho nhân viên này thành Hà Nội4) Xóa nhân viên này khỏi bảng nhân viênCác lệnh trên có thực hiện được không? Lệnh nào không thực hiện được giải thích tại sao không thực thi được?5) Thêm quyền cập nhật dữ liệu cho bảng LuongNV cho user HungNT. Sau đó thực hiện lại các

lệnh trên. Nhận xét.

Bài 2:Câu 1: Tạo database tên QuanLyDaoTao

File Size MaxSize FileGrowth

Data 100 Không giới hạn 10

Log 300 Không giới hạn 30

Câu 2: Tạo các bảng thuộc CSDL QuanLyDaoTaoBảng Lop


MaLop Varchar 20 Khóa chính

TenLop Nvarchar 100

Bảng SinhVien


MaSv Varchar 20 Khóa chính

TenSv Nvarchar 100

NgaySinh Varchar 10

NoiSinh Nvarchar 50

MaLop Varchar 20 Khóa ngoại

Bảng MonHoc


Trang 12

MaMh Varchar 20 Khóa chính

TenMh Nvarchar 100

SoGio Int

Bảng DiemTP


MaSv Varchar 20

Khóa chínhMaMh Varchar 20

Diem Float

Câu 3: Thiết lập ràng buộc dữ liệuCâu 4: Thêm dữ liệu cho các bảngBảng Lop

MaLop TenLop

CN0201 Khóa 2001

CN0202 Khóa 2002

Bảng SinhVien

MaSv TenSv NgaySinh(dd/mm/yyyy) NoiSinh MaLop

sv01Nguyễn Văn Hưng 12/02/1988

Hồ Chí Minh CN0201

sv02 Lê Hùng 17/03/1990Bình Dương CN0201

sv03 Lê Hùng 02/12/1991Bình Dương CN0202

Bảng MonHoc

MaMh TenMh SoGio

THVP Tin học văn phòng 45

THDC Tin học đại cương 45

CSDL Cơ sở dữ liệu 30

Bảng DiemTP

MaSv MaMh Diem

sv01 THVP 8.0

Trang 13

sv01 THDC 7.0

sv01 CSDL 6.0

sv02 THVP 9.0

sv02 THDC 4.0

sv02 CSDL 7.0

sv03 THVP 5.0

sv03 THDC 5.0

sv03 CSDL 5.0

Câu 5: Cấp quyền

5.1 Tạo các nhóm quyền thuộc CSDL QuanLyDaoTao tương ứng với các phòng ban như mô tả trên

5.2 Cấp quyền cho các phòng ban trên CSDL QuanLyDaoTao

1. Ban Giam Hiệu được phép xem tất dữ liệu tất cả các bảng của database2. Khoa Mạng Truyền Thông được phép xem trên bảng SINHVIEN, LOP3. Khoa Kỹ thuật phần mềm đươc phép xem, thêm, xóa và cập nhật dữ liệu trên bảng SINHVIEN,

LOP, MONHOC4. Khoa Tài chính kế toán chỉ được phép xem dữ liệu trên bảng DiemTP5. Phòng Đào tạo được phép xem,thêm,xóa và cập nhật dữ liệu trên tất cả các bảng của database

5.3 Tạo danh sách nhân sự cho các phòng ban (Lưu ý tất cả tài khoản phải đổi mật khẩu trong lần đăng nhập đầu tiên)Ban Giám Hiệu

Tên Mật khẩu

AnhNH Abc12345

HoangNT Abc12345

Khoa Mạng Truyền Thông

Tên Mật khẩu

Trang 14

http://giasutinhoc.vn/wp-content/uploads/2016/04/bao-mat-co-so-du-lieu-sql-server-12.png

TrungDM Abc12345

CongND Abc12345

DangNS Abc12345

Khoa Kỹ thuật phần mềm

Tên Mật khẩu

ThuatDV Abc12345

DatDT Abc12345

NguyenTT Abc12345

Khoa Tài chính kế toán

Tên Mật khẩu

TuanTV Abc12345

DieuNT Abc12345

GiangNN Abc12345

Phòng Đào tạo

Tên Mật khẩu

TramNTH Abc12345

ThuyLT Abc12345

5.4 Chọn một thành viên đại diện cho mỗi phòng ban, bạn thực hiện các lệnh insert, update, delect, select cho mỗi table, giải thích từng lệnh mà bạn đã thực hiện được.

Tuần 4-5-6: Transaction

PHẦN 1: BACKUP AND RECOVERY

Trang 15

Mục tiêu: - Backup and recovery- Index- Transaction

1. Tạo CSDL Qlsach tham số tùy ý. Tạo các table có cấu trúc sau:NhomSach(MaNhom char(5), TenNhom nvarchar(25))NhanVien(MaNV char(5), HoLot nvarchar(25), TenNV nvarchar(10), Phai nvarchar(3), NgaySinh Smalldatetime, DiaChi nvarchar(40))DanhMucSach(MaSach char(5), TenSach nvarchar(40), TacGia nvarchar(20),

MaNhom char(5), DonGia Numeric(5), SLTon numeric(5))HoaDon(MaHD char(5), NgayBan SmallDatetime, MaNV char(5))ChiTietHoaDon(MaHD char(5), MaSach char(5), SoLuong numeric(5))

Nhập dữ liệu cho các table trênNhomSach

MANHOM TENNHOMN001 Kỹ thuật trồng trọt

SachMaS

H TenSach TacGia MaNH DonGia SlTon

S111 Đèn không hắt bóng

Dzunichi Watanabe (Cao Xuân Hạo dịch)

N001 55000 45

S112Kỹ thuật trồng hoa phong lan

Nguyễn Lân Hùng N001 45000 35

S113Kỹ thuật chăm sóc hoa mai

Lê Xuân A N007 35000 15

S114Kỹ thuật chăm sóc cây cam

Trần Ha N001 24000 12

Các Table khác sinh viên tự thêm dữ liệu vào2. Tạo thiết bị backup có tên QuanLySachBackup lưu trong thư mục T:\backup\

QuanLySachBackup.bakViết câu lệnh để set CSDL QLSach về chế độ phục hồi là FULL

3. Thực hiện các backup sau cho CSDL QLSach, tất cả backup đều lưu vào thiết bị backup vừa tạoa. Thực hiện full backup CSDL QLSachb. Thực hiện việc insert sau:

insert NhomSach values('N007','Truyen ngan')

insert HoaDon values('11',GETDATE(),'NV001')

Trang 16

Bạn muốn backup CSDL QLSach đến thời điểm đã thay đổi trong câu b ( nghĩa là đã có 2 nhóm sách 'N007' và hóa đơn 11), thay vì thực hiện full backup để backup lại CSDL đến thời điểm ở câu b, bạn sẽ thực hiện loại backup nào kết hợp với full backup ở câu a để thực hiện yêu cầu trên mà tiết kiệm được dung lượng đĩa? Bạn hãy thực hiện việc backup mà bạn chọn lựa.

Xóa CSDL, thực hiện phục hồi lại CSDL QLSach đến thời điểm của câu b

Chú ý xem có mẫu tin vừa insert không

c. Thực hiện việc insert sau:

insert NhomSach values('N008','Phap van')


Giả sử đến thời điểm này bị sự cố ( bạn tự giả lập sự cố này cách cho database offline rồi xóa datafile), hiện tại bạn chỉ có bản backup của câu a, câu b. Bạn hãy thực hiện việc phục hồi dữ liệu sao cho không mất dữ liệu nào cả (nghĩa là vẫn có nhóm sách N008 và hóa đơn 12)

d. Thực hiện việc insert sau:

insert NhomSach values('N009','Dia ly')


Tạo 1 transaction log backup Xóa CSDL QLSach

Thực hiện việc restore CSDL QLSach về thời điểm của câu d

e. Xóa CSDL QLSach và phục hồi dữ liệu về thời điểm insert hóa đơn 11 ở câu b

Câu 2: Thực hiện giống như bài 1, thay vì tạo thiết bị backup QuanLySachBackup, các file backup sẽ được lưu trực tiếp vào file cụ thể (sv tự đặt tên file).

PHẦN 2: INDEX

Cơ sở dữ liệu mẫu: AdventureWork2008

Trong CSDL mẫu trên đã tạo sẵn các chỉ mục sau cho bảng Production.WorkOrder:

CREATE TABLE [Production].[WorkOrder](

[WorkOrderID] [int] IDENTITY(1,1) NOT NULL,

[ProductID] [int] NOT NULL,

[OrderQty] [int] NOT NULL,

[StockedQty] AS (isnull([OrderQty]-[ScrappedQty],(0))),

Trang 17

[ScrappedQty] [smallint] NOT NULL,

[StartDate] [datetime] NOT NULL,

[EndDate] [datetime] NULL,

[DueDate] [datetime] NOT NULL,

[ScrapReasonID] [smallint] NULL,

[ModifiedDate] [datetime] NOT NULL,

CONSTRAINT [PK_WorkOrder_WorkOrderID] PRIMARY KEY CLUSTERED

([WorkOrderID] ASC)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,

IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY];

Trong đó: PK_WorkOrder_WorkOrderID (clustered) IX_WorkORder_ProductID (non-unique, non-clustered) IX_WorkOrder_ScrapReasonID (non-unique, non-clustered)

SQL Server sử dụng 3 thao tác chính để tìm dữ liệu: Table scan: đọc toàn bộ heapIndex scan: đọc toàn bộ mức lá của chỉ mục clustered hay non-clustered ̣Index seek: định vị dòng dữ liệu thông qua b-tree và trả về chỉ các dòng được chọn

1. Vào Management Studio vào màn hình query:

Chọn CSDL mẫu AdventureWorks2008:

Dùng lệnh:

use AdventureWorks2008

(Hoặc thực hiện bằng thao tác chọn database AdventureWorks2008)

Dùng lệnh để xem các chỉ mục đã tạo trong bảng Production.WorkOrder

sp_helpindex 'Production.WorkOrder'

Thực hiện các câu truy vấn sau:

SELECT *

FROM Production.WorkOrderTrang 18

Câu truy vấn trên sử dụng các chỉ mục nào và sử dụng thao tác gì ?

Bạn hãy sử dụng Query Optimizer để xem cách xử lý câu truy vấn trên bằng cách

bôi đen câu lệnh trên và nhấn vào biểu tượng Execution Plan

Hoặc bôi đen câu lệnh và click chuột phải, chọn Display Estimated Execution Plan

2. SELECT *

FROM Production.WorkOrder

WHERE WorkOrderID = 1234;

Câu truy vấn trên sử dụng các chỉ mục nào và sử dụng thao tác gì?

Bạn hãy sử dụng Query Optimizer để xem cách xử lý câu truy vấn trên

3. SELECT *


WHERE WorkOrderID between 10000 and 10010;

Câu truy vấn trên sử dụng các chỉ mục nào và sử dụng thao tác gì?


4. Giả sử bạn có nhu cầu truy vấn thường xuyên câu lệnh sau:

SELECT *


WHERE StartDate = '2003-06-25';

Bạn hãy thực hiện việc tạo chỉ mục thích hợp cho câu trên?


So sánh với trước khi tạo chỉ mục.

5. Giả sử có nhu cầu truy vấn thường xuyên câu sau:

SELECT WorkOrderID, StartDate


Trang 19

WHERE ProductID = 757

Để giảm chi phí bookmark lookup, nghĩa là thực hiện việc tìm kiếm trên 1 bảng, không cần nhảy sang 1 bảng khác, bạn hãy xóa 1 chỉ mục có sẵn và tạo lại chỉ mục sao cho thích hợp đối với câu trên?

Bạn hãy sử dụng Query Optimizer để xem cách xử lý câu truy vấn trên, có nhận xét gì so

với trước khi tạo chỉ mục vừa tạo.

Bạn hãy sử dụng Database Tuning Advisor để xem lời khuyên về việc tạo chỉ mục đối với câu truy vấn trên.

6. Giả sử có nhu cầu truy vấn thường xuyên câu sau:

SELECT WorkOrderID, StartDate


WHERE ProductID = 757

AND StartDate = '2002-01-04';

Bạn hãy thực hiện việc tạo chỉ mục thích hợp cho câu trên? Nếu có nhiều giải pháp, hãy đưa ra nhận xét cho từng giải pháp và lựa chọn giải pháp phù hợp nhất.

Bạn hãy sử dụng Query Optimizer để xem cách xử lý câu truy vấn trên.

Bạn hãy sử dụng Database Tuning Advisor để xem lời khuyên về việc tạo chỉ mục đối với câu truy vấn trên.

7. Thực hiện lệnh để xem tình trạng phân mảnh chỉ mục của các bảng sau:

a. Production.Product

b. HumanResource.Employee

Nếu có phân mảnh hãy viết lệnh để tổ chức lại chỉ mục.

PHẦN 3: TRANSACTION

Cơ sở dữ liệu mẫu: QLSach

1. So sách mức cô lập READ UNCOMMITTED và READ COMMITTED.

Trường hợp 1T1 T2

Trang 20

USE QLSach

BEGIN TRAN

SELECT sum(SLTon)

FROM DanhMucSach

WHERE MaNhom = 'N001'

UPDATE DanhMucSach

SET SLTon = SLTon +1

WHERE MaSach = 'S001'

waitfor delay '00:00:15‘

USE QLSach

BEGIN TRAN

SELECT sum(SLTon)

FROM DanhMucSach


COMMIT TRAN

Nhận xét kết quả ? Giải thích ?Các vấn đề :

1. Mức cô lập mặc định là gì ? Chú ý kết quả xuất của T2, có phản ánh sự thay đổi của T1?

Trang 21

Trường hợp 1aT1 T2

USE QLSach

SELECT sum(SLTon)

FROM DanhMucSach


UPDATE DanhMucSach




USE QLSach

BEGIN TRAN

SELECT sum(SLTon)

FROM DanhMucSach


COMMIT TRAN

Nhận xét kết quả ? Giải thích ?

Trang 22

Trường hợp 1bT1 T2

USE QLSach

BEGIN TRAN

SELECT sum(SLTon)

FROM DanhMucSach


UPDATE DanhMucSach




SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

USE QLSach

BEGIN TRAN

SELECT sum(SLTon)

FROM DanhMucSach


COMMIT TRAN


Trang 23

Trường hợp 2T1 T2

USE QLSach

BEGIN TRAN

SELECT sum(SLTon)

FROM DanhMucSach


UPDATE DanhMucSach




SET TRANSACTION ISOLATION LEVEL READ COMMITTED

USE QLSach

BEGIN TRAN

SELECT sum(SLTon)

FROM DanhMucSach


COMMIT TRAN


2. So sách mức cô lập READ COMMITTED và REPEATABLE READ. Thử nghiệm nếu 1 transaction đang thực hiện thao tác đọc, có cho phép transaction khác thực hiện thao tác ghi (update, delete) trên đơn vị dữ liệu mà thao tác đọc đang quan tâm không?

Trang 24

Trường hợp 1a

T1 T2


USE QLSach

BEGIN TRAN

SELECT sum(SLTon)

FROM DanhMucSach



USE QLSach

BEGIN TRAN

SELECT sum(SLTon)

FROM DanhMucSach


UPDATE DanhMucSach



SELECT sum(SLTon)

Nhận xét: T2 không cần chờ T1 thực hiện xong mới thực hiện được lệnh Update. Giải thích ?

Trang 25

Trường hợp 1b

T1 T2


USE QLSach

BEGIN TRAN

SELECT sum(SLTon)

FROM DanhMucSach



USE QLSach

BEGIN TRAN

SELECT sum(SLTon)

FROM DanhMucSach


UPDATE DanhMucSach



SELECT sum(SLTon)

FROM DanhMucSach



Trang 26

Trường hợp 2

T1 T2

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

USE QLSach

BEGIN TRAN

SELECT sum(SLTon)

FROM DanhMucSach



USE QLSach

BEGIN TRAN

SELECT sum(SLTon)

FROM DanhMucSach


UPDATE DanhMucSach



SELECT sum(SLTon)

FROM DanhMucSach


Nhận xét kết quả ? Giải thích?

Trang 27

3. So sách mức cô lập REPEATABLE READ và SERIALIZABLE. Thử nghiệm xem nếu 1 transaction đang đọc có cho phép một transaction khác thực hiện ghi (insert) trên dữ liệu mà giao tác đọc đang quan tâm không?

Trường hợp 1

T1 T2

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

USE QLSach

BEGIN TRAN

SELECT sum(SLTon)

FROM DanhMucSach



SELECT sum(SLTon)

FROM DanhMucSach


USE QLSach

BEGIN TRAN INSERT DanhMucSach values('S011',N'Hợp ngữ và LT

Điều kiển thiết bị',N'Nguyễn Minh Tuấn','N001',25000,20)


Trường hợp 2a

Trang 28

T1 T2

SET TRANSACTION ISOLATION LEVEL

SERIALIZABLE

USE QLSach

BEGIN TRAN

SELECT sum(SLTon)

FROM DanhMucSach



USE QLSach

BEGIN TRAN INSERT DanhMucSach values('S011',N'Hợp ngữ và LT


Trường hợp 2b

T1 T2

Trang 29

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

USE QLSach

BEGIN TRAN

SELECT sum(SLTon)

FROM DanhMucSach



USE QLSach

BEGIN TRAN INSERT DanhMucSach values('S011',N'Hợp ngữ và

LT Điều kiển thiết bị',N'Nguyễn Minh Tuấn ,

'N001',25000,20)


Trang 30

Bài 2:+ Nội dung kiến thức thực hành:

+Transaction: Chỉ định khoá trực tiếp trong từng lệnhThay vì các mức cô lập read uncommitted, read committed, repeatable read, serializable, trở lại mức cô lập mặc định, hãy đặt khóa trực tiếp vào các lệnh sao cho có thể giải quyết các vấn đề trên thay cho mức cô lập read uncommitted, read committed, repeatable read, serializable.Bài 3:+ Nội dung kiến thức thực hành:

+Transaction: mức cô lập OptimisticMức cô lập Read Committed ( Snapshot Isolation ) : chỉ thiết lập ở database

Thiết lập mức cô lập:

ALTER DATABASE <Tên Database>

SET READ_COMMITTED_SNAPSHOT ON

Ví dụ:

ALTER DATABASE QLSach

SET READ_COMMITTED_SNAPSHOT ON

T1 T2

31

USE QLSach

BEGIN TRAN

SELECT sum(SLTon)

FROM DanhMucSach


UPDATE DanhMucSach



SELECT sum(SLTon)

FROM DanhMucSach



USE QLSach

BEGIN TRAN

SELECT sum(SLTon)

FROM DanhMucSach



Nhận xét kết quả ? Giải thích ?Mức cô lập Snapshot Isolation (SI): Thiết lập ở 2 nơi: ở database và ở connection

Thiết lập mức cô lập:

ALTER DATABASE <Tên Database>

SET ALLOW_SNAPSHOT_ISOLATION ON

Ví dụ: ALTER DATABASE QLSach SET ALLOW_SNAPSHOT_ISOLATION ON

32

T1 T2

USE QLSach

BEGIN TRAN

SELECT sum(SLTon)

FROM DanhMucSach


UPDATE DanhMucSach



SELECT sum(SLTon)

FROM DanhMucSach



COMMIT TRAN

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

USE QLSach

BEGIN TRAN

SELECT sum(SLTon)

FROM DanhMucSach



SELECT sum(SLTon)

FROM DanhMucSach


33

Sự xung đột khi 2 transaction cùng cập nhật ở mức cô lập Snapshot Isolation

T1 T2


BEGIN TRAN

USE QLSach

BEGIN TRAN

SELECT sum(SLTon)

FROM DanhMucSach

WHERE MaNhom = 'N001' waitfor delay '00:00:10'

UPDATE DanhMucSach



USE QLSach

BEGIN TRAN

UPDATE DanhMucSach


WHERE MaSach = 'S001' waitfor delay '00:00:15'

Nhận xét kết quả ? Giải thích ?Giải quyết lỗi trên bằng cách đặt lock hint trực tiếp cho lệnh

34

T1 T2


BEGIN TRAN

USE QLSach

BEGIN TRAN

SELECT sum(SLTon)

FROM DanhMucSach WITH (UPDLOCK)

WHERE MaNhom = 'N001' waitfor delay '00:00:10'

UPDATE DanhMucSach



USE QLSach

BEGIN TRAN

UPDATE DanhMucSach


WHERE MaSach = 'S001' waitfor delay '00:00:15'

BÀI TẬP LÀM THÊMSINGLE TRANSACTIONAutocommit mode là chế độ quản lý giao dịch mặc định của SQL Server DatabaseEngine. Mỗi lệnh Transact-SQL được commit hoặc Rollback khi nó hoàn thành.1) Thêm vào bảng Department một dòng dữ liệu tùy ý bằng câu lệnhINSERT..VALUES.1) Thực hiện lệnh chèn thêm vào bảng Department một dòng dữ liệu tùy ý bằng

35

Giữ khóa cho đến hết giao tác

cách thực hiện lệnh Begin tran và rollback, dùng câu lệnh Select*fromDepartment xem kết quả.2) Thực hiện câu lệnh trên với lệnh commit và kiểm tra kết quả.2) Tắt chế độ autocommit của SQL Server (SET IMPLICIT_TRANSACTIONSON). Tạo đoạn batch gồm các thao tác: Thêm một dòng vào bảng Department Tạo một bảng Test (id int, name nvarchar(10)) Thêm một dòng vào Test ROLLBACK; Xem dữ liệu ở bảng Department và Test để kiểm tra dữ liệu, giải thích kếtquả.Mục tiêu: Sinh viên hiểu khái niệm về transaction, 4 thuộc tính cơ bản củatransaction, cơ chế hoạt động của transaction Hiện thực được transaction trong một ngữ cảnh cụ thể3) Viết đoạn batch thực hiện các thao tác sau (lưu ý thực hiện lệnh SETXACT_ABORT ON: nếu câu lệnh T-SQL làm phát sinh lỗi run-time, toàn bộ giaodịch được chấm dứt và Rollback) Câu lệnh SELECT với phép chia 0 :SELECT 1/0 as Dummy Cập nhật một dòng trên bảng Department với id=’9’ (id này không tồn tại) Xóa một dòng không tồn tại trên bảng Department (id=’66’) Thêm một dòng bất kỳ vào bảng Department COMMIT;Thực thi đoạn batch, quan sát kết quả và các thông báo lỗi và giải thích kết quả.4) Thực hiện lệnh SET XACT_ABORT OFF (những câu lệnh lỗi sẽ rollback,transaction vẫn tiếp tục) sau đó thực thi lại các thao tác của đoạn batch ở câu 3. Quansát kết quả và giải thích kết quả?II. CONCURRENT TRANSACTIONS1) Tạo bảng Accounts (AccountID int NOT NULL PRIMARY KEY,balance int NOT NULLCONSTRAINT unloanable_account CHECK (balance >= 0)Chèn dữ liệu:INSERT INTO Accounts (acctID,balance) VALUES (101,1000);INSERT INTO Accounts (acctID,balance) VALUES (202,2000);2) SET TRANSACTION ISOLATION LEVELSET TRANSACTION ISOLATION LEVEL

36

{ READ UNCOMMITTED| READ COMMITTED| REPEATABLE READ| SNAPSHOT| SERIALIZABLE}[ ; ] READ UNCOMMITTED: có thể đọc những dòng đang được hiệu chỉnhbởi các transaction khác nhưng chưa commit READ COMMITTED: không thể đọc những dòng đang hiệu chỉnh bởinhững transaction khác mà chưa commit3) Mở 2 cửa sổ Query của SQL server, thiết lập SET TRANSACTION ISOLATIONLEVEL READ COMMITTED ở cả 2 cửa sổ (tạm gọi là clients A bên trái, vàclient B bên phải). Client A, client B: cùng thực hiện lệnh SELECT trên bảng Accounts vớiAccountID =101 Clients A cập nhật account trên AccountID =101, balance =1000-200 Client B cập nhật account trên AccountID =101, balance =1000-500 Client A: SELECT trên Accounts với AccountID =101; COMMIT; Client B: SELECT trên Accounts với AccountID =101; COMMIT;Quan sát kết quả hiển thị và giải thích.4) Thiết lập ISOLATION LEVEL REPEATABLE READ (không thể đọc được dữliệu đã được hiệu chỉnh nhưng chưa commit bởi các transaction khác và không cótransaction khác có thể hiệu chỉnh dữ liệu đã được đọc bởi các giao dịch hiện tạicho đến transaction hiện tại hoàn thành) ở 2 client. Thực hiện yêu cầu sau: Client A, client B: cùng thực hiện lệnh SELECT trên bảng Accounts vớiAccountID =101 Clients A cập nhật accounts trên AccountID =101, balance =1000-200 Client B cập nhật accounts trên AccountID =101, balance =1000-500. Client A: SELECT trên Accounts với AccountID =101; COMMIT; Quan sát kết quả hiển thị và giải thích.5) Giả sử có 2 giao dịch chuyển tiền từ tài khoản 101 và 202 như sau: Client A chuyển 100$ từ tài khoản 101 sang 202 Client B chuyển 200$ từ tài khoản 202 sang 101.Viết các lệnh tương ứng ở 2 client để kiểm soát các giao dịch xảy ra đúng6) Xóa tất cả dữ liệu của bảng Accounts. Thêm lại các dòng mớiINSERT INTO Accounts (AccountID ,balance) VALUES (101,1000);

37

INSERT INTO Accounts (AccountID ,balance) VALUES (202,2000); Client A: cập nhật balance của account giảm đi 100 cho AccountID =101,cập nhật balance của account tăng lên 100 cho AccountID =202 Client B: thiết lập ISOLATION LEVEL READ UNCOMMITTEDSELECT * FROM Accounts;COMMIT; Client A:ROLLBACK;SELECT * FROM Accounts;COMMIT;Quan sát kết quả và giải thích.7) Xóa tất cả dữ liệu của bảng Account, thêm lại các dòng mớiINSERT INTO Accounts (AccountID ,balance) VALUES (101,1000);INSERT INTO Accounts (AccountID ,balance) VALUES (202,2000);- Client A: thiết lập ISOLATION LEVEL REPEATABLE READ;Lấy ra các Accounts có Balance>1000- Client B:INSERT INTO Accounts (AccountID ,balance)VALUES (303,3000);COMMIT;- Client A:SELECT * FROM Accounts WHERE balance > 1000;COMMIT;

Quan sát kết quả và giải thích.

38

Tuần 7-8: Audting

Bài tập 1 :

Tạo giám sát sự đăng nhập thông qua window application log

1. Tạo Audit server (lưu file trong application)CREATE SERVER AUDIT KiemTraDoiTuongTO FILE(FILEPATH='T:\BMCSDL\AUdit\AuditFile') /* substitute in here network drive */WITH (ON_FAILURE=FAIL_OPERATION, QUEUE_DELAY=0);

2. Bật lên (Enable) Audit ServerALTER SERVER AUDIT KiemTraDoiTuong WITH (STATE=ON);

3. Tạo Server SpecificatetioncCREATE SERVER AUDIT SPECIFICATION ThucThiKiemTraDoiTuong FOR SERVER AUDIT KiemTraDoiTuongadd (AUDIT_CHANGE_GROUP)

4. Bật lên Server SpecificatetionALTER SERVER AUDIT SPECIFICATION ThucThiKiemTraDoiTuong WITH (STATE=ON);

5. Thay đổi đường dẫn ALTER SERVER AUDIT KiemTraDoiTuong WITH (STATE=OFF); ALTER SERVER AUDIT KiemTraDoiTuong TO FILE(FILEPATH='D:\Audit'); ALTER SERVER AUDIT KiemTraDoiTuong WITH (STATE=ON);

6. Test drop server audit Giam_Sat_Tong //thử xóa hay tạo audit thì sẽ ghi lại

39

Mục tiêu: - Kiểm soát quá trình hoạt động của một CSDL

7. Truy cập file

SELECT * FROM sys.server_file_auditsSELECT * FROM sys.fn_get_audit_file('D:\Audit\*', NULL, NULL);SELECT * FROM sys.dm_server_audit_status

40

Hình của ví dụ 1

Bài tập 2 :

Tạo giám sát về sự thay đổi dữ liệu trong một bảng nào đó (lưu trong file và đọc từ file ra )

1. Tạo audit sever CREATE SERVER AUDIT KiemTraTao_ThemTO FILE(FILEPATH='D:\Audit1') /* substitute in here network drive */WITH (ON_FAILURE=FAIL_OPERATION, QUEUE_DELAY=0);

2. Enable ALTER SERVER AUDIT KiemTraTao_Them WITH (STATE=ON);

3. Tạo Database audit specificate

CREATE DATABASE AUDIT SPECIFICATION KiemTraTao_ThemFOR SERVER AUDIT KiemTraTao_ThemADD (SELECT , INSERTON [dbo].[NguoiLaoDong] BY dbo ) WITH (STATE = ON) ; GO

41

4. Test thử

select * from NguoiLaoDongInsert into NguoiLaoDong(maNLD,hoTen) values ('NLD5000','Hoai-Yen')Insert into NguoiLaoDong(maNLD,hoTen) values ('NLD6000','Hoai-Yen sua cua user')

5. Đọc file

SELECT * FROM sys.dm_server_audit_statusSELECT * FROM sys.fn_get_audit_file('D:\Audit1\*', NULL, NULL);SELECT * FROM sys.dm_server_audit_status

42

Tuần 9-10: Mã hóa và giải mã

BÀI 1: EncryptionUSE masterGOCREATE DATABASE EncryptTestgo

USE EncryptTestGOCREATE TABLE TestTable (FirstCol INT, SecondCol VARBINARY(256))go

/* Create Database Master Key */CREATE MASTER KEY ENCRYPTIONBY PASSWORD = 'SQLAuthority'GO

/* Create Encryption Certificate */CREATE CERTIFICATE EncryptTestCertWITH SUBJECT = 'SQLAuthority'GO

/* Create Symmetric Key */CREATE SYMMETRIC KEY TestTableKeyWITH ALGORITHM = TRIPLE_DES ENCRYPTIONBY CERTIFICATE EncryptTestCertGO

43

Mục tiêu: - Thực hiện được mã hóa và giải mã trong SQL Server

OPEN SYMMETRIC KEY TestTableKey DECRYPTION BY CERTIFICATE EncryptTestCertGO--UPDATE TestTable--SET EncryptSecondCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),SecondCol)--GOINSERT INTO TestTable values(1,ENCRYPTBYKEY(KEY_GUID('TestTableKey'),'Hello'))INSERT INTO TestTable values(2,ENCRYPTBYKEY(KEY_GUID('TestTableKey'),'123456'))INSERT INTO TestTable values(3,ENCRYPTBYKEY(KEY_GUID('TestTableKey'),'gogogo'))goSELECT * FROM TestTableGO

/* Decrypt the data of the SecondCol */OPEN SYMMETRIC KEY TestTableKey DECRYPTION BY CERTIFICATE EncryptTestCertSELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(SecondCol)) AS DecryptSecondColFROM TestTableGOCLOSE SYMMETRIC KEY TestTableKeyGO

BÀI 2/* Create Database */USE masterGOCREATE DATABASE EncryptTestON PRIMARY ( NAME = N'EncryptTest', FILENAME = N'C:\EncryptTest.mdf')LOG ON ( NAME = N'EncryptTest_log', FILENAME =N'C:\EncryptTest_log.ldf')GO

44

First, let’s create a sample table and then populate it with sample data. We will now encrypt one of the two columns of the table.

/* Create table and insert data in the table */USE EncryptTestGOCREATE TABLE TestTable (FirstCol INT, SecondCol VARCHAR(50))GOINSERT INTO TestTable (FirstCol, SecondCol)SELECT 1,'First'UNION ALLSELECT 2,'Second'UNION ALLSELECT 3,'Third'UNION ALLSELECT 4,'Fourth'UNION ALLSELECT 5,'Fifth'GO/* Check the content of the TestTable */USE EncryptTestGOSELECT *FROM TestTableGOThe preceding code will return the result depicted in the subsequent figure.

Result of the SQL query

Every database can have one master key. Database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys present in the database. It uses

45

Triple DES algorithm together with user-provided password to encrypt the keys.

/* Create Database Master Key */USE EncryptTestGOCREATE MASTER KEY ENCRYPTIONBY PASSWORD = 'SQLAuthority'GOCertificates are used to safeguard encryption keys, which are used to encrypt data in the database. SQL Server 2005 has the capability to generate self-signed X.509 certificates.

/* Create Encryption Certificate */USE EncryptTestGOCREATE CERTIFICATE EncryptTestCertWITH SUBJECT = 'SQLAuthority'GOThe symmetric key can be encrypted by using various options such as certificate, password, symmetric key, and asymmetric key. A number of different algorithms can be employed for encrypting key. The supported algorithms are DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192, and AES_256.

/* Create Symmetric Key */USE EncryptTestGOCREATE SYMMETRIC KEY TestTableKeyWITH ALGORITHM = TRIPLE_DES ENCRYPTIONBY CERTIFICATE EncryptTestCertGONow add a column of type varbinary to the original table, which will store the encrypted value for the SecondCol.

/* Encrypt Data using Key and CertificateAdd Columns which will hold the encrypted data in binary */USE EncryptTestGO

46

ALTER TABLE TestTableADD EncryptSecondCol VARBINARY(256)GOBefore the key is used, it needs to be decrypted using the same method that was used for encrypting it. In our example, we have used a certificate for encrypting the key. Because of the same reason, we are using the same certificate for opening the key and making it available for use. Subsequent to opening it and making it available for use, we can use the encryptkey function and store the encrypted values in the database, in the EncryptSecondCol column.

/* Update binary column with encrypted data created by certificate and key */USE EncryptTestGOOPEN SYMMETRIC KEY TestTableKey DECRYPTIONBY CERTIFICATE EncryptTestCertUPDATE TestTableSET EncryptSecondCol =ENCRYPTBYKEY(KEY_GUID('TestTableKey'),SecondCol)GOWe can drop the original SecondCol column, which we have now encrypted in the EncryptSecondCol column. If you do not want to drop the column, you can keep it for future comparison of the data when we decrypt the column.

/* DROP original column which was encrypted for protect the data */USE EncryptTestGOALTER TABLE TestTableDROP COLUMN SecondColGOWe can run a SELECT query on our database and verify if our data in the table is well protected and hackers will not be able to make use of it even if they somehow manage to reach the data.

/* Check the content of the TestTable */USE EncryptTestGO

47

SELECT *FROM TestTableGO

Result of the previous SQL query

Authorized user can use the decryptbykey function to retrieve the original data from the encrypted column. If Symmetric key is not open for decryption, it has to be decrypted using the same certificate that was used to encrypt it. An important point to bear in mind here is that the original column and the decrypted column should have the same data types. If their data types differ, incorrect values could be reproduced. In our case, we have used a VARCHAR data type for SecondCol and EncryptSecondCol.

/* Decrypt the data of the SecondCol */USE EncryptTestGOOPEN SYMMETRIC KEY TestTableKey DECRYPTIONBY CERTIFICATE EncryptTestCertSELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptSecondCol)) ASDecryptSecondColFROM TestTableGO

If you drop the database after the entire processing is complete, you do not have to worry about cleaning up the database. However, in real world on production servers, the database is not dropped. It is a good practice for developers to close the key after using it. If keys and

48

certificates are used only once or their use is over, they can be dropped as well. Dropping a database will drop everything it contains – table, keys, certificates, all the data, to name a few.

/* Clean up database */USE EncryptTestGOCLOSE SYMMETRIC KEY TestTableKeyGODROP SYMMETRIC KEY TestTableKeyGODROP CERTIFICATE EncryptTestCertGODROP MASTER KEYGOUSE [master]GODROP DATABASE [EncryptTest]GOSummaryEncryption is a very important security feature of SQL Server 2005. Long keys and asymmetric keys create unassailable, stronger encryption and stronger encryption uses lots of CPU to encrypt data. Stronger encryption is slower to process. When there is a huge amount of data to encrypt, it is suggested to encrypt it using a symmetric key. The same symmetric key can be encrypted further with an asymmetric key for additional protection, thereby adding the advantage of a stronger encryption. It is also recommended to compress data before encryption, as encrypted data cannot be compressed.

49

kimchidhcn.files.wordpress.com€¦ · Web viewBài Thực Hành Security. Tuần 1-2-3: Login...

Documents

Transcript of kimchidhcn.files.wordpress.com€¦ · Web viewBài Thực Hành Security. Tuần 1-2-3: Login...