建立遠端存取帳號

1. 用戶 root 登入 mariadb。

   [root@kvm3 ~]# mycli -uroot -p123123
   Connecting to socket /var/lib/mysql/mysql.sock, owned by user mysql
   MariaDB 10.11.11
   mycli 1.39.1
   Home: http://mycli.net
   Bug tracker: https://github.com/dbcli/mycli/issues
   Thanks to the contributor - jweiland.net
   MariaDB root@(none):(none)>
   

2. 增加一個只能從 192.168.122.1 存取的帳號 hosttest。

   MariaDB root@(none):(none)> grant select,insert,update on *.* to 'hosttest'@192.1
                            -> 68.122.1 identified by '123qwe';
   Query OK, 0 rows affected
   Time: 0.006s
   

3. 更新權限。

   MariaDB root@(none):(none)> flush privileges;
   Query OK, 0 rows affected
   Time: 0.004s
   

4. 查看 user，出現 hosttest。

   MariaDB root@(none):(none)> select user,host from mysql.user;
   +-------------+---------------+
   | User        | Host          |
   +-------------+---------------+
   | hosttest    | 192.168.122.1 |
   | mariadb.sys | localhost     |
   | mysql       | localhost     |
   | root        | localhost     |
   +-------------+---------------+
   
   5 rows in set
   Time: 0.041s
   

5. 查看用戶 hosttest 權限，只有 select, insert, update 權限，而且 host 是 192.168.122.1，也就是只能從 192.168.122.1 這台主機登入。

   MariaDB root@(none):(none)> select * from mysql.user where user='hosttest'\G
   ***************************[ 1. row ]***************************
   Host                   | 192.168.122.1
   User                   | hosttest
   Password               | *050376F3855A67F5E2C6514FD3130B31006C1276
   Select_priv            | Y
   Insert_priv            | Y
   Update_priv            | Y
   Delete_priv            | N
   Create_priv            | N
   Drop_priv              | N
   Reload_priv            | N
   Shutdown_priv          | N
   Process_priv           | N
   File_priv              | N
   Grant_priv             | N
   References_priv        | N
   Index_priv             | N
   Alter_priv             | N
   Show_db_priv           | N
   Super_priv             | N
   Create_tmp_table_priv  | N
   Lock_tables_priv       | N
   Execute_priv           | N
   Repl_slave_priv        | N
   Repl_client_priv       | N
   Create_view_priv       | N
   Show_view_priv         | N
   Create_routine_priv    | N
   Alter_routine_priv     | N
   Create_user_priv       | N
   Event_priv             | N
   Trigger_priv           | N
   Create_tablespace_priv | N
   Delete_history_priv    | N
   ssl_type               |
   ssl_cipher             |
   x509_issuer            |
   x509_subject           |
   max_questions          | 0
   max_updates            | 0
   max_connections        | 0
   max_user_connections   | 0
   plugin                 | mysql_native_password
   authentication_string  | *050376F3855A67F5E2C6514FD3130B31006C1276
   password_expired       | N
   is_role                | N
   default_role           |
   max_statement_time     | 0.000000
   
   1 row in set
   Time: 0.006s
   

6. root 登出 mariadb。

   MariaDB root@(none):mysql> quit;
   Goodbye!