Mysql数据用户管理

C:\Programing\Mysql\mysql-5.7.19-winx64\bin>mysql -hlocalhost -uroot -p
Enter password: ***********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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.

show databases;-- 查看数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wushaobo           |
+--------------------+
5 rows in set (0.02 sec)

mysql> use wushaobo;-- 选择数据库
Database changed
mysql> show tables;-- 查看表
+--------------------+
| Tables_in_wushaobo |
+--------------------+
| departments        |
| dept_emp           |
| dept_manager       |
| employees          |
| hobbies            |
| information        |
| items              |
| messages           |
| messasges          |
| personal           |
| personalities      |
| salaries           |
| scorings           |
| scroings           |
| t_student          |
| titles             |
| users              |
| wushao             |
+--------------------+
18 rows in set (0.01 sec)

select user from mysql.user;-- 查看现有用户
+---------------+
| user          |
+---------------+
| mysql.session |
| mysql.sys     |
| root          |
| test          |
+---------------+
4 rows in set (0.02 sec)

mysql> create user 'hi'@'localhost' identified by '1234';-- 创建一个用户名为hi，密码为1234的用户。其中localhost代表主机登录，如果允许远程登陆，则选择%
Query OK, 0 rows affected (0.03 sec)

mysql> select user from mysql.user;
+---------------+
| user          |
+---------------+
| hi            |
| mysql.session |
| mysql.sys     |
| root          |
| test          |
+---------------+
5 rows in set (0.00 sec)

mysql> create user 'hello'@'%' identified by '1234';-- 创建一个用户名为hello,密码为1234的用户，允许远程登陆。
Query OK, 0 rows affected (0.01 sec)

mysql> select user from mysql.user;-- 创建成功
+---------------+
| user          |
+---------------+
| hello         |
| hi            |
| mysql.session |
| mysql.sys     |
| root          |
| test          |
+---------------+
6 rows in set (0.00 sec)

mysql> drop user hi@localhost;-- 删除hi用户
Query OK, 0 rows affected (0.00 sec)

mysql> drop user hello@'%';-- 删除hello用户
Query OK, 0 rows affected (0.00 sec)

mysql> select user from mysql.user;-- 删除成功
+---------------+
| user          |
+---------------+
| mysql.session |
| mysql.sys     |
| root          |
| test          |
+---------------+
4 rows in set (0.00 sec)

select authentication_string from mysql.user where user='test'; -- 查看密码，密码是加密的
+-------------------------------------------+
| authentication_string                     |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
1 row in set (0.00 sec)

update mysql.user set authentication_string=password('1234567') where user='test';-- 修改密码，需要刷新
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1

flush privileges;-- 刷新
Query OK, 0 rows affected (0.02 sec)

select authentication_string from mysql.user where user='test'; -- 查看密码
+-------------------------------------------+
| authentication_string                     |
+-------------------------------------------+
| *6A7A490FB9DC8C33C2B025A91737077A7E9CC5E5 |
+-------------------------------------------+
1 row in set (0.00 sec)

grant all privileges on `wushaobo`.* to 'test'@localhost;-- 授予用户test通过本地对wushaobo数据库的全部权限，注意，数据库用的符号是`
Query OK, 0 rows affected (0.00 sec)

show grants for 'test'@localhost;-- 查看用户权限；
+------------------------------------------------------------+
| Grants for test@localhost                                  |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost'                   |
| GRANT ALL PRIVILEGES ON `wushaobo`.* TO 'test'@'localhost' |
+------------------------------------------------------------+
2 rows in set (0.00 sec)

revoke all privileges on `wushaobo`.* from 'test'@localhost;-- 收回用户test通过本地对wushaobo数据库的全部权限，注意，数据库用的符号是`
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'test'@localhost;
+------------------------------------------+
| Grants for test@localhost                |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost' |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

grant delete on `wushaobo`.`departments` to 'test'@localhost;-- 授予test用户本地对wushaobo数据库中表departments的删除权限
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'test'@localhost;
+--------------------------------------------------------------------+
| Grants for test@localhost                                          |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost'                           |
| GRANT SELECT, INSERT, UPDATE ON `wushaobo`.* TO 'test'@'localhost' |
| GRANT DELETE ON `wushaobo`.`departments` TO 'test'@'localhost'     |
+--------------------------------------------------------------------+
3 rows in set (0.00 sec)