建立用戶

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