Câu lệnh GRANT và REVOKE trong MySQL

Câu lệnh GRANT trong MySQL được sử dụng để cấp quyền cho người dùng hoặc nhóm người dùng trên các đối tượng như cơ sở dữ liệu, bảng, cột, thủ tục, hàm, hoặc trên toàn bộ hệ thống. Việc quản lý quyền sử dụng người dùng là rất quan trọng trong MySQL để bảo mật và phân quyền rõ ràng.

Dưới đây là hướng dẫn chi tiết về câu lệnh GRANT trong MySQL, bao gồm các trường hợp từ cơ bản đến nâng cao.

1. Cú pháp cơ bản của câu lệnh GRANT

GRANT quyền [quyền,...] 
ON đối_tượng
TO 'người_dùng'@'máy_chủ'
[IDENTIFIED BY 'mật khẩu']
[WITH GRANT OPTION];

Trong đó:

• quyền: Danh sách các quyền muốn cấp (xem phần danh sách quyền phổ biến bên dưới).
• đối_tượng: Có thể là cơ sở dữ liệu, bảng, cột hoặc toàn hệ thống (ví dụ: *.* nghĩa là tất cả các cơ sở dữ liệu).
• người_dùng: Tên người dùng MySQL.
• máy_chủ: Địa chỉ máy chủ mà người dùng có thể kết nối (có thể là % để chỉ định tất cả máy chủ).
• WITH GRANT OPTION: Nếu có thêm tùy chọn này, người dùng được cấp quyền cũng có quyền cấp lại quyền đó cho người dùng khác.

2. Các quyền phổ biến

• ALL PRIVILEGES: Cấp tất cả các quyền.
• SELECT: Cho phép đọc dữ liệu từ bảng.
• INSERT: Cho phép chèn dữ liệu vào bảng.
• UPDATE: Cho phép cập nhật dữ liệu trong bảng.
• DELETE: Cho phép xóa dữ liệu từ bảng.
• CREATE: Cho phép tạo cơ sở dữ liệu hoặc bảng.
• DROP: Cho phép xóa cơ sở dữ liệu hoặc bảng.
• ALTER: Cho phép thay đổi cấu trúc bảng.
• INDEX: Cho phép tạo và xóa chỉ mục.
• EXECUTE: Cho phép thực thi các thủ tục lưu trữ hoặc hàm.
• GRANT OPTION: Cho phép cấp quyền cho người dùng khác.

3. Ví dụ cấp quyền cơ bản

3.1. Cấp quyền toàn bộ cho một người dùng

Để cấp tất cả các quyền (ALL PRIVILEGES) trên toàn hệ thống cho người dùng user1 kết nối từ bất kỳ máy chủ nào:

GRANT ALL PRIVILEGES ON *.* TO 'user1'@'%';

Câu lệnh trên sẽ cấp tất cả các quyền cho người dùng user1 để thực hiện bất kỳ thao tác nào trên tất cả các cơ sở dữ liệu từ bất kỳ máy chủ nào.

3.2. Cấp quyền truy cập vào một cơ sở dữ liệu

Để cấp quyền SELECT, INSERT, và UPDATE cho người dùng user2 chỉ trên cơ sở dữ liệu shop:

GRANT SELECT, INSERT, UPDATE ON shop.* TO 'user2'@'localhost';

Câu lệnh trên cấp quyền truy cập cho user2 chỉ có thể thực hiện các quyền được cấp trên tất cả các bảng trong cơ sở dữ liệu shop.

4. Cấp quyền trên bảng cụ thể

Bạn có thể giới hạn quyền chỉ trên một bảng cụ thể trong một cơ sở dữ liệu.

Ví dụ:

Cấp quyền SELECT và DELETE cho người dùng user3 chỉ trên bảng employees của cơ sở dữ liệu company:

GRANT SELECT, DELETE ON company.employees TO 'user3'@'localhost';

Người dùng user3 sẽ chỉ có thể đọc và xóa dữ liệu trong bảng employees của cơ sở dữ liệu company.

5. Cấp quyền cho cột cụ thể

Bạn cũng có thể cấp quyền chỉ trên một hoặc nhiều cột trong bảng.

Ví dụ:

Cấp quyền SELECT cho người dùng user4 chỉ trên cột first_name và last_name của bảng customers:

GRANT SELECT (first_name, last_name) ON shop.customers TO 'user4'@'localhost';

Người dùng user4 chỉ có quyền đọc cột first_name và last_name trong bảng customers.

6. Cấp quyền sử dụng thủ tục và hàm lưu trữ

Để cấp quyền thực thi (EXECUTE) các thủ tục lưu trữ hoặc hàm cho một người dùng cụ thể, bạn có thể sử dụng quyền EXECUTE.

Ví dụ:

Cấp quyền EXECUTE cho người dùng user5 trên tất cả các thủ tục trong cơ sở dữ liệu finance:

GRANT EXECUTE ON finance.* TO 'user5'@'localhost';

Người dùng user5 sẽ có quyền thực thi tất cả các thủ tục lưu trữ và hàm trong cơ sở dữ liệu finance.

