mysql权限管理（实例）
2023-09-14 08:59:49 时间
mysql权限管理实例
本文并没有很详细的介绍对具体的对象授权，只是简单的限制了大的权限。
1.目前现状：
研发一直使用root用户进行操作。不变修改代码，因此我们采用新建一个超级用户，回收root的部分权限来实现对研发用户的限制
mysql select user,host from user;
+-----------+---------------+
| user | host |
+-----------+---------------+
| mydba | % | ---我新建的超级用户
| root | % | ---安装就自带
| server | % | ---无用用户
| repli | 192.168.1.3 | ---我创建的主从复制的用户
| root | 192.168.1.9 | ---无用用户
| mysql.sys | localhost |
| root | localhost | ---安装就自带
+-----------+---------------+
7 rows in set (0.00 sec)
回收前，先用root进入，创建一个超级用户：
grant all privileges on *.* to mydba@% identified by tina with grant option;
grant all privileges on mysql.* to mydba@% identified by tina with grant option;
2.删除无用的用户：（上班时先回收所有权限，下班后再删除)
mysql 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
mysql show grants for server;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for server@% |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO server@% WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `mysql`.* TO server@% WITH GRANT OPTION
mysql revoke all privileges on *.* from server@%;
Query OK, 0 rows affected (0.07 sec)
mysql show grants for server;
+---------------------------------------------------------------------+
| Grants for server@% |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO server@% WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `mysql`.* TO server@% WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql revoke all privileges on mysql.* from server@%; ---操作会同步到从库，因此不需要到从库执行，因为主从同步的库包括mysql库
Query OK, 0 rows affected (0.10 sec)
mysql show grants for server;
+------------------------------------------------------------+
| Grants for server@% |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO server@% WITH GRANT OPTION |
| GRANT USAGE ON `mysql`.* TO server@% WITH GRANT OPTION |
+------------------------------------------------------------+
2 rows in set (0.00 sec)
删除：delete from mysql.user where user=server and host=%;
delete from mysql.user where user=root and host=192.168.1.9;
3.主从复制用户权限
mysql show grants for repli@192.168.1.3;
+--------------------------------------------------------------------------------+
| Grants for repli@192.168.1.3 |
+--------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repli@192.168.1.3 | --repli因为是用于主从复制的，因此需要这两个权限。
+--------------------------------------------------------------------------------+
1 row in set (0.01 sec)
4.回收root用户的file,process,super,drop,create 权限，依然可以创建、删除临时表
原始权限：
--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@% |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO root@% WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `mysql`.* TO root@% WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
| Grants for root@localhost |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO root@localhost WITH GRANT OPTION |
| GRANT PROXY ON @ TO root@localhost WITH GRANT OPTION
回收了root远程主机的部分权限：（process权限保留了，因为要用来监控主从同步状态）
revoke file,process,super,drop,create,create view,reload, shutdown,index, alter, replication slave, replication client, create view,create routine,
alter routine, create user,create tablespace on *.* from root@%;
回收本地root对mysql库的所有权限:
revoke all privileges on mysql.* from root@localhost;
测试一下：
mysql select * from t1 into outfile /tmp/a.txt fields terminated by ,;
ERROR 1045 (28000): Access denied for user root@% (using password: YES) --load的权限被禁用了
mysql create view v_2 as select id from t2 where id
ERROR 1142 (42000): CREATE VIEW command denied to user root@192.168.1.4 for table v_2; ---不能创建视图
mysql create index i_2 on t2(id);
ERROR 1142 (42000): INDEX command denied to user root@192.168.1.4 for table t2 --不能创建索引
mysql show index from t1; --可以查看索引
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1 | 1 | i_1 | 1 | id | A | 0 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.03 sec)
6.刷新权限
flush privileges;
7.回收后：
mysql select user,host from user;
+-----------+---------------+
| user | host |
+-----------+---------------+
| mydba | % |
| root | % |
| repli | 192.168.1.4 |
| mysql.sys | localhost |
| root | localhost |
+-----------+---------------+
5 rows in set (0.00 sec)
mysql show grants for root;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@% |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES,process ，SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, SHOW VIEW, EVENT, TRIGGER ON *.* TO root@% WITH GRANT OPTION |
| GRANT USAGE ON `mysql`.* TO root@% WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql show grants for root@localhost;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES,process， SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, SHOW VIEW, EVENT, TRIGGER ON *.* TO root@localhost WITH GRANT OPTION |
| GRANT PROXY ON @ TO root@localhost WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql show grants for mydba; --超级用户：
+--------------------------------------------------------------------+
| Grants for mydba@% |
+--------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO mydba@% WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `mysql`.* TO mydba@% WITH GRANT OPTION |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)
补充说明：
1.usage权限：可以连接db，可以show databases和tables，但是没有其他权限，select都不行
2.mysql的权限是会叠加的，如果第一次授予了这个用户select权限，第二次授予了全部权限，当你回收所有权限后，会发现还有select权限，必须逐条回收
MySQL权限管理实战 不清楚各位同学对数据库用户权限管理是否了解，作为一名 DBA ，用户权限管理是绕不开的一项工作内容。特别是生产库，数据库用户权限更应该规范管理。本篇文章将会介绍下 MySQL 用户权限管理相关内容。
5.5 MySQL高级（视图，事务，索引，权限管理，主从） 视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。 视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据
MySQL权限,mysql权限管理,mysql添加新用户,mysql分配权限,mysql grant,mysql revoke MySQL权限,mysql权限管理,mysql添加新用户,mysql分配权限,mysql grant,mysql revoke MySQL添加新用户,为新用户分配权限,Mysql版本5.7 首先进入Mysql控制台： mysql -uroot -p grant授权格式：grant 权限列表 on 库.
mysql-数据（记录）相关操作（增删改查）及权限管理 一、介绍 在MySQL管理软件中，可以通过SQL语句中的DML语言来实现数据的操作，包括 使用INSERT实现数据的插入 UPDATE实现数据的更新 使用DELETE实现数据的删除 使用SELECT查询数据以及。
本文并没有很详细的介绍对具体的对象授权，只是简单的限制了大的权限。
1.目前现状：
研发一直使用root用户进行操作。不变修改代码，因此我们采用新建一个超级用户，回收root的部分权限来实现对研发用户的限制
mysql select user,host from user;
+-----------+---------------+
| user | host |
+-----------+---------------+
| mydba | % | ---我新建的超级用户
| root | % | ---安装就自带
| server | % | ---无用用户
| repli | 192.168.1.3 | ---我创建的主从复制的用户
| root | 192.168.1.9 | ---无用用户
| mysql.sys | localhost |
| root | localhost | ---安装就自带
+-----------+---------------+
7 rows in set (0.00 sec)
回收前，先用root进入，创建一个超级用户：
grant all privileges on *.* to mydba@% identified by tina with grant option;
grant all privileges on mysql.* to mydba@% identified by tina with grant option;
2.删除无用的用户：（上班时先回收所有权限，下班后再删除)
mysql 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
mysql show grants for server;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for server@% |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO server@% WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `mysql`.* TO server@% WITH GRANT OPTION
mysql revoke all privileges on *.* from server@%;
Query OK, 0 rows affected (0.07 sec)
mysql show grants for server;
+---------------------------------------------------------------------+
| Grants for server@% |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO server@% WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `mysql`.* TO server@% WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql revoke all privileges on mysql.* from server@%; ---操作会同步到从库，因此不需要到从库执行，因为主从同步的库包括mysql库
Query OK, 0 rows affected (0.10 sec)
mysql show grants for server;
+------------------------------------------------------------+
| Grants for server@% |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO server@% WITH GRANT OPTION |
| GRANT USAGE ON `mysql`.* TO server@% WITH GRANT OPTION |
+------------------------------------------------------------+
2 rows in set (0.00 sec)
删除：delete from mysql.user where user=server and host=%;
delete from mysql.user where user=root and host=192.168.1.9;
3.主从复制用户权限
mysql show grants for repli@192.168.1.3;
+--------------------------------------------------------------------------------+
| Grants for repli@192.168.1.3 |
+--------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repli@192.168.1.3 | --repli因为是用于主从复制的，因此需要这两个权限。
+--------------------------------------------------------------------------------+
1 row in set (0.01 sec)
4.回收root用户的file,process,super,drop,create 权限，依然可以创建、删除临时表
原始权限：
--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@% |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO root@% WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `mysql`.* TO root@% WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
| Grants for root@localhost |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO root@localhost WITH GRANT OPTION |
| GRANT PROXY ON @ TO root@localhost WITH GRANT OPTION
回收了root远程主机的部分权限：（process权限保留了，因为要用来监控主从同步状态）
revoke file,process,super,drop,create,create view,reload, shutdown,index, alter, replication slave, replication client, create view,create routine,
alter routine, create user,create tablespace on *.* from root@%;
回收本地root对mysql库的所有权限:
revoke all privileges on mysql.* from root@localhost;
测试一下：
mysql select * from t1 into outfile /tmp/a.txt fields terminated by ,;
ERROR 1045 (28000): Access denied for user root@% (using password: YES) --load的权限被禁用了
mysql create view v_2 as select id from t2 where id
ERROR 1142 (42000): CREATE VIEW command denied to user root@192.168.1.4 for table v_2; ---不能创建视图
mysql create index i_2 on t2(id);
ERROR 1142 (42000): INDEX command denied to user root@192.168.1.4 for table t2 --不能创建索引
mysql show index from t1; --可以查看索引
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1 | 1 | i_1 | 1 | id | A | 0 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.03 sec)
6.刷新权限
flush privileges;
7.回收后：
mysql select user,host from user;
+-----------+---------------+
| user | host |
+-----------+---------------+
| mydba | % |
| root | % |
| repli | 192.168.1.4 |
| mysql.sys | localhost |
| root | localhost |
+-----------+---------------+
5 rows in set (0.00 sec)
mysql show grants for root;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@% |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES,process ，SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, SHOW VIEW, EVENT, TRIGGER ON *.* TO root@% WITH GRANT OPTION |
| GRANT USAGE ON `mysql`.* TO root@% WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql show grants for root@localhost;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES,process， SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, SHOW VIEW, EVENT, TRIGGER ON *.* TO root@localhost WITH GRANT OPTION |
| GRANT PROXY ON @ TO root@localhost WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql show grants for mydba; --超级用户：
+--------------------------------------------------------------------+
| Grants for mydba@% |
+--------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO mydba@% WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `mysql`.* TO mydba@% WITH GRANT OPTION |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)
补充说明：
1.usage权限：可以连接db，可以show databases和tables，但是没有其他权限，select都不行
2.mysql的权限是会叠加的，如果第一次授予了这个用户select权限，第二次授予了全部权限，当你回收所有权限后，会发现还有select权限，必须逐条回收
MySQL权限管理实战 不清楚各位同学对数据库用户权限管理是否了解，作为一名 DBA ，用户权限管理是绕不开的一项工作内容。特别是生产库，数据库用户权限更应该规范管理。本篇文章将会介绍下 MySQL 用户权限管理相关内容。
5.5 MySQL高级（视图，事务，索引，权限管理，主从） 视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。 视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据
MySQL权限,mysql权限管理,mysql添加新用户,mysql分配权限,mysql grant,mysql revoke MySQL权限,mysql权限管理,mysql添加新用户,mysql分配权限,mysql grant,mysql revoke MySQL添加新用户,为新用户分配权限,Mysql版本5.7 首先进入Mysql控制台： mysql -uroot -p grant授权格式：grant 权限列表 on 库.
mysql-数据（记录）相关操作（增删改查）及权限管理 一、介绍 在MySQL管理软件中，可以通过SQL语句中的DML语言来实现数据的操作，包括 使用INSERT实现数据的插入 UPDATE实现数据的更新 使用DELETE实现数据的删除 使用SELECT查询数据以及。
相关文章
- Mysql远程连接数据库报错排查：pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on '**.**.**.**' (timed out)")
- 【MySql】mysql 的权限体系介绍
- MySQL运维---多实例
- MYSQL错误解决：ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
- [root@master mysql]# mysql -uroot -pjsb ERROR 2002 (HY000): Can't connect to local MySQL server thro
- mysql远程连接设置
- MySQL · 引擎特性 · 基于InnoDB的物理复制实现
- Spring+Mybatis+SpringMVC+Maven+MySql搭建实例
- MySQL 中 count(*) 和 count(1) 有什么区别？哪个性能最好？
- MySQL保留字不能作为字段名使用_Table字段不能设为关键字range，range在mysql中是分区,是mysql的关键字
- Atitit postgresql data type 数据类型与mysql对应表 数据库常用数据类型 PostgreMysql 整数intgreterInt 小数numericFL
- Atitit.软件GUI按钮与仪表盘--db数据库区--导入mysql sql错误的解决之道
- ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)
- mysql事务 mysql事务回滚 MySQL事务死锁 如何解除死锁 资金出入账
- file /usr/share/mysql/charsets/README from install of MySQL-server-5.1.73-1.glibc23.i386 conflicts with file from package mysql-libs-5.1.73-8.el6_8.i686
- MYSQL提权之反弹SHELL——数据库提权属于webshell到管理员的纵向提权，本质还是利用udf提权，无非是在mysql自定义函数中使用了反弹shell而已
- ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'
- Mysql查看当前实例工作目录
- MySql高级----Linux下的mysql的安装与初始化配置
- MySql查询的生命周期和性能优化思路
- Docker学习笔记17：docker实例之安装 Node.js、PHP、MySQL、Tomcat、Python、Redis、MongoDB、Apache
- MySQL 配置, 避坑 sqlyog连接mysql错误码2058
- Linux系统之安装mysql数据库