next up previous contents
Next: mysqld 設定檔 Up: MySQL 管理 Previous: MySQL 管理   Contents

建立使用者

  1. 遠端登入 kvm8.deyu.wang 虛擬機。
    [root@dyw219 ~]# ssh root@kvm8.deyu.wang
    root@kvm8.deyu.wang's password: 
    Last login: Sat Dec  6 09:45:29 2014 from 192.168.122.1
    [root@kvm8 ~]#
    
  2. 登入 mysql。
    [root@kvm8 ~]# mysql -uroot -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 115
    Server version: 5.5.38 MySQL Community Server (GPL) by Remi
    
    Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
  3. 使用 mysql 資料庫。
    mysql> use mysql;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    
  4. 顯示資料庫 mysql 中的資料表,其中 user 儲存用戶的表格。
    mysql> show tables;
    +---------------------------+
    | Tables_in_mysql           |
    +---------------------------+
    | columns_priv              |
    | db                        |
    | event                     |
    | func                      |
    | general_log               |
    | help_category             |
    | help_keyword              |
    | help_relation             |
    | help_topic                |
    | host                      |
    | ndb_binlog_index          |
    | plugin                    |
    | proc                      |
    | procs_priv                |
    | proxies_priv              |
    | servers                   |
    | slow_log                  |
    | tables_priv               |
    | time_zone                 |
    | time_zone_leap_second     |
    | time_zone_name            |
    | time_zone_transition      |
    | time_zone_transition_type |
    | user                      |
    +---------------------------+
    24 rows in set (0.00 sec)
    
  5. 列出資料表 user 的所有欄位,其中增加新用戶最常或必須設定的欄位為前 6 項。
    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 |
    | ssl_type               |
    | ssl_cipher             |
    | x509_issuer            |
    | x509_subject           |
    | max_questions          |
    | max_updates            |
    | max_connections        |
    | max_user_connections   |
    | plugin                 |
    | authentication_string  |
    +------------------------+
    42 rows in set (0.00 sec)
    
  6. 新增一筆用戶名稱為 dywang,密碼 123qwe,此用戶只能由本機登入,擁有 select, insert, update 權限。
    mysql> INSERT INTO user (host,user,password,select_priv,insert_priv,update_priv) 
        ->       VALUES ('localhost', 'dywang', PASSWORD('123qwe'), 'Y', 'Y', 'Y');
    Query OK, 1 row affected, 3 warnings (0.04 sec)
    
  7. 更新用戶權限。
    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.11 sec)
    
  8. 列出剛剛新增的用戶的 host,user,password 欄位,其中密碼已經加密。
    mysql> select host,user,password from user where user='dywang';
    +-----------+--------+-------------------------------------------+
    | host      | user   | password                                  |
    +-----------+--------+-------------------------------------------+
    | localhost | dywang | *050376F3855A67F5E2C6514FD3130B31006C1276 |
    +-----------+--------+-------------------------------------------+
    1 row in set (0.00 sec)
    
  9. 退出資料庫。
    mysql> exit
    Bye
    
  10. 使用新用戶登入 mysql 資料庫。
    [root@kvm8 ~]# mysql -udywang -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 116
    Server version: 5.5.38 MySQL Community Server (GPL) by Remi
    
    Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql>
    
  11. 退出資料庫。
    mysql> exit
    Bye
    



2018-06-19