ค้นหาเว็บไซต์

วิธีการใช้การรักษาความปลอดภัยระดับแถวและคอลัมน์ใน SQL Server

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

ความปลอดภัย หลักการ และการอนุญาต

ก่อนที่เราจะสามารถเจาะลึกความปลอดภัยของแถวและคอลัมน์ได้ เราจำเป็นต้องตรวจสอบพื้นฐานด้านความปลอดภัยของ SQL Server บางประการก่อน โดยพื้นฐานแล้ว การรักษาความปลอดภัยของฐานข้อมูลเกี่ยวข้องกับการอนุญาตให้ผู้อื่นเข้าถึงข้อมูลที่จัดเก็บไว้ในฐานข้อมูลและดำเนินการกับข้อมูลดังกล่าว “บุคคล” ซึ่งเป็น อาจารย์ใหญ่ การดำเนินการที่พวกเขาได้รับอนุญาตให้ดำเนินการคือ สิทธิ์ ของพวกเขา และข้อมูลในการดำเนินการนั้น ปลอดภัย — ตัวอย่างของความปลอดภัย ได้แก่ ฐานข้อมูล ตาราง สคีมา แถว และคอลัมน์

มาดูการอนุญาตที่สามารถนำไปใช้กับความปลอดภัยได้:

  • SELECT — อาจารย์ใหญ่ต้องการสิทธิ์นี้เพื่อรับหรืออ่านข้อมูลจากตาราง
  • INSERT — อาจารย์ใหญ่ต้องได้รับอนุญาตนี้เพื่อเพิ่มแถวใหม่ลงในตาราง
  • อัปเดต — อาจารย์ใหญ่ต้องได้รับอนุญาตนี้จึงจะแก้ไขข้อมูลในตารางได้
  • ลบ — อาจารย์ใหญ่ต้องได้รับอนุญาตนี้เพื่อลบแถวออกจากตาราง
  • ข้อมูลอ้างอิง — อาจารย์ใหญ่จำเป็นต้องได้รับอนุญาตนี้เพื่อสร้างคีย์นอกให้กับตาราง หากไม่มีสิทธิ์อื่น ๆ บนโต๊ะ

ความปลอดภัยระดับคอลัมน์เซิร์ฟเวอร์ SQL

มีสองวิธีในการป้องกันคอลัมน์ใน SQL Server: สิทธิ์ระดับคอลัมน์และการเข้ารหัสระดับคอลัมน์ สิ่งที่ยอดเยี่ยมสำหรับผู้ดูแลระบบคือสามารถผสมผสานเทคนิคทั้งสองนี้เข้าด้วยกันได้

สิทธิ์ระดับคอลัมน์

สิทธิ์ระดับคอลัมน์ให้ระดับความปลอดภัยที่ละเอียดยิ่งขึ้นสำหรับข้อมูลในฐานข้อมูลของคุณ คุณไม่จำเป็นต้องดำเนินการคำสั่ง GRANT หรือ DENY แยกกันสำหรับแต่ละคอลัมน์ เพียงตั้งชื่อพวกเขาทั้งหมดในแบบสอบถาม:

GRANT SELECT ON data1.table (column1, column2) TO user1;

GO

DENY SELECT ON data1.table (column3) TO user1;

GO

หากคุณดำเนินการคำสั่ง DENY ที่ระดับตารางในคอลัมน์สำหรับผู้ใช้ และหลังจากนั้นคุณดำเนินการคำสั่ง GRANT ในคอลัมน์เดียวกัน สิทธิ์ DENY จะถูกลบออก และผู้ใช้จะสามารถเข้าถึงคอลัมน์นั้นได้ ในทำนองเดียวกัน หากคุณดำเนินการ GRANT แล้วตามด้วย DENY การอนุญาต DENY จะมีผลใช้บังคับ

การเข้ารหัสระดับคอลัมน์

