用户管理例子
显示所有的用户
连接进入控制台之后,首先输入
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)