1. mysql用户和权限管理

1. mysql用户和权限管理

1.1. 常用授权语句

1.1.1. 5.7以及以前的版本

5.7和之前的版本可以有两种方式创建用户：

方法一：
执行create user/grant命令（推荐方式）

# 创建finley 这只是创建用户并没有权限
mysql> CREATE USER 'finley'@'localhost' IDENTIFIED BY 'some_pass';
# 把finley 变成管理员用户
mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'localhost' WITH
GRANT OPTION;
#创建用户并赋予RELOAD,PROCESS权限 ，在所有的库和表上
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost' identified by '123456';
 
# 创建keme用户，在test库，temp表， 上的id列只有select 权限
mysql> grant select(id) on test.temp to keme@'localhost' identified by '123456';


CREATE USER `gkdb_for_safety_check`@`192.168.61%` IDENTIFIED WITH mysql_native_password PASSWORD EXPIRE INTERVAL 30 DAY;

方法二：
直接执行下面的授权语句：
（注意：这里使用了identified by password 参数表示授权的时候使用的密码不是明文的密码，而是通过Mysql.user表的authentication_string字段查询出来的，
是加密处理过的密码，如果密码是明文密码，则可以直接identified by '密码'即可）;
要给新用户授权必须要加上密码标识，mysql一个用户是：用户名@ip 这个整体如 pushadmin@'10.25.148.2__'）

1.查看加密之后的密码：
select distinct user ,host,authentication_string from mysql.user where host like '10.25.148%';
select user,host,authentication_string from mysql.user;

2.使用非明文密码方式grant 并创建用户：
grant SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER 
 on `push-prod`.* to pushadmin@'10.25.148.2__' identified by PASSWORD '*2FD6709924B6C2D0D316B4A34660D21F6F8C9999';

高金：
系统用的账号：
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT 
ON `goldkinn_center_dev_bpm%`.* to `gk-bpm-dev`@'172.28.%' identified by 'gk-bpm-dev#Fy18StjR';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT 
ON `goldkinn_center_test_bpm%`.* to `gk-bpm-test`@'172.28.%' identified by 'gk-bpm-test#XsHy82JJ';

grant SHOW_ROUTINE on *.* to 'xulin'@'%';

flush privileges;

3.使用明文密码的方式grant 并创建用户：
grant SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER 
 on `push-prod`.* to pushadmin@'10.25.148.2__' identified by 'abc123';

grant SHOW_ROUTINE on *.* to 'xulin'@'%';
grant create routine,alter routine on *.* to 'gkdb-amb-prod'@'192.168.%';
grant PROCESS,SYSTEM_USER  on *.* to 'gkdb-amb-prod'@'192.168.%';
grant super  on *.* to 'gkdb-amb-prod'@'192.168.%';


5.7授予视图修改权限（全局）
注意视图的和函数的定义者必须存在，否则也可能报access deaied 的错误，而不是报错用户不存在

grant RELOAD,PROCESS,CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE on *.* TO 'cube-center-dev'@'182.131.18.107';


flush PRIVILEGES


8.0：
grant SHOW_ROUTINE on *.* to 'xulin'@'%';
grant create routine,alter routine on *.* to 'gkdb-amb-prod'@'192.168.%';


FLUSH PRIVILEGES;


复制权限
REPLICATION CLIENT
REPLICATION SLAVE

GRANT Replication,REPLICATION SLAVE CLIENT ON *.* TO `gkdb-cxxxx`@`%`;

1.1.1.1. 存储过程权限管理

1.给指定的存储过程授予执行的权限：
对函数授权不能直接授权给数据库，需要授权到function

查看存储过程：
select * from mysql.proc where db = 'test' and `type` = 'PROCEDURE'
show create procedure idata1;

use db;
GRANT EXECUTE  ON  PROCEDURE `idata1` TO 'trigger'@'%';
GRANT alter routine,EXECUTE  ON  PROCEDURE `tyqk_mzk_proc` to tyqk_material@'%';
flush privileges;

2.授予查看存储过程的权限

Grant select on a.* to 'func1'@'%' identified by 'func1';

