文章目录
权限管理
一 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,然后就可以以新密码登录了