Partially Contained Databases

Partially Contained Databases

ในเรื่องของ Partially Contained Databases นั้น ผู้เขียนเห็นครั้งแรกใน Microsoft SQL server 2012 แล้ว แต่ยังไม่มีโอกาสได้เล่าให้ฟังกัน  

หลักการสำคัญคือการเก็บการตั้งค่าและ Metadata ของฐานข้อมูลนั้น ๆ ไว้บนตัวมันเอง
ไม่ต้องพึ่งการจัดเก็บไว้ในระดับ Instance หรือ Server (บนฐานข้อมูล Master) อีกต่อไป

เรื่องที่ได้ประโยชน์จาก Partially Contained Databases ไปเต็ม ๆ
เห็นจะหนีไม่พ้นเรื่องของการพิสูจน์ตัวตน บน Microsoft SQL Server เพราะแต่ก่อนจะทำในระดับ Instance หรือระดับ Server เรียกว่า Login

จากนั้น เราจึงทำการแมป จาก Login ไปเป็น User ในฐานข้อมูลใด ๆ อีกทอดหนึ่ง ดังรูป

หากผู้อ่านมีความจำเป็นต้องย้ายฐานข้อมูลไปอยู่ใน Instance หรือ Server อื่น
การเข้าถึงฐานข้อมูลก็จะมีปัญหาหากเราไม่นำ Logins ที่สัมพันธ์กันกับ Users ซึ่งเก็บไว้ใน Instance หรือ Server เครื่องเดิมตามมาด้วย
(จัดเก็บไว้ในฐานข้อมูล master ของ Instance หรือ Server เครื่องเดิม)