7. Cấp quyền với tùy chọn cấp tiếp (GRANT OPTION)

Sử dụng WITH GRANT OPTION để cho phép người dùng được cấp quyền có thể cấp lại quyền đó cho người dùng khác.

Ví dụ:

Cấp tất cả các quyền cho người dùng admin và cho phép họ cấp quyền lại cho người dùng khác:

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

Người dùng admin giờ đây có thể cấp các quyền của họ cho các người dùng khác.

8. Cấp quyền chỉ cho một số kết nối cụ thể

Bạn có thể giới hạn quyền truy cập của người dùng chỉ từ một địa chỉ IP cụ thể hoặc từ máy chủ cụ thể.

Ví dụ:

Cấp quyền SELECT cho người dùng user6 chỉ khi họ kết nối từ địa chỉ IP 192.168.1.100:

GRANT SELECT ON *.* TO 'user6'@'192.168.1.100';

Người dùng user6 sẽ chỉ có thể đọc dữ liệu khi họ kết nối từ địa chỉ IP 192.168.1.100.

9. Xem quyền của người dùng

Để xem quyền của một người dùng cụ thể, bạn có thể sử dụng câu lệnh:

SHOW GRANTS FOR 'user_name'@'host';

Ví dụ:

Xem quyền của người dùng user7:

SHOW GRANTS FOR 'user7'@'localhost';

Kết quả sẽ hiển thị tất cả các quyền mà người dùng user7 đã được cấp.

10. Thu hồi quyền (REVOKE)

Nếu bạn muốn thu hồi các quyền đã cấp, bạn có thể sử dụng câu lệnh REVOKE.

Ví dụ:

Thu hồi quyền SELECT từ người dùng user7 trên cơ sở dữ liệu sales:

REVOKE SELECT ON sales.* FROM 'user7'@'localhost';

11. Cấp quyền cho nhiều người dùng

Bạn có thể cấp quyền cho nhiều người dùng trong cùng một câu lệnh.

Ví dụ:

Cấp quyền SELECT và INSERT cho cả user8 và user9 trên bảng orders:

GRANT SELECT, INSERT ON shop.orders TO 'user8'@'localhost', 'user9'@'localhost';

Tổng kết cấp quyền:

• GRANT là câu lệnh được sử dụng để cấp quyền cho người dùng hoặc nhóm người dùng trong MySQL.
• Các quyền có thể được cấp ở nhiều cấp độ: toàn bộ hệ thống, cơ sở dữ liệu, bảng, cột, hoặc thậm chí cấp độ thủ tục và hàm.
• WITH GRANT OPTION cho phép người dùng cấp quyền cho người dùng khác.
• Quyền có thể được giới hạn dựa trên địa chỉ máy chủ hoặc IP.
• Để thu hồi quyền, bạn sử dụng câu lệnh REVOKE.

Với những thông tin trên, bạn có thể quản lý và phân quyền người dùng trong MySQL một cách chi tiết và hiệu quả.

12. Ví dụ cấp quyền nâng cao trong MySQL

Các ví dụ cấp quyền nâng cao sẽ tập trung vào việc kiểm soát chi tiết và chuyên sâu hơn, bao gồm việc cấp quyền trên nhiều cấp độ đối tượng khác nhau, phân quyền cho cột và điều khiển truy cập nâng cao.

12.1. Cấp quyền chỉ cho một số bảng trong cơ sở dữ liệu

Bạn có thể cấp quyền cho một người dùng chỉ trên một số bảng cụ thể trong một cơ sở dữ liệu. Ví dụ, nếu bạn muốn cấp quyền SELECT và INSERT cho người dùng user1 chỉ trên bảng employees và departments trong cơ sở dữ liệu

GRANT SELECT, INSERT ON company.employees TO 'user1'@'localhost';
GRANT SELECT, INSERT ON company.departments TO 'user1'@'localhost';

Trong ví dụ này, người dùng user1 chỉ có quyền đọc và chèn dữ liệu vào các bảng employees và departments trong cơ sở dữ liệu company.

12.2. Cấp quyền cho các cột cụ thể trong bảng

Trong nhiều trường hợp, bạn muốn giới hạn quyền truy cập của người dùng chỉ trên một số cột cụ thể thay vì toàn bộ bảng. Điều này thường được sử dụng để tăng cường bảo mật dữ liệu.

Ví dụ:

Cấp quyền SELECT cho người dùng user2 chỉ trên các cột first_name, last_name trong bảng employees của cơ sở dữ liệu company:

GRANT SELECT (first_name, last_name) ON company.employees TO 'user2'@'localhost';

Người dùng user2 sẽ chỉ có thể xem dữ liệu từ các cột first_name và last_name, không có quyền truy cập vào các cột khác trong bảng employees.

12.3. Cấp quyền EXECUTE cho các thủ tục lưu trữ (Stored Procedures)

Trong trường hợp bạn muốn cấp quyền cho người dùng thực hiện các thủ tục lưu trữ (stored procedures) hoặc hàm (functions), bạn cần cấp quyền EXECUTE.

Ví dụ:

