《MYSQL 8 教學–DAY05–用戶與權限管理》操作分享 | 學習筆記

本次的教學，主要分享MySQL在對資料表(Table)有了基本操作的相關知識後，後續開始針對用戶管理操作，在做更進階的討論，主要我們會談用戶與權限管理的實際操作，相關指令會圍繞在CREATE USER、GRANT 、GRANT ALL、REVOKE 、DROP USER，本教學是以Mysql 8.0為出發的教學，當然一些通用語法Mysql 8.0之前的版本也適用喔，所以大家不用擔心，只要一起做，相信你也可以了解MySQL 中的資料表操作是怎麼一回事。

本教學的範例說明

在MySQL中，用戶和權限管理是關於管理資料庫用戶及其訪問權限的工作。以下是一些與MySQL用戶和權限管理相關的操作

資料庫名稱:rdpapashop

CREATE DATABASE `rdpapashop` /*!40100 COLLATE 'utf8mb4_unicode_ci' */

可設定權限的資料表名稱:employees員工資料表、department部門資料表

建立資料表(employees)

use `rdpapashop`;
CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department_id INT,
    salary DECIMAL(10, 2),
    age INT,
    hire_date DATE
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

建立範例資料(employees)

INSERT INTO employees (first_name, last_name, department_id, salary, age, hire_date)
VALUES
    ('John', 'Doe', 1, 55000.00, 30, '2020-01-15'),
    ('Jane', 'Smith', 2, 60000.50, 28, '2019-11-20'),
    ('Michael', 'Johnson', 3, 75000.75, 35, '2018-05-10'),
    ('Emily', 'Brown', 4, 50000.25, 27, '2021-02-18'),
    ('William', 'Jones', 1, 60000.00, 29, '2017-09-03'),
    ('Olivia', 'Davis', 3, 70000.50, 32, '2016-12-22'),
    ('James', 'Wilson', 2, 62000.75, 31, '2020-08-14'),
    ('Sophia', 'Taylor', 4, 51000.25, 26, '2019-03-25'),
    ('Benjamin', 'Anderson', 3, 77000.00, 33, '2015-06-12'),
    ('Emma', 'Martinez', 1, 58000.50, 28, '2022-04-30'),
    ('Mason', 'Hernandez', 4, 52000.75, 27, '2018-10-05'),
    ('Ava', 'Lopez', 2, 61000.25, 30, '2020-07-02'),
    ('Liam', 'Garcia', 3, 76000.00, 34, '2017-02-11'),
    ('Isabella', 'Rodriguez', 1, 59000.50, 29, '2019-09-19'),
    ('Noah', 'Martinez', 4, 53000.75, 26, '2021-01-08'),
    ('Oliver', 'Gonzalez', 3, 78000.25, 35, '2016-04-17'),
    ('Sophia', 'Hernandez', 2, 63000.50, 31, '2020-11-27'),
    ('Mia', 'Smith', 1, 60000.00, 28, '2018-08-07'),
    ('Liam', 'Anderson', 3, 79000.75, 36, '2017-03-15'),
    ('Ava', 'Johnson', 4, 54000.25, 27, '2019-06-28'),
    ('Lucas', 'Brown', 1, 65000.75, 29, '2022-01-05'),
    ('Harper', 'Garcia', 2, 72000.00, 32, '2017-11-12'),
    ('Evelyn', 'Davis', 3, 59000.50, 28, '2018-03-19'),
    ('Logan', 'Taylor', 4, 56000.25, 27, '2021-04-14'),
    ('Avery', 'Martinez', 1, 61000.00, 30, '2019-09-22'),
    ('Jackson', 'Wilson', 2, 68000.75, 33, '2016-02-28'),
    ('Penelope', 'Lopez', 3, 60000.25, 28, '2020-05-17'),
    ('Sebastian', 'Hernandez', 4, 57000.50, 27, '2021-08-10'),
    ('Luna', 'Gonzalez', 1, 62000.00, 29, '2019-12-03'),
    ('Mateo', 'Johnson', 2, 70000.75, 34, '2017-04-25'),
    ('Grace', 'Smith', 3, 61000.50, 30, '2018-06-28');

建立資料表(department)

use `rdpapashop`;
CREATE TABLE departments(
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

建立範例資料(department)

INSERT INTO departments (department_name)
VALUES
    ('HR'),
    ('Finance'),
    ('Engineering'),
    ('Sales'),
    ('Marketing'),
    ('IT'),
    ('Customer Support'),
    ('Research and Development'),
    ('Quality Assurance'),
    ('Operations'),
    ('Legal'),
    ('Public Relations'),
    ('Production'),
    ('Supply Chain'),
    ('Administration'),
    ('Business Development'),
    ('Design'),
    ('Training'),
    ('Health and Safety'),
    ('Facilities');

✒RDPAPA Tip

安裝好以上範例資料後，我們就可以開始我們的教學練習，本範例資料為employees與departments的資料表，包含員工的基本資訊，如姓名、部門、薪水等欄位

操作實例

建立新的使用者帳號

CREATE USER 'rd_user'@'localhost' IDENTIFIED BY 'password';

#這將創建一個名為rd_user的用戶，限制其僅能從本地主機(localhost)訪問，你也可以設定為不限制IP=>'rd_user'@'%'
#並設定用戶密碼為password。你可以根據需要更改主機和密碼。

授予使用者SELECT, INSERT, UPDATE, DELETE權限-授予資料表部分權限

GRANT SELECT, INSERT, UPDATE, DELETE ON employees.* TO 'rd_user'@'localhost';
flush privileges;­
#授予使用者'rd_user'@'localhost'，employees資料表的SELECT, INSERT, UPDATE, DELETE權限

授權資料表權限-授予單一資料表全部權限

GRANT ALL PRIVILEGES ON employees.* TO 'rd_user'@'localhost';
flush privileges;­

#授予使用者'rd_user'@'localhost'，employees資料表的全部權限

刪除資料表權限-刪除單一資料表全部權限

REVOKE ALL PRIVILEGES ON employees.* FROM 'rd_user'@'localhost';
flush privileges;­

#移除使用者'rd_user'@'localhost'，employees資料表的全部權限

授權所有資料庫權限

GRANT ALL PRIVILEGES ON *.* TO 'rd_user'@'localhost';
flush privileges;­

移除所有資料庫權限

REVOKE ALL PRIVILEGES ON *.* FROM 'rd_user'@'localhost';
flush privileges;­

查詢使用者的被賦予的權限(驗證設定資料)

SHOW GRANTS FOR 'rdpa_user'@'localhost';

#查詢權限資料庫中使用者rdpa_user，有哪些權限。

=>查詢結果

修改使用者密碼

ALTER USER 'rdpa_user'@'localhost' IDENTIFIED BY 'new_password';

修改使用者名稱

RENAME USER 'rdpa_user'@'localhost' TO 'rdpapa_usr'@'localhost';
#使用者名稱的主機名稱(localhost or IP)部分（如果省略）預設為 '%'。

刪除用戶

DROP USER 'rdpa_user'@'localhost';

新增root帳號，並且給予全部權限

mysql>CREATE USER 'root'@'localhost' IDENTIFIED BY 'password';

mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;

mysql>FLUSH PRIVILEGES;

※如果你是超級管理員root，可以用以下指令查詢目前所有使用者權限列表

select Host, Db, User, Insert_priv, Update_priv, Delete_priv, Create_tmp_table_priv, Alter_priv from mysql.db;

Reference

• MySQL 8.0 Reference Manual Account Management Statements