linux12 -MYSQL数据库 --＞08权限管理

权限管理

一 mysql库下的授权表

1.linux和mysql用户对比

环境	linux系统	mysql数据库
用户作用	1.登陆系统 2.管理系统文件	1.登陆MySQL数据库 2.管理数据库对象
创建用户	1.useradd 2.adduser	1.create user test@’%’; 2.grant
用户密码	1.useradd cm -p 123 2.passwd cm	1.create user test@’%’ identified by ‘123’; 2.mysqladmin
删除用户	userdel	1.drop user test@’%’; 2.delete
修改用户	usermod	update

mysql库下的授权表及其放行权限的范围

#授权
      *.*->mysql.user
    grant all on *.* to 'egon'@'%' identified by '123';


    db.*->mysql.db
    grant all on db1.* to 'tom'@'%' identified by '123';
    revoke select on db1.* from 'tom'@'%';


    db1.blog->mysql.tables_priv
    grant all on db1.blog to 'lili'@'%' identified by '123';


    db1.blog->mysql.columns_priv, update权限依赖select权限
    grant select (id,sub_time),update (id) on db1.blog to 'xxx'@'%' identified by '123';
    
==============================================================================
#查看
#  1、mysql.user 

  针对所有数据、所有库下所有表、以及表下的所有字段
  
    mysql> use mysql;
    mysql> select *from mysql.user\G;

#  2、msyql.db 

  只针对某一数据库下的所有表，以及表下的所有字段
    mysql> use mysql;
    mysql> select *from mysql.db\G;
#  3、tables_priv 

  只针对某一张表、以及该表下的所有字段
  
  mysql> select * from mysql.tables_priv\G；

#  4、columns_priv 

  只针对某一个字段
  
  # 查看
  mysql> select * from mysql.columns_priv\G; #不常用

例如：如下图，权限表放行权限的范围

mysql.user：针对库db1，db2及其包含的所有
db：只针对库例如db1，及其db1包含的所有
tables_priv:只针对db1.table1，及其该表包含的所有
columns_prive:只针对db1.table1.column1，只放行该字段

二 权限相关操作

2.1 创建\查询\删除用户

创建用户语法

create user '用户名'@'主机' identified by '密码';

例

create user 'mm'@'192.168.1.%' identified by '123';
create user 'mm'@'%' identified by '123';

查询用户

mysql> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
|      | db01      |
| root | db01      |
|      | localhost |
| root | localhost |
+------+-----------+
6 rows in set (0.00 sec)

#上面是6个用户，在数据库中一个完整的用户是：'用户名'@'主机域'
'用户名'@'主机域'
主机域写法：
	localhost
	127.0.0.1
	10.%.%.%
	%
	10.0.0.5%（10.0.0.50-10.0.0.59）
	db01
	10.0.0.0/255.255.255.0
	10.0.0.0/24 #可以创建但是不生效
测试
	grant all on *.* to lhd@'10.0.0.0/255.255.255.0' identified by '123';
	grant all on *.* to qiudao@'10.0.0.0/24' identified by '123';

#创建的用户是数字，必须用引号一起来表示字符串
mysql> create user 123@'localhost';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '123@'localhost'' at line 1

mysql> create user '123'@'localhost';
Query OK, 0 rows affected (0.02 sec)

删除用户

mysql>  drop user tmo@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> drop user ''@db04;
Query OK, 0 rows affected (0.00 sec)

mysql> drop user 'root'@db04;
Query OK, 0 rows affected (0.00 sec)

mysql> drop user ''@localhost;
Query OK, 0 rows affected (0.00 sec)

修改密码

# 方式1
mysqladmin -uroot -p123 password '1'

# 方式2
update mysql.user set password=password('123') where user='root' and host='localhost';

# 方式3
set password=password('1');   修改当前用户的密码

# 方式4
grant all on *.* to 'root'@'localhost' identified by '123';

2.2 授权

MySQL 赋予用户权限命令的简单格式可概括为：

grant 权限 on 数据库对象 to 用户

注意：grant, revoke 用户权限后，该用户只有重新连接 MySQL 数据库，权限才能生效。

查看帮助

mysql> help grant

权限：对文件夹，对文件，对文件某一字段的权限

常用权限有：select, insert, update, delete
all可以代表除了grant之外的所有权限

MySQL 的 权限，分别可以作用在多个层次上

• 1、所有库的所有表

• 2、单库下的所有表（最常用的授权级别）

• 3、单表下的所有列

