การจัดการ User และกำหนดสิทธิ์ใน SQL Server

MSSQL-User-Management

การจัดการผู้ใช้ (User) และการกำหนดสิทธิ์ (Permission) ใน SQL Server หรือฐานข้อมูลต่างๆ เป็นกระบวนการสำคัญที่ช่วยให้ผู้ดูแลระบบสามารถควบคุมการเข้าถึงและการใช้งานข้อมูลได้อย่างมีประสิทธิภาพและปลอดภัย การจัดการ User และสิทธิ์ไม่เพียงแต่ช่วยให้คุณควบคุมการเข้าถึงข้อมูล แต่ยังช่วยให้คุณจัดการความเป็นส่วนตัวของข้อมูลและป้องกันการเข้าถึงไม่พึงประสงค์อีกด้วย ในบทความนี้ เราจะมาบอกคำสั่งพื้นฐานในการจัดการ User และกำหนดสิทธิ์ใน SQL Server

ในการสร้าง User เพื่อที่จะเข้าใช้ Database ต่างๆ ใน Azure SQL Server หรือ Azure SQL Database ผู้ใช้ Microsoft SQL Server Management Studio (SSMS)

Create the new Login on the server

ในรูปแบบนี้จะเป็นการสร้าง User ตั้งแต่ Login เข้า Server นั้นหมายความว่า User ที่ถูกสร้างขึ้นมาด้วยวิธีนี้ จะสามารถเห็นได้หลาย Database

อย่างแรกเลยเมื่อเราใช้ SSMS ใน Login SQL Server ด้วย Super Admin (Owner) ให้เราเลือกที่อยู่เป็น master เพื่อที่จะสร้าง User ใน Level SQL Server

*In master (via the Available databases drop down in SSMS – this is because USE master doesn’t work in Azure):

สร้าง user login (จะสร้างให้กับ SQL Server)

-- create SQL auth login from master 
CREATE LOGIN <username> WITH PASSWORD = 'password';

สร้าง user ให้กับ database
โดยเราจะทำการสร้าง user ที่อ้างอิงจาก user login โดยก่อนจะใช้คำสั่งด้านล่างนี้ เราต้องทำการสลับไปยังฐานข้อมูลที่เราต้องการให้ user เชื่อมต่อ

-- select your db in the dropdown and create a user mapped to a login 
CREATE USER <username> FROM LOGIN <username>;

การจัดกลุ่ม User (Optional)

หากเรามีจำนวน User ที่มาก เราอาจพิจารณาในการจัดกลุ่ม User เพื่อให้ง่ายต่อการจัดการและการกำหนดสิทธิ์ โดยสร้าง Role หรือ Group แล้วกำหนดสิทธิ์ให้กับ Role นั้นๆ ซึ่งสามารถกำหนดสิทธิ์ให้กับ User ในกลุ่มได้ทั้งหมดพร้อมกัน

สร้าง Role หรือ Group
โดยการสร้าง Role หรือ Group นั้น อย่าลืมดูฐานข้อมูลด้วย ว่าเราเลือกฐานข้อมูลไหนอยู่

CREATE ROLE <role_or_group>;

กำหนดสิทธิ์ (gain permission)
หลังจากสร้าง user เสร็จแล้วเราก็มาถึงขั้นตอนการกำหนดสิทธิ์ โดยสิทธิ์หลักๆ ก็จะมี SELECT, INSERT, UPDATE, DELETE

-- add user to role(s) in db 
ALTER ROLE <role_or_group> ADD MEMBER <username>;

Revoke permission & Remove User

Revoke permission
Revoke หรือถอดสิทธิ์หรือการเข้าถึงของกลุ่มนั้นๆ

-- REVOKE permission form role or group
REVOKE <permission> ON <object_name> FROM <role_or_group>;
  • <permission> แทนที่สิทธิ์ที่จะเพิกถอน เช่น SELECT, INSERT, UPDATE, DELETE
  • <object_name> แทนด้วย database หรือ table ที่่ต้องการแก้ไข ตัวอย่างเช่น DATABASE :: <database_name>
  • <role_or_group> แทนที่ด้วยชื่อ role หรือชื่อ กลุ่มที่เราสร้างขึ้นมา

Remove User

-- Remove user form role or group
ALTER ROLE <role_or_group> DROP MEMBER <username>;

Roles & Group เบื้องต้นที่ SQL มีให้แล้ว

  • db_accessadmin
  • db_backupoperator
  • db_datareader
  • db_datawriter
  • db_ddladmin
  • db_denydatareader
  • db_denydatawriter
  • db_owner
  • db_securityadmin

ในส่วนของ Security stored procedures ของ SQL Server อ่านต่อได้ที่ลิงค์

Check roles member

เมื่อการจัดการผู้ใช้ (User) และการกำหนดสิทธิ์ (Permission) ได้เรียบร้อยแล้ว ลองเช็ค list ดูได้จาก Query ด้านล่างนี้

SELECT    roles.principal_id                       AS RolePrincipalID
  ,    roles.name                                  AS RolePrincipalName
  ,    database_role_members.member_principal_id   AS MemberPrincipalID
  ,    members.name                                AS MemberPrincipalName
FROM sys.database_role_members AS database_role_members  
JOIN sys.database_principals AS roles  
  ON database_role_members.role_principal_id = roles.principal_id  
JOIN sys.database_principals AS members  
  ON database_role_members.member_principal_id = members.principal_id; 

หวังว่าบทความนี้จะช่วยให้คุณเข้าใจการจัดการผู้ใช้ (User) และการกำหนดสิทธิ์ (Permission) ใน SQL Server ได้อย่างมีประสิทธิภาพ

ขอบคุณรูปภาพปกจาก https://blog.satoricyber.com/wp-content/uploads/2022/10/6-Effective-MSSQL-User-Management-Strategies.png

0