1.1.2. 8.0的版本：

（注：8以后的版本无法使用grant语句直接创建新的用户，所以必须要先创建用户，并且加密算法有所改变）
1.使用新的加密算法创建用户：

使用非明文密码方式创建用户：
CREATE USER 'msadmin'@'10.25.148.34' IDENTIFIED WITH 'mysql_native_password' AS '*F2ED2094D0ECC118A74AE788E7C86CE9B2329920' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;

使用明文密码方式建用户：
CREATE USER 'msadmin'@'10.25.148.34' IDENTIFIED WITH 'mysql_native_password' by '123' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;

2.授权验证：

grant SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER 
 on `ms-admin_prod`.* to msadmin@'10.25.148.34' ;

3.修改密码策略和密码过期时间

ALTER USER 'ebusapp1'@'%' IDENTIFIED BY 'ebusapp1' PASSWORD EXPIRE NEVER;
ALTER USER 'ebusapp1'@'%' IDENTIFIED WITH mysql_native_password BY 'ebusapp1';
FLUSH PRIVILEGES;

1.1.3. 删除用户及权限

1.查看用户权限和创建用户的语句

show grants for 'msadmin'@'10.25.148.34';
show create user [email protected];

2.通过user表的user加上host列确定新的用户名：

drop user 'msadmin'@'10.25.148.34';

注意：千万不要使用delete from 删除用户，风险太大

3.mysql常用的连接方式：

mysql -uroot -p
mysql -S /data/mysql/mysql.sock -uroot -p  推荐使用该方式连接
mysql -h ip -uroot -p -P 端口

批量回收某个库的所有账号的权限

update db set Select_priv='N' ,Insert_priv='N',Update_priv='N',Delete_priv='N',Create_priv='N',Drop_priv='N' where db like '%pre%' and user='gk-center-pre';

1.1.4. 修改

1.1.4.1. 修改密码

方法1 、alter语句（推荐使用）

ALTER USER 'ebusapp1'@'%' IDENTIFIED BY 'ebusapp1' PASSWORD EXPIRE NEVER;

方法2、直接修改权限表（不推荐，容易误操作）

update user set password = password('你的新密码') where user = 'root' and host='';

方法3、set方式设置密码（推荐）

set password for 'zhangsan'@'localhost'=password('hello');
set password for 'root'@'localhost'=password('root');

方法4、使用mysqlamdin修改

shell> mysqladmin -u user_name -h host_name password "new_password"

mysql> ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
mysql> SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('mypass');
mysql> GRANT USAGE ON *.* TO 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
shell> mysqladmin -u user_name -h host_name password "new_password"

此外还有如下两种：
创建用户时指定密码

mysql> CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

修改当前会话本身用户密码的方式包括：

mysql> ALTER USER USER() IDENTIFIED BY 'mypass';
mysql> SET PASSWORD = PASSWORD('mypass');

1.1.4.2. 修改用户账号名称

rename user'zhangsan'@'localhost'to'lisi'@'localhost';

1.1.5. 回收权限

1.回收指定用户的指定权限

revoke select,insert on dbname.* from 'xulin'@'%';

1.1.6. grant 授权和直接操作权限表的区别

grant语句会同时修改数据表和内存，而判断权限的时候使用的是内存中的数据，因此规范的使用grant和revoke是不需要执行flush privileges语句的。

flush privileges语句的作用是：
使用数据表本身的数据重建一份内存数据，所以在权限是在权限数据（内存中的数据和数据表中的数据）不一致的情况下执行。
而这往往就是直接操作权限表导致的，所以我们不建议直接操作权限表。

1.1.7. with grant optio问题

授予当前系统中一个不存在的用户zhou在数据库mysql_test的表customers上拥有select 和update 的权限，并允许其将自身的这个权限授予其他用户（通过with grant option）：

grant select,update
    on mysql_test.customers
    to'zhou'@'localhost'identified by'123'
    with grant option;

如果上面with子句后面跟的是max_queries_per_hour count、 max_updates_per_hour count、
max_connections_per_hour count、 max_user_connections count
中的某一项，则该grant语句可用于限制权限

