一、MySQL的连接方式
1.密码登陆
默认使用root用户
mysql -p
适用于本地socket连接
mysql -S /tmp/mysql.sock -uroot -pxxx
适用任何远程方式连接，前提是在mysql.user表中已经有这个记录了
mysql -uxxx -pxxx -hxxx
要注意一个localhost，127.0.0.1，空用户的问题
2.免密码登陆
方法1：my.cnf中增加[client] 的标签
对定义不同的客户端
[mysql] 这个是属于/usr/local/mysql/bin/mysql二进制那个程序使用的
user="root"
password="密码"
[mysqladmin] 这个是属于/usr/local/mysql/bin/mysqladmin二进制那个程序使用的
user="root"
password="密码"
每个不同的客户端使用不同的密码，而当使用了[client]标签则可以统一使用1个密码
[client]
user="root"
password="密码"
方法2：使用login-path
1.根据用户名和密码生成login-path的别名
[root@nazeebo ~]# mysql_config_editor set -G login-path-test -S /tmp/mysql.sock -uroot -p
Enter password:
[root@nazeebo ~]#
2.打印输出所有的login-path
[root@nazeebo ~]# mysql_config_editor print --all
[login-path-test]
user = root
password = *****
socket = /tmp/mysql.sock
3.使用login-path登陆，这个时候就不需要输入用户名和密码了
[root@nazeebo ~]# mysql --login-path=login-path-test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 759
Server version: 5.7.22-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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.
mysql>
4.login-path的存放位置在~/.mylogin.cnf
[root@nazeebo ~]# ls ~/.mylogin.cnf
/root/.mylogin.cnf
[root@nazeebo ~]# more ~/.mylogin.cnf
[root@nazeebo ~]#
方法3：类似方案1使用[client]，在home目录下创建~/.my.cnf
cat ~/.my.cnf
[client]
user="root"
password="密码"
二、MySQL权限详解
1.MySQL的权限的定义
一个完整的用户标识 = 用户名+IP 假设一台主机的ip地址为192.168.0.2
sure@localhost
sure@127.0.0.1
sure@192.168.0.2
以上三个用户标识是不同的三个用户标识，虽然它们所指的是同一个主机
2.权限管理
2.1 系统表权限信息
相关数据字典
mysql.user //全局所有库的权限
mysql.db //指定库的权限
mysql.tbale_priv //指定表的权限
mysql.column_priv //指定列的权限
desc table_name //查看表结构的定义
2.2 常用权限
SQL语句：select、insert、update、delete、index
存储过程：create routine、alter routine、execute、trigger
管理权限：super、reload、show database、shutdown
查看所有的权限，链接： https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html
2.3 显示当前用户的权限
show grants;
show grants for current_user;
show grants for current_user();
2.4 配置可选资源
2.4.1 MAX_QUERIES_PER_HOUR 用来限制用户每小时运行的查询数量
mysql> grant select on *.* to 'sure'@'%' identified by '123456' with max_queries_per_hour 5;
Query OK, 0 rows affected (0.00 sec)
mysql> select user();
+----------------+
| user() |
+----------------+
| sure@127.0.0.1 |
+----------------+
1 row in set (0.00 sec)
......
......
当执行到第6次的时候，就报错：
mysql> select user();
ERROR 1226 (42000): User 'sure' has exceeded the 'max_questions' resource (current value: 5)
mysql>
2.4.2 MAX_UPDATES_PER_HOUR 用来限制用户每小时的修改数据库数据的数量。
mysql> grant select on *.* to 'sure'@'%' with max_updates_per_hour 5;
Query OK, 0 rows affected (0.00 sec)
2.4.3 MAX_CONNECTIONS_PER_HOUR 用来限制用户每小时打开新连接的数量。
mysql> grant select on *.* to 'sure'@'%' with max_connections_per_hour 5;
Query OK, 0 rows affected (0.00 sec)
2.4.4 MAX_USER_CONNECTIONS 用来限制有多少用户连接MYSQL服务器
mysql> grant select on *.* to 'sure'@'%' with max_user_connections 2;
Query OK, 0 rows affected (0.00 sec)
2.4.5 计数清0
想将所有账户当前的记数重设为0，可以执行FLUSH USER_RESOURCES语句。还可以通过重载授权表来重设记数。
mysql> flush user_resources;
3.权限在MySQL数据库中的验证流程:
- 用户名和ip是否被允许
- 查看mysql.user表
- 查看mysql.db表
- 查看mysql.table_priv表
- 查看mysql.column_priv表
权限的相关建议：
- 删除所有用户名为空的账户
- 不允许密码为空的永不存在
- 管理员账号可以给所有库权限
- 开发应用账户只需要给相应库的权限
4.日常操作示例
4.1 创建用户及授权
//创建一个叫用户标识 'nazeebo'@'127.0.0.1'，密码为123456
mysql> create user 'nazeebo'@'127.0.0.1' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
//授权 'nazeebo'@'127.0.0.1'用户 mysql库下面表的所有权限，如果是所有库则用*.*
mysql> grant all on mysql.* to 'nazeebo'@'127.0.0.1' ;
Query OK, 0 rows affected (0.01 sec)
//grant会先搜索用户'nazeebo'@'127.0.0.1' 是否存在，不存在则先创建，如果不带identified则密码为空；之后再授权
mysql> grant all on mysql.* to 'nazeebo'@'127.0.0.1' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)
//会发现有个warning，查看后意思是建议用create+grant的方式来代替grant创建用户
mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
//创建一个用户'lowa'以及所有的192.168.0.x的网段，密码为123456，它可以对所有库的表都有操作权限，并且它有给其他用户授权的权限
mysql> grant all privileges on *.* to 'lowa'@'192.168.0.%' identified by '123456' with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)
4.2 查看一个用户的权限
mysql> show grants for 'lowa'@'192.168.0.%';
+-----------------------------------------------------------------------+
| Grants for lowa@192.168.0.% |
+-----------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'lowa'@'192.168.0.%' WITH GRANT OPTION |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
4.3 回收权限
revoke关键字只回收权限，并不删除用户 语法用grant， to改为from
mysql> revoke ALL PRIVILEGES ON *.* from 'lowa'@'192.168.0.%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'lowa'@'192.168.0.%';
+--------------------------------------------------------------+
| Grants for lowa@192.168.0.% |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lowa'@'192.168.0.%' WITH GRANT OPTION |
+--------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
4.MySQL权限信息
mysql> select * from mysql.user where user='sure'\G
*************************** 1. row ***************************
Host: %
User: sure
Select_priv: Y //
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 5
max_updates: 5
max_connections: 5
max_user_connections: 2
plugin: mysql_native_password
authentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
password_expired: N
password_last_changed: 2018-06-13 16:47:49
password_lifetime: NULL
account_locked: N
1 row in set (0.01 sec)
三、MySQL中的rbac模型
1.角色的定义
role和Oracle数据库的role类似，批量的对用户进行管理，同一个role下的所有用户的权限一样。 MySQL 5.7版本通过mysql.proxies_prvi来实现
2.模拟角色操作
1.首先创建一个用户select_role_group，当成role
mysql> create user 'select_role_group'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
2.创建2个用户 yeye和nainai
mysql> create user 'yeye'@'%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> create user 'nainai'@'%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
3.将select_role_group的权限映射给yeye和nainai
mysql> grant proxy on 'select_role_group'@'%' to 'yeye'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> grant proxy on 'select_role_group'@'%' to 'nainai'@'%';
Query OK, 0 rows affected (0.00 sec)
4.授予select_role_group查询的权限
mysql> grant select on *.* to 'select_role_group'@'%';
Query OK, 0 rows affected (0.00 sec)
5.查看select_role_group、yeye、nainai的权限
mysql> show grants for 'select_role_group'@'%';
+------------------------------------------------+
| Grants for select_role_group@% |
+------------------------------------------------+
| GRANT SELECT ON *.* TO 'select_role_group'@'%' |
+------------------------------------------------+
1 row in set (0.00 sec)
mysql> show grants for 'yeye'@'%';
+------------------------------------------------------+
| Grants for yeye@% |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO 'yeye'@'%' |
| GRANT PROXY ON 'select_role_group'@'%' TO 'yeye'@'%' |
+------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show grants for 'nainai'@'%';
+--------------------------------------------------------+
| Grants for nainai@% |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'nainai'@'%' |
| GRANT PROXY ON 'select_role_group'@'%' TO 'nainai'@'%' |
+--------------------------------------------------------+
2 rows in set (0.00 sec)
6.查看相应的数据字典的信息
mysql> select * from mysql.proxies_priv;
+-----------+--------+--------------+-------------------+------------+----------------+---------------------+
| Host | User | Proxied_host | Proxied_user | With_grant | Grantor | Timestamp |
+-----------+--------+--------------+-------------------+------------+----------------+---------------------+
| localhost | root | | | 1 | | 2018-06-13 14:24:39 |
| nazeebo | root | | | 1 | | 2018-06-13 14:24:39 |
| % | yeye | % | select_role_group | 0 | root@localhost | 0000-00-00 00:00:00 |
| % | nainai | % | select_role_group | 0 | root@localhost | 0000-00-00 00:00:00 |
+-----------+--------+--------------+-------------------+------------+----------------+---------------------+
4 rows in set (0.00 sec)
mysql> exit
Bye
7.以yeye为例登陆数据库进行查询测试
[root@nazeebo ~]# mysql -uyeye -p -h127.0.0.1
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 5.7.22-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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.
mysql> select user();
+----------------+
| user() |
+----------------+
| yeye@127.0.0.1 |
+----------------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
在这个地方可以看到可以有mysql库的查询权限
mysql> select user,host from mysql.user;
+-------------------+-------------+
| user | host |
+-------------------+-------------+
| nainai | % |
| select_role_group | % |
| sure | % |
| system | % |
| yeye | % |
| nazeebo | 127.0.0.1 |
| root | 127.0.0.1 |
| lowa | 192.168.0.% |
| root | ::1 |
| | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
| | nazeebo |
| root | nazeebo |
+-------------------+-------------+
15 rows in set (0.00 sec)
在这可以看到因为只授权了select的权限，所以create database的时候会报错
mysql> create database dandan;
ERROR 1044 (42000): Access denied for user 'select_role_group'@'%' to database 'dandan'
mysql>