การที่จะนำข้อมูล Logins ตาม Users ในฐานข้อมูลมาด้วย
ทำได้โดยการ Backup ฐานข้อมูล Master จาก Instance หรือ Server เดิมมา Restore บน Instance หรือ Server ใหม่
ซึ่งมีขั้นตอนยุ่งยากบ้าง คือจำเป็นต้อง Restore ในขณะอยู่ใน Single-User Mode (https://msdn.microsoft.com/en-us/library/ms190679.aspx)

ขั้นตอนที่ยุ่งยากก่อนหน้าจะหมดไป หากผู้อ่านใช้ความสามารถจาก Partially Contained Databases
เพราะได้รวบรวม Metadata ของการพิสูจน์ตัวตนว่าไว้ในฐานข้อมูลเองเลย

ทำให้เราสามารถพิสูจน์ตัวตนให้กับ user ในฐานข้อมูลได้โดยตรงไม่ต้องแมปมาจาก Login อีกต่อไป ดังรูป

ก่อนอื่นต้องตั้งค่าให้ระบบจัดการฐานข้อมูลรองรับ Contained Databases เสียก่อน
โดยการกำหนดค่าในระดับ  Instance หรือ Server  ด้วยการคลิกขวาไปที่ชื่อ Server
และ เลือก Properties จากนั้นเลือกไปที่เพจ Advanced

จะพบค่า Enable Contained Databases ให้ตั้งค่าเป็น TRUE ดังแสดง 

หรือผ่านคำสั่ง T-SQL ดังต่อไปนี้

sp_configure 'contained database authentication', 1;
GO
RECONFIGURE;
GO

อย่าลืมทำการ Restart Service ของ Database Engine หลังการเปลี่ยนแปลงการตั้งค่าระดับ ระดับ  Instance หรือ Server ด้วย

ถึงตอนนี้ผู้อ่านก็สามารถที่จะสร้างฐานข้อมูลแบบ  Partially Contained Database ได้แล้ว
โดยขณะสร้างฐานข้อมูลผ่าน SSMS นั้นเราสามารถกำหนด Database Options
ด้วยการกำหนด Containment Type เป็น Partial ดังแสดง

หรือผ่านคำสั่ง T-SQL ดังต่อไปนี้

CREATE DATABASE TestDB CONTAINMENT = PARTIAL;

กรณีมีฐานข้อมูลอยู่ก่อนแล้ว แต่ไม่ได้เป็น Partially Contained Database ก็สามารถปรับเปลี่ยนให้เป็น Partially Contained Database ได้
ด้วยการตั้งค่าผ่าน Database Options โดยกำหนด Containment Type เป็น Partial ได้เช่นกัน

หากจะเปลี่ยนแปลงผ่านคำสั่ง T-SQL ทำได้ดังนี้

ALTER DATABASE AdventureWorks SET CONTAINMENT = PARTIAL;

เมื่อฐานข้อมูลมีคุณสมบัติ Partially Contained Database แล้ว ก็จะสามารถสร้าง User พร้อมกับ Password เก็บไว้ในตัวฐานข้อมูลเอง
เพื่อใช้พิสูจน์ตัวตน โดยไปที่โฟลเดอร์ Security ในฐานข้อมูลใด ๆ

จากนั้นคลิกขวาที่ Users เลือก New User ดังรูป

หรือใช้คำสั่ง T-SQL ดังนี้

USE AdventureWorks
GO
CREATE USER superman WITH PASSWORD = N'Pa$$word'
GO

ผู้เขียนจะทดลองสืบค้นข้อมูลจาก Metadata ที่เก็บ Security Principals
ของทั้งระดับ Instance (หรือ Server) และ ระดับฐานข้อมูล ดังคำสั่ง T-SQL ต่อไปนี้

--ตรวจสอบว่า Database User ชื่อ superman หรือไม่
USE AdventureWorks;

SELECT * FROM sys.database_principals WHERE name='superman';

--ตรวจสอบว่า Login ที่มี sid ตรงกับ User ชื่อ superman หรือไม่
USE Master;

SELECT * FROM sys.server_principals
WHERE sid = (
    SELECT sid
    FROM AdventureWorks.sys.database_principals
    WHERE name = 'superman'
)

ผลลัพธ์ที่ได้เป็นดังนี้

จะเห็นว่ามี User ชื่อ Superman ในระดับฐานข้อมูล แต่เมื่อนำเอา SID ของ Superman ไปค้นหาใน System View ชื่อ sys.server_principals บนฐานข้อมูล Master
กลับไม่พบว่ามี Login ที่สัมพันธ์กับ User นี้แต่อย่างใด (ปกติการแมป Login ไปเป็น User ในฐานข้อมูลใด ๆ สิ่งที่ใช้แมปคือ SID)

นั่นหมายถึง User เกิดขึ้นในฐานข้อมูลเท่านั้น ไม่ได้สร้างหรือแมปมาจาก Login แต่อย่างใด

เมื่อได้ User ซึ่งสามารถพิสูจน์ตัวตนจากฐานข้อมูลเองแล้ว ผู้เขียนทดลองสร้างการเชื่อมต่อเข้าไปใช้งาน
แต่ต้องตั้งค่าการเชื่อมต่อเป็นพิเศษ เพราะปกติแล้ว SQL Server Management Studio นั้นจะพิสูจน์ตัวตนผ่านระดับ Instance หรือ Server เป็นหลัก

สิ่งที่พิเศษขึ้นมานี้ คือ การกำหนดให้เชื่อมต่อไปยังฐานข้อมูลที่ต้องการเข้าถึงทันทีผ่าน Additional Connection String Parameters
ชื่อว่า Initial Catalog ในที่นี้ระบุเป็นฐานข้อมูล AdventureWorks 

จากรูป เป็นการเชื่อมต่อด้วย SQL Server Authentication โดยระบุ Login
(ในที่นี้หมายถึง User ในฐานข้อมูล) เป็น superman ซึ่งเราพิสูจน์มาแล้วว่ามีอยู่เฉพาะในฐานข้อมูลเท่านั้น
 
จากนั้นคลิกไปที่ปุ่ม Options

จากนั้นเลือกไปที่แท็ป Additional Connection Parameters
 
แล้วพิมพ์ Initial Catalog=AdventureWorks
 
จากนั้นกดปุ่ม Connect

เพียงแค่นี้ก็จะเป็นการเชื่อมต่อไปยังฐานข้อมูล AdventureWorks โดยตรง
และ การพิสูจน์ตัวตนทำโดยฐานข้อมูล AdventureWorks เอง

ผลของการเชื่อมต่อเป็นดังรูป