root用户登录之后无法授权的问题可能就是由于本地登录时，没有给root用户授权加上with grant option导致的，
解决方案：
修改权限表，添加with grant option权限

uodate mysql.user set Grant_priv='Y' where user='root' and host='%';
flush privileges;

1.1.8. 给用户授予导出文件的权限

需要授予file权限（只能是对全局授予）

1.2. MySQL 权限介绍

1.2.1. MySQL 权限介绍

mysql中存在4个控制权限的表，分别为user表，db表，tables_priv表，columns_priv表
mysql权限表的验证过程为：

先从user表中的Host,User,Password这3个字段中判断连接的ip、用户名、密码是否存在，存在则通过验证。
通过身份认证后，进行权限分配，按照user，db，tables_priv，columns_priv的顺序进行验证。即先检查全局权限表user，如果user中对应的权限为Y，则此用户对所有数据库的权限都为Y，将不再检查db, tables_priv,columns_priv；如果为N，则到db表中检查此用户对应的具体数据库，并得到db中为Y的权限；如果db中为N，则检查tables_priv中此数据库对应的具体表，取得表中的权限Y，以此类推。z

1.2.1.1. 权限表介绍：

User表：存放用户账户信息以及全局级别（所有数据库）权限，决定了来自哪些主机的哪些用户可以访问数据库实例，如果有全局权限则意味着对所有数据库都有此权限 
Db表：存放数据库级别的权限，决定了来自哪些主机的哪些用户可以访问此数据库 
Tables_priv表：存放表级别的权限，决定了来自哪些主机的哪些用户可以访问数据库的这个表 
Columns_priv表：存放列级别的权限，决定了来自哪些主机的哪些用户可以访问数据库表的这个字段 
Procs_priv表：存放存储过程和函数级别的权限

User权限表结构中的特殊字段：

Plugin,authentication_string  字段存放用户认证信息
Password_expired              设置成’Y’则表明允许DBA将此用户的密码设置成过期而且过期后要求用户的使用者重置密码（alter user/set password重置密码）
Password_last_changed         作为一个时间戳字段代表密码上次修改时间，执行create user/alter user/set password/grant                等命令创建用户或修改用户密码时此数值自动更新
Password_lifetime             代表从password_last_changed时间开始此密码过期的天数
Account_locked                代表此用户被锁住，无法使用

在mysql 5.7 以前在user表有password 这个字段。

1.2.2. MySQL 权限级别

mysql权限主要分为如下三个级别

全局性的管理权限：作用于整个MySQL实例级别
数据库级别的权限：作用于某个指定的数据库上或者所有的数据库上
数据库对象级别的权限：作用于指定的数据库对象上（表、视图等）或者所有的数据库对象上

1，整个服务器，使用 grant ALL  和revoke  ALL
grant select on *.* to dba@localhost; -- dba 可以查询 MySQL 中所有数据库中的表。

2，整个数据库，使用on  database.*    （on *.*）
grant all on *.* to dba@localhost; -- dba 可以管理 MySQL 中的所有数据库s

3，特点表，使用on  database.table     （on db.tab）
grant select, insert, update, delete on testdb.orders to dba@localhost;

4，特定的列
grant select(id, se, rank) on testdb.apache_log to dba@localhost;

5，grant 作用在存储过程、函数上：
grant execute on procedure testdb.pr_add to ’dba’@’localhost’
grant execute on function testdb.fn_add to ’dba’@’localhost’

权限存储在mysql库的user, db, tables_priv, columns_priv, and procs_priv这几个系统表中，待MySQL实例启动后就加载到内存中

1.2.3. 常用权限详解

All/All Privileges权限代表全局或者全数据库对象级别的所有权限

Alter权限代表允许修改表结构的权限，但必须要求有create和insert权限配合。如果是rename表名，则要求有alter和drop原表， create和insert新表的权限

Alter routine权限代表允许修改或者删除存储过程、函数的权限

Create权限代表允许创建新的数据库和表的权限

Create routine权限代表允许创建存储过程、函数的权限

Create tablespace权限代表允许创建、修改、删除表空间和日志组的权限

