MySQL系列之六用户与授权

吾爱主题阅读：255 2024-04-02 08:03:50 评论：0

一、用户管理

1、用户账号

用户的账号由用户名和HOST俩部分组成（'USERNAME'@'HOST'）

HOST的表示：

主机名
具体IP地址
网段/掩码

可以使用通配符表示，%和_；192.168.%即表示这个网段的所有主机

2、增加删除账号

主要：在数据库中修改了用户信息需要执行FLUSH PRIVILEGES;来刷新授权表使其生效

创建

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 MariaDB [mysql]> CREATE USER 'user1' @ '192.168.%' ; MariaDB [mysql]> CREATE USER 'user2' @ '192.168.%' IDENTIFIED BY 'your_password' ; MariaDB [mysql]> SELECT user ,host, password FROM user ; + -------+-----------+-------------------------------------------+ | user | host | password | + -------+-----------+-------------------------------------------+ | root | localhost | *4A54C3F37C03C7FBACE31591D6A8C546F93DF5C5 | | root | centos7 | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | centos7 | | | user1 | 192.168.% | | | user2 | 192.168.% | *9E72259BA9214F692A85B240647C4D95B0F2E08B | + -------+-----------+-------------------------------------------+

删除

1 2 3 4 5 6 7 8 9 10 11 12 13 MariaDB [mysql]> DROP USER user2@ '192.168.%' ; MariaDB [mysql]> SELECT user ,host, password FROM user ; + -------+-----------+-------------------------------------------+ | user | host | password | + -------+-----------+-------------------------------------------+ | root | localhost | *4A54C3F37C03C7FBACE31591D6A8C546F93DF5C5 | | root | centos7 | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | centos7 | | | user1 | 192.168.% | | + -------+-----------+-------------------------------------------+

重命名

1 2 3 4 5 6 7 8 9 10 11 12 13 MariaDB [mysql]> RENAME USER user1@ '192.168.%' TO testuser@ '%' ; MariaDB [mysql]> SELECT user ,host, password FROM mysql. user ; + ----------+-----------+-------------------------------------------+ | user | host | password | + ----------+-----------+-------------------------------------------+ | root | localhost | *4A54C3F37C03C7FBACE31591D6A8C546F93DF5C5 | | root | centos7 | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | centos7 | | | testuser | % | | + ----------+-----------+-------------------------------------------+

修改密码

1 2 3 4 5 6 7 8 9 10 11 12 13 MariaDB [mysql]> SET PASSWORD FOR testuser@ '%' = PASSWORD ( 'testpass' ); MariaDB [mysql]> SELECT user ,host, password FROM mysql. user ; + ----------+-----------+-------------------------------------------+ | user | host | password | + ----------+-----------+-------------------------------------------+ | root | localhost | *4A54C3F37C03C7FBACE31591D6A8C546F93DF5C5 | | root | centos7 | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | centos7 | | | testuser | % | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 | + ----------+-----------+-------------------------------------------+

其他修改密码的方法：

1	`UPDATE` `user` `SET` `password` `=` `PASSWORD` `(` `'testpass'` `)` `WHERE` `user` `=` `'testuser'` `;`

1	`# mysqladmin -uroot -poldpass` `password` `'newpass'`

3、破解管理账号密码

空数据库的情况下恢复密码

1 2 3 # systemctl stop mariadb # rm -rf /var/lib/mysql/* #删库跑路 # systemctl start mariadb

有数据的情况下恢复密码

1）在/etc/my.cnf配置文件的[mydqld]下添加skip-grant-tables和skip-networking参数

2）# systemctl restart mariadb 重启服务

3）执行mysql登录到数据库

4）MariaDB [(none)]> UPDATE mysql.user SET password=PASSWORD('newpassword') WHERE user='root' AND host='localhost'; #更新密码

5）MariaDB [(none)]> FLUSH PRIVILEGES; #刷新授权表

6）退出，修改配置文件，删除skip-grant-tables和skip-networking参数，重启服务
也可以在启动mysqld进程时，为其使用如下选项：

--skip-grant-tables

--skip-networking

二、授权管理

1、授权

语法：GRANT priv_type ON [object_type] priv_level TO user@'%' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];

授权时如果用户不存在则创建，所以我们一般不会单独去创建一个用户，而是授权创建一块完成。

priv_type 授权类型

- SELECT

- INSERT

- UPDATE

- DELETE

- CREATE

- DROP

- INDEX

- ALTER

- SHOW DATABASES

- CREATE TEMPORARY TABLES

- LOCK TABLES

- CREATE VIEW

- SHOW VIEW

- CREATE USER

- ALL PRIVILEGES 或 ALL

object_type 授权对象

- TABLE

- FUNCTION

- PROCEDURE

priv_level 授权级别

- *或*.* 表示所有库

- db_name.* 表示指定库中的所有表

- db_name.tbl_name 指定库中的指定表

- tbl_name 表示当前库的表

- db_name.routine_name 表示指定库的函数,存储过程,触发器

WITH GRANT OPTION

- MAX_QUERIES_PER_HOUR count

- MAX_UPDATES_PER_HOUR count

- MAX_CONNECTIONS_PER_HOUR count

- MAX_USER_CONNECTIONS count

1 2	`MariaDB [school]>` `GRANT` `SELECT` `(stuid,` `name` `)` `ON` `TABLE` `school.students` `TO` `admin@` `'%'` `IDENTIFIED` `BY` `'admin'` `; #把students表的stuid和` `name` `字段的查询权限授权于admin@` `'%'` `用户` `MariaDB [school]> FLUSH` `PRIVILEGES` `; #刷新授权表`

2、查询授权

1 2 3 4 5 MariaDB [school]> SHOW GRANTS FOR admin@ '%' \G #查看指定用户的权限 *************************** 1. row *************************** Grants for admin@%: GRANT USAGE ON *.* TO 'admin' @ '%' IDENTIFIED BY PASSWORD '*4ACFE3202A5FF5CF467898FC58AAB1D615029441' *************************** 2. row *************************** Grants for admin@%: GRANT SELECT (stuid, name ) ON `school`.`students` TO 'admin' @ '%'

1 2 3 4 5 6 [root@working ~] # mysql -uadmin -padmin -h192.168.0.7 MariaDB [(none)]> SHOW GRANTS FOR CURRENT_USER()\G #查询自己的权限 *************************** 1. row *************************** Grants for admin@%: GRANT USAGE ON *.* TO 'admin' @ '%' IDENTIFIED BY PASSWORD '*4ACFE3202A5FF5CF467898FC58AAB1D615029441' *************************** 2. row *************************** Grants for admin@%: GRANT SELECT (stuid, name) ON `school`.`students` TO 'admin' @ '%'

3、收回授权

1	`MariaDB [school]>` `REVOKE` `SELECT` `(stuid)` `ON` `school.students` `FROM` `admin@` `'%'` `; #收回admin@` `'%'` `用户对stuid字段的查询权限`