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,create routine权限

管理权限-表级别

权限	列	可以针对级别
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

 

管理权限-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

 

replication client VS replication slave

Replication client 执行  show master status； show slave status；权限的监控用的

Replication slave 复制传输用的

Shutdown 关机的权限

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

账号管理

(product)root@localhost [(none)]> help create user   #查看帮助文档
(product)root@localhost [(none)]> create user wwb@192.168.244.128 identified by '123456' ; #创建wwb用户只能从128这台机器登陆
Query OK, 0 rows affected (0.00 sec)
(product)root@localhost [(none)]> create user 'wwb'@'192.168.244.%' identified by '123456'; #创建244段都可以登陆的用户
Query OK, 0 rows affected (0.00 sec)
(product)root@localhost [(none)]> create user 'wwb'@'%' identified by '123456'; #创建可以从任何来源登陆的wwb账号
Query OK, 0 rows affected (0.00 sec)

 更改密码和权限

(product)wwb@localhost [(none)]> set password=password('mysql'); #给当前用户修改密码
Query OK, 0 rows affected (0.00 sec)
(product)root@localhost [(none)]> set password for 'wwb'@'192.168.244.128'=password('123456'); #给指定用户修改密码
Query OK, 0 rows affected (0.00 sec)
(product)root@localhost [(none)]> update mysql.user set password=password('123456') where user = 'wwb' and host = '%'; #直接修改授权表
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
(product)root@localhost [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)
(product)root@localhost [(none)]> grant usage on *.* to 'wwb'@'%' identified by 'mysql'; #通过grant更改密码
Query OK, 0 rows affected (0.01 sec)

忘记root密码

1.Skip-grants-table需要重启mysql

2.现有的user表拷贝到别的地方 核心是让mysqld重新加载一下权限表

密码过期

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

(product)root@localhost [(none)]> alter user 'wwb'@'%' password expire;
Query OK, 0 rows affected (0.01 sec)

5.6以后的客户端 能把用户的密码搞过期

添加权限

通过grant添加

grant相应的权限to用户

(product)root@localhost [(none)]> grant insert on wwb.* to 'wwb'@'192.168.244.128';
Query OK, 0 rows affected (0.00 sec)

grant授权可以达到 库级 表级 列

grant select（col1），insert（col1，col2） on wubx.tb1 to’wub‘@‘%’；

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

用户改名

rename user old_user to new_user；

(product)root@localhost [(none)]> rename user 'wwb'@'192.168.244.128' to 'wwb_bak'@'192.168.244.128';
Query OK, 0 rows affected (0.01 sec)

权限清理

revoke权限on对象from用户

(product)root@localhost [(none)]> revoke insert on wwb.* from 'wwb_bak'@'192.168.244.128';
Query OK, 0 rows affected (0.00 sec)

(product)root@localhost [(none)]> show grants for 'wwb_bak'@'192.168.244.128';
+----------------------------------------------------------------------------------------------------------------------+
| Grants for wwb_bak@192.168.244.128                                                                                   |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wwb_bak'@'192.168.244.128' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)