CREATE ROLE tạo một tập hợp các đặc quyền có thể được gán cho người dùng cơ sở dữ liệu. Khi một vai trò được chỉ định cho người dùng, (những) người đó sẽ nhận được tất cả các Đặc quyền của vai trò đó. Bằng cách tạo và cấp vai trò, phương tiện bảo mật cơ sở dữ liệu tốt nhất có thể được thực hiện
Cú pháp SQL
CREATE ROLE role_name [WITH ADMIN {CURRENT_USER | CURRENT_ROLE}]Thông số
NameDescriptionrole_nameTên để xác định vai trò. passwordTạo một vai trò cục bộ được xác thực bằng giá trị chuỗi của mật khẩu. Chỉ cho phép các ký tự một byte trong mật khẩu ngay cả khi sử dụng bộ ký tự nhiều byte. package_nameTạo vai trò ứng dụng chỉ kích hoạt vai trò thông qua ứng dụng sử dụng gói PL/SQL của package_name. Nếu bạn bỏ qua lược đồ, Oracle sẽ giả định rằng gói nằm trong lược đồ của bạn
Việc bạn sử dụng bất kỳ thông tin hoặc tài liệu nào trên trang web này hoàn toàn do bạn chịu rủi ro. Nó được cung cấp chỉ cho mục đích giáo dục. Nó đã được thử nghiệm nội bộ, tuy nhiên, chúng tôi không đảm bảo rằng nó sẽ hiệu quả với bạn. Đảm bảo rằng bạn chạy nó trong môi trường thử nghiệm trước khi sử dụngTóm lược. trong hướng dẫn này, bạn sẽ học cách sử dụng vai trò MySQL để đơn giản hóa việc quản lý đặc quyền
Giới thiệu về vai trò MySQL
Thông thường, bạn có nhiều người dùng với cùng một nhóm đặc quyền. Trước đây, cách duy nhất để cấp và thu hồi đặc quyền cho nhiều người dùng là thay đổi đặc quyền của từng người dùng, điều này rất tốn thời gian
Để dễ dàng hơn, MySQL đã cung cấp một đối tượng mới gọi là vai trò. Một vai trò là một tập hợp các đặc quyền được đặt tên
Giống như tài khoản người dùng, bạn có thể cấp đặc quyền cho vai trò và thu hồi đặc quyền từ họ
Nếu muốn cấp cùng một nhóm quyền cho nhiều người dùng, bạn làm theo các bước sau
- Đầu tiên, tạo một vai trò mới
- Thứ hai, cấp đặc quyền cho vai trò
- Thứ ba, cấp vai trò cho người dùng
Trường hợp muốn thay đổi đặc quyền của user thì chỉ cần thay đổi đặc quyền của role được cấp. Các thay đổi sẽ có hiệu lực đối với tất cả người dùng được cấp vai trò
Ví dụ vai trò MySQL
Đầu tiên, tạo một cơ sở dữ liệu mới có tên CRM, viết tắt của quản lý quan hệ khách hàng
CREATE DATABASE crm;
Code language: SQL (Structured Query Language) (sql)Tiếp theo, sử dụng cơ sở dữ liệu
INSERT INTO customers(first_name,last_name,phone,email) VALUES('John','Doe','(408)-987-7654','john.doe@mysqltutorial.org'), ('Lily','Bush','(408)-987-7985','lily.bush@mysqltutorial.org');
Code language: SQL (Structured Query Language) (sql)4USE crm;
Code language: SQL (Structured Query Language) (sql)Sau đó, tạo bảng
INSERT INTO customers(first_name,last_name,phone,email) VALUES('John','Doe','(408)-987-7654','john.doe@mysqltutorial.org'), ('Lily','Bush','(408)-987-7985','lily.bush@mysqltutorial.org');
Code language: SQL (Structured Query Language) (sql)5 bên trong cơ sở dữ liệuINSERT INTO customers(first_name,last_name,phone,email) VALUES('John','Doe','(408)-987-7654','john.doe@mysqltutorial.org'), ('Lily','Bush','(408)-987-7985','lily.bush@mysqltutorial.org');
Code language: SQL (Structured Query Language) (sql)6CREATE TABLE customers( id INT PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, phone VARCHAR(15) NOT NULL, email VARCHAR(255) );
Code language: SQL (Structured Query Language) (sql)Sau đó, chèn dữ liệu vào bảng
INSERT INTO customers(first_name,last_name,phone,email) VALUES('John','Doe','(408)-987-7654','john.doe@mysqltutorial.org'), ('Lily','Bush','(408)-987-7985','lily.bush@mysqltutorial.org');
Code language: SQL (Structured Query Language) (sql)7INSERT INTO customers(first_name,last_name,phone,email) VALUES('John','Doe','(408)-987-7654','john.doe@mysqltutorial.org'), ('Lily','Bush','(408)-987-7985','lily.bush@mysqltutorial.org');
Code language: SQL (Structured Query Language) (sql)Cuối cùng, xác minh phần chèn bằng cách sử dụng câu lệnh
INSERT INTO customers(first_name,last_name,phone,email) VALUES('John','Doe','(408)-987-7654','john.doe@mysqltutorial.org'), ('Lily','Bush','(408)-987-7985','lily.bush@mysqltutorial.org');
Code language: SQL (Structured Query Language) (sql)8 sau đâySELECT * FROM customers;
Code language: SQL (Structured Query Language) (sql)Tạo vai trò
Giả sử bạn phát triển một ứng dụng sử dụng cơ sở dữ liệu
INSERT INTO customers(first_name,last_name,phone,email) VALUES('John','Doe','(408)-987-7654','john.doe@mysqltutorial.org'), ('Lily','Bush','(408)-987-7985','lily.bush@mysqltutorial.org');
Code language: SQL (Structured Query Language) (sql)6. Để tương tác với cơ sở dữ liệuINSERT INTO customers(first_name,last_name,phone,email) VALUES('John','Doe','(408)-987-7654','john.doe@mysqltutorial.org'), ('Lily','Bush','(408)-987-7985','lily.bush@mysqltutorial.org');
Code language: SQL (Structured Query Language) (sql)6, bạn cần tạo tài khoản cho những nhà phát triển cần toàn quyền truy cập vào cơ sở dữ liệu. Ngoài ra, bạn cần tạo tài khoản cho những người dùng chỉ cần quyền đọc và những người khác cần cả quyền đọc/ghiĐể tránh cấp đặc quyền cho từng tài khoản người dùng riêng lẻ, bạn tạo một nhóm vai trò và cấp các vai trò thích hợp cho từng tài khoản người dùng
Để tạo vai trò mới, bạn sử dụng câu lệnh
SELECT * FROM customers;
Code language: SQL (Structured Query Language) (sql)1CREATE ROLE crm_dev, crm_read, crm_write;
Code language: SQL (Structured Query Language) (sql)Tên vai trò tương tự như tài khoản người dùng bao gồm hai phần. tên và máy chủ
role_name@host_name
Code language: SQL (Structured Query Language) (sql)Nếu bỏ phần host thì mặc định là ‘%’ có nghĩa là bất kỳ host nào
Cấp đặc quyền cho vai trò
Để cấp đặc quyền cho một vai trò, bạn sử dụng câu lệnh
SELECT * FROM customers;
Code language: SQL (Structured Query Language) (sql)2. Câu lệnh sau đây cấp tất cả các đặc quyền cho vai tròSELECT * FROM customers;
Code language: SQL (Structured Query Language) (sql)3GRANT ALL ON crm.* TO crm_dev;
Code language: SQL (Structured Query Language) (sql)Tuyên bố sau trao đặc quyền của
INSERT INTO customers(first_name,last_name,phone,email) VALUES('John','Doe','(408)-987-7654','john.doe@mysqltutorial.org'), ('Lily','Bush','(408)-987-7985','lily.bush@mysqltutorial.org');
Code language: SQL (Structured Query Language) (sql)8 cho vai trò củaSELECT * FROM customers;
Code language: SQL (Structured Query Language) (sql)5GRANT SELECT ON crm.* TO crm_read;
Code language: SQL (Structured Query Language) (sql)Tuyên bố sau đây cấp các đặc quyền của
SELECT * FROM customers;
Code language: SQL (Structured Query Language) (sql)6,SELECT * FROM customers;
Code language: SQL (Structured Query Language) (sql)7 vàSELECT * FROM customers;
Code language: SQL (Structured Query Language) (sql)8 cho vai trò củaSELECT * FROM customers;
Code language: SQL (Structured Query Language) (sql)9GRANT INSERT, UPDATE, DELETE ON crm.* TO crm_write;
Code language: SQL (Structured Query Language) (sql)Gán vai trò cho tài khoản người dùng
Giả sử bạn cần một tài khoản người dùng với tư cách là nhà phát triển, một tài khoản người dùng có thể có quyền truy cập chỉ đọc và hai tài khoản người dùng có thể có quyền truy cập đọc/ghi
Để tạo người dùng mới, bạn sử dụng câu lệnh
CREATE ROLE crm_dev, crm_read, crm_write;
Code language: SQL (Structured Query Language) (sql)0 như sauUSE crm;
Code language: SQL (Structured Query Language) (sql)0Để gán vai trò cho người dùng, bạn sử dụng câu lệnh
SELECT * FROM customers;
Code language: SQL (Structured Query Language) (sql)2Câu lệnh sau cấp vai trò
CREATE ROLE crm_dev, crm_read, crm_write;
Code language: SQL (Structured Query Language) (sql)2 cho tài khoản người dùngCREATE ROLE crm_dev, crm_read, crm_write;
Code language: SQL (Structured Query Language) (sql)3USE crm;
Code language: SQL (Structured Query Language) (sql)1Câu lệnh sau cấp vai trò
SELECT * FROM customers;
Code language: SQL (Structured Query Language) (sql)5 cho tài khoản người dùngCREATE ROLE crm_dev, crm_read, crm_write;
Code language: SQL (Structured Query Language) (sql)5USE crm;
Code language: SQL (Structured Query Language) (sql)2Câu lệnh sau cấp vai trò
SELECT * FROM customers;
Code language: SQL (Structured Query Language) (sql)5 vàSELECT * FROM customers;
Code language: SQL (Structured Query Language) (sql)9 cho tài khoản người dùngCREATE ROLE crm_dev, crm_read, crm_write;
Code language: SQL (Structured Query Language) (sql)8 vàCREATE ROLE crm_dev, crm_read, crm_write;
Code language: SQL (Structured Query Language) (sql)9USE crm;
Code language: SQL (Structured Query Language) (sql)3Để xác minh việc gán vai trò, bạn sử dụng câu lệnh
role_name@host_name
Code language: SQL (Structured Query Language) (sql)0 như ví dụ sauUSE crm;
Code language: SQL (Structured Query Language) (sql)4Câu lệnh trả về tập kết quả sau
Như bạn có thể thấy, nó vừa trả về các vai trò được cấp. Để hiển thị các đặc quyền mà các vai trò đại diện, bạn sử dụng mệnh đề
role_name@host_name
Code language: SQL (Structured Query Language) (sql)1 với tên của các vai trò được cấp như sauUSE crm;
Code language: SQL (Structured Query Language) (sql)5Câu lệnh trả về đầu ra sau
Đặt vai trò mặc định
Bây giờ nếu bạn kết nối với MySQL bằng tài khoản người dùng
role_name@host_name
Code language: SQL (Structured Query Language) (sql)2 và thử truy cập cơ sở dữ liệuINSERT INTO customers(first_name,last_name,phone,email) VALUES('John','Doe','(408)-987-7654','john.doe@mysqltutorial.org'), ('Lily','Bush','(408)-987-7985','lily.bush@mysqltutorial.org');
Code language: SQL (Structured Query Language) (sql)6USE crm;
Code language: SQL (Structured Query Language) (sql)6Tuyên bố đưa ra thông báo lỗi sau
USE crm;
Code language: SQL (Structured Query Language) (sql)7Điều này là do khi bạn cấp vai trò cho tài khoản người dùng, nó không tự động kích hoạt vai trò khi tài khoản người dùng kết nối với máy chủ cơ sở dữ liệu
Nếu bạn gọi hàm
role_name@host_name
Code language: SQL (Structured Query Language) (sql)4, nó sẽ trả vềrole_name@host_name
Code language: SQL (Structured Query Language) (sql)5, nghĩa là không có vai trò tích cực nàoUSE crm;
Code language: SQL (Structured Query Language) (sql)8Đây là đầu ra
USE crm;
Code language: SQL (Structured Query Language) (sql)9Để chỉ định vai trò nào sẽ hoạt động mỗi khi tài khoản người dùng kết nối với máy chủ cơ sở dữ liệu, bạn sử dụng câu lệnh
role_name@host_name
Code language: SQL (Structured Query Language) (sql)6Câu lệnh sau đặt mặc định cho tài khoản
CREATE ROLE crm_dev, crm_read, crm_write;
Code language: SQL (Structured Query Language) (sql)5 tất cả các vai trò được chỉ định của nóCREATE TABLE customers( id INT PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, phone VARCHAR(15) NOT NULL, email VARCHAR(255) );
Code language: SQL (Structured Query Language) (sql)0Bây giờ, nếu bạn kết nối với máy chủ cơ sở dữ liệu MySQL bằng tài khoản người dùng
role_name@host_name
Code language: SQL (Structured Query Language) (sql)2 và gọi hàmrole_name@host_name
Code language: SQL (Structured Query Language) (sql)4CREATE TABLE customers( id INT PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, phone VARCHAR(15) NOT NULL, email VARCHAR(255) );
Code language: SQL (Structured Query Language) (sql)1Bạn sẽ thấy các vai trò mặc định cho tài khoản người dùng
role_name@host_name
Code language: SQL (Structured Query Language) (sql)2CREATE TABLE customers( id INT PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, phone VARCHAR(15) NOT NULL, email VARCHAR(255) );
Code language: SQL (Structured Query Language) (sql)2Bạn có thể kiểm tra các đặc quyền của tài khoản
SELECT * FROM customers;
Code language: SQL (Structured Query Language) (sql)5 bằng cách chuyển cơ sở dữ liệu hiện tại sangINSERT INTO customers(first_name,last_name,phone,email) VALUES('John','Doe','(408)-987-7654','john.doe@mysqltutorial.org'), ('Lily','Bush','(408)-987-7985','lily.bush@mysqltutorial.org');
Code language: SQL (Structured Query Language) (sql)6, thực hiện câu lệnhINSERT INTO customers(first_name,last_name,phone,email) VALUES('John','Doe','(408)-987-7654','john.doe@mysqltutorial.org'), ('Lily','Bush','(408)-987-7985','lily.bush@mysqltutorial.org');
Code language: SQL (Structured Query Language) (sql)8 và câu lệnhSELECT * FROM customers;
Code language: SQL (Structured Query Language) (sql)8 như sauCREATE TABLE customers( id INT PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, phone VARCHAR(15) NOT NULL, email VARCHAR(255) );
Code language: SQL (Structured Query Language) (sql)3Nó hoạt động như mong đợi. Khi chúng tôi đưa ra câu lệnh
SELECT * FROM customers;
Code language: SQL (Structured Query Language) (sql)8, MySQL đã đưa ra lỗi vì tài khoản người dùngrole_name@host_name
Code language: SQL (Structured Query Language) (sql)2 chỉ có quyền đọcĐặt vai trò tích cực
Tài khoản người dùng có thể sửa đổi các đặc quyền có hiệu lực của người dùng hiện tại trong phiên hiện tại bằng cách chỉ định vai trò được cấp nào đang hoạt động
Câu lệnh sau đặt vai trò tích cực thành
role_name@host_name
Code language: SQL (Structured Query Language) (sql)5, nghĩa là không có vai trò tích cực nàoCREATE TABLE customers( id INT PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, phone VARCHAR(15) NOT NULL, email VARCHAR(255) );
Code language: SQL (Structured Query Language) (sql)4Để đặt vai trò tích cực cho tất cả vai trò được cấp, bạn sử dụng
CREATE TABLE customers( id INT PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, phone VARCHAR(15) NOT NULL, email VARCHAR(255) );
Code language: SQL (Structured Query Language) (sql)5Để đặt vai trò tích cực thành vai trò mặc định được đặt bởi câu lệnh
role_name@host_name
Code language: SQL (Structured Query Language) (sql)6, bạn sử dụngCREATE TABLE customers( id INT PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, phone VARCHAR(15) NOT NULL, email VARCHAR(255) );
Code language: SQL (Structured Query Language) (sql)6Để đặt vai trò được đặt tên đang hoạt động, bạn sử dụng
CREATE TABLE customers( id INT PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, phone VARCHAR(15) NOT NULL, email VARCHAR(255) );
Code language: SQL (Structured Query Language) (sql)7Thu hồi đặc quyền từ vai trò
Để thu hồi đặc quyền từ một vai trò cụ thể, bạn sử dụng câu lệnh
GRANT ALL ON crm.* TO crm_dev;
Code language: SQL (Structured Query Language) (sql)9. Tuyên bốGRANT SELECT ON crm.* TO crm_read;
Code language: SQL (Structured Query Language) (sql)0 có hiệu lực không chỉ vai trò mà còn bất kỳ tài khoản nào được cấp vai tròVí dụ: để tạm thời đặt tất cả người dùng đọc/ghi ở chế độ chỉ đọc, bạn thay đổi vai trò
SELECT * FROM customers;
Code language: SQL (Structured Query Language) (sql)9 như sauCREATE TABLE customers( id INT PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, phone VARCHAR(15) NOT NULL, email VARCHAR(255) );
Code language: SQL (Structured Query Language) (sql)8Để khôi phục các đặc quyền, bạn cần cấp lại chúng như sau
CREATE TABLE customers( id INT PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, phone VARCHAR(15) NOT NULL, email VARCHAR(255) );
Code language: SQL (Structured Query Language) (sql)9Xóa vai trò
Để xóa một hoặc nhiều vai trò, bạn sử dụng câu lệnh
GRANT SELECT ON crm.* TO crm_read;
Code language: SQL (Structured Query Language) (sql)2 như sauINSERT INTO customers(first_name,last_name,phone,email) VALUES('John','Doe','(408)-987-7654','john.doe@mysqltutorial.org'), ('Lily','Bush','(408)-987-7985','lily.bush@mysqltutorial.org');
Code language: SQL (Structured Query Language) (sql)0Giống như câu lệnh
GRANT SELECT ON crm.* TO crm_read;
Code language: SQL (Structured Query Language) (sql)0, câu lệnhGRANT SELECT ON crm.* TO crm_read;
Code language: SQL (Structured Query Language) (sql)2 thu hồi các vai trò từ mọi tài khoản người dùng mà họ đã được cấpVí dụ: để xóa vai trò
SELECT * FROM customers;
Code language: SQL (Structured Query Language) (sql)5,SELECT * FROM customers;
Code language: SQL (Structured Query Language) (sql)9, bạn sử dụng câu lệnh sauINSERT INTO customers(first_name,last_name,phone,email) VALUES('John','Doe','(408)-987-7654','john.doe@mysqltutorial.org'), ('Lily','Bush','(408)-987-7985','lily.bush@mysqltutorial.org');
Code language: SQL (Structured Query Language) (sql)1Sao chép đặc quyền từ tài khoản người dùng này sang tài khoản khác
MySQL coi tài khoản người dùng như vai trò, do đó, bạn có thể cấp tài khoản người dùng cho tài khoản người dùng khác giống như cấp vai trò cho tài khoản người dùng đó. Điều này cho phép bạn sao chép các đặc quyền từ người dùng này sang người dùng khác
Giả sử bạn cần một tài khoản nhà phát triển khác cho cơ sở dữ liệu
INSERT INTO customers(first_name,last_name,phone,email) VALUES('John','Doe','(408)-987-7654','john.doe@mysqltutorial.org'), ('Lily','Bush','(408)-987-7985','lily.bush@mysqltutorial.org');
Code language: SQL (Structured Query Language) (sql)6Đầu tiên, tạo tài khoản người dùng mới
INSERT INTO customers(first_name,last_name,phone,email) VALUES('John','Doe','(408)-987-7654','john.doe@mysqltutorial.org'), ('Lily','Bush','(408)-987-7985','lily.bush@mysqltutorial.org');
Code language: SQL (Structured Query Language) (sql)2Thứ hai, sao chép các đặc quyền từ tài khoản người dùng
GRANT SELECT ON crm.* TO crm_read;
Code language: SQL (Structured Query Language) (sql)8 sang tài khoản người dùngGRANT SELECT ON crm.* TO crm_read;
Code language: SQL (Structured Query Language) (sql)9 như sauINSERT INTO customers(first_name,last_name,phone,email) VALUES('John','Doe','(408)-987-7654','john.doe@mysqltutorial.org'), ('Lily','Bush','(408)-987-7985','lily.bush@mysqltutorial.org');
Code language: SQL (Structured Query Language) (sql)3Trong hướng dẫn này, bạn đã học cách sử dụng vai trò MySQL để quản lý đặc quyền của tài khoản người dùng dễ dàng hơn