MySQL 用户管理

相关数据库和表

元数据数据库：mysql
系统授权表：db, host, user,columns_priv, tables_priv, procs_priv, proxies_priv

用户帐号:

'USERNAME'@'HOST'
@'HOST': 主机名： user1@'web1.longxuan.vip'
IP地址或Network
通配符： % _
示例：wang@172.31.%.%
user2@'192.168.1.%'
longxuan@'10.0.0.0/255.255.0.0'

创建用户：CREATE USER

CREATE USER 'USERNAME'@'HOST' [IDENTIFIED BY 'password']；
#示例:
create user test@'10.0.0.0/255.255.255.0' identified by '123456';
create user test2@'10.0.0.%' identified by 123456;

新建用户的默认权限：USAGE

用户重命名：RENAME USER

RENAME USER old_user_name TO new_user_name;

删除用户：

DROP USER 'USERNAME'@'HOST'

范例：删除默认的空用户

DROP USER ''@'localhost';

修改密码

注意：

新版mysql中用户密码可以保存在mysql.user表的authentication_string字段中
如果mysql.user表的authentication_string和password字段都保存密码，authentication_string
优先生效

#方法1,用户可以也可通过此方式修改自已的密码
SET PASSWORD FOR 'user'@'host' = PASSWORD('password'); #MySQL8.0 版本不支持此方法,
因为password函数被取消
set password for root@'localhost'='123456' ; #MySQL8.0版本支持此方法,此方式直接将密码
123456加密后存放在mysql.user表的authentication_string字段
#方法2
ALTER USER test@'%' IDENTIFIED BY 'centos'; #通用改密码方法, 用户可以也可通过此方式修
改自已的密码,MySQL8 版本修改密码
#方法3 此方式MySQL8.0不支持,因为password函数被取消
UPDATE mysql.user SET password=PASSWORD('password') WHERE clause;
#mariadb 10.3
update mysql.user set authentication_string=password('ubuntu') where
user='mage';
#此方法需要执行下面指令才能生效：
FLUSH PRIVILEGES;

忘记管理员密码的解决办法：

1. 启动mysqld进程时，为其使用如下选项：

--skip-grant-tables
--skip-networking

2. 使用UPDATE命令修改管理员密码
3. 关闭mysqld进程，移除上述两个选项，重启mysqld

范例：Mariadb 和MySQL5.6版之前破解root密码

[root@centos8 ~]#vim /etc/my.cnf
[mysqld]
skip-grant-tables
skip-networking
[root@centos8 ~]#systemctl restart mysqld|mariadb
[root@centos8 ~]#mysql
#方法1
#mariadb 旧版和MySQL5.6版之前
MariaDB [(none)]> update mysql.user set password=password('123456') where
user='root';
#mariadb 新版
MariaDB [(none)]> update mysql.user set authentication_string=password('123456')
where user='root';
#方法2
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> alter user root@'localhost' identified by 'ubuntu';
[root@centos8 ~]#vim /etc/my.cnf
[mysqld]
#skip-grant-tables
#skip-networking
[root@centos8 ~]#systemctl restart mysqld|mariadb
[root@centos8 ~]#mysql -uroot -p123456

范例: MySQL5.7和8.0 破解root密码

[root@centos8 ~]#vim /etc/my.cnf
[mysqld]
skip-grant-tables
skip-networking #MySQL8.0不需要
[root@centos8 ~]#systemctl restart mysqld
#方法1
mysql> update mysql.user set authentication_string='' where user='root' and
host='localhost';
#方法2
mysql> flush privileges;
#再执行下面任意一个命令
mysql> alter user root@'localhost' identified by '123456';
mysql> set password for root@'localhost'='123456';
[root@centos8 ~]#vim /etc/my.cnf
[mysqld]
#skip-grant-tables
#skip-networking
[root@centos8 ~]#systemctl restart mysqld
[root@centos8 ~]#mysql -uroot -p123456

范例: 删库跑路之清空root密码方法

#此方法适用于包安装方式的MySQL或Mariadb
[root@centos8 ~]#systemctl stop mysqld
[root@centos8 ~]#rm -rf /var/lib/mysql/*
[root@centos8 ~]#systemctl start mysqld

权限管理和DCL语句

权限类别

管理类

程序类

数据库级别

表级别

字段级别

管理类：

CREATE USER
FILE
SUPER
SHOW DATABASES
RELOAD
SHUTDOWN
REPLICATION SLAVE
REPLICATION CLIENT
LOCK TABLES
PROCESS
CREATE TEMPORARY TABLES

程序类：针对 FUNCTION、PROCEDURE、TRIGGER

CREATE
ALTER
DROP
EXCUTE

库和表级别：针对 DATABASE、TABLE

ALTER
CREATE
CREATE VIEW
DROP INDEX
SHOW VIEW
WITH GRANT OPTION：能将自己获得的权限转赠给其他用户

数据操作

SELECT
INSERT
DELETE
UPDATE

字段级别

SELECT(col1,col2,...)
UPDATE(col1,col2,...)
INSERT(col1,col2,...)

所有权限：ALL PRIVILEGES 或 ALL

授权：GRANT

GRANT priv_type [(column_list)],... ON [object_type] priv_level TO 'user'@'host'
[IDENTIFIED BY 'password'] [WITH GRANT OPTION];
priv_type: ALL [PRIVILEGES]
object_type:TABLE | FUNCTION | PROCEDURE
priv_level: *(所有库) |*.* | db_name.* | db_name.tbl_name | tbl_name(当前库的
表) | db_name.routine_name(指定库的函数,存储过程,触发器)
with_option: GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count

参考：https://dev.mysql.com/doc/refman/5.7/en/grant.html

范例：

GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';
GRANT ALL ON wordpress.* TO wordpress@'10.0.0.%' ;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.0.0.%' WITH GRANT OPTION;
#创建用户和授权同时执行的方式在MySQL8.0取消了
GRANT ALL ON wordpress.* TO wordpress@'192.168.1.%' IDENTIFIED BY 'longxuan';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.%' IDENTIFIED BY 'longxuan'
WITH GRANT OPTION;

取消授权：REVOKE

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON
[object_type] priv_level FROM user [, user] ...

参考：https://dev.mysql.com/doc/refman/5.7/en/revoke.html

范例：

REVOKE DELETE ON *.* FROM 'testuser'@'172.31.0.%';

查看指定用户获得的授权

Help SHOW GRANTS
SHOW GRANTS FOR 'user'@'host';
SHOW GRANTS FOR CURRENT_USER[()];

注意：

MariaDB服务进程启动时会读取mysql库中所有授权表至内存
(1) GRANT或REVOKE等执行权限操作会保存于系统表中，MariaDB的服务进程通常会自动重读授权表，
使之生效
(2) 对于不能够或不能及时重读授权表的命令，可手动让MariaDB的服务进程重读授权表：
mysql> FLUSH PRIVILEGES;