Create temporary tables权限代表允许创建临时表的权限

Create user权限代表允许创建、修改、删除、重命名user的权限

Create view权限代表允许创建视图的权限

Delete权限代表允许删除行数据的权限

Drop权限代表允许删除数据库、表、视图的权限，包括truncate table命令

Event权限代表允许查询，创建，修改，删除MySQL事件

Execute权限代表允许执行存储过程和函数的权限

File权限代表允许在MySQL可以访问的目录进行读写磁盘文件操作，可使用的命令包括load data infile,select … into outfile,load file()函数

Grant option权限代表是否允许此用户授权或者收回给其他用户你给予的权限,重新付给管理员的时候需要加上这个权限

Index权限代表是否允许创建和删除索引

Insert权限代表是否允许在表里插入数据，同时在执行analyze table,optimize table,repair table语句的时候也需要insert权限

Lock权限代表允许对拥有select权限的表进行锁定，以防止其他链接对此表的读或写

Process权限代表允许查看MySQL中的进程信息，比如执行show processlist, mysqladmin processlist, show engine等命令

Reference权限是在5.7.6版本之后引入，代表是否允许创建外键

Reload权限代表允许执行flush命令，指明重新加载权限表到系统内存中，refresh命令代表关闭和重新开启日志文件并刷新所有的表

Replication client权限代表允许执行show master status,show slave status,show binary logs命令

Replication slave权限代表允许slave主机通过此用户连接master以便建立主从复制关系

Select权限代表允许从表中查看数据，某些不查询表数据的select执行则不需要此权限，如Select 1+1， Select PI()+2；而且select权限在执行update/delete语句中含有where条件的情况下也是需要的

Show databases权限代表通过执行show databases命令查看所有的数据库名

Show view权限代表通过执行show create view命令查看视图创建的语句

Shutdown权限代表允许关闭数据库实例，执行语句包括mysqladmin shutdown

Super权限代表允许执行一系列数据库管理命令，包括kill强制关闭某个连接命令， change master to创建复制关系命令，以及create/alter/drop server等命令

Trigger权限代表允许创建，删除，执行，显示触发器的权限

Update权限代表允许修改表中的数据的权限

Usage权限是创建一个用户之后的默认权限，其本身代表连接登录权限

1.2.4. 权限列表

1.2.4.1. 注意：

管理权限（如 super， process， file等）不能够指定某个数据库，on后面必须跟 .
有人会问truncate权限呢，其实truncate权限就是create+drop，这点需要注意

1.3. 用户组成

MySQL的授权用户由两部分组成：用户名和登录主机名
表达用户的语法为’user_name’@’host_name’
单引号不是必须，但如果其中包含特殊字符则是必须的
”@‘localhost’代表匿名登录的用户
Host_name可以使主机名或者ipv4/ipv6的地址。 Localhost代表本机， 127.0.0.1代表ipv4本机地址， ::1代表ipv6的本机地址
Host_name字段允许使用%和_两个匹配字符，比如’%’代表所有主机， ’%.mysql.com’代表
来自mysql.com这个域名下的所有主机， ‘192.168.1.%’代表所有来自192.168.1网段的主机

localhost    localhost不会被解析成IP地址，直接通过UNIXsocket连接
127.0.0.1      会通过TCP/IP协议连接，并且只能在本机访问；
::1                 ::1就是兼容支持ipv6的，表示同ipv4的127.0.0.1

1.3.1. 修改用户权限的生效时间

执行Grant,revoke,set password,rename user命令修改权限之后， MySQL会自动将修改后的权限信息同步加载到系统内存中
如果执行insert/update/delete操作上述的系统权限表之后，则必须再执行刷新权限命令才能同步到系统内存中，刷
新权限命令包括： flush privileges/mysqladmin flush-privileges / mysqladmin reload
如果是修改tables和columns级别的权限，则客户端的下次操作新权限就会生效
如果是修改database级别的权限，则新权限在客户端执行use database命令后生效
如果是修改global级别的权限，则需要重新创建连接新权限才能生效
如果是修改global级别的权限，则需要重新创建连接新权限才能生效 (例如修改密码)

