MySQL用户权限管理

用户管理

在MySQL中用户的定义：
'用户名'@'主机域'

mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| root          | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+

主机域：
10.0.0.5% （ 10.0.0.50-10.0.0.59 ~ 10.0.0.5 ）
localhost
10.0.0.%
10.0.%.% （10.0.0-255.1-255）
10.%.%.%
%
10.0.0.0/255.255.255.0

mysql> grant all on *.* to test1@'10.0.0.0/24' identified by '123';
mysql> grant all on *.* to test2@'10.0.0.0/255.255.255.0' identified by '123';

vim /etc/profile.d/xxxx.sh
HISTCONTROL=ignorespace

创建用户

create user zls2@'localhost' identified by '123';

grant all on *.* to zls2@'localhost';
grant all on *.* to zls3@'localhost' identified by '123';

insert into mysql.user values ('localhost','root',PASSWORD('123')...

删除用户

drop user zls3@'localhost';

delete from mysql.user where user='root' and host='10.0.0.%';

改密码

mysqladmin -u'用户' -p'旧密码' password '新密码'

mysqladmin -uzls1 -p123 password '111'

MySQL5.7以上版本专用
alter user zls1@'localhost' identified by '123';

set password=PASSWORD('222');

##### 修改表结构
MySQL5.6
mysql> update mysql.user set password=PASSWORD('333') where user='root' and host='localhost';
mysql> flush privileges; // 危险，全局SQL语句


MySQL5.7
mysql> update mysql.user set authentication_string=PASSWORD('333') where user='root' and host='localhost';
mysql> flush privileges; // 危险，全局SQL语句

grant all on *.* to root@'localhost' identified by '1111';

查询用户

select user,host from mysql.user;
select * from mysql.user\G
show grants for root@'localhost';

权限管理

INSERT,SELECT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE,
REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK
TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW,
CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE

grant all privileges on *.* to oldboy@’10.0.0.%’ identified by‘123’ ;
            权限       作用         归属                    密码

## 给开发开权限（原始人版）
INSERT,SELECT, UPDATE, DELETE
## 给开发开权限（规范版本）
INSERT,SELECT, UPDATE

*.* [当前MySQL实例中所有库下的所有表]
wordpress.* [当前MySQL实例中wordpress库中所有表（单库级别）] （常用）
wordpress.user [当前MySQL实例中wordpress库中的user表（单表级别）]

# 脱敏：脱离敏感信息
mysql> grant insert,select(user,host), update, delete on mysql.user to
dev1@'localhost' identified by '123';

忘记root密码 或者 误删除root用户 找回免密

# 1.挂维护页
# 2.停掉数据库
[root@db01 ~]# /etc/init.d/mysqld stop
# 3.跳过授权表，跳过网络启动数据库
[root@db01 ~]# mysqld_safe --skip_grant_tables --skip-networking
# 4.连接数据库
mysql

### 创建库和数据
insert into mysql.user(host,user,password,ssl_cipher,x509_issuer,x509_subject)values('localhost','root',PASSWORD('123'),'','','');

insert into mysql.user values ('localhost','root',PASSWORD('123'),
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'',
'',
'',
MySQL用户权限管理
用户管理
在MySQL中用户的定义：
'用户名'@'主机域'
'',
0,
0,
0,
0,
'mysql_native_password',
'',
'N');

#### 方法二
## 正确答案 方法二：
mysql> flush privileges;
mysql> grant all on *.* to root@'localhost' identified by '123' with grant option;


# 5.重启数据库
/etc/init.d/mysql