คู่มือที่ดีที่สุดในการมอบสิทธิ์ในการเข้าถึงตารางผู้ใช้ทั้งหมดใน SQL Server

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

ความท้าทาย: การมอบสิทธิ์อย่างมีประสิทธิภาพ

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

ทางออก: Stored Procedure สำหรับการมอบสิทธิ์

เพื่อให้สามารถมอบสิทธิ์ได้อย่างมีประสิทธิภาพ เราสามารถสร้าง stored procedure ที่ใช้ cursor เพื่อวนซ้ำผ่านวัตถุผู้ใช้ทั้งหมดและมอบสิทธิ์ที่ต้องการ มาลงรายละเอียดกระบวนการในขั้นตอนที่ง่ายดาย:

ขั้นตอนที่ 1: สร้าง Stored Procedure

เริ่มต้นโดยการนิยาม stored procedure ของคุณ ใช้รหัส SQL ดังต่อไปนี้เป็นเทมเพลต:

IF EXISTS (
    SELECT 1 FROM sysobjects
    WHERE name = 'sp_grantastic'
    AND type = 'P'
)
DROP PROCEDURE sp_grantastic
GO

CREATE PROCEDURE sp_grantastic
AS
DECLARE
 @object_name VARCHAR(30),
 @time VARCHAR(8),
 @rights VARCHAR(20),
 @role VARCHAR(20)

DECLARE c_objects CURSOR FOR
    SELECT name
    FROM sysobjects
    WHERE type IN ('P', 'U', 'V')
    FOR READ ONLY

BEGIN
    SELECT @rights = 'ALL',
           @role = 'PUBLIC'

    OPEN c_objects
    WHILE (1=1)
    BEGIN
        FETCH c_objects INTO @object_name
        IF @@SQLSTATUS <> 0 BREAK

        SELECT @time = CONVERT(VARCHAR, GetDate(), 108)
        PRINT '[%1!] hitting up object %2!', @time, @object_name
        EXECUTE('GRANT '+ @rights +' ON '+ @object_name+' TO '+@role)
    END

    PRINT '[%1!] fin!', @time

    CLOSE c_objects
    DEALLOCATE CURSOR c_objects
END
GO

GRANT ALL ON sp_grantastic TO PUBLIC
GO

ขั้นตอนที่ 2: เข้าใจส่วนต่าง ๆ

• การประกาศ Cursor: Cursor c_objects ถูกตั้งขึ้นเพื่อวนซ้ำผ่านตารางผู้ใช้ทั้งหมด (U), มุมมอง (V), และ stored procedures (P).
• การดำเนินการ Grants: สำหรับแต่ละวัตถุที่ดึงข้อมูลผ่าน cursor ระบบจะทำการสั่ง GRANT เพื่อใช้งานสิทธิ์ที่กำหนดกับบทบาทที่ระบุ
• ข้อมูลผลลัพธ์: Procedure จะพิมพ์บันทึกที่บ่งบอกถึงเวลาของการดำเนินการและวัตถุใด ๆ ที่ได้รับสิทธิ์

ขั้นตอนที่ 3: ดำเนินการ Procedure

เมื่อได้สร้าง stored procedure เสร็จแล้ว คุณสามารถดำเนินการได้ด้วยคำสั่งง่าย ๆ:

EXEC sp_grantastic

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

สรุป

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

ขอให้การเขียนโค้ดเป็นไปอย่างราบรื่น!