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
回收权并展示用户具有权限