• 4、单列权限：企业业里称单列授权为 脱敏，即脱离敏感信息，涉及到敏感信息一定要脱敏

  例如
  授予vip账号对某一表下所有列的查询权限
  而授予非vip账号对某一表下的某一列的查询权限
  

• 5、针对存储过程的权限

• 6、针对函数的权限

举例如下

#（1）针对所有库的所有表：*.* 
grant select on *.* to 'mm1'@'localhost' identified by '123'; 
只在user表中可以查到mm1用户的select权限被设置为Y

#（2）针对某一数据库：db1.*
grant select on db1.* to 'mm2'@'%' identified by '123'; 
只在db表中可以查到mm2用户的select权限被设置为Y

#（3）针对某一个表：db1.t1
grant select on db1.t1 to 'mm3'@'%' identified by '123';  
只在tables_priv表中可以查到mm3用户的select权限

#（4）针对某一个字段：
mysql> select * from t3;
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | mm1 |   18 |
|    2 | mm2 |   19 |
|    3 | mm3 |   29 |
+------+-------+------+

grant select (id,name),update (age) on db1.t3 to 'mm4'@'localhost' identified by '123'; 
#可以在tables_priv和columns_priv中看到相应的权限
mysql> select * from tables_priv where user='mm4'\G
*************************** 1. row ***************************
       Host: localhost
         Db: db1
       User: mm4
 Table_name: t3
Column_name: name
  Timestamp: 0000-00-00 00:00:00
Column_priv: Select

#（5）作用在存储过程上：
use db1;

delimiter //
create procedure p1()
BEGIN
    select * from blog;
    INSERT into blog(name,sub_time) values("xxx",now());
END //
delimiter ;

show procedure status; -- 查看到db1下有一个名为p1的存储过程 

grant execute on procedure db1.p1 to 'dba'@'localhost';


#（6）作用在函数上：
delimiter //
create function f1(
    i1 int,
    i2 int)
returns int
BEGIN
    declare num int;
    set num = i1 + i2;
    return(num);
END //
delimiter ;

show function status; -- 查看到db1下有一个名为f1的函数

grant execute on function db1.f1 to 'dba'@'localhost';

测试：
[root@localhost ~]# mysql -udba -p
mysql> select db1.f1(1,2);

all可以代表除了grant之外的所有权限，可以用with带上grant,授权一个超级管理员

grant all on *.* to mm@'%' identified by '123' with grant option;
查看mysql.user表中可以查到mm用户的grant权限也被设置为Y

#加入这个就是超级权限，可以db1这个用户创建用户
grant select on testdb.* to db1@'localhost' with grant option 

2.3 查看授权

#1.授权:
grant all privileges on *.* to oldboy@'10.0.0.%' identified by '123';
grant    all       on    *.*             to  oldboy@'10.0.0.%' identified  by '123';
授权SQL  所有权限	  在	 所有库.所有表 上面	 给  用户名@主机域        密码    为    123；

#2.查看用户权限
查看当前用户（自己）权限：
mysql> show grants;

查看其他 MySQL 用户权限：
mysql> show grants for root@'%';
mysql> show grants for root@'localhost'

2.4 撤销权限。

revoke 跟 grant 的语法差不多，只需要把关键字 “to” 换成 “from” 即可：

mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| root          | %         |
| tom           | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)

mysql> revoke all on *.* from tom@'%'; #撤销
Query OK, 0 rows affected (0.00 sec)

revoke all on *.* from dba@localhost;
revoke select on db1.* from 'mm'@'%';

2.5 扩展授权

max_queries_per_hour：一个用户每小时可发出的查询数量
max_updates_per_hour：一个用户每小时可发出的更新数量
max_connections_per_hour：一个用户每小时可连接到服务器的次数
max_user_connections：允许同时连接数量

mysql> grant all on *.* to test@'%' identified by '123' with max_user_connections 1;

# 测试：
mysql -utest -p123 -h192.168.15.100

不要用-hlocalhost，它代表通过本地套接字链接

2.6 针对不同角色权限分配

2.6.1针对普通用户

grant 普通数据用户，查询、插入、更新、删除 数据库中所有表数据的权利。

grant select on testdb.* to common_user@'%'
grant insert on testdb.* to common_user@'%'
grant update on testdb.* to common_user@'%'
grant delete on testdb.* to common_user@'%'

或者，用一条 MySQL 命令来替代：

grant select, insert, update, delete on testdb.* to common_user@'%'

2.6.2针对开发人员

grant 数据库开发人员，创建表、索引、视图、存储过程、函数。。。等权限，具体操作如下

1、grant 创建、修改、删除 MySQL 数据表结构权限。

