mysql 用户管理

登录
[root@mysql-1 ~]# mysql -uroot -p
查看有哪些数据库
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
±-------------------+
4 rows in set (0.01 sec)
进入musql 库
mysql> use mysql;
查看user 表有哪些 用户
mysql> select user,host from user;
±--------------±----------+
| user | host |
±--------------±----------+
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
±--------------±----------+
3 rows in set (0.00 sec)

创建本地登录用户
mysql> create user old@‘localhost’;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user
-> ;
±--------------±----------+
| user | host |
±--------------±----------+
| mysql.session | localhost |
| mysql.sys | localhost |
| old | localhost |
| root | localhost |
±--------------±----------+
4 rows in set (0.00 sec)

限制白名单IP 创建的用户
mysql> create user new8@‘172.16.1.%’ identified by ‘123456’;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select user,host,authentication_string from user;
±--------------±-----------±------------------------------------------+
| user | host | authentication_string |
±--------------±-----------±------------------------------------------+
| root | localhost | |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| old | localhost | |
| new8 | 172.16.1.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
±--------------±-----------±------------------------------------------+
5 rows in set (0.01 sec)

创建用的几种方式
1 old@‘loccalhost’ 本地登录mysql
2 old@‘172.16.1.10’ 通过固定的IP 远程登录
3 old@‘172.16.1.%’ 通过16.1 网段的IP登录
4 old@‘172.16.1.5% 1.50到59 的IP都能登录
5 old@’%’ 所有的IP都能登录

修改用户密码
mysql> alter user new8@‘172.16.1.%’ identified by ‘123’;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host ,authentication_string from user;
±--------------±-----------±------------------------------------------+
| user | host | authentication_string |
±--------------±-----------±------------------------------------------+
| root | localhost | |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| old | localhost | |
| new8 | 172.16.1.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
±--------------±-----------±------------------------------------------+
5 rows in set (0.00 sec)

修改用户登录的方式
mysql> update user set host = ‘%’ where user = ‘new8’;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select user,host ,authentication_string from user;
±--------------±----------±------------------------------------------+
| user | host | authentication_string |
±--------------±----------±------------------------------------------+
| root | localhost | |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| old | localhost | |
| new8 | % | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
±--------------±----------±------------------------------------------+
5 rows in set (0.00 sec)

授权
mysql> grant all on . to old@‘localhost’ identified by ‘123456’ ;
Query OK, 0 rows affected, 1 warning (0.00 sec)
all 是所有权限
. 第一* 库名，第二* 是表名
查看授权
mysql> show grants for old@‘localhost’;
±-------------------------------------------------+
| Grants for old@localhost |
±-------------------------------------------------+
| GRANT ALL PRIVILEGES ON . TO ‘old’@‘localhost’ |
±-------------------------------------------------+
1 row in set (0.00 sec)

grant 授权时可以同时修改 host 的值
mysql> select user,host from user;
±--------------±----------+
| user | host |
±--------------±----------+
| new8 | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| old | localhost |
| root | localhost |
±--------------±----------+
mysql> grant create,select,insert on app.* to old@’%’ identified by ‘123456’;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show grants for old@’%’
-> ;
±-----------------------------------------------------+
| Grants for old@% |
±-----------------------------------------------------+
| GRANT USAGE ON . TO ‘old’@’%’ |
| GRANT SELECT, INSERT, CREATE ON app.* TO ‘old’@’%’ |
±-----------------------------------------------------+
2 rows in set (0.00 sec)

回收权限
mysql> revoke create on app.* from old@’%’ ;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for old@’%’;
±---------------------------------------------+
| Grants for old@% |
±---------------------------------------------+
| GRANT USAGE ON . TO ‘old’@’%’ |
| GRANT SELECT, INSERT ON app.* TO ‘old’@’%’ |
±---------------------------------------------+
2 rows in set (0.00 sec)

mysql>