时间：2021-07-01 10:21:17 帮助过：9人阅读
================================================================================
概述：
================================================================================
1.权限类别
★用户账号：user@host
user：账户名称；
host：此账户可通过哪些客户端主机请求创建连接线程；
%：任意长度的任意字符；
_：任意单个字符；
★MySQL权限类别：
※数据类权限：
库级别：把某个数据库的所有权限或某些权限授权给指定用户（数据库级别的）；
表级别：把一个数据库中的某个表或有限的几个表授权给指定用户；
字段级别：把一个表上指定的字段授权给指定用户；
※管理类：如改变服务器变量的值等；
※程序类：能否调用或执行函数，程序代码等程序；
☉管理类：
CREATE USER； //创建用户
RELOAD； //可以使mysql关闭日志文件，滚动日志文件并打开新文件
LOCK TABLES；
REPLICATION CLIENT， REPLICATION SLAVE；
SHUTDOWN； //关闭mysql服务器进程的权限
FILE； //从文件中加载数据的权限
SHOW DATABASES；
PROCESS； //查看进程
SUPER //不受控制的任何管理功能等权限
☉程序类：
FUNCTION（存储函数），PROCEDURE（存储过程），TRIGGER（触发器）
操作：CREATE，ALTER，DROP，EXECUTE（执行）
☉库和表级别：
CREATE，ALTER，DROP
INDEX
CREATE VIEW
SHOW VIEW
GRANT：授权
OPTION：能够把自己获得的权限生成一个副本转赠给其它用户；
☉数据操作权限：
※表：
INSERT/DELETE/UPDATE/SELECT
※字段：
SELECT(col1,col2,...)
UPDATE(col1,col2,...)
INSERT(col1,col2,...)
☉所有权限：
ALL
ALL PRIVILEGES
★元数据数据库（数据字典）：mysql
授权：
·db, host, user
·tables_priv, column_priv, procs_priv, proxies_priv
MariaDB [(none)]> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [mysql]> SHOW TABLES; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | # 字段级别的权限 | db | # 库级别的权限 | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | host | # 主机连接限制 | ndb_binlog_index | | plugin | | proc | | procs_priv | # 程序级别的权限 | proxies_priv | # 代理权限 | servers | | slow_log | | tables_priv | # 表级别的权限 | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | # 用户账号 +---------------------------+ 24 rows in set (0.00 sec)
2.MySQL用户管理：
★用户账号：user@host
user：账户名称；
host：此账户可通过哪些客户端主机请求创建连接线程;（IP,主机名，NETWORK）
%：任意长度的任意字符；
_：任意单个字符；
注意：
基于主机名授权和IP地址授权是两个不同的账号，如果基于主机名授权时会反解主机名到IP地址，一般不建议，并且要跳过主机名解析
★skip_name_resolve={ON|OFF}
是否跳过主机名解析
★创建用户：
CREATE USER ‘user‘@‘host‘ [IDENTIFIED BY [PASSWORD] ‘password‘] [,‘user‘@‘host‘ [IDENTIFIED BY [PASSWORD] ‘password‘]...]
★重命名：RENAME USER
RENAME USER old_user TO new_user[, old_user TO new_user] ...
★删除用户：
DROP USER ‘user‘@‘host‘ [, ‘user‘@‘host‘] ...
★修改用户密码
SET PASSWORD [FOR ‘user‘@‘host‘] = PASSWORD(‘cleartext password‘);
UPDATE mysql.user SET Password=PASSWORD(‘cleartext password‘) WHERE User=‘USERNAME‘ AND Host=‘HOST‘;
mysqladmin -uUSERNAME -hHOST -p password ‘NEW_PASS‘
※ FLUSH PRIVILEGES //通知mysql进程重读授权表
命令演示：
1.可以使用SHOW PROCESSLIST 查看当前线程列表，如下：
MariaDB [hellodb]> SHOW PROCESSLIST; +----+---------+-----------------+---------+---------+------+-------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+---------+-----------------+---------+---------+------+-------+------------------+----------+ | 3 | root | localhost | hellodb | Query | 0 | NULL | SHOW PROCESSLIST | 0.000 | | 4 | rsyslog | 127.0.0.1:38101 | Syslog | Sleep | 123 | | NULL | 0.000 | +----+---------+-----------------+---------+---------+------+-------+------------------+----------+ 2 rows in set (0.00 sec)
2.创建用户，修改密码，修改用户名，并查看刚创建用户的权限
# 创建用户账号及密码 MariaDB [(none)]> CREATE USER ‘tao‘@‘192.168.1.%‘ IDENTIFIED BY ‘134296‘; Query OK, 0 rows affected (0.00 sec)
# 修改用户账户密码，并重读授权表 MariaDB [(none)]> SET PASSWORD FOR ‘tao‘@‘192.168.1.%‘ = PASSWORD(‘123456‘); Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec)
# 修改用户名 MariaDB [(none)]> RENAME USER ‘tao‘@‘192.168.1.%‘ TO ‘xiu‘@‘192.168.1.%‘; Query OK, 0 rows affected (0.02 sec)
# 并查看刚创建用户的权限，发现为usage MariaDB [(none)]> SHOW GRANTS FOR ‘xiu‘@‘192.168.1.%‘; +--------------------------------------------------------------------------------------------------------------+ | Grants for xiu@192.168.1.% | +--------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO ‘xiu‘@‘192.168.1.%‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘ | +--------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
3.登录创建的用户账户，创建数据库，可以发现权限不被允许，可见新创建的用户几乎是没有任何权限的；要想有权限就需要给用户授予相关的管理、程序、数据类的权限等
[root@centos7 ~]# mysql -uxiu -h192.168.1.107 -p123456 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 6 Server version: 5.5.44-MariaDB MariaDB Server Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | test | +--------------------+ 2 rows in set (0.05 sec) MariaDB [test]> CREATE DATABASE mydb; ERROR 1044 (42000): Access denied for user ‘xiu‘@‘192.168.1.%‘ to database ‘mydb‘
4.要想有权限就需要给用户授予相关的管理、程序、数据类的权限等，授予xiu用户相关的权限如下：
MariaDB [(none)]> GRANT ALL ON mydb.* TO ‘xiu‘@‘192.168.1.%‘; # 授予所有的权限 Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SHOW GRANTS FOR ‘xiu‘@‘192.168.1.%‘; +--------------------------------------------------------------------------------------------------------------+ | Grants for xiu@192.168.1.% | +--------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO ‘xiu‘@‘192.168.1.%‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘ | | GRANT ALL PRIVILEGES ON `mydb`.* TO ‘xiu‘@‘192.168.1.%‘ | +--------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) MariaDB [(none)]> REVOKE UPDATE ON mydb.* FROM ‘xiu‘@‘192.168.1.%‘; # 回收UPDATE权限 Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SHOW GRANTS FOR ‘xiu‘@‘192.168.1.%‘; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for xiu@192.168.1.% | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO ‘xiu‘@‘192.168.1.%‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘ | | GRANT SELECT, INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mydb`.* TO ‘xiu‘@‘192.168.1.%‘ | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
3.忘记管理员密码的解决办法：
★解决步骤如下：
(1)启动mysqld进程时，使用--skip-grant-tables和--skip-networking选项；
CentOS 7：mariadb.service
CentOS 6：/etc/init.d/mysqld
(2)通过UPDATE命令修改管理员密码；
(3)以正常方式启动mysqld进程；
演示：
1.以CentOS 7 为例，首先查看mysql数据库user表中有关用户的账户名和密码如下：
MariaDB [(none)]> SELECT user,host,password FROM mysql.user; +------------+-------------+-------------------------------------------+ | user | host | password | +------------+-------------+-------------------------------------------+ | root | localhost | *41EE0F8759D5340036B009143E1727DB5787A448 | | root | centos7 | *41EE0F8759D5340036B009143E1727DB5787A448 | | root | 127.0.0.1 | *41EE0F8759D5340036B009143E1727DB5787A448 | | root | ::1 | *41EE0F8759D5340036B009143E1727DB5787A448 | | ultraxuser | 127.0.0.1 | *41EE0F8759D5340036B009143E1727DB5787A448 | | ultraxuser | localhost | *41EE0F8759D5340036B009143E1727DB5787A448 | | rsyslog | 127.0.0.1 | *41EE0F8759D5340036B009143E1727DB5787A448 | | rsyslog | local | *41EE0F8759D5340036B009143E1727DB5787A448 | | zbxuser | 10.1.%.% | *24E65C3D3577DA6C2A596788CEAA02923A74B75D | | zbxuser | 127.0.0.1 | *24E65C3D3577DA6C2A596788CEAA02923A74B75D | | xiu | 192.168.1.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +------------+-------------+-------------------------------------------+
2.假如现在我忘记了root管理员用户的密码，执行过程如下：
1）首先关闭mysql服务，修改启动mariadb.service的文件，添加 --skip-grant-tables和--skip-networking选项，如下：
[root@centos7 ~]# systemctl stop mariadb.service # 停止mariadb服务 [root@centos7 ~]# vim /usr/lib/systemd/system/mariadb.service # 修改mariadb启动文件 ExecStart=/usr/bin/mysqld_safe --basedir=/usr --skip-grant-tables --skip-networking [root@centos7 ~]# systemctl daemon-reload # 要想生效需要重载
2）再次启动mariadb服务，然后使用mysql直接连接，连接成功后使用UPDATE修改密码
[root@centos7 ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 5.5.44-MariaDB MariaDB Server Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. MariaDB [(none)]> UPDATE mysql.user SET Password=PASSWORD(‘taoxiu‘) WHERE User=‘root‘; # 修改密码 Query OK, 4 rows affected (0.00 sec) Rows matched: 4 Changed: 4 Warnings: 0 MariaDB [(none)]> exit Bye
3）然后再次关闭mariadb服务，把mariadb启动文件中添加的--skip-grant-tables和--skip-networking选项删除即可
[root@centos7 ~]# systemctl stop mariadb.service [root@centos7 ~]# vim /usr/lib/systemd/system/mariadb.service ExecStart=/usr/bin/mysqld_safe --basedir=/usr # 把添加的选项删除即可 [root@centos7 ~]# systemctl daemon-reload [root@centos7 ~]# systemctl start mariadb.service [root@centos7 ~]# mysql # 再使用mysql直接登录，发现已经登录不上了 ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost‘ (using password: NO) [root@centos7 ~]# mysql -uroot -ptaoxiu # 使用修改的密码成功登录 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3 Server version: 5.5.44-MariaDB MariaDB Server Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. MariaDB [(none)]>
4.授权：GRANT