next up previous contents
Next: 查詢測試新用戶 Up: 用戶管理 Previous: 用戶管理   Contents   DYWANG_HOME

建立用戶

  1. 遠端登入 kvm8.deyu.wang 虛擬機。
    [root@dyw219 ~]# ssh root@kvm8.deyu.wang
    root@kvm8.deyu.wang's password: 
    Last login: Sat Jan 25 11:22:15 2020 from 192.168.122.1
    [root@kvm8 ~]#
    
  2. MariaDB 還是使用 mysql 命令登入資料庫,登入後命令提示列會顯示目前使用的資料庫名稱,還有指定使用資料庫,所以顯示 none。
    [root@kvm8 ~]# 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@kvm8 ~]# 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
    



De-Yu Wang 2020-04-07