แนวทางการแก้ปัญหา User ในฐานข้อมูลใด ๆ เป็นกำพร้า
แนวทางการแก้ปัญหา User ในฐานข้อมูลใด ๆ เป็นกำพร้า
บทความนี้เกิดขึ้น เพื่อสนับสนุนหลักสูตร Microsoft SQL Server database Administration ที่ผู้เขียนบรรยายอยู่เป็นประจำโดยปกติผู้เขียนจะสาธิตประกอบคำบรรยายโดยละเอียดดีแล้ว หรือคิดเองว่าดีแล้ว มีบ้างที่ผู้อบรมเก็บข้อสงสัยอาไว้ไม่ถามทันที แล้วนำกลับมาถามใหม่อาจภายในช่วงฝึกอบรมอยู่ หรือหลังจากฝึกอบรมเสร็จสิ้นไปแล้วก็ตาม ผู้เขียนเลยตัดสินใจ นำมาเขียนเป็นบทความไว้ดีกว่าเพื่อให้ผู้อบรม หรือผู้สนใจทั่วไป สามารถมาศึกษาเพิ่มเติมได้
User ในฐานข้อมูลใด ๆ
User ในฐานข้อมูลใด ๆ นั้นสามารถสร้างได้หลายแบบ ดังแสดงในรูปภาพ
แต่แบบที่ถูกใช้งานมากที่สุดคือ SQL User with login หรือการสร้าง User โดย map มาจาก Login ที่สร้างไว้ก่อนหน้าในระดับ instance (จัดเก็บไว้ในฐานข้อมูล Master) นั้นเอง
ตัวอย่างสคริปต์ในการสร้าง Login และ User
USE master
จากสคริปต์เป็นการสร้าง login ขึ้นมาก่อน
หลังจากนั้น เมื่อเราต้องการให้ login ดังกล่าว สามารถใช้งาน ในฐานข้อมูลใด ๆได้
เราก็จะดึงเอา Login ดังกล่าว ไปสร้างเป็น User หรือ Map ไปเป็น User ในฐานข้อมูลใด ๆที่ต้องการเข้าถึงในที่นี้คือฐานข้อมูล TestDB
มีหลายคนเลยที่คิดไปเองว่าในเมื่อ User สร้างมาจาก Login
หากทำการลบ login ทิ้ง ก็น่าจะลบ User ทิ้งให้เสร็จสรรพตามไปด้วย
แต่ไม่เป็นดังนั้น เพราะหากเราพยายามจะลบ login ทิ้ง จะมีการแจ้งเตือนออกมาดังแสดง
ตัวอย่างสคริปต์ในการสร้าง Login และ User
USE master
CREATE LOGIN superman WITH PASSWORD=N'Pa55w.rd';
GO
USE TestDB
CREATE USER superman FOR LOGIN superman;
GO
จากสคริปต์เป็นการสร้าง login ขึ้นมาก่อน
หลังจากนั้น เมื่อเราต้องการให้ login ดังกล่าว สามารถใช้งาน ในฐานข้อมูลใด ๆได้
เราก็จะดึงเอา Login ดังกล่าว ไปสร้างเป็น User หรือ Map ไปเป็น User ในฐานข้อมูลใด ๆที่ต้องการเข้าถึงในที่นี้คือฐานข้อมูล TestDB
มีหลายคนเลยที่คิดไปเองว่าในเมื่อ User สร้างมาจาก Login
หากทำการลบ login ทิ้ง ก็น่าจะลบ User ทิ้งให้เสร็จสรรพตามไปด้วย
แต่ไม่เป็นดังนั้น เพราะหากเราพยายามจะลบ login ทิ้ง จะมีการแจ้งเตือนออกมาดังแสดง
เป็นการแจ้งว่า หากลบ login จะไม่มีการลบ User ที่สัมพันธ์กับ Login และจะทำให้ User เป็นกำพร้า
ผู้เขียนเข้าใจความตั้งใจของทาง Microsoft ที่ทำแบบนี้
เพราะปกติเราให้สิทธิ์การเข้าถึงกันในระดับ User หาก User แต่ละรายมีการกำหนดสิทธิ์เอาไว้มากมาย
แต่มีใครไปลบ Login เข้า User ที่สัมพันธ์กับ Login หากถูกลบตามไปด้วยสิทธิ์ต่าง ๆ ก็จะหายไป เพิ่มภาระงานมากกว่าที่จะต้องมากำหนดสิทธิ์กันใหม่ ซึ่งต่างจากการแก้ไขการเป็นกำพร้าของ User ภาระงานในการแก้ไขนั้นน้อยกว่า
นอกจากการเป็นกำพร้าด้วยกรณีข้างต้นแล้ว
ยังกรณีสร้าง User แบบ SQL user without login อันนี้ตั้งใจให้ User เป็นกำพร้าเลย ดังแสดง
การสร้าง User แบบนี้ขึ้นมาเพื่อกำหนดสิทธิ์ต่างๆ ที่ควรได้รับเสียก่อน และนำไปใช้งานตามจุดประสงค์ด้าน Security ที่ไม่ขอพูดถึงในบทความนี้
โดยผู้เขียนทดลองสร้าง Login ชื่อ phakkhaphong แต่ map ไปเป็น User ชื่อ ake ดังแสดง
จากนั้นผู้เขียนใช้คำสั่งต่อไปนี้ เพื่อสืบค้นข้อมูลของ Login และ User ออกมา
ผลลัพธ์ที่ได้ คือ
ผู้เขียนเข้าใจความตั้งใจของทาง Microsoft ที่ทำแบบนี้
เพราะปกติเราให้สิทธิ์การเข้าถึงกันในระดับ User หาก User แต่ละรายมีการกำหนดสิทธิ์เอาไว้มากมาย
แต่มีใครไปลบ Login เข้า User ที่สัมพันธ์กับ Login หากถูกลบตามไปด้วยสิทธิ์ต่าง ๆ ก็จะหายไป เพิ่มภาระงานมากกว่าที่จะต้องมากำหนดสิทธิ์กันใหม่ ซึ่งต่างจากการแก้ไขการเป็นกำพร้าของ User ภาระงานในการแก้ไขนั้นน้อยกว่า
นอกจากการเป็นกำพร้าด้วยกรณีข้างต้นแล้ว
ยังกรณีสร้าง User แบบ SQL user without login อันนี้ตั้งใจให้ User เป็นกำพร้าเลย ดังแสดง
USE TestDB
CREATE USER supergirl WITHOUT LOGIN;
CREATE USER spiderman WITHOUT LOGIN;
CREATE USER batman WITHOUT LOGIN;
GO
การสร้าง User แบบนี้ขึ้นมาเพื่อกำหนดสิทธิ์ต่างๆ ที่ควรได้รับเสียก่อน และนำไปใช้งานตามจุดประสงค์ด้าน Security ที่ไม่ขอพูดถึงในบทความนี้
Security ID ผู้อยู่เบื้องหลังความสัมพันธ์
การผูกความสัมพันธ์ระหว่าง Login กับ User นั้นไม่ได้ใช้ชื่อ แต่เป็น Security IDโดยผู้เขียนทดลองสร้าง Login ชื่อ phakkhaphong แต่ map ไปเป็น User ชื่อ ake ดังแสดง
USE master
CREATE LOGIN phakkhaphong WITH PASSWORD=N'Pa55w.rd';
GO
USE TestDB
CREATE USER ake FOR LOGIN phakkhaphong;
GO
จากนั้นผู้เขียนใช้คำสั่งต่อไปนี้ เพื่อสืบค้นข้อมูลของ Login และ User ออกมา
USE master
SELECT name,sid,type,type_desc FROM sys.server_principals WHERE name='phakkhaphong';
GO
USE TestDB
SELECT name,sid,type,type_desc FROM sys.database_principals WHERE name='ake';
GO
ผลลัพธ์ที่ได้ คือ
จะเห็นว่า Security ID ของทั้ง Login และ User เป็นหมายเลขเดียวกันทั้งที่ชื่อคนละชื่อ
จากนั้น ผู้เขียนทดสอบ ลบเฉพาะ Login ทิ้งไป แล้วใช้คำสั่ง เดิมดึงข้อมูลออกมาแสดงอีกครั้ง
ผลลัพธ์ที่ได้ คือ
จากนั้น ผู้เขียนทดสอบ ลบเฉพาะ Login ทิ้งไป แล้วใช้คำสั่ง เดิมดึงข้อมูลออกมาแสดงอีกครั้ง
USE master
DROP LOGIN phakkhaphong;
GO
USE master
SELECT name,sid,type,type_desc FROM sys.server_principals WHERE name='phakkhaphong';
GO
USE TestDB
SELECT name,sid,type,type_desc FROM sys.database_principals WHERE name='ake';
GO
ผลลัพธ์ที่ได้ คือ
จะเห็นว่าข้อมูลของ login หายไปเหลือเฉพาะข้อมูลของ User
บางคนคิดว่าเพียงสร้าง login ชื่อเดิมกลับมาก็สามารถใช้งานได้แล้ว
อย่าลืมผู้เขียนทดสอบให้เห็นแล้วว่าระหว่าง Login กับ User นั้นใช้ Seurity ID ในการเชื่อมไม่ใช่ชื่อ
ผู้เขียนทดสอบ สร้าง Login ชื่อเดิมกลับคืนมาด้วยคำสั่งต่อไปนี้
จากนั้นสืบค้นข้อมูลของ Login และ User ออกมาแสดงอีกครั้งด้วยคำสั่งต่อไปนี้
ผลลัพธ์ที่ได้ คือ
บางคนคิดว่าเพียงสร้าง login ชื่อเดิมกลับมาก็สามารถใช้งานได้แล้ว
อย่าลืมผู้เขียนทดสอบให้เห็นแล้วว่าระหว่าง Login กับ User นั้นใช้ Seurity ID ในการเชื่อมไม่ใช่ชื่อ
ผู้เขียนทดสอบ สร้าง Login ชื่อเดิมกลับคืนมาด้วยคำสั่งต่อไปนี้
USE master
CREATE LOGIN phakkhaphong WITH PASSWORD=N'Pa55w.rd';
GO
จากนั้นสืบค้นข้อมูลของ Login และ User ออกมาแสดงอีกครั้งด้วยคำสั่งต่อไปนี้
USE master
SELECT name,sid,type,type_desc FROM sys.server_principals WHERE name='phakkhaphong';
GO
USE TestDB
SELECT name,sid,type,type_desc FROM sys.database_principals WHERE name='ake';
GO
ผลลัพธ์ที่ได้ คือ
จะเห็นว่า Security ID ของ Login ไม่ตรงกับของ User
ดังนั้น ในกรณีนี้ยังถือว่า User เป็นกำพร้าอยู่
วิธีแรก คือ การระบุหมายเลข Security ID เดิมที่ได้มาจาก User ลงไปตอนสร้าง Login
ในที่นี้ User ชื่อ ake มี Security ID หมายเลข 0x3F3B1FD2CA6E184CA60B34C57E2381D2 (เป็นเลขฐาน 16)
ผู้เขียนจะทำการลบ Login ทิ้งไป จากนั้นสร้าง Login ขึ้นมาใหม่โดยใช้ Security ID ของ User ด้วยคำสั่งต่อไปนี้
จากนั้นสืบค้นข้อมูลของ Login และ User ออกมาแสดงอีกครั้งด้วยคำสั่งต่อไปนี้
ผลลัพธ์ที่ได้ คือ
ดังนั้น ในกรณีนี้ยังถือว่า User เป็นกำพร้าอยู่
Note:
เกิดกับ SQL Authentication เท่านั้น แต่จะไม่เกิดกับ Windows Authentication
เพราะ Security ID ของ Windows authentication นั้น ดึงมาจาก Windows
ไม่ว่าจะดึงมากี่ครั้ง ก็ยังคงได้ Security ID เดิม จึงไม่เกิดปัญหาแบบนี้
เราสามารถสร้าง Login กลับคืนมา โดยการ look up ไปดึง User มาจาก Windows ได้เลย
เกิดกับ SQL Authentication เท่านั้น แต่จะไม่เกิดกับ Windows Authentication
เพราะ Security ID ของ Windows authentication นั้น ดึงมาจาก Windows
ไม่ว่าจะดึงมากี่ครั้ง ก็ยังคงได้ Security ID เดิม จึงไม่เกิดปัญหาแบบนี้
เราสามารถสร้าง Login กลับคืนมา โดยการ look up ไปดึง User มาจาก Windows ได้เลย
แต่สำหรับ SQL authentication นั้น Security ID ถูกสร้างขึ้นโดย Microsoft SQL Server เอง
โดยแต่ละครั้งที่สร้าง ก็จะเป็นเลขใหม่เสมอ หากไม่ได้ระบุหมายเลข Security ID เดิมลงไปตอนสร้าง Login
โดยแต่ละครั้งที่สร้าง ก็จะเป็นเลขใหม่เสมอ หากไม่ได้ระบุหมายเลข Security ID เดิมลงไปตอนสร้าง Login
การแก้ไขการเป็นกำพร้าของ User
มีแนวทางทำได้ 2 วิธีวิธีแรก คือ การระบุหมายเลข Security ID เดิมที่ได้มาจาก User ลงไปตอนสร้าง Login
ในที่นี้ User ชื่อ ake มี Security ID หมายเลข 0x3F3B1FD2CA6E184CA60B34C57E2381D2 (เป็นเลขฐาน 16)
ผู้เขียนจะทำการลบ Login ทิ้งไป จากนั้นสร้าง Login ขึ้นมาใหม่โดยใช้ Security ID ของ User ด้วยคำสั่งต่อไปนี้
USE master
DROP LOGIN phakkhaphong;
GO
USE master
CREATE LOGIN phakkhaphong WITH PASSWORD=N'Pa55w.rd',SID=0x3F3B1FD2CA6E184CA60B34C57E2381D2;
GO
จากนั้นสืบค้นข้อมูลของ Login และ User ออกมาแสดงอีกครั้งด้วยคำสั่งต่อไปนี้
USE master
SELECT name,sid,type,type_desc FROM sys.server_principals WHERE name='phakkhaphong';
GO
USE TestDB
SELECT name,sid,type,type_desc FROM sys.database_principals WHERE name='ake';
GO
ผลลัพธ์ที่ได้ คือ
หากผู้อ่านติดตามอ่านตั้งแต่ต้น จะเห็นว่าผู้เขียนทำให้ User ชื่อ superman เป็นกำพร้า
โดยการลบ Login ทิ้งไป แต่มี User ชื่อ supergirl, spiderman และ batman
ผู้เขียนทำให้เป็นกำพร้าด้วยการสร้างแบบ SQL user without login ทั้ง 3 คนกลับไม่แสดงขึ้นมาด้วย
ผู้เขียนเลยลองสืบค้นจาก System Schema View ชื่อ sys.database_principals โดยตรงด้วยคำสั่งต่อไปนี้
ผลลัพธ์ที่ได้ คือ
โดยการลบ Login ทิ้งไป แต่มี User ชื่อ supergirl, spiderman และ batman
ผู้เขียนทำให้เป็นกำพร้าด้วยการสร้างแบบ SQL user without login ทั้ง 3 คนกลับไม่แสดงขึ้นมาด้วย
ผู้เขียนเลยลองสืบค้นจาก System Schema View ชื่อ sys.database_principals โดยตรงด้วยคำสั่งต่อไปนี้
SELECT name, sid, authentication_type_desc
FROM sys.database_principals
FROM sys.database_principals
WHERE type = 'S' AND name NOT IN ('guest', 'INFORMATION_SCHEMA', 'sys');
ผลลัพธ์ที่ได้ คือ
จะเห็นว่ากรณี superman นั้นมีค่า authentication_type_desc เป็น INSTANCE หมายถึงเป็นแบบ SQL user with login ปกติ
แต่เผอิญ Login ถูกลบไป
ส่วน supergirl, spiderman และ batman มีค่า authentication_type_desc เป็น NONE หมายถึงเป็นการสร้าง user แบบ SQL user without login
นั่นแสดงว่า Stored Procedure ชื่อ sp_change_users_login สามารถรายงานการเป็นกำพร้าได้เฉพาะกรณีลบ Login ที่เคยสัมพันธ์ออกไปเท่านั้น
การแก้ไขการเป็นกำพร้าด้วย sp_change_users_login เราสามารถกำหนดค่า Auto_Fix ให้กับพารามิเตอร์ ชื่อ @Action
เพื่อทำการสร้าง Login ที่สัมพันธ์กับ User ขึ้นมาดังคำสั่งต่อไปนี้
GO
จากนั้นสืบค้นข้อมูลของ Login และ User ออกมาแสดงอีกครั้งด้วยคำสั่งต่อไปนี้
ผลลัพธ์ที่ได้คือ รูปที่
แต่เผอิญ Login ถูกลบไป
ส่วน supergirl, spiderman และ batman มีค่า authentication_type_desc เป็น NONE หมายถึงเป็นการสร้าง user แบบ SQL user without login
นั่นแสดงว่า Stored Procedure ชื่อ sp_change_users_login สามารถรายงานการเป็นกำพร้าได้เฉพาะกรณีลบ Login ที่เคยสัมพันธ์ออกไปเท่านั้น
การแก้ไขการเป็นกำพร้าด้วย sp_change_users_login เราสามารถกำหนดค่า Auto_Fix ให้กับพารามิเตอร์ ชื่อ @Action
เพื่อทำการสร้าง Login ที่สัมพันธ์กับ User ขึ้นมาดังคำสั่งต่อไปนี้
USE TestDB
EXEC sp_change_users_login @Action='Auto_Fix',@UserNamePattern=superman,@LoginName=NULL,@Password='Pa55w.rd';
GO
จากนั้นสืบค้นข้อมูลของ Login และ User ออกมาแสดงอีกครั้งด้วยคำสั่งต่อไปนี้
USE master
SELECT name,sid,type,type_desc FROM sys.server_principals WHERE name='superman';
GO
USE TestDB
SELECT name,sid,type,type_desc FROM sys.database_principals WHERE name='superman';
GO
ผลลัพธ์ที่ได้คือ รูปที่
จะเห็นว่า Security ID ของทั้ง Login และ User เป็นหมายเลขเดียวกันแล้ว ทำให้ User ชื่อ superman ไม่เป็นกำพร้า
ผู้เขียนต้องการทดสอบต่อไปว่าสามารถใช้ sp_change_users_login กับกรณี User ที่สร้างแบบ SQL user without login ได้หรือไม่
โดยดึง spiderman มาทดสอบดังคำสั่งต่อไปนี้
เมื่อลองรันไม่เกิดข้อผิดพลาด แต่ได้รับข้อความดังต่อไปนี้
นั่นแสดงว่าไม่สามารถใช้ sp_change_users_login แก้ไขกรณี User ที่สร้างแบบ SQL user without login ได้
และสามารถรับมือกับกรณีการเป็นกำพร้าของ User ที่อาจเกิดขึ้นได้
แต่ทั้งนี้ทั้งนั้นหากจำนวน User ที่เป็นกำพร้ามีจำนวนมาก ก็อาจเป็นภาระงานที่หนักได้
ก่อนหน้านี้ผู้เขียนเจอคนมาปรึกษาว่าจะทำอย่างไรกรณีมีการเป็นกำพร้าเกิดขึ้นถึง 2,000 กว่า Users ด้วยกัน
ต้องบอกเลยว่าทางที่ดีที่สุดคือการ Backup ฐานข้อมูล Master เพื่อเก็บ Login เอาไว้ แล้วใช้วิธีการกู้คืนฐานข้อมูล master ก็จะดีกว่า
แต่หากมีแต่ Backup ของฐานข้อมูลใด และได้คืนมาแต่ User จำเป็นต้องเขียน Script อาจใช้ CURSOR เพื่อวนลูปตามจำนวน Users มีเพื่อสร้าง Login คืนมา แต่เดี๋ยวนี้มี 3rd Party ทำเรื่องเหล่านี้ออกมาขายอยูบ้างเท่าที่ผู้เขียนเคยผ่านตามา
ผู้เขียนต้องการทดสอบต่อไปว่าสามารถใช้ sp_change_users_login กับกรณี User ที่สร้างแบบ SQL user without login ได้หรือไม่
โดยดึง spiderman มาทดสอบดังคำสั่งต่อไปนี้
USE TestDB
EXEC sp_change_users_login @Action='Auto_Fix',@UserNamePattern=spiderman,@LoginName=NULL,@Password='Pa55w.rd';
GOเมื่อลองรันไม่เกิดข้อผิดพลาด แต่ได้รับข้อความดังต่อไปนี้
The number of orphaned users fixed by updating users was 0.
The number of orphaned users fixed by adding new logins and then updating users was 0.
สรุป
เนื้อหาในบทความนี้ต้องการในผู้อ่านเข้าใจความสัมพันธ์ระหว่าง Login กับ User ในฐานข้อมูลใด ๆและสามารถรับมือกับกรณีการเป็นกำพร้าของ User ที่อาจเกิดขึ้นได้
แต่ทั้งนี้ทั้งนั้นหากจำนวน User ที่เป็นกำพร้ามีจำนวนมาก ก็อาจเป็นภาระงานที่หนักได้
ก่อนหน้านี้ผู้เขียนเจอคนมาปรึกษาว่าจะทำอย่างไรกรณีมีการเป็นกำพร้าเกิดขึ้นถึง 2,000 กว่า Users ด้วยกัน
ต้องบอกเลยว่าทางที่ดีที่สุดคือการ Backup ฐานข้อมูล Master เพื่อเก็บ Login เอาไว้ แล้วใช้วิธีการกู้คืนฐานข้อมูล master ก็จะดีกว่า
แต่หากมีแต่ Backup ของฐานข้อมูลใด และได้คืนมาแต่ User จำเป็นต้องเขียน Script อาจใช้ CURSOR เพื่อวนลูปตามจำนวน Users มีเพื่อสร้าง Login คืนมา แต่เดี๋ยวนี้มี 3rd Party ทำเรื่องเหล่านี้ออกมาขายอยูบ้างเท่าที่ผู้เขียนเคยผ่านตามา
บทความที่เกี่ยวข้อง
