核心开发权限
权限 | 列 | 可以针对的级别 |
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’