การเข้ารหัสระดับคอลัมน์คือการเข้ารหัสข้อมูลในคอลัมน์โดยตรง จำเป็นต้องเปลี่ยนแปลงโค้ดเนื่องจากสามารถใช้ได้เฉพาะในขณะที่เขียนโปรแกรมฐานข้อมูลเท่านั้น เป็นวิธีการเข้ารหัสข้อมูลแบบละเอียด เพื่อให้สามารถเข้ารหัสได้เฉพาะคอลัมน์หรือบางส่วนของคอลัมน์เท่านั้น คุณสามารถใช้คีย์เข้ารหัสที่แตกต่างกันสำหรับคอลัมน์ต่างๆ ได้ การเข้ารหัสระดับคอลัมน์ต้องใช้คอลัมน์ไบนารีเพื่อจัดเก็บข้อมูลที่เข้ารหัส นอกจากนี้ยังจัดเก็บคีย์ไว้บนเซิร์ฟเวอร์ ดังนั้นฐานข้อมูล SQL จึงต้องเชื่อถือเซิร์ฟเวอร์ที่เก็บคีย์ดังกล่าว

การเข้ารหัสระดับคอลัมน์ใช้คีย์แบบสมมาตรในการเข้ารหัสข้อมูล เนื่องจากจะช่วยรักษาประสิทธิภาพการทำงาน และคีย์แบบสมมาตรแต่ละคีย์ได้รับการปกป้องโดยคีย์แบบอสมมาตร คุณสามารถใช้อัลกอริธึมที่แตกต่างกันสำหรับคีย์สมมาตร แต่ AES เป็นตัวเลือกที่ดีที่สุด นอกจากนี้ คีย์แบบสมมาตรยังสามารถป้องกันด้วยรหัสผ่านหรือคีย์หลักได้อีกด้วย

เพื่อรักษาคีย์ของคุณให้ปลอดภัย คุณสามารถใช้สิทธิ์ SQL เพื่อจำกัดการเข้าถึงคีย์ให้เฉพาะบัญชีผู้ใช้ที่เชื่อถือได้เท่านั้น หมายเหตุสำคัญอีกประการหนึ่งคือข้อมูลที่เข้ารหัสไม่สามารถจัดทำดัชนีได้ หากคุณต้องการจัดทำดัชนี คุณจะต้องถอดรหัสก่อน

หากต้องการใช้การเข้ารหัสระดับคอลัมน์ เราต้องทำตามขั้นตอนต่อไปนี้:

  1. ขั้นแรก เราสร้างคีย์หลักฐานข้อมูลเพื่อเข้ารหัสคีย์สมมาตรของเรา ซึ่งสามารถทำได้โดยใช้คำสั่ง CREATE MASTER KEY และอาร์กิวเมนต์ ENCRYPTION BY PASSWORD ซึ่งกำหนดรหัสผ่านเพื่อเข้ารหัสคีย์:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@$$w0rd' 

GO
  1. จากนั้นเราจะต้องสร้างคีย์สมมาตร แต่เพื่อความปลอดภัย เราควรสร้างใบรับรองที่เซ็นชื่อแบบดิจิทัล หากต้องการสร้างใบรับรอง ให้ใช้คำสั่ง CREATE CERTIFICATE ซึ่งจะได้รับการปกป้องโดยคีย์หลักของเรา จากนั้นเราสามารถสร้างคีย์สมมาตรได้โดยใช้คำสั่ง CREATE SYMMETRIC KEY และอัลกอริทึมการเข้ารหัส AES_256
CREATE CERTIFICATE SelfSignedCertificate WITH SUBJECT = 'Column Encryption'; 

GO 

CREATE SYMMETRIC KEY SQLSymmetricKey 

WITH ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE SelfSignedCertificate; 

GO
  1. ต่อไป เราต้องเพิ่มคอลัมน์ลงในตารางของเราเพื่อจัดเก็บข้อมูลที่เข้ารหัส
USE DB; 

GO 

ALTER TABLE Testtable 

ADD EncryptedColumn varbinary(MAX )NULL 

GO
  1. ในการเข้ารหัสข้อมูลตาราง เราจำเป็นต้องเปิดคีย์สมมาตรและอัปเดตตาราง
OPEN SYMMETRIC KEY SQLSymmetricKey 

DECRYPTION BY CERTIFICATE SelfSignedCertificate;
  1. เมื่อคีย์สมมาตรเปิดขึ้น เราจำเป็นต้องอัปเดตตาราง
