MySQL学习笔记-用户管理例子

用户管理例子

---

显示所有的用户
连接进入控制台之后，首先输入

show databases;

结果显示所有的数据库名称

+--------------------+
| Database           |
+--------------------+
| information_schema |
| computer           |
| ee615b             |
| mysql              |
| nvigative          |
| performance_schema |
| php_test           |
| sakila             |
| stock              |
| test               |
| testdb             |
| train_from         |
| weixinqun          |
| world              |
+--------------------+
14 rows in set (0.00 sec)

用户数据库在mysql数据库中，用来选择数据库，键入

use mysql;

结果显示

Database changed

显示mysql数据库中下面所有的表，键入

show tables;

结果显示

+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
28 rows in set (0.00 sec)

查看user表结构，键入

show columns from user;

结果显示

+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N       |       |
| File_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N       |       |
| References_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher             | blob                              | NO   |     | NULL    |       |
| x509_issuer            | blob                              | NO   |     | NULL    |       |
| x509_subject           | blob                              | NO   |     | NULL    |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0       |       |
| plugin                 | char(64)                          | YES  |     |         |       |
| authentication_string  | text                              | YES  |     | NULL    |       |
| password_expired       | enum('N','Y')                     | NO   |     | N       |       |
+------------------------+-----------------------------------+------+-----+---------+-------+
43 rows in set (0.01 sec)

查看所有用户列表，键入

select Host,User from user;

用户列表显示为

+-------+-----------+
| User  | Host      |
+-------+-----------+
| bjtu  | %         |
| utest | %         |
| root  | 127.0.0.1 |
| root  | ::1       |
| root  | localhost |
+-------+-----------+
5 rows in set (0.00 sec)

在用户列表中
参数说明: %代表任何客户机都可以连接，localhost代表只可以本机连接。127.0.0.1是IPV4的本机地址。::1是IPV6的本机地址？（不是很清楚::1）。

创建用户

在控制台键入

create user 'utest'@'%' identified by 'test’;

‘utest’是用户名，’%’是任何IP都可以访问的意思，’test’是密码
结果显示

Query OK, 0 rows affected (0.00 sec)

再次查询一次用户列表如下所示：

+-------+-----------+
| User  | Host      |
+-------+-----------+
| bjtu  | %         |
| utest | %         |
| root  | 127.0.0.1 |
| root  | ::1       |
| root  | localhost |
+-------+-----------+
5 rows in set (0.00 sec)

添加完用户之后需要给用户增加权限，给utest添加mysql数据库增删改查的权限

首先查看utest用户的权限

show grants for utest;

显示用户权限为

+------------------------------------------------------------------------------------------------------+
| Grants for utest@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'utest'@'%' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

**

增加权限

**
给用户utest增加mysql数据库查询，更新，插入权限

grant select,update,delete,insert on mysql.* to utest;

mysql是数据库名称，执行结果为：

Query OK, 0 rows affected (0.00 sec)

再次查看用户utest的权限为：

+------------------------------------------------------------------------------------------------------+
| Grants for utest@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'utest'@'%' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.* TO 'utest'@'%'                                     |
+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)