Cấp quyền EXECUTE cho người dùng user3 trên tất cả các thủ tục và hàm trong cơ sở dữ liệu sales:

GRANT EXECUTE ON PROCEDURE sales.* TO 'user3'@'localhost';

Người dùng user3 giờ đây có thể thực hiện tất cả các thủ tục và hàm trong cơ sở dữ liệu sales.

12.4. Cấp quyền GRANT OPTION cho phép người dùng cấp quyền lại

Trong một số tình huống, bạn muốn một người dùng có thể cấp quyền cho người dùng khác. Điều này được thực hiện thông qua tùy chọn WITH GRANT OPTION.

Ví dụ:

Cấp quyền ALL PRIVILEGES cho người dùng admin1 và cho phép họ cấp quyền lại cho người khác trên cơ sở dữ liệu company:

GRANT ALL PRIVILEGES ON company.* TO 'admin1'@'localhost' WITH GRANT OPTION;

Người dùng admin1 không chỉ có toàn bộ quyền trên cơ sở dữ liệu company mà còn có thể cấp các quyền này cho những người dùng khác.

12.5. Giới hạn quyền truy cập dựa trên địa chỉ IP

Bạn có thể hạn chế quyền của người dùng chỉ từ một địa chỉ IP hoặc dải địa chỉ IP nhất định. Điều này giúp tăng cường bảo mật, chỉ cho phép truy cập từ những địa chỉ cụ thể.

Ví dụ:

Cấp quyền SELECT cho người dùng user4 chỉ khi họ truy cập từ địa chỉ IP 192.168.1.50:

GRANT SELECT ON *.* TO 'user4'@'192.168.1.50';

Người dùng user4 chỉ có thể thực hiện truy vấn SELECT khi truy cập từ địa chỉ IP 192.168.1.50.

12.6. Cấp quyền truy cập theo vai trò (Role-based permissions)

MySQL 8.0 trở lên hỗ trợ khái niệm về vai trò (roles), giúp bạn quản lý các quyền dễ dàng hơn bằng cách gán nhiều quyền vào một vai trò, sau đó gán vai trò đó cho người dùng.

Bước 1: Tạo một vai trò

CREATE ROLE 'manager';

Bước 2: Gán quyền cho vai trò

GRANT SELECT, INSERT, UPDATE ON company.* TO 'manager';

Bước 3: Gán vai trò cho người dùng

GRANT 'manager' TO 'user5'@'localhost';

Giờ đây, người dùng user5 có tất cả các quyền liên quan đến vai trò manager, bao gồm quyền SELECT, INSERT, và UPDATE trên cơ sở dữ liệu company.

12.7. Cấp quyền chỉ đọc (Read-only)

Bạn có thể tạo một người dùng chỉ có quyền đọc (read-only), nghĩa là họ chỉ có thể thực hiện các câu lệnh SELECT mà không có quyền thay đổi dữ liệu.

Ví dụ:

Cấp quyền chỉ đọc cho người dùng user6 trên cơ sở dữ liệu inventory:

GRANT SELECT ON inventory.* TO 'user6'@'localhost';

Người dùng user6 chỉ có thể đọc dữ liệu từ tất cả các bảng trong cơ sở dữ liệu inventory, mà không có quyền sửa đổi.

12.8. Thu hồi quyền truy cập (REVOKE)

Bạn có thể thu hồi các quyền đã cấp cho một người dùng bằng cách sử dụng câu lệnh REVOKE.

Ví dụ:

Thu hồi quyền INSERT của người dùng user7 trên bảng orders trong cơ sở dữ liệu shop:

REVOKE INSERT ON shop.orders FROM 'user7'@'localhost';

Sau khi thu hồi, người dùng user7 sẽ không còn quyền chèn dữ liệu vào bảng orders nữa.

12.9. Cấp quyền sử dụng kết nối SSL

Trong những môi trường yêu cầu bảo mật cao, bạn có thể giới hạn quyền truy cập của người dùng chỉ khi họ sử dụng kết nối SSL.

Ví dụ:

Cấp quyền SELECT cho người dùng user8 chỉ khi họ kết nối thông qua SSL:

GRANT SELECT ON *.* TO 'user8'@'localhost' REQUIRE SSL;

Người dùng user8 sẽ chỉ có thể truy cập dữ liệu nếu họ sử dụng kết nối bảo mật SSL.

12.10. Cấp quyền cho người dùng tạm thời

Bạn có thể giới hạn quyền của người dùng trong một khoảng thời gian cụ thể bằng cách sử dụng lệnh CREATE USER với các tùy chọn giới hạn thời gian truy cập.

Ví dụ:

CREATE USER 'temp_user'@'localhost' IDENTIFIED BY 'password' WITH MAX_CONNECTIONS_PER_HOUR 500;
GRANT ALL PRIVILEGES ON project_db.* TO 'temp_user'@'localhost';

Người dùng temp_user sẽ có quyền truy cập đầy đủ vào cơ sở dữ liệu project_db, nhưng bị giới hạn số kết nối tối đa mỗi giờ là 500, và có thể kết nối trong thời gian quy định.

• Câu Lệnh Trong MySQL
• MySQL