1.3.2. 设置MySQL用户资源限制

通过设置全局变量max_user_connections可以限制所有用户在同一时间连接MySQL实例的数量，但此参数无法对每个用户区别对待，所以MySQL提供了对每个用户的资源限制管理

MAX_QUERIES_PER_HOUR：一个用户在一个小时内可以执行查询的次数（基本包含所有语句）
MAX_UPDATES_PER_HOUR：一个用户在一个小时内可以执行修改的次数（仅包含修改数据库或表的语句）
MAX_CONNECTIONS_PER_HOUR：一个用户在一个小时内可以连接MySQL的时间
MAX_USER_CONNECTIONS：一个用户可以在同一时间连接MySQL实例的数量

从5.0.3版本开始，对用户‘user’@‘%.example.com’的资源限制是指所有通过example.com域名主机连接user用户的连接，而不是分别指从host1.example.com和host2.example.com主机过来的连接

1.3.3. 设置MySQL用户密码过期策略

设置系统参数default_password_lifetime作用于所有的用户账户

default_password_lifetime=180 设置180天过期
default_password_lifetime=0 设置密码不过期

如果为每个用户设置了密码过期策略，则会覆盖上述系统参数

ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER; 密码不过期
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT; 默认过期策略

手动强制某个用户密码过期

ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE;

1.3.4. mysql锁定用户的方法

通过执行create user/alter user命令中带account lock/unlock子句设置用户的lock状态
Create user语句默认的用户是unlock状态

# 创建的时候给用户锁定
mysql> create user abc2@localhost identified by 'mysql' account lock;

1.3.4.1. 5.6版本用户锁定：

ALTER USER 'test'@'localhost' PASSWORD EXPIRE;
update mysql.user set password_expired='N' where user='test' and host='localhost';

1.3.4.2. 5.7+ 版本用户锁定：

Alter user语句默认不会修改用户的lock/unlock状态

alter user 'test'@'%' account lock;
alter user 'test'@'%' account unlock;

1.4. 常见权限

1.4.1. rename 表的权限

rename 表的权限处理常用的SELECT, INSERT, UPDATE, DELETE权限，还需要如下几个权限

GRANT create,alter,drop ON *.* TO `mg_gongzhenyu`@`localhost` identified by 'abc#123';

1.4.2. Mysql select INTO OUTFILE 配置权限

Mysql可使用 into outfile 参数把表中数据导出到文件，
例如可用以下命令把 test 表的数据导出到 test.txt

SELECT * FROM newdb.test INTO OUTFILE 'test.txt';

以上命令在mysql5.6下运行没有问题，但在mysql5.7下运行则出现了以下错误：'ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv
option so it cannot execute this statement'

1.4.2.1. secure_file_priv

查看官方文档，secure_file_priv参数用于限制LOAD DATA, SELECT …OUTFILE,
LOAD_FILE()传到哪个指定目录。secure_file_priv 为 'NULL' 时，表示限制mysqld不允许导入或导出。

secure_file_priv 为 '/tmp' 时，表示限制mysqld只能在/tmp目录中执行导入导出;
secure_file_priv '没有值'时，表示不限制mysqld在任意目录的导入导出。

show global variables like '%secure_file_priv%';

1.4.2.2. secure_file_priv配置

因为 secure_file_priv 参数是只读参数，'不能使用set global命令修改'。

mysql> # set global secure_file_priv='';
'ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable'

打开my.cnf ，加入以下语句后重启mysql。# vim /etc/my.cnf

[mysqld]
secure-file-priv = ""

1.4.2.3. 授予普通用户select outfile 权限

GRANT FILE ON *.* TO `circleadmin`@`localhost`;

注意： file 权限是全局，不可以单独指定给某个数据库授权，只能是.

设置过期时间

#密码90天过期：
ALTER USER ‘testuser’@‘localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
#禁用密码过期
ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE NEVER;
#让用户使用默认的密码过期全局策略：
ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE DEFAULT;

标签：grant,用户,user,mysql,权限,localhost
From： https://www.cnblogs.com/xulinforDB/p/17496179.html

mysql的用户和权限管理