mysql用户的管理
一、查看当前的连接帐号信息
1.1、查看当前数据库的以连接的帐号信息
使用命令：show processlist
MySQL [(none)]> show processlist;
+--------+-------------+---------------------+--------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+-------------+---------------------+--------+---------+------+----------+------------------+
| 232091 | zabbix | 172.17.207.88:558 | zabbix | Sleep | 20 | | NULL |
1.2、查看当前使用的是什么帐号登录
使用命令select user()命令进行查看
MySQL [(none)]> select user();
+--------------------+
| user() |
+--------------------+
| root@172.17.37.88 |
+--------------------+
1 row in set (0.00 sec)
MySQL [(none)]>
二、创建用户
2.1、新用户的创建
使用creat user命令创建用户并创建密码
列子：create user 'zhang'@'localhost' identified by 'zhang';
创建zhang用户可以使用任意地址访问并设置密码为zhang
MySQL [(none)]> create user 'zhang'@'%' identified by 'zhang';
Query OK, 0 rows affected (0.01 sec)
MySQL [(none)]>
设置完成后查看是否创建成功
MySQL [(none)]> select user,host from mysql.user;
+-------------+----------------+
| user | host |
+-------------+----------------+
| jumpserver | % |
| root | % |
| wordpress | % |
| zabbix | 39.106.3.162 |
| % | localhost |
| zhang | localhost |
+-------------+----------------+
9 rows in set (0.01 sec)
MySQL [(none)]>
使用新创建的用户zhang进行登录并查看数据库
[root@iZ2zegql6fupnhn8aei0bgZ ~]# mysql -uzhang -h120.26.32.14 -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1204
Server version: 5.6.35 Source distribution
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
查看数据库
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.02 sec)
MySQL [(none)]>
三、删除数据库帐号
使用drop user 命令删除用户
MySQL [(none)]> drop user 'zhang'@'localhost';
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]>
四、重命名用户
4.1、使用rename user命令进行修改重命名用户
MySQL [(none)]> rename user 'zhang'@'%c' to 'zhang'@'%' ;
Query OK, 0 rows affected (0.02 sec)
MySQL [(none)]> select user,host from mysql.user;
+-------------+-------------------+
| user | host |
+-------------+-------------------+
| root | % |
| user_name | % |
| xuchangming | % |
| zhang | % |
| root | 127.0.0.1 |
| root | ::1 |
| | instance-jvfp1b6r |
| root | instance-jvfp1b6r |
| root | localhost |
| xuchangming | localhost |
+-------------+-------------------+
10 rows in set (0.01 sec)
MySQL [(none)]>
五、授权帐号
5.1、使用grant 命令进行授权帐号
命令格式为：
grant 权限 privileges on 库.表 to ‘帐号’@‘ip’ [identified by ‘登录密码’]；
库表权限说明：
on *.* ：管理员权限，任何数据库都可以操作
on db_name.* ：指定对某个库进行操作，只有某个库的权限
on db_name.tables_name：指定某一个库中的一个表有操作权限
on db_name.routine_name：指定某个库的存储过程或者存储函数
5.2、使用命令 show grants命令查看权限
SQL [(none)]> show grants;
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@% |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*0FC3121124C80F34B383F5FCA33F0D68B6AFA1C0' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
MySQL [(none)]>
5.3、列子
5.3.1、授权所有权限【管理员权限】给某一个帐号
创建boos用户并设置登录密码为boss，对所有库和表授权所有操作并允许所有地址连接
MySQL [(none)]> grant all privileges on *.* to 'boos'@'%' identified by 'boss';
Query OK, 0 rows affected (0.02 sec)
MySQL [(none)]>
登录查看
[root@iZ2zegql6fupnhn8aei0bgZ ~]# mysql -uboos -h120.76.32.14 -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1217
Server version: 5.6.35 Source distribution
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| 测试 |
| ceshi |
| employees |
| mysql |
| performance_schema |
| test |
+--------------------+
7 rows in set (0.01 sec)
MySQL [(none)]>
MySQL [(none)]> select user();
+---------------------+
| user() |
+---------------------+
| boos@120.76.32.14 |
+---------------------+
1 row in set (0.02 sec)
MySQL [(none)]>
5.3.2、授权所有权限给某一帐号只针对某一个数据库
创建帐号zhang并添加密码zhang，修改权限对 ceshi库的所有操作
MySQL [(none)]> grant all privileges on ceshi.* to 'zhang'@'%' identified by 'zhang' ;
Query OK, 0 rows affected (0.02 sec)
MySQL [(none)]>
[root@iZ2zegql6fupnhn8aei0bgZ ~]# mysql -uzhang -h120.76.32.14 -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1458
Server version: 5.6.35 Source distribution
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ceshi |
| test |
+--------------------+
3 rows in set (0.02 sec)
MySQL [(none)]> show grants;
+-------------------------------------------------------------------+
| Grants for zhang@% |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhang'@'%' IDENTIFIED BY PASSWORD <secret> |
| GRANT ALL PRIVILEGES ON `ceshi`.* TO 'zhang'@'%' |
+-------------------------------------------------------------------+
2 rows in set (0.01 sec)
MySQL [(none)]> use test;
Database changed
MySQL [test]> show tables;
Empty set (0.02 sec)
5.3.3、授权某一个权限给某一个帐号，只针对有一个数据库进行操作
创建帐号zhang并运行所有ip地址连接并创建密码zhang，设置权限为只对ceshi数据库进行select查询
MySQL [(none)]> grant select on ceshi.* to 'zhang'@'%' identified by 'zhang';
Query OK, 0 rows affected (0.02 sec)
MySQL [(none)]>
MySQL [(none)]> show grants;
+-------------------------------------------------------------------+
| Grants for zhang@% |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhang'@'%' IDENTIFIED BY PASSWORD <secret> |
| GRANT SELECT ON `ceshi`.* TO 'zhang'@'%' |
+-------------------------------------------------------------------+
2 rows in set (0.02 sec)
MySQL [(none)]>
使用create 创建表进行测试，是否有权限创建，如下显示则没有创建成功，表示没有权限
MySQL [ceshi]> create table t1;
ERROR 1142 (42000): CREATE command denied to user 'zhang'@'120.76.32.14' for table 't1'
MySQL [ceshi]>
在zhang帐号中添加create创建权限
MySQL [(none)]> grant create on ceshi.* to 'zhang'@'%' identified by 'zhang';
Query OK, 0 rows affected (0.02 sec)
查看此帐号权限
MySQL [(none)]> show grants for 'zhang'@'%';
+------------------------------------------------------------------------------------------------------+
| Grants for zhang@% |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhang'@'%' IDENTIFIED BY PASSWORD '*5D83A6402DF44A7D8EC2B8861B19F8A2F4F3EA2F' |
| GRANT SELECT, CREATE ON `ceshi`.* TO 'zhang'@'%' |
+------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)
MySQL [(none)]>
5.3.4、授权某一列
MySQL [ceshi]> grant select(table_name,engine) on test.t to 'zhang'@'localhost';
六、撤销权限
格式命令：revoke 权限 on 库.表 from 'user'@'host';
查看zhang用户目前的权限列表
MySQL [ceshi]> show grants for 'zhang'@'%';
+------------------------------------------------------------------------------------------------------+
| Grants for zhang@% |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhang'@'%' IDENTIFIED BY PASSWORD '*5D83A6402DF44A7D8EC2B8861B19F8A2F4F3EA2F' |
| GRANT SELECT, CREATE ON `ceshi`.* TO 'zhang'@'%' |
+------------------------------------------------------------------------------------------------------+
2 rows in set (0.02 sec)
MySQL [ceshi]>
把zhang用户的create的权限删掉，使其不能使用create创建
MySQL [ceshi]> revoke create on ceshi.* from 'zhang'@'%';
Query OK, 0 rows affected (0.02 sec)
MySQL [ceshi]> show grants for 'zhang'@'%';
+------------------------------------------------------------------------------------------------------+
| Grants for zhang@% |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhang'@'%' IDENTIFIED BY PASSWORD '*5D83A6402DF44A7D8EC2B8861B19F8A2F4F3EA2F' |
| GRANT SELECT ON `ceshi`.* TO 'zhang'@'%' |
+------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)
MySQL [ceshi]>
七、修改帐号的密码
格式命令：set password for 'user'@'host' = password('NEW-password');
MySQL [ceshi]> set password for 'zhang'@'%' = password('boss');
Query OK, 0 rows affected (0.02 sec)
八、如何对一个帐号进行资源限制
资源可以包括为：
resource_option: {
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
每一个小时的链接次数
每一个帐号每一个小时的查询多少次
每一个帐号每一个小时更新多少次
每一个帐号每一个小时并发链接多少次
8.1、每一个小时不能超过2次查询
MySQL [ceshi]> grant all privileges on *.* to 'boss'@'%' with MAX_QUERIES_PER_HOUR 2;
Query OK, 0 rows affected (0.02 sec)
MySQL [ceshi]>
九、找回密码
[root@iZ2ze2rrr9fg73mstmet9tZ ~]# /usr/local/mysql/bin/mysqld_safe --skip-grant-tables &
[root@iZ2ze2rrr9fg73mstmet9tZ ~]#mysql
清空root密码
MySQL [ceshi]> update user set password='' where user='root' and host='localhost'