2.12Mysql 用户管理
相关数据库和表
元数据数据库：mysql
系统授权表：db, host, user,columns_priv, tables_priv, procs_priv, proxies_priv
用户帐号:
'USERNAME'@'HOST'
@'HOST': 主机名： user1@'web1.test.org'
IP地址或Network
通配符： % _
示例：test@172.16.%.%
test@'192.168.1.%'
test@'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
#服务器创建新用户haha
MariaDB [mysql]> create user haha@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.000 sec)
MariaDB [mysql]> select user,host,password from user;
+------+-----------------------+-------------------------------------------+
| user | host | password |
+------+-----------------------+-------------------------------------------+
| root | localhost | *366428608497F2CF536AE0D5612599507BDB5AFB |
| root | localhost.localdomain | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | localhost.localdomain | |
| haha | 10.0.0.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-----------------------+-------------------------------------------+
7 rows in set (0.000 sec)
#客户端远程连接
root@ubuntu1804:~# mysql -uhaha -p'123456' -h'10.0.0.8'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.5.5-10.3.28-MariaDB MariaDB Server
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases; #只能看到两个库，权限受限
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.00 sec)
用户重命名：RENAME USER
RENAME USER old_user_name TO new_user_name;
删除用户：
DROP USER 'USERNAME'@'HOST'
#删除默认的空用户
MariaDB [mysql]> drop user ''@'localhost';
Query OK, 0 rows affected (0.000 sec)
MariaDB [mysql]> select user,host,password from user;
+------+-----------------------+-------------------------------------------+
| user | host | password |
+------+-----------------------+-------------------------------------------+
| root | localhost | *366428608497F2CF536AE0D5612599507BDB5AFB |
| root | localhost.localdomain | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost.localdomain | |
| haha | 10.0.0.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-----------------------+-------------------------------------------+
6 rows in set (0.000 sec)
修改密码：
注意：
新版mysql中用户密码可以保存在mysql.user表的authentication_string字段中
如果mysql.user表的authentication_string和password字段都保存密码，authentication_string优先生效
#方法一：SET PASSWORD FOR 'user'@'host' = PASSWORD('password'); #MySQL8.0 版本不支持此方法,因为password函数被取消
MariaDB [mysql]> set password for 'haha'@'10.0.0.%' = password('abcd');
Query OK, 0 rows affected (0.001 sec)
MariaDB [mysql]> select user,host,password from user;
+------+-----------------------+-------------------------------------------+
| user | host | password |
+------+-----------------------+-------------------------------------------+
| root | localhost | *366428608497F2CF536AE0D5612599507BDB5AFB |
| root | localhost.localdomain | |
| root | 127.0.0.1 | |
| root | ::1 | |
| haha | 10.0.0.% | *A154C52565E9E7F94BFC08A1FE702624ED8EFFDA |
+------+-----------------------+-------------------------------------------+
5 rows in set (0.000 sec)
root@ubuntu1804:~# mysql -uhaha -p'123456' -h'10.0.0.8'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'haha'@'10.0.0.18' (using password: YES)
root@ubuntu1804:~# mysql -uhaha -p'abcd' -h'10.0.0.8'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.5-10.3.28-MariaDB MariaDB Server
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
#方法二：ALTER USER test@'%' IDENTIFIED BY 'password'; #通用改密码方法, 用户可以也可通过此方式修改自已的密码,MySQL8 版本修改密码
MariaDB [mysql]> alter user haha@'10.0.0.%' identified by 'ubuntu';
Query OK, 0 rows affected (0.000 sec)
MariaDB [mysql]> select user,host,password from user;
+------+-----------------------+-------------------------------------------+
| user | host | password |
+------+-----------------------+-------------------------------------------+
| root | localhost | *366428608497F2CF536AE0D5612599507BDB5AFB |
| root | localhost.localdomain | |
| root | 127.0.0.1 | |
| root | ::1 | |
| haha | 10.0.0.% | *3CD53EE62F8F7439157DF288B55772A2CA36E60C |
+------+-----------------------+-------------------------------------------+
5 rows in set (0.000 sec)
#客户端通过haha帐号连接服务器尝试修改密码直接报错，因为权限不够
mysql> alter user haha@'10.0.0.%' identified by 'ubuntu';
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
#方法三
UPDATE mysql.user SET password=PASSWORD('password') WHERE clause; #此方式MySQL8.0不支持,因为password函数被取消
MariaDB [mysql]> update mysql.user set password=password('test') where user='haha';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0
#此方法需要执行下面指令才能生效
MariaDB [mysql]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.000 sec)
MariaDB [mysql]> select user,host,password from user;
+------+-----------------------+-------------------------------------------+
| user | host | password |
+------+-----------------------+-------------------------------------------+
| root | localhost | *366428608497F2CF536AE0D5612599507BDB5AFB |
| root | localhost.localdomain | |
| root | 127.0.0.1 | |
| root | ::1 | |
| haha | 10.0.0.% | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
+------+-----------------------+-------------------------------------------+
5 rows in set (0.000 sec)
忘记管理员密码的解决方案：（最好别重置，因为很多程序写入了数据库的账号密码修改之后数据库我们是可以管理了但是很多程序无法正常调用此数据库会出大问题）
启动mysqld进程时，为其使用如下选项：
--skip-grant-tables #登录无需密码
--skip-networking #只能指定的主机进行登录
[root@centos8 ~]#vim /etc/my.cnf
[mysqld]
skip-grant-tables
skip-networking
[root@centos8 ~]#systemctl restart mysqld|mariadb
[root@centos8 ~]#mysql
#mariadb 旧版和MySQL5.6版之前
#方法1
MariaDB [(none)]> update mysql.user set password=password('test') where user='root';
Query OK, 4 rows affected (0.001 sec)
Rows matched: 4 Changed: 4 Warnings: 0
#mariadb 新版
MariaDB [(none)]> update mysql.user set authentication_string=password('ubuntu') where user='root';
Query OK, 4 rows affected (0.000 sec)
Rows matched: 4 Changed: 4 Warnings: 0
[root@centos8 ~]#vim /etc/my.cnf
[mysqld]
#skip-grant-tables
#skip-networking
[root@centos8 ~]#systemctl restart mysqld|mariadb
[root@centos8 ~]#mysql -uroot -pubuntu
#方法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 -pubuntu
[root@centos8 ~]#vim /etc/my.cnf
[mysqld]
skip-grant-tables
skip-networking #MySQL8.0不需要
[root@centos8 ~]#systemctl restart mysqld
# MySQL5.7和8.0 破解root密码
#方法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 'ubuntu';
mysql> set password for root@'localhost'='ubuntu';
[root@centos8 ~]#vim /etc/my.cnf
[mysqld]
#skip-grant-tables
#skip-networking
[root@centos8 ~]#systemctl restart mysqld
[root@centos8 ~]#mysql -uroot -pubuntu
#再来一个高危操作，如果是测试环境，或者准备删库跑路的话可以使用一下方式
#此方法适用于包安装方式的MySQL或Mariadb
[root@centos8 ~]#systemctl stop mysqld
[root@centos8 ~]#rm -rf /var/lib/mysql/*
[root@centos8 ~]#systemctl start mysqld
2.13权限管理和DCL语句
2.13.1权限类别
权限类别：
管理类
程序类
数据库级别
表级别
字段级别
管理类：
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
2.13.2授权
授权：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
#服务器上为haha用户授权
MariaDB [mysql]> grant all on hellodb.* to haha@'10.0.0.%';
Query OK, 0 rows affected (0.001 sec)
#客户端上用haha连接查看数据库信息已经可以查看到hellodb库了
root@ubuntu1804:~# mysql -uhaha -p'test' -h'10.0.0.8'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.5.5-10.3.28-MariaDB MariaDB Server
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| test |
+--------------------+
3 rows in set (0.00 sec)
2.13.3取消权限
取消授权：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
#服务器取消用户haha的授权
MariaDB [mysql]> revoke all on hellodb.* from haha@'10.0.0.%';
Query OK, 0 rows affected (0.000 sec)
#客户端haha登录已经看不到hellodb库的信息了
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.00 sec)
2.13.4查看指定用户获得的授权
Help SHOW GRANTS
SHOW GRANTS FOR 'user'@'host';
SHOW GRANTS FOR CURRENT_USER[()];
注意：
MariaDB服务进程启动时会读取mysql库中所有授权表至内存
(1) GRANT或REVOKE等执行权限操作会保存于系统表中，MariaDB的服务进程通常会自动重读授权表，
使之生效
(2) 对于不能够或不能及时重读授权表的命令，可手动让MariaDB的服务进程重读授权表：
mysql> FLUSH PRIVILEGES;
2.14Mysql图形化管理工具
在MySQL数据库中创建用户并授权后，可以使用相关图形化工具进行远程的管理。
常见的图形化管理工具：
Navicat
SQLyog
需要先创建用户并授权
GRANT ALL PRIVILEGES ON *.* TO 'haha'@'10.0.0.%' IDENTIFIED BY 'sunxiang' WITH GRANT OPTION;
2.14.2 SQLyog 工具
