高级DBA教你Mysql数据库指定某数据库或某表赋予增删改查操作权限各类划分权限的方法总结实战

一、mysql创建用户只赋予指定数据库的增删改查操作权限

在日常生产运维工作中，我们经常需要给其他厂商或者合作伙伴提供数据库的账号，并且需要指定某个用户只能查询指定的数据库，并且赋予增删改查的指定权限。

（1）创建某个用户，赋予只能操作(增、删、改、查)指定某个数据库，并且其他数据库看不到，只能看到指定的数据库

CREATE USER '用户名'@'%' IDENTIFIED BY '登录数据库的密码';
GRANT SELECT, INSERT, UPDATE, DELETE ON `数据库名称`.* TO '用户名'@'%';

（2）创建某个用户，赋予只能查询指定某个数据库的所有表，其他数据库完全看不到！

CREATE USER '用户名'@'%' IDENTIFIED BY '登录数据库的密码';
GRANT SELECT  ON `数据库名称`.* TO '用户名'@'%';

（3）创建某个用户，只能查询指定数据库的某个表，该库其他表都看不到！其他数据库完全看不到！

CREATE USER '用户名'@'%' IDENTIFIED BY '登录数据库的密码';
GRANT SELECT ON `数据库名称`.`表名` TO '用户名'@'%';

（4）创建某个用户，只能操作（增、删、改、查）指定数据库的某个表，该库其他表都看不到！其他表也操作不了!

CREATE USER '用户名'@'%' IDENTIFIED BY '登录数据库的密码';
GRANT SELECT, INSERT, UPDATE, DELETE ON `数据库名称`.`表名` TO '用户名'@'%';

（5）创建某个用户，只能查询指定数据库的某个表，该库其他表都看不到！并且指定数据库用户登录的IP地址！其他IP无法登录！

CREATE USER '用户名'@'IP地址' IDENTIFIED BY '登录数据库的密码';
GRANT SELECT ON `数据库名称`.`表名` TO '用户名'@'IP地址';

总结一下运维常用的几种赋予权限的方法！

二、将A服务器的MYSQL的所有用户权限复制迁移到B服务器MYSQL的方法

在日常生产运维工作中，我们经常需要迁移服务器，如果源服务器上的用户建立的非常多，并且还在生产上用着，整个迁移过程就很繁琐，如果漏了，会导致生产事故，笔者介绍一种MYSQL服务器用户权限的迁移方法，方便简单，节省工作量。

MYSQL导出所有用户权限shell脚本实例觉得有效

实际生产生活中，我经常要迁移MYSQL服务器，需要导出数据库的所有用户名密码以及权限。
给大家一个确定可行的shell脚本实例

cd /home/

touch exp_grant.sh

chmod 777 exp_grant.sh

vim exp_grant.sh

#!/bin/bash

#Function export user privileges

  

pwd=123456  #root的密码

expgrants()

{

  mysql -B -u'root' -p${pwd} -N $@ -e "SELECT CONCAT(

    'SHOW GRANTS FOR ''', user, '''@''', host, ''';'

    ) AS query FROM mysql.user" | \

  mysql -u'root' -p${pwd} $@ | \

  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}'

}

expgrants > ./grants.sql

sh exp_grant.sh #执行命令

-- Grants for nasen@% 
GRANT USAGE ON *.* TO `nasen`@`%`;

-- Grants for root@% 
GRANT SELECT, INSERT, UPDATE, DELETE, 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, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%` WITH GRANT OPTION;

-- Grants for mysql.infoschema@localhost 
GRANT SELECT ON *.* TO `mysql.infoschema`@`localhost`;
GRANT AUDIT_ABORT_EXEMPT,SYSTEM_USER ON *.* TO `mysql.infoschema`@`localhost`;

-- Grants for mysql.session@localhost 
GRANT SHUTDOWN, SUPER ON *.* TO `mysql.session`@`localhost`;
GRANT AUDIT_ABORT_EXEMPT,BACKUP_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,SESSION_VARIABLES_ADMIN,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN ON *.* TO `mysql.session`@`localhost`;
GRANT SELECT ON `performance_schema`.* TO `mysql.session`@`localhost`;
GRANT SELECT ON `mysql`.`user` TO `mysql.session`@`localhost`;

-- Grants for mysql.sys@localhost 
GRANT USAGE ON *.* TO `mysql.sys`@`localhost`;
GRANT AUDIT_ABORT_EXEMPT,SYSTEM_USER ON *.* TO `mysql.sys`@`localhost`;
GRANT TRIGGER ON `sys`.* TO `mysql.sys`@`localhost`;
GRANT SELECT ON `sys`.`sys_config` TO `mysql.sys`@`localhost`;

去另个服务器执行即可, 当然各个用户的密码仍然需要重新定义！！！！！！！

笔者简介
国内某一线知名软件公司企业认证在职员工：任JAVA高级研发工程师,大数据领域专家,数据库领域专家兼任高级DBA!10年软件开发经验！现任国内某大型软件公司大数据研发工程师、MySQL数据库DBA，软件架构师。直接参与设计国家级亿级别大数据项目!并维护真实企业级生产数据库300余个!紧急处理数据库生产事故上百起，挽回数据丢失所造成的灾难损失不计其数!并为某国家级大数据系统的技术方案（国家知识产权局颁布）专利权的第一专利发明人！