UPDATE Testtable 

SET [EncryptedColumn] = EncryptByKey(Key_GUID('SQLSymmetricKey'), ColumnWithSensitiveData); 

GO 

select * from Testtable
  1. สร้างคอลัมน์ที่เข้ารหัสแล้ว ประกอบด้วยข้อมูลที่เข้ารหัสของ columnwithsensitivedata เพื่อให้บรรลุเป้าหมายด้านความปลอดภัยของ SQL Server เราควรละทิ้งคอลัมน์ข้อความธรรมดา columnwithsensitivedata ที่มีอยู่โดยใช้คำสั่ง DROP และเก็บเฉพาะคอลัมน์ที่เข้ารหัสเท่านั้น

ความปลอดภัยระดับแถวของเซิร์ฟเวอร์ SQL

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

  • ฟังก์ชันภาคแสดง — นี่คือฟังก์ชันสคีมาที่ควบคุมว่าบัญชีผู้ใช้ที่ดำเนินการค้นหามีสิทธิ์เข้าถึงแถวหรือไม่

  • เพรดิเคตการรักษาความปลอดภัย — เพรดิเคตการรักษาความปลอดภัยผูกฟังก์ชันเพรดิเคตเข้ากับตาราง เพรดิเคตการรักษาความปลอดภัยมีสองประเภท:

    • ภาคแสดงตัวกรอง — ซึ่งจะจำกัดการดำเนินการอ่าน (SELECT) ในแถว การกรองนี้จะดำเนินการแบบเงียบๆ โดยไม่มีบันทึกเหตุการณ์ใดๆ
    • ภาคแสดงบล็อก — สิ่งนี้จำกัดการดำเนินการเขียน (INSERT) ในแถว เพรดิเคต BLOCK มีสี่ประเภท: หลังจากแทรก, ก่อนอัปเดต, หลังอัปเดต และก่อนลบ
  • นโยบายความปลอดภัย — นี่คือชุดของภาคแสดงการรักษาความปลอดภัยที่จัดกลุ่มไว้ในออบเจ็กต์ใหม่เดียว

ตรรกะนั้นง่ายมาก: ฟังก์ชันเพรดิเคตจะกำหนดว่าจะอนุญาตหรือบล็อกแถวที่ผู้ใช้สามารถเข้าถึงได้ตามพารามิเตอร์บางตัว จากนั้นฟังก์ชันจะถูกนำไปใช้กับตารางที่มีนโยบายความปลอดภัยและจะเปิดใช้งาน มาดูกระบวนการนี้อย่างใกล้ชิด:

    1. ขั้นแรก เราต้องสร้างเพรดิเคตตัวกรอง สำหรับตัวอย่างนี้ เราจะอนุญาตให้เข้าถึงแถวสำหรับผู้ใช้หากตรงกับคอลัมน์ privilegeuser ของแถวนั้น หรือหากชื่อผู้ใช้คือ ADMIN
    CREATE FUNCTION dbo.NewPredicateFunction

( @privilegeuser AS SYSNAME )

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN SELECT 1 AS AccessRight

    WHERE @privilegeuser = USER_NAME() OR USER_NAME() = 'ADMIN'

GO
    1. ต่อไป เราจะสร้างนโยบายความปลอดภัยโดยการรันสคริปต์ต่อไปนี้ ซึ่งจะเพิ่มฟังก์ชันเพรดิเคตที่เราเพิ่งสร้างเป็นเพรดิเคตตัวกรองในตารางทดสอบของเรา
    CREATE SECURITY POLICY PrivacyPolicy

ADD FILTER PREDICATE

dbo.NewPredicateFunction(privilegeuser) ON dbo.testtable

WITH (STATE = ON)

    นั่นคือทั้งหมดที่ นโยบายความปลอดภัยของเราใช้งานอยู่ในฐานข้อมูล SQL ของเรา และจะอนุญาตให้ผู้ใช้เข้าถึงเฉพาะแถวของตนในขณะที่อนุญาตให้บัญชี ADMIN เข้าถึงทุกแถวได้

    บทความที่เกี่ยวข้อง