建立用戶

1. 遠端登入 kvm3.deyu.wang 虛擬機。

   [root@dyw219 ~]# ssh root@kvm3.deyu.wang
   root@kvm3.deyu.wang's password: 
   Last login: Sat Jan 25 11:22:15 2020 from 192.168.122.1
   [root@kvm3 ~]#
   

2. MariaDB 還是使用 mysql 命令登入資料庫，登入後命令提示列會顯示目前使用的資料庫名稱，還有指定使用資料庫，所以顯示 none。

   [root@kvm3 ~]# mysql -uroot -pqweqwe
   Welcome to the MariaDB monitor.  Commands end with ; or \g.
   Your MariaDB connection id is 20
   Server version: 10.3.11-MariaDB MariaDB Server
   
   Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
   MariaDB [(none)]>
   

3. 改用 mycli 登入 mariadb 資料庫，命令提示列更詳細了，包含用戶及主機名稱。

   [root@kvm3 ~]# mycli -S /var/lib/mysql/mysql.sock -uroot -pqweqwe
   mariadb 10.3.11-MariaDB
   mycli 1.20.1
   Chat: https://gitter.im/dbcli/mycli
   Mail: https://groups.google.com/forum/#!forum/mycli-users
   Home: http://mycli.net
   Thanks to the contributor - mrdeathless
   mariadb root@localhost:(none)>
   

4. 使用名為 mysql 的資料庫。

   mariadb root@localhost:(none)> use mysql;                                       
   You are now connected to database "mysql" as user "root"
   Time: 0.001s
   mariadb root@localhost:mysql>
   

5. 顯示資料庫 mysql 中的資料表，其中 user 儲存用戶的表格。

   mariadb root@localhost:mysql> show tables;                                      
   +---------------------------+
   | Tables_in_mysql           |
   +---------------------------+
   | column_stats              |
   | columns_priv              |
   | db                        |
   | event                     |
   | func                      |
   | general_log               |
   | gtid_slave_pos            |
   | help_category             |
   | help_keyword              |
   | help_relation             |
   | help_topic                |
   | host                      |
   | index_stats               |
   | innodb_index_stats        |
   | innodb_table_stats        |
   | plugin                    |
   | proc                      |
   | procs_priv                |
   | proxies_priv              |
   | roles_mapping             |
   | servers                   |
   | slow_log                  |
   | table_stats               |
   | tables_priv               |
   | time_zone                 |
   | time_zone_leap_second     |
   | time_zone_name            |
   | time_zone_transition      |
   | time_zone_transition_type |
   | transaction_registry      |
   | user                      |
   +---------------------------+
   

6. 列出資料表 user 的所有欄位，其中增加新用戶最常或必須設定的欄位為前 6 項。

   mariadb root@localhost:mysql> select column_name from information_schema.columns
                                  where table_name='user';                         
   +------------------------+
   | column_name            |
   +------------------------+
   | Host                   |
   | User                   |
   | Password               |
   | Select_priv            |
   | Insert_priv            |
   | Update_priv            |
   | Delete_priv            |
   | Create_priv            |
   | Drop_priv              |
   | Reload_priv            |
   | Shutdown_priv          |
   | Process_priv           |
   | File_priv              |
   | Grant_priv             |
   | References_priv        |
   | Index_priv             |
   | Alter_priv             |
   | Show_db_priv           |
   | Super_priv             |
   | Create_tmp_table_priv  |
   | Lock_tables_priv       |
   | Execute_priv           |
   | Repl_slave_priv        |
   | Repl_client_priv       |
   | Create_view_priv       |
   | Show_view_priv         |
   | Create_routine_priv    |
   | Alter_routine_priv     |
   | Create_user_priv       |
   | Event_priv             |
   | Trigger_priv           |
   | Create_tablespace_priv |
   | Delete_history_priv    |
   | ssl_type               |
   | ssl_cipher             |
   | x509_issuer            |
   | x509_subject           |
   | max_questions          |
   | max_updates            |
   | max_connections        |
   | max_user_connections   |
   | plugin                 |
   | authentication_string  |
   | password_expired       |
   | is_role                |
   | default_role           |
   | max_statement_time     |
   +------------------------+
   
   47 rows in set
   Time: 0.035s
   

7. 新增一筆用戶名稱為 dywang，密碼 123qwe，此用戶只能由本機登入，擁有 select, insert, update 權限，但報錯如下，原因為 mariadb 配置模式禁止透過 insert 修改用戶表 user。

   mariadb root@localhost:mysql> insert into user (host,user,password,select_priv,i
                                 nsert_priv,update_priv) values ('localhost','dywan
                                 g',password('123qwe'),'Y','Y','Y');               
   (1364, "Field 'ssl_cipher' doesn't have a default value")
   

8. 改用 grant，其中 *.* 表示所有資料庫的所有資料表，若要限定可以修改此項。

   mariadb root@localhost:(none)> grant select,insert,update on *.* to 'dywang'@'lo
                                  calhost' identified by '123qwe';                 
   Query OK, 0 rows affected
   Time: 0.003s
   

9. 更新用戶權限。

   mariadb root@localhost:(none)> flush privileges;                                
   Query OK, 0 rows affected
   Time: 0.006s