mysql权限管理

mysql权限管理

1、新增用户

mysql>CREATE USER ‘username‘@‘host‘ IDENTIFIED BY ‘password‘;
host:主机ip，%【任意ip】 localhost【本机】 192.168.31.22【指定ip】
IDENTIFIED BY:将密码用默认的加密方式进行加密后放入表中，不直接存放可以明码
默认的加密方式需要查一下user表中的plugin,如果客户端不支持，可以指定加密方式进行加密
mysql>CREATE USER 'username'@'host' IDENTIFIED MySqlSHA1 BY 'password';
新增后的用户默认是没有任何权限的，也就是useage,只能登陆罢了

更新用户密码：
>update user set password=PASSWORD(‘123456’) where User='root'
该表，是用户远程登陆
mysql -u root –p
mysql>use mysql;
mysql>update user set host = '%' where user = 'root';
mysql>select host, user from user;

2、查看和管理用户权限

1、查看当前用户的权限
MariaDB [(none)]> show grants;
2、查看某个用户的权限
MariaDB [(none)]> show grants for 'root'@'192.168.37.7';
+----------------------------------------------------------------------------------------------------------------+
| Grants for root@192.168.37.7                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'192.168.37.7' IDENTIFIED BY PASSWORD '*4696E4EA186115A19260A14736411E3AEC2247D6' |
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
3、回收权限
revoke delete on *.* from 'jack'@'localhost';
5、删除用户
mysql> select host,user,password from user;
mysql> drop user 'jack'@'localhost';
5、对账户重命名
mysql> rename user 'jack'@'%' to 'jim'@'%';
6、修改密码
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');
update user set PASSWORD = PASSWORD('1234abcd') where user = 'root';

你想root使用123456从任何主机连接到mysql服务器。
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

实例：
创建用户 wang@localhost，并赋予所有数据库上的所有表的 select 权限
CREATE USER ‘wang‘@‘localhost‘ IDENTIFIED BY ‘123456‘;
GRANT ALL PRIVILEGES ON *.* TO 'wang'@'localhost' IDENTIFIED BY '123456' WITH GRANT OPTION;
如果你想允许用户jack从ip为10.10.50.127的主机连接到mysql服务器，并使用654321作为密码

mysql>GRANT ALL PRIVILEGES ON *.* TO 'jack'@’10.10.50.127’ IDENTIFIED BY '654321' WITH GRANT OPTION;
mysql>FLUSH RIVILEGES

1、MySQL 'root'@'localhost'无法登录
 # mysql -u root -p 
提示”Access denied for user ‘root’@’localhost’ (using password: YES)”  
root用户不能登录的原因
1、你的root密码忘记了，就是密码错了，所有不能登录。
2、设置了root的访问权限，比如说ip写成了一个不存在的（这个是猜测的，因为我记得我就只改了访问权限，密码肯定是不会错的。
 步骤：
 1、停止mysql服务
 # /etc/init.d/mysql stop
 2、配置mysql配置文件
  27 [mysqld]                   # 增加口令并禁止远程登陆
 28 skip-grant-tables 
 29 skip-networking 
 2、查看root信息并更新root口令（本表root口令表为正常状态）
 
 MariaDB [mysql]> select user,password,host from user;
+------+-------------------------------------------+--------------------+
| user | password                                  | host               |
+------+-------------------------------------------+--------------------+
| root | *4696E4EA186115A19260A14736411E3AEC2247D6 | localhost          |
| root | *4696E4EA186115A19260A14736411E3AEC2247D6 | 127.0.0.1          |
| root | *4696E4EA186115A19260A14736411E3AEC2247D6 | ::1                |
| root | *4696E4EA186115A19260A14736411E3AEC2247D6 | ’192.168.37.7’     |
| root | *4696E4EA186115A19260A14736411E3AEC2247D6 | 192.168.37.7       |
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | %                  |
| wang | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | %                  |
+------+-------------------------------------------+--------------------+

> update mysql.user set password=password(‘wang’) where user=’root’;
再次查询确认是否修改成功
 [mysql]> select user,password,host from user;
 mysql>flush privileges; 
 mysql>quit
 
 如果使用新密码还是无法登录，提示跟上面一样。换一个非root账号登录，查看一下user表
 grant all privileges on . to ‘root’@’localhost’ identified by ‘wang’ with grant option; 
grant all privileges on . to ‘root’@’192.168.37.xxx’ identified by ‘wang’ with grant option;
实例如下图

Vim /etc/my.cnf
破解口令并禁止远程登录

更新root口令

创建用户并授权 grant

回收权并展示用户具有权限