Cách phân quyền cho người dùng trong MS SQL Server

Thứ ba - 24/10/2023 10:38

Cấp quyền cho user trong SQL Server không khó. Dưới đây là hướng dẫn phân quyền trong SQL Server.

Khi nhắc tới bảo vệ dữ liệu nhạy cảm và giữ cho môi trường database an toàn, quản lý quyền & đặc quyền đăng nhập database sẽ là trọng tâm. Ví dụ, bạn muốn một người nào đó, có thể chỉnh sửa và thao tác dữ liệu cá nhân, còn người khác chỉ có thể xem dữ liệu. Ở trường hợp này, quản trị database sẽ cần tạo người dùng hoặc vai trò mới với quyền cụ thể được phân tại server, database hay cấp schema đó. Để triển khai nhiệm vụ này, họ có thể dùng các lệnh T-SQL được cung cấp bởi SQL Server hay công cụ bên thứ ba.

Thực tế, tạo đăng nhập mới trong SQL Server không khó. Đầu tiên, bạn cần tạo một đăng nhập mới dựa trên xác thực Windows, SQL Server, chứng chỉ hoặc một key bất đối xứng.

Để thêm đăng nhập mới, dùng lệnh CREATE LOGIN. Nó tạo một đăng nhập được kết nối tới phiên bản SQL Server. Đăng nhập này sau đó sẽ được map tới tài khoản người dùng cụ thể. Cú pháp của nó như sau:

Xác thực Windows

CREATE LOGIN login_name
FROM WINDOWS
[ WITH DEFAULT_DATABASE = database_name
| DEFAULT_LANGUAGE = language_name ];

Xác thực SQL Server

CREATE LOGIN login_name
WITH PASSWORD = { 'password' | hashed_password HASHED } [ MUST_CHANGE ]
[ , SID = sid_value
| DEFAULT_DATABASE = database_name
| DEFAULT_LANGUAGE = language_name
| CHECK_EXPIRATION = { ON | OFF }
| CHECK_POLICY = { ON | OFF }
| CREDENTIAL = credential_name ];

Chứng chỉ

CREATE LOGIN login_name
FROM CERTIFICATE certificate_name;

Key bất đối xứng

CREATE LOGIN login_name
FROM ASYMMETRIC KEY asym_key_name;

Giới hạn và những hạn chế bạn cần biết trước khi phân quyền

Phân quyền hay vai trò, không phải đăng nhập hay người dùng cá nhân. Khi thay người được phân quyền, nghĩa là loại bỏ một cá nhân khỏi vai trò đó và thay vào đó bằng người mới. Bạn có thể kết hợp phân nhiều quyền cho một vai trò. Nếu một số người trong công ty hay tổ chức của bạn cần được phân quyền giống nhau, hãy lần lượt thêm họ vào vai trò đó.

Cấu hình các bảo mật tương tự (bảng, trình xem và quy trình) để được sở hữu bởi một schema, sau đó cấp quyền cho schema đó. Ví dụ: lược đồ bảng lương có thể sở hữu một số bảng, cửa sổ xem và thủ tục được lưu trữ. Bằng cách cấp quyền truy cập vào lược đồ, tất cả các quyền cần thiết để thực hiện chức năng tính lương có thể được cấp cùng một lúc.

Nếu những hạn chế trên không phải vấn đề với bạn thì bạn có thể ngay lập tức áp dụng các cách phân quyền cho người dùng MS SQL Server như sau:

Bạn có thể phân quyền trong SQL Server và thu hồi chúng trên các đối tượng database khác nhau. Quyền người dùng luôn ở cấp database.

Bạn có thể cấp bất kỳ hoặc một kết hợp kiểu quyền sau:

Select: Cấp cho người dùng khả năng triển khai hoạt động chọn trên bảng.
Insert: Cho phép người dùng triển khai các hoạt động chèn dữ liệu trên bảng.
Update: Cấp cho người dùng khả năng thực hiện các hoạt động cập nhật bảng.
Delete: Cấp cho người dùng khả năng thực hiện hoạt động xóa trên bảng.
Alter: Cấp cho người dùng khả năng thay đổi định nghĩa bảng.
References: Quyền tham khảo cần thiết cho việc tạo giới hạn khóa ngoại trên bảng. Nó cũng cần để tạo một hàm hoặc lệnh View WITH SCHEMABINDING tham chiếu tới đối tượng đó.
Control: Cấp quyền SELECT, INSERT, UPDATE, DELETE và REFERENCES cho người dùng trên bảng.

Tương tự, bạn có thể thu hồi bất kỳ quyền đã phân.

Bạn có thể cấp hoặc thu hồi quyền bằng SSMS hoặc chạy tập lệnh T-SQL. Dưới đây là hướng dẫn chi tiết.

Cách 1: Dùng T-SQL

Cú pháp:

Use <database name>Grant <permission name> on <object name> to <username\principle>

Ví dụ:

Để phân quyền chọn cho người dùng có tên TestUser trên đối tượng là TestTable trên CSDL có tên TestDB, bạn chạy truy vấn dưới đây.

USE TestDBGOGrant select on TestTable to TestUser

Cách 2: Dùng SQL Server Management Studio SSMS

Bước 1: Kết nối tới CSDL, mở rộng thư mục như trong hình dưới đây.

Mở thư mục Security > Users và tìm tên người dùng muốn phân quyền

Bước 2: Click chuột phải vào TestUser và chọn Properties. Màn hình dưới đây sẽ hiện ra.

Màn hình quản lý người dùng TestUser

Bước 3: Dùng Search để tìm kiếm sau đó chọn đối tượng muốn phân quyền, ở đây là bảng TestTable.

Bước 4: Click chọn bảng TestTable, sau đó chọn hộp checkbox Grant cho các thao tác trong danh sách bên dưới như trong hình dưới đây. Ở ví dụ này là trao quyền chọn bảng (Select).

Tick chọn hộp phân quyền