文章目录

mysql用户权限管理

一、mysql权限介绍

mysql数据库管理软件将权限信息都存放于mysql库下，该库下有一系列授权表，权限信息都存放于这一系列表中

关于mysql的权限简单的理解就是mysql允许你做你全力以内的事情，不可以越界。
 比如:只允许你执行select操作，那么你就不能执行update操作,只允许你从某台机器上连接mysql，那么你就不能从除那台机器以外的其他机器连接mysql

1、mysql数据库管理用户的作用:

- 1、登陆MySQL数据库
- 2、管理库与表等数据库对象

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

1)#mysql.user 
 针对所有数据、所有库下所有表、以及表下的所有字段

2)#2、msyql.db 
 只针对某一数据库下的所有表，以及表下的所有字段

3)#tables_priv 
 只针对某一张表、以及该表下的所有字段

4)#columns_priv

【权限分布】

12@ mysql用户权限管理及用户权限分发_mariadb

3、案列：

【权限表放行权限的范围】

mysql.user：    #针对库db1，db2及其包含的所有

db：            #只针对库例如db1，及其db1包含的所有

tables_priv:    #只针对db1.table1，及其该表包含的所有

columns_prive:  #只针对db1.table1.column1，只放行该字段

12@ mysql用户权限管理及用户权限分发_mariadb_02

二、mysql权限的相关操作

1、创建，查询，删除用户

【创建用户语法】

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

【案列】

create user 'hzl'@'172.16.1.15' identified by '123';   #创键一个只能使用172.16.1.15的登录的用户
create user 'hzl'@'192.168.1.%' identified by '123';   #只能使用192.168.15.0这个网段登录
create user 'hzl'@'%' identified by '123';             #使用任意ip登录数据库

【查询用户】

mysql> select user,host from mysql.user;  #查看所有创建的用户
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
|      | db04      |
| root | db04      |
|      | localhost |
| root | localhost |

【删除用户】

mysql> drop user root@'::1';            #删除用户root
Query OK, 0 rows affected (0.01 sec)

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

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

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

2、修改密码（更新密码）

# 方式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';  #授权用户并设定密码

3、用户授权

【mysql授权的基本格式】：

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

     #注意：grant（授权）, revoke（撤销权利） 的用户权限后，该用户只有重新连接 MySQL 数据库，权限才能生效
mysql> help grant       #查看帮助信息

【常见的用户权限】

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

常用权限有：select, insert, update, delete


   **all** :     #代表除了grant之外的所有权限

4、MySQL 的权限的作用范围

1)#所有库的所有表     (权限较大，一般只有管理员所有的权限)


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


3)#单表下的所有列   (作用区间较小)


4)#单列权限：企业业里称单列授权为脱敏，即脱离敏感信息，涉及到敏感信息一定要脱敏
  #例如:
  授予vip账号对某一表下所有列的查询权限
  而授予非vip账号对某一表下的某一列的查询权限



5)#针对存储过程的权限


6)#针对函数的权限

【案列】

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


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


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


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

grant select (id,name),update (age) on db1.t3 to 'hzl4'@'localhost' identified by '123'; 


#可以在tables_priv和columns_priv中看到相应的权限
mysql> select * from tables_priv where user='hzl4'\G
*************************** 1. row ***************************
       Host: localhost
         Db: db1
       User: hzl4
 Table_name: t3
    Grantor: root@localhost
  Timestamp: 0000-00-00 00:00:00
 Table_priv:
Column_priv: Select,Update
row in set (0.00 sec)

mysql> select * from columns_priv where user='hzl4'\G
*************************** 1. row ***************************
       Host: localhost
         Db: db1
       User: hzl4
 Table_name: t3
Column_name: id
  Timestamp: 0000-00-00 00:00:00
Column_priv: Select
*************************** 2. row ***************************
       Host: localhost
         Db: db1
       User: hzl4
 Table_name: t3
Column_name: name
  Timestamp: 0000-00-00 00:00:00
Column_priv: Select
*************************** 3. row ***************************
       Host: localhost
         Db: db1
       User: hzl4
 Table_name: t3
Column_name: age
  Timestamp: 0000-00-00 00:00:00
Column_priv: Update
rows in set (0.00 sec)




（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 hzl@'%' identified by '123' with grant option;

   #查看mysql.user表中可以查到hzl用户的grant权限也被设置为Y(拥有超级管理员权限)

5、查看授权

1)#授权:
mysql> grant all on *.* to root@'%' identified by '123';

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


3)#查看其他 MySQL 用户权限：
mysql> show grants for root@'%';   #查看来自root用户的所有权限

6、撤销权限（删除权限）

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

grant  all on *.* to   dba@localhost;   #授权dba用户权限
revoke all on *.* from dba@localhost;   #删除dba的权限
revoke select on db1.* from 'hzl'@'%';  #删除hzl用户对db1库下表的所有权限

7、扩展授权（了解即可）

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;     #授权test用户同时只能连接一个用户



# 测试：
mysql -utest -p123 -h192.168.15.51    #使用test用户连接数据库，不要用-hlocalhost，它代表通过本地套接字链接

三、不同角色权限分配

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、针对开发人员

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

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

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


 

2)#grant 操作 MySQL 外键权限。

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


 

3)#grant 操作 MySQL 临时表权限。

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


 

4)#grant 操作 MySQL 索引权限。

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


 

5)#grant 操作 MySQL 视图、查看视图源代码 权限。

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

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




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

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

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

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

3、 针对普通DBA

#格式：
grant 普通 DBA 管理某个 MySQL 数据库的权限

#使用:
grant all privileges on testdb.* to dba@'localhost'


#其中，授权时关键字 “privileges” 是可以省略的

4、 针对高级DBA

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

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

【在企业中权限的设定】

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


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


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



3）#注意：不能直接给root权限

四 mysql破解密码

1、 linux平台

方法一（不推荐）

1）rm -rf /var/lib/mysql/mysql    #删除所有数据，所有授权信息全部丢失！！！

2）systemctl restart mariadb      #重新启动数据库

3）mysql                          #直接登录即可

方法二(重新启动时跳过授权库)

1）vim /etc/my.cnf    #mysql主配置文件

[mysqld]
skip-grant-table     #配置文件加入此参数可调过授权表认证




2）systemctl restart mariadb    #重新启动数据库即可
mysql                           #登录数据库
MariaDB [(none)]> update mysql.user set   password=password("123") where user="root" and host="localhost";    #重新设定新密码
MariaDB [(none)]> flush privileges;  #刷新权限
MariaDB [(none)]> \q



3)打开/etc/my.cnf去掉skip-grant-table参数,然后重启
systemctl restart mariadb   #重新启动数据库
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）#登录并修改密码
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）用修改后的密码登录即可
mysql -u root -p123     #使用新密码进行登录数据库

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    #杀掉数据库进程
netstat -aon|findstr "3306"    #查询端口检查



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



7）#启动myqsld        #使用新密码登录数据库