MySQL用户权限管理

核心开发权限

权限

列

可以针对的级别

DELETE

DELETE_PRIV

TABLES

INSERT

INSERT_PRIV

TABLES OR COLUMNS

SELECT

SEELCT_PRIV

TABLES OR COLUMNS

UPDATE

UPDATE_PRIV

TABLES OR COLUMNS

ALTER

ALTER_PRIV

TABLES

CREATE_VIEW

CREATE_TMP_TABLES_PRIV

TABLES

CREATE_TEMPORARY TABLES

CREATE_TMP_TABLE_PRIV

TABLES

TRIGGER

TRIGGER_PRIV

TABLES

CREATE_VIEW

CREATE_VIEW_PRIV

VIEWS

SHOW VIEW

SHOW_VIEW_PRIV

VIEWS

ALTER ROUTINE

ALTER_ROUTINE_PRIV

STORED ROUTINES

CREATE ROUTINE

CREATE_ROUTINEZ_PRIV

STORED ROUTINES

EXECUTE

EXECUTE_PRIV

STORED ROUTINES

INDEX

INDEX_PRIV

TABLES

EVENT

EVENT_PRIV

DATABASES

 

线上最小权限

开发权限就是给delete，insert，update，select 权限 一般情况下不给alter权限

如果用到存储过程会给excute，createroutine权限

 

管理权限-表级别

权限

列

可以针对级别

CREATE

CREATE_PRIV

DATABASES,TABLES,OR INDEX

FILE

FILE_PRIV

FILE ACCESS ON SERVER HOST

DROP

DROP_PRIV

DATABASES,TABLES,OR VIEWS

LOCK TABLES

LOCK_TABLES_PRIV

DATABASES

 

--File 权限要禁用掉  load /etc/passwd 到数据库里面破解密码

 

管理权限-SERVER级别 

权限

列

可以针对级别

GRANT OPTION

GRANT_PRIV

DATABASES,TABLES,OR STORED ROUTINES

CREATE_TABLESPACE

CREATE_TABLESPACE_PRIV

SERVER ADMINISTRATION

CREATE USER

CREATE_USER_PRIV

SERVER ADMINISTRATION

PROCESS

PROCESS_PRIV

SERVER ADMINISTRATION

PROXY

SEE_PROXIES_PRIV_TABLE

SERVER ADMINISTRATION

RELOAD

RELOAD_PRIV

SERVER ADMINISTRATION

REPLICATION CLIENT

REPL_CLIENT_PRIV

SERVER ADMINISTRATION

REPLICATION SLAVE

REPL_SLAVE_PRI

SERVER ADMINISTRATION

SHOW DATABASES

SHOW_DB_PRIV

SERVER ADMINISTRATION

SHUTDOWN

SHUTDOWN_PRIV

SERVER ADMINISTRATION

SUPER

SUPER_PRIV

SERVER ADMINISTRATION

ALL[privileges]

 

SERVER ADMINISTRATION

USAGE

 

SERVER ADMINISTRATION

 

查看用户权限

show grants for root@localhost;

Reload 重新加载数据库的权限  flush开头的权限

replicationcloent VS replication slave

这两个权限有什么区别么？

replication client 执行  show master status； show slavestatus；权限的监控用的

replication slave 复制传输用的

shutdown 关机的权限

all 权限是除了grant option之外的权限

 

用户权限管理

用户和密码

MySQL用户存储在：mysql.user

查看一下MySQL里有哪些用户

select host,user from mysql.user;

和权限相关表

mysql.db

mysql.columns_priv

用户名和密码约束

MySQL用户名长度<=16 character

密码需要是可见的字符

MySQL用户指:用户名@来源两部分

 

更改密码和权限

给用户设置密码及过期规则

设置更改密码：

给当前用户改密码：set password=password(‘new_pass’);

给指定用户改密码：set passwordfor 'wwb'@'%'=password('new_pass');

直接更改授权表：

update mysql.user setpassword=password('new_pass') where user='wwb' and host='%';

flush privileges;

通过grant 改 grant usage on *.* to 'wwb'@'%'identified by 'new_pass';

 

添加用户

create user 'wwb'@'localhost';

grant all privileges on *.* to'wwb'@'localhost' with grant option;

 直接授权添加用户

grant select,insert,update,delete on *.*to 'wwb'@'192.168.247.11' identified by 'wwb';

 

让密码过期

指定一个用户的密码过期，用户上来只能先去改密码（调用set password使用）

alter user 'wwb'@'%' password expire;

 

添加权限

Grant授权可以达到

库级

表级

列

grant select(id),insert(id,name) on wwb.t1to 'wwb'@'%';

授予权限：库 表 列（表的名字要带上）

用户改名

Rename user old_user to new_user；

如

rename user 'wwb'@'%' to 'wwb_bak'@'%';

权限清理

Revoke 权限 on 对象 from 用户

show grants for 'wwb_bak'@'%';

+---------------------------------------------------------------------------------------------------------+

|Grants for wwb_bak@%                                                                                   |

+---------------------------------------------------------------------------------------------------------+

| GRANTSELECT ON *.* TO 'wwb_bak'@'%' IDENTIFIED BY PASSWORD'*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |

| GRANTSELECT (id), INSERT (name, id) ON `wwb`.`t1` TO 'wwb_bak'@'%'                                     |

+---------------------------------------------------------------------------------------------------------+

revokeinsert on wwb.t1 from 'wwb_bak'@'%';

QueryOK, 0 rows affected (0.00 sec)

 

>show grants for'wwb_bak'@'%';

+---------------------------------------------------------------------------------------------------------+

|Grants for wwb_bak@%                                                                                    |

+---------------------------------------------------------------------------------------------------------+

| GRANTSELECT ON *.* TO 'wwb_bak'@'%' IDENTIFIED BY PASSWORD'*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |

| GRANTSELECT (id) ON `wwb`.`t1` TO 'wwb_bak'@'%'                                                       |

+---------------------------------------------------------------------------------------------------------+

2 rowsin set (0.00 sec) 

 

Tips

利用来源精细化拒绝用户请求

服务账号：grant select，update，insert，delete on wwb.* to ‘admin’@‘192.168.10.%’ identified by ‘wwb’

但是实际接入的服务器是192.168.10.[11.12.13.14.15]

 某些原因想拒绝192.168.10.15怎么处理

想拒绝15怎么处理

直接创建一个没有权限的账号

grant usage on *.* to ‘admin’@‘192.168.10.15’identified by ‘wwb’