mysql的权限管理
2023-09-27 14:21:15 时间
show create user tmp@'10.10.10.10'; show grants for tmp@'10.10.10.10';
# 添加超级用户 grant all privileges on *.* to 'dump_tmp'@'10.10.10.10' identified by 'dump_tmp'; grant all privileges on *.* to 'tmp'@'10.%' identified by 'tmp' with grant option; grant all privileges on *.* to 'tmp'@'127.0.0.1' identified by password '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' with grant option; # 删除超级用户 drop user dump_tmp@'10.10.10.10'; > # 其中将“123456”加密后的密码就是“*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9”,根据自己的需要来添加用户的密码。 > select password('123456'); +-------------------------------------------+ | password('123456') | +-------------------------------------------+ | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +-------------------------------------------+ 1 row in set (0.00 sec) Mon Nov 25 09:44:19 2019 > ##########################################
原来的权限:
> show grants for 'glc_x'@'10.10.10.10';
+-----------------------------------------------------------------------------------------+
| Grants for glc_x@10.10.10.10 |
+-----------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'glc_x'@'10.10.10.10' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `ms`.* TO 'glc_x'@'10.10.10.10' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `metadata`.* TO 'glc_x'@'10.10.10.10' |
##############
新增权限:
> grant select,insert,update,delete on `rd`.* to 'glc_x'@'10.136.26.35';
##############
现在的权限: > show grants for 'misc_cms_x'@'10.10.10.10'; +-----------------------------------------------------------------------------------------+ | Grants for misc_cms_x@10.10.10.10 | +-----------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'glc_x'@'10.10.10.10' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `rd`.* TO 'glc_x'@'10.10.10.10' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `ms`.* TO 'glc_x'@'10.10.10.10' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `metadata`.* TO 'glc_x'@'10.10.10.10' | +-----------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) Wed Dec 18 11:50:34 2019 >
##########################################
重新加载一下配置,才能有效(仅仅针对公司)
./load reload
##########################################
查看用户权限:
show grants for 'user'@'host';
回收权限:
root@xxx((none)) > show grants for glc_x@'10.10.10.10'; +-------------------------------------------------------------------------------------------+ | Grants for glc_x@10.10.10.10 | +-------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'glc_x'@'10.10.10.10' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `metadata`.* TO 'glc_x'@'10.10.10.10' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `business`.* TO 'glc_x'@'10.10.10.10' | +-------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) Fri Nov 27 15:51:43 2020 root@xxx((none)) > revoke INSERT, UPDATE, DELETE ON `metadata`.* from 'glc_x'@'10.10.10.10'; Query OK, 0 rows affected (0.00 sec) Fri Nov 27 15:53:03 2020 Fri Nov 27 15:53:52 2020 root@xxx((none)) > revoke INSERT, UPDATE, DELETE ON `business`.* from 'glc_x'@'10.10.10.10'; Query OK, 0 rows affected (0.00 sec) Fri Nov 27 15:54:02 2020 root@xxx((none)) > show grants for glc_x@'10.10.10.10'; +-------------------------------------------------------------------+ | Grants for glc_x@10.10.10.10 | +-------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'glc_x'@'10.10.10.10' | | GRANT SELECT ON `metadata`.* TO 'glc_x'@'10.10.10.10' | | GRANT SELECT ON `business`.* TO 'glc_x'@'10.10.10.10' | +-------------------------------------------------------------------+ 3 rows in set (0.00 sec) Fri Nov 27 15:54:09 2020 root@xxx((none)) >
使用超级用户在线备份数据库
mysql -udump_tmp -pdump_tmp -hXXX -P3306 -e 'show databases;' | grep -Ev 'Database|information_schema|mysql|performance_schema|sys' | xargs mysqldump --set-gtid-purged=off -udump_tmp -pdump_tmp -hXXX -P3306 --single-transaction --master-data=2 --databases > data_backup.sql pt-show-grants -udump_tmp -pdump_tmp -hXXX -P3306 >grant_backup.sql
相关文章
- mysql给root开启远程访问权限
- MySQL的普通索引和唯一索引到底什么区别?
- mysql权限管理
- MySQL 用户管理与权限管理
- mysql通配符使用
- MySQL 操作命令梳理(4)-- grant授权和revoke回收权限
- MySQL 0Day漏洞出现 该漏洞可以拿到本地Root权限 绿盟科技发布防护方案
- Mysql:账号与权限
- mysql 文件夹权限修改
- mysql创建用户,并授予权限
- [数据库]MYSQL之授予/查验binlog权限
- [数据库/Linux]CentOS7安装MySQL Percona版(RPM方式)
- mysql 用户及权限管理 小结
- mysql创建用户、赋予指定权限命令
- 【MySQL】若sql语句中order by指定了多个字段,则怎么排序?
- mysql授予权限
- MySQL权限整理及授权命令
- Mysql INSTR函数
- MySQL 优化 (一)
- mysql 远程连接权限
- C#底层库--MySQL数据库访问操作辅助类(推荐阅读)
- 详解nginx、php-fpm和mysql用户权限
- 全球MySQL数据库沦为新一轮勒索软件攻击目标
- [ 数据库 ] MySQL 入门到放弃(八) --- 权限管理和备份
- 113.网络安全渗透测试—[权限提升篇11]—[Windows 2003 Mysql MOF提权]
- MYSQL权限管理
- MySQL JDBC编程
- MySQL用户与权限管理
- mysql 存在update不存在insert