中文 (中国)
English
Trong MySQL, role (vai trò) là một tập hợp quyền hạn được đặt tên, giúp quản trị viên dễ dàng gán hoặc thu hồi quyền truy cập cho người dùng. Khi một ứng dụng cơ sở dữ liệu có hàng trăm hoặc hàng nghìn người dùng, việc quản lý quyền riêng lẻ cho từng tài khoản có thể trở thành một bài toán phức tạp. Đây chính là lúc hệ thống vai trò trong MySQL phát huy tác dụng, giúp đơn giản hóa quy trình quản lý quyền hạn một cách linh hoạt và hiệu quả.
Hãy xem xét một hệ thống ngân hàng giả định, nơi có nhiều nhóm người dùng với các quyền khác nhau: Nhà phát triển hệ thống (System Developers) – Cần quyền truy cập đầy đủ để tạo, đọc, cập nhật và xóa các đối tượng trong cơ sở dữ liệu. Quản lý ngân hàng (Bank Managers) – Chỉ có thể INSERT, UPDATE, DELETE bản ghi nhưng không thể thay đổi cấu trúc bảng hay xóa dữ liệu quan trọng. Nhân viên giao dịch (Tellers) – Chỉ có quyền INSERT giao dịch mới vào hệ thống. Nếu cần xóa bản ghi nhập sai, họ phải yêu cầu quản lý can thiệp. Kiểm toán viên (Auditors) – Chỉ có quyền READ-ONLY, nhằm phục vụ việc giám sát và báo cáo.
Trong MySQL 8.x, việc sử dụng roles giúp bạn kiểm soát quyền truy cập một cách nhanh chóng và bảo mật hơn. Thay vì chỉnh sửa từng tài khoản người dùng, bạn chỉ cần cập nhật hoặc thu hồi quyền của một vai trò duy nhất, đảm bảo tính linh hoạt khi có thay đổi trong hệ thống. Trong hướng dẫn này, chúng ta sẽ triển khai và quản lý vai trò trong MySQL, áp dụng vào một hệ thống ngân hàng với 1 nhà phát triển, 1 quản lý, 2 nhân viên giao dịch và 1 kiểm toán viên. Hãy cùng khám phá cách sử dụng roles để tối ưu hóa quản lý quyền hạn trong MySQL ngay bây giờ!
Yêu cầu tiên quyết
Trước khi tiến hành theo hướng dẫn, hãy đảm bảo rằng bạn đã có:
- Một máy chủ chạy Ubuntu 20.04.
- Một tài khoản có quyền sudo.
- Một bộ LAMP Stack.
Hướng dẫn này được kiểm tra trên một cơ sở dữ liệu MySQL chạy trên máy chủ Ubuntu 20.04, tuy nhiên cũng có thể áp dụng cho các hệ điều hành khác hỗ trợ MySQL phiên bản 8.x.
Tạo cơ sở dữ liệu mẫu và các vai trò hệ thống
Kết nối với máy chủ của bạn và đăng nhập vào MySQL với tư cách root.
$ sudo mysql -u root -p
Nhập mật khẩu root của MySQL và nhấn Enter để tiến hành. Bắt đầu bằng cách tạo một cơ sở dữ liệu ngân hàng trống.
mysql> CREATE DATABASE bank;
Chuyển sang sử dụng cơ sở dữ liệu bank.
mysql> USE bank;
Khi cơ sở dữ liệu đã được tạo, bạn sẽ tiến hành tạo các bảng mẫu.
Tạo bảng khách hàng
Tiếp theo, tạo bảng clients để lưu trữ thông tin KYC (Know Your Customer) cho ngân hàng giả định. Bảng này sẽ gán một client_id duy nhất cho mỗi khách hàng và lưu trữ tên cũng như thông tin địa chỉ của họ.
mysql> CREATE TABLE clients (
client_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
address VARCHAR(255)
) ENGINE = InnoDB;
Chèn dữ liệu mẫu vào bảng clients.
mysql> INSERT INTO clients (first_name, last_name, address) VALUES ('JOHN', 'DOE', '1ST STREET, FRESNO, CA XYZ');
mysql> INSERT INTO clients (first_name, last_name, address) VALUES ('MARY', 'SMITH', '2ND STREET, MIAMI, FL 123');
mysql> INSERT INTO clients (first_name, last_name, address) VALUES ('STEVE', 'MARK', '3RD STREET, PHOENIX, AZ ABC');
Truy vấn bảng clients để đảm bảo các bản ghi đã được chèn thành công.
mysql> SELECT
client_id,
first_name,
last_name,
address
FROM clients;
Kết quả hiển thị các khách hàng như sau:
+-----------+------------+-----------+-----------------------------+ | client_id | first_name | last_name | address | +-----------+------------+-----------+-----------------------------+ | 1 | JOHN | DOE | 1ST STREET, FRESNO, CA XYZ | | 2 | MARY | SMITH | 2ND STREET, MIAMI, FL 123 | | 3 | STEVE | MARK | 3RD STREET, PHOENIX, AZ ABC | +-----------+------------+-----------+-----------------------------+ 3 rows in set (0.00 sec)
Tạo bảng giao dịch
Trong ngân hàng giả định, bạn sẽ lưu trữ các hoạt động của khách hàng trong bảng clients_transactions. Bảng này gán một transaction_id duy nhất cho mỗi giao dịch ghi nhận mỗi lần ghi nợ hoặc ghi có của khách hàng. Cột client_id trong bảng này liên kết với bảng clients để tạo mối quan hệ cha-con giữa các giao dịch và khách hàng.
- Cột
transaction_datelưu trữ ngày thực khi một giao dịch mới được chèn vào cơ sở dữ liệu. - Cột
transaction_typelưu giá trị “C” cho Credit (ghi có) hoặc “D” cho Debit (ghi nợ). - Cột
amountlưu trữ giá trị tiền tệ của giao dịch.
Tạo bảng clients_transactions.
mysql> CREATE TABLE clients_transactions (
transaction_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
client_id INT NOT NULL,
transaction_date DATE,
transaction_type VARCHAR(1),
amount DECIMAL(17, 2)
) ENGINE = InnoDB;
Chèn dữ liệu mẫu vào bảng clients_transactions.
mysql> INSERT INTO clients_transactions (client_id, transaction_date, transaction_type, amount) VALUES ('1', '2021-01-21', 'C', '45.00');
mysql> INSERT INTO clients_transactions (client_id, transaction_date, transaction_type, amount) VALUES ('2', '2021-01-21', 'C', '60.00');
mysql> INSERT INTO clients_transactions (client_id, transaction_date, transaction_type, amount) VALUES ('3', '2021-01-21', 'C', '90.00');
mysql> INSERT INTO clients_transactions (client_id, transaction_date, transaction_type, amount) VALUES ('3', '2021-01-21', 'D', '14.00');
Truy vấn bảng clients_transactions để đảm bảo dữ liệu đã được chèn thành công.
mysql> SELECT
transaction_id,
client_id,
transaction_date,
transaction_type,
amount
FROM clients_transactions;
Kết quả hiển thị như sau:
+----------------+-----------+------------------+------------------+--------+ | transaction_id | client_id | transaction_date | transaction_type | amount | +----------------+-----------+------------------+------------------+--------+ | 1 | 1 | 2021-01-21 | C | 45.00 | | 2 | 2 | 2021-01-21 | C | 60.00 | | 3 | 3 | 2021-01-21 | C | 90.00 | | 4 | 3 | 2021-01-21 | D | 14.00 | +----------------+-----------+------------------+------------------+--------+ 4 rows in set (0.00 sec)
Tạo các vai trò hệ thống
Sau khi đã có dữ liệu mẫu, giờ bạn sẽ tạo các vai trò hệ thống. Trong hướng dẫn này, bạn sẽ có các nhóm sau:
- system_developer: Vai trò này được gán cho các nhà phát triển hệ thống thiết kế ứng dụng ngân hàng. Họ sẽ có quyền truy cập đầy đủ vào cơ sở dữ liệu. Trong hướng dẫn này, sẽ có
1nhà phát triển hệ thống. - bank_manager: Vai trò này được dùng để quản lý quyền của các quản lý ngân hàng. Họ có thể tạo, đọc, cập nhật và xóa các bản ghi, nhưng không có quyền tạo hay xóa các đối tượng của cơ sở dữ liệu.
- bank_clerk: Trong thực tế, sẽ có nhiều nhân viên giao dịch để ghi nhận các khoản gửi và rút tiền của khách hàng trong bảng
clients_transactions. Do đó, nhân viên giao dịch cần có quyền tạo khách hàng mới, nhưng nếu cần chỉnh sửa hoặc xóa bản ghi, họ phải nhờ quản lý. Trong hướng dẫn này, bạn sẽ tạo2nhân viên giao dịch. - bank_auditor: Kiểm toán viên chỉ cần quyền chỉ đọc các bảng dữ liệu để phục vụ báo cáo. Trong hướng dẫn này, chỉ có
1kiểm toán viên.
Tạo tất cả các vai trò hệ thống bằng cách chạy các lệnh sau từng bước một.
mysql> CREATE ROLE 'system_developer'@'localhost'; mysql> CREATE ROLE 'bank_manager'@'localhost'; mysql> CREATE ROLE 'bank_clerk'@'localhost'; mysql> CREATE ROLE 'bank_auditor'@'localhost';
Kiểm tra các vai trò đã được chèn vào cơ sở dữ liệu.
mysql> SELECT User FROM mysql.user;
Kết quả hiển thị tên các vai trò trên danh sách người dùng MySQL như dưới đây:
+------------------+ | User | +------------------+ | ... | | bank_auditor | | bank_clerk | | bank_manager | | system_developer | | ... | +------------------+
Gán quyền cho các vai trò
Trong bước này, bạn sẽ cấp quyền cho các vai trò vừa tạo.
Cấp quyền đầy đủ cho vai trò system_developer:
mysql> CREATE USER 'mary'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_MARY_PASSWORD';
Cấp quyền cho vai trò bank_manager. Cho phép quản lý có quyền tạo, đọc và cập nhật bảng clients, và quyền INSERT, SELECT, UPDATE, DELETE cho bảng clients_transactions.
mysql> GRANT INSERT, SELECT, UPDATE ON bank.clients TO 'bank_manager'@'localhost'; mysql> GRANT INSERT, SELECT, UPDATE, DELETE ON bank.clients_transactions TO 'bank_manager'@'localhost';
Cấp quyền cho vai trò bank_clerk:
Cho phép nhân viên giao dịch có quyền INSERT và SELECT cho cả bảng clients và clients_transactions. (Lưu ý: không cho phép UPDATE và DELETE vì quản lý sẽ xử lý các thao tác rủi ro đó.)
mysql> GRANT INSERT, SELECT, UPDATE ON bank.clients TO 'bank_manager'@'localhost'; mysql> GRANT INSERT, SELECT, UPDATE, DELETE ON bank.clients_transactions TO 'bank_manager'@'localhost';
Cấp quyền cho vai trò bank_auditor:
Chỉ cho phép quyền SELECT (chỉ đọc) trên cả hai bảng.
mysql> GRANT SELECT ON bank.clients TO 'bank_auditor'@'localhost'; mysql> GRANT SELECT ON bank.clients_transactions TO 'bank_auditor'@'localhost';
Để kiểm tra quyền của bất kỳ vai trò nào, sử dụng lệnh sau:
mysql> SHOW GRANTS FOR '[Role Name]'@'localhost';
Ví dụ, để kiểm tra quyền của bank_auditor:
mysql> SHOW GRANTS FOR 'bank_auditor'@'localhost';
Kết quả hiển thị các quyền của bank_auditor sẽ như sau:
+----------------------------------------------------------------+ | Grants for bank_auditor@localhost | +----------------------------------------------------------------+ | GRANT USAGE ON *.* TO `bank_auditor`@`localhost` | | GRANT SELECT ON `bank`.`clients` TO `bank_auditor`@`localhost` | +----------------------------------------------------------------+ 2 rows in set (0.00 sec)
Tạo người dùng hệ thống
Sau khi đã định nghĩa các vai trò hệ thống, bạn có thể thêm người dùng vào từng vai trò. Bắt đầu bằng cách tạo người dùng có tên mary, người sau này sẽ được gán vào vai trò system_developer.
mysql> CREATE USER 'mary'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_MARY_PASSWORD';
Sau đó, tạo người dùng raphael người sẽ đảm nhận vai trò bank_manager.
mysql> CREATE USER 'raphael'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_RAPHAEL_PASSWORD';
Tiếp theo, tạo 2 nhân viên giao dịch với tên james và andrew. Họ sẽ chịu trách nhiệm tạo khách hàng mới và ghi nhận các hoạt động giao dịch.
mysql> CREATE USER 'james'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_JAMES_PASSWORD'; mysql> CREATE USER 'andrew'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_ANDREW_PASSWORD';
Cuối cùng, thêm kiểm toán viên bob vào MySQL để thực hiện các truy vấn nhằm tạo báo cáo cho ngân hàng.
mysql> CREATE USER 'bob'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_BOB_PASSWORD';
Kiểm tra bảng mysql.user để đảm bảo rằng các người dùng đã được tạo thành công.
mysql> SELECT User FROM mysql.user;
Kết quả sẽ hiển thị danh sách các người dùng như sau:
+------------------+ | User | +------------------+ | ... | | andrew | | bob | | james | | mary | | raphael | | ... | +------------------+
Gán người dùng vào các vai trò hệ thống
Bây giờ bạn đã có các vai trò và người dùng, bước tiếp theo là gán người dùng vào vai trò tương ứng bằng cú pháp dưới đây:
mysql> GRANT '[ROLE NAME]'@'[HOSTNAME]' TO '[USERNAME]'@'[HOSTNAME]';
Chạy các lệnh dưới đây để gán người dùng vào vai trò của họ:
Gán mary vào vai trò system_developer:
mysql> GRANT 'system_developer'@'localhost' TO 'mary'@'localhost';
Gán raphael vào vai trò bank_manager:
mysql> GRANT 'bank_manager'@'localhost' TO 'raphael'@'localhost';
Gán james và andrew vào vai trò bank_clerk:
mysql> GRANT 'bank_clerk'@'localhost' TO 'james'@'localhost'; mysql> GRANT 'bank_clerk'@'localhost' TO 'andrew'@'localhost';
Gán bob vào vai trò bank_auditor:
mysql> GRANT 'bank_auditor'@'localhost' TO 'bob'@'localhost';
Sau khi đã gán người dùng vào các vai trò tương ứng, chạy các lệnh dưới đây để kích hoạt các quyền mới:
mysql> SET DEFAULT ROLE ALL TO 'mary'@'localhost'; mysql> SET DEFAULT ROLE ALL TO 'raphael'@'localhost'; mysql> SET DEFAULT ROLE ALL TO 'james'@'localhost'; mysql> SET DEFAULT ROLE ALL TO 'andrew'@'localhost'; mysql> SET DEFAULT ROLE ALL TO 'bob'@'localhost';
Kết thúc phiên làm việc với MySQL:
mysql> QUIT;
Kiểm tra quyền hạn hệ thống
Cuối cùng, bạn sẽ đăng nhập bằng các tài khoản người dùng mới và kiểm tra xem các quyền đã được cấp có hoạt động như mong đợi không.
Đăng nhập với tài khoản của kiểm toán viên bob:
mysql> mysql -u bob -p
Sau khi nhập mật khẩu cho user bob, chạy lệnh dưới đây để kiểm tra vai trò hiện tại:
mysql> SELECT current_role();
Kết quả hiển thị rằng bob đang đăng nhập với vai trò bank_auditor:
+----------------------------+ | current_role() | +----------------------------+ | `bank_auditor`@`localhost` | +----------------------------+ 1 row in set (0.00 sec)
Thực hiện truy vấn SELECT trên 2 bảng để kiểm tra quyền hạn:
Chuyển sang cơ sở dữ liệu bank:
mysql> USE bank;
Với vai trò kiểm toán viên, chạy lệnh dưới đây để hiển thị tên khách hàng đã thực hiện các giao dịch gửi và rút tiền nhằm phục vụ báo cáo:
mysql> SELECT
clients_transactions.client_id,
first_name,
last_name,
transaction_id,
transaction_date
FROM clients_transactions
LEFT JOIN clients
ON clients_transactions.client_id = clients.client_id;
Kết quả hiển thị như sau:
+-----------+------------+-----------+----------------+------------------+ | client_id | first_name | last_name | transaction_id | transaction_date | +-----------+------------+-----------+----------------+------------------+ | 1 | JOHN | DOE | 1 | 2021-01-21 | | 2 | MARY | SMITH | 2 | 2021-01-21 | | 3 | STEVE | MARK | 3 | 2021-01-21 | | 4 | STEVE | MARK | 4 | 2021-01-21 | +-----------+------------+-----------+----------------+------------------+ 4 rows in set (0.00 sec)
Kiểm tra lệnh INSERT không được phép với vai trò kiểm toán viên:
Thử thực hiện lệnh chèn dữ liệu vào bảng clients_transactions:
mysql> INSERT INTO clients_transactions (client_id, transaction_date, transaction_type, amount) VALUES ('1', '2021-01-21', 'D', '3.00');
Bạn sẽ nhận được thông báo lỗi vì vai trò bank_auditor không có quyền INSERT:
ERROR 1142 (42000): INSERT command denied to user 'bob'@'localhost' for table 'clients_transactions'
Thoát khỏi giao diện dòng lệnh MySQL:
mysql> QUIT;
Đăng nhập với tài khoản của nhân viên giao dịch (bank_clerk):
Đăng nhập với user james:
mysql> mysql -u james -p
Sau khi nhập mật khẩu cho james, kiểm tra vai trò hiện tại:
mysql> SELECT current_role();
Kết quả hiển thị rằng bạn đang đăng nhập với vai trò bank_clerk:
+--------------------------+ | current_role() | +--------------------------+ | `bank_clerk`@`localhost` | +--------------------------+ 1 row in set (0.00 sec)
Chèn khách hàng mới vào bảng clients:
mysql> USE bank;
mysql> INSERT INTO clients (first_name, last_name, address) VALUES ('FRANK', 'MANU', '4TH STREET, TUCSON, AZ DEF');
Kết quả: lệnh INSERT thành công
Query OK, 1 row affected (0.01 sec)
Tương tự, chèn một giao dịch mới vào bảng clients_transactions:
mysql> INSERT INTO clients_transactions (client_id, transaction_date, transaction_type, amount) VALUES ('1', '2021-01-21', 'D', '3.00');
Dữ liệu giao dịch mới sẽ được ghi nhận thành công.
Query OK, 1 row affected (0.00 sec)
Thử thực hiện lệnh DELETE với vai trò nhân viên giao dịch:
Vì vai trò bank_clerk không được phép xóa dữ liệu, hãy thử xóa một bản ghi trong bảng clients:
mysql> DELETE FROM clients where client_id = '1';
Kết quả sẽ hiển thị thông báo lỗi:
ERROR 1142 (42000): DELETE command denied to user 'james'@'localhost' for table 'clients'
Sau đó, thoát khỏi MySQL:
mysql> QUIT;
Đăng nhập với tài khoản của quản lý (bank_manager):
Đăng nhập với user raphael:
mysql> mysql -u raphael -p
Nhập mật khẩu cho raphael và kiểm tra vai trò hiện tại:
mysql> SELECT current_role();
Kết quả xác nhận bạn đang đăng nhập với vai trò bank_manager:
+----------------------------+ | current_role() | +----------------------------+ | `bank_manager`@`localhost` | +----------------------------+ 1 row in set (0.00 sec)
Chuyển sang cơ sở dữ liệu bank:
mysql> USE bank;
Thử xóa bản ghi đầu tiên trong bảng clients_transactions:
mysql> DELETE FROM clients_transactions where transaction_id = '1';
Bản ghi giao dịch sẽ bị xóa thành công do vai trò quản lý có quyền DELETE.
Query OK, 1 row affected (0.01 sec)
Tuy nhiên, với vai trò bank_manager, bạn không được phép thay đổi cấu trúc của cơ sở dữ liệu. Hãy xác nhận điều này bằng cách thử xóa toàn bộ bảng clients_transactions:
mysql> DROP TABLE clients_transactions;Kết quả sẽ hiển thị lỗi:
mysql> ERROR 1142 (42000): DROP command denied to user 'raphael'@'localhost' for table 'clients_transactions'
Thoát khỏi MySQL:
mysql> QUIT;
Cuối cùng, đăng nhập với tài khoản của nhà phát triển hệ thống (system_developer):
Đăng nhập với user mary:
mysql> mysql -u mary -p
Nhập mật khẩu cho mary và kiểm tra vai trò hiện tại:
mysql> SELECT current_role();
Kết quả xác nhận rằng mary thuộc vai trò system_developer:
+--------------------------------+ | current_role() | +--------------------------------+ | `system_developer`@`localhost` | +--------------------------------+ 1 row in set (0.00 sec)
Với vai trò system_developer, bạn có đầy đủ quyền trên cơ sở dữ liệu ngân hàng. Hãy chuyển sang cơ sở dữ liệu và thử xóa bảng clients_transactions:
mysql> USE bank; mysql> DROP TABLE clients_transactions;
Lệnh DROP sẽ chạy thành công vì bạn là super-admin của cơ sở dữ liệu.
Query OK, 0 rows affected (0.03 sec)
Kết luận
Trong hướng dẫn này, bạn đã tìm hiểu cách tạo roles (vai trò) và gán chúng cho người dùng trong MySQL. Bạn cũng đã thấy cách cấp quyền cho các vai trò để tối ưu hóa quản lý bảo mật cơ sở dữ liệu một cách hiệu quả. Mặc dù đây chỉ là một ví dụ minh họa, nhưng bạn hoàn toàn có thể áp dụng các đoạn mã từ hướng dẫn này để nâng cao tính bảo mật và đơn giản hóa quản lý quyền truy cập cho các ứng dụng MySQL của mình.