grant create on testdb.* to developer@'192.168.15.%';
grant alter  on testdb.* to developer@'192.168.15.%';
grant drop   on testdb.* to developer@'192.168.15.%';

2、grant 操作 MySQL 外键权限。

grant references on testdb.* to developer@'192.168.15.%';

3、grant 操作 MySQL 临时表权限。

grant create temporary tables on testdb.* to developer@'192.168.15.%';

4、grant 操作 MySQL 索引权限。

grant index on testdb.* to developer@'192.168.15.%';

5、rant 操作 MySQL 视图、查看视图源代码 权限。

grant create view on testdb.* to developer@'192.168.15.%';

grant show view on testdb.* to developer@'192.168.15.%';

6、grant 操作 MySQL 存储过程、函数 权限。

grant create routine on testdb.* to developer@'192.168.15.%'; -- now, can show procedure status

grant alter  routine on testdb.* to developer@'192.168.15.%'; -- now, you can drop a procedure

grant execute        on testdb.* to developer@'192.168.15.%';

2.6.3 针对普通DBA

grant 普通 DBA 管理某个 MySQL 数据库的权限。

grant all privileges on testdb.* to dba@'localhost'

其中，关键字 “privileges” 可以省略。

2.6.4 针对高级DBA

grant 高级 DBA 管理 MySQL 中所有数据库的权限。

grant all on *.* to dba@'localhost'

2.7 在企业中权限的设定

#开发人员说：请给我开一个用户

#1.首先进行沟通
	1.你需要对哪些库、表进行操作
	2.你从哪里连接过来
	3.用户名有没有要求
	4.密码要求
	5.你要使用多长时间
	6.发邮件

#2.一般给开发创建用户权限，建议不给delete权限
grant select,update,delete,insert on *.* to mm@'10.0.0.%'' identified by '123';

#3.注意：
开发图方便想要root用户，这玩意，谁给谁背锅，自己斟酌，可以考虑让部门老大来给

==============================================================================
# 权限总结
如果在不同级别都包含某个表的管理能力时，权限是相加关系。
但是我们不推荐在多级别定义重复权限。
最常用的权限设定方式是单库级别授权，即：wordpress.*
如果涉及到用户的敏感信息,需要做脱敏,单列级别授权 即:select(列名) 单库.单表

四 破解密码

4.1 linux平台

方法一：不推荐

[root@mm ~]# rm -rf /var/lib/mysql/mysql  # 所有授权信息全部丢失！！！
[root@mm ~]# systemctl restart mariadb
[root@mm ~]# mysql

方法二：启动时，跳过授权库

[root@mm ~]# vim /etc/my.cnf    #mysql主配置文件
[mysqld]
skip-grant-table

[root@mm ~]# systemctl restart mariadb

[root@mm ~]# mysql
MariaDB [(none)]> update mysql.user set password=password("123") where user="root" and host="localhost";
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> \q

[root@mm ~]# #打开/etc/my.cnf去掉skip-grant-table,然后重启
[root@mm ~]# systemctl restart mariadb
[root@mm ~]# mysql -u root -p123  # 以新密码登录

方法三：

# 1、先关闭数据库服务
方式一
systemctl stop mysql
方式二
mysqladmin -uroot -p123 -S /tmp/mysql.sock shutdown

# 2、跳过授权表启动mysql
mysqld_safe --skip-grant-tables --skip-networking &

ps：还需要跳过网络，否则在操作过程中很不安全

# 3、登录并修改密码
[root@mm ~]# mysql
MariaDB [(none)]> update mysql.user set password=password("123") where user="root" and host="localhost";
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> \q

# 4、重启服务
systemctl start mysql

# 5、用修改后的密码登录即可

4.2 windows平台下

5.7版本mysql
方式一

#1 关闭mysql
#2 在cmd中执行：mysqld --skip-grant-tables
#3 在cmd中执行：mysql
#4 执行如下sql：
update mysql.user set authentication_string=password('') where user = 'root';
flush privileges;

#5 tskill mysqld #或taskkill -f /PID 7832
#6 重新启动mysql

方式二

#1. 关闭mysql，可以用tskill mysqld将其杀死
#2. 在解压目录下，新建mysql配置文件my.ini
#3. my.ini内容,指定
[mysqld]
skip-grant-tables

#4.启动mysqld
#5.在cmd里直接输入mysql登录，然后操作
update mysql.user set authentication_string=password('') where user='root and host='localhost';

flush privileges;

#6.注释my.ini中的skip-grant-tables，然后启动myqsld，然后就可以以新密码登录了