MySQL高级（一）
一、用户与权限管理
1. 用户管理
1.1 登录MySQL服务器
mysql -h hostname|hostIP -P port -u username -p databaseName -e "SQL语句"
# 举例
mysql -h localhost -P 3306 -uroot -p mysql -e "select host, user from user"
1.2 创建用户
CREATE USER 用户名 [IDENTIFIED BY '密码'][,用户名 [INDENTIFIED BY '密码']];
CREATE USER zhang INDENTIFIED BY '123123'; # 默认host是 %
CREATE USER 'ZHANG'@'localhost' INDENTIFIED BY '123456';
1.3 修改用户
UPDATE mysql.user SET USER='li' WHERE USER='zhang';
FLUSH PRIVILEGES;
1.4 删除用户
# 方式1：使用DROP方式删除（推荐）
DROP USER user1[, user2 ...];
# 方式2：使用DELETE方式删除
DELETE FROM mysql.user WHERE host='localhost' AND USER='emily';
FLUSH PRIVILEGES;
1.5 密码管理
# 1.ALTER USER
ALTER USER USER()/username INDENTIFIED BY 'new password';
# 2.SET修改当前用户
SET PASSWORD='new_password';
# 该语句会自动将密码加密后再赋给当前用户
# 3.SET修改普通用户密码
SET PASSWORD FOR 'username'@'hostname'='new_password';
2.权限管理
2.1 权限列表
show privileges;
CREATE和DROP权限，可以创建新的数据库和表，或删除（移掉）已有的数据库和表。如果将 MySQL数据库中的DROP权限授予某用户，用户就可以删除MySQL访问权限保存的数据库。
SELECT、INSERT、UPDATE和DELETE权限，允许在一个数据库现有的表上实施操作。
SELECT权限，只有在它们真正从一个表中检索行时才被用到。
INDEX权限，允许创建或删除索引，INDEX适用于已有的表。如果具有某个表的CREATE权限，就可以在CREATE TABLE语句中包括索引定义。
ALTER权限，可以使用ALTER TABLE来更改表的结构和重新命名表。
CREATE ROUTINE权限用来创建保存的程序（函数和程序），ALTER ROUTINE权限用来更改和删除保存的程序， EXECUTE权限用来执行保存的 程序。
GRANT权限允许授权给其他用户，可用于数据库、表和保存的程序。
FILE权限使用户可以使用LOAD DATA INFILE和SELECT … INTO OUTFILE语句读或写服务器上的文件，任何被授予FILE权限的用户都能读或写MySQL服务器上的任何文件（说明用户可以读任何数据库目录下的文件，因为服务器可以访问这些文件）。
2.2 授予权限的原则
- 只授予能
满足需要的最小权限，防止用户干坏事。- 创建用户的时候
限制用户的登录主机，一般是限制成指定IP或者内网IP段。- 为每个用户
设置满足密码复杂度的密码。定期清理不需要的用户，回收权限或者删除用户。
2.3 授予权限
GRANT 权限1, 权限2，..., 权限n ON 数据库名称.表名称 TO 用户名@用户地址 [INDENTIFIED BY '密码口令'];
# 如果没有用户，就创建用户
2.4 查看权限
# 查看当前用户权限
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRRNT_USER();
# 查看某用户的全局权限
SHOW GRANTS FOR 'user'@'主机地址';
2.5 回收权限
注意：在将用户账户从user表删除之前，应该收回相应用户的所有权限
# 收回权限命令（须重新登录后生效）
REVOKE 权限1, 权限2, ..., 权限n ON 数据库名称.表名称 FROM 用户名@用户名地址;
3.角色管理
3.1 创建角色
CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]...
3.2 赋予角色权限
CREATE privileges ON table_name TO 'role_name'[@'host_name'];
3.3 查看角色的权限
mysql> SHOW GRANTS FOR 'manager';
+-------------------------------------------------------+
| Grants for manager@% |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `manager`@`%` |
| GRANT SELECT ON `demo`.`goodsmaster` TO `manager`@`%` |
| GRANT SELECT ON `demo`.`invcount` TO `manager`@`%` |
| GRANT SELECT ON `demo`.`settlement` TO `manager`@`%` |
+-------------------------------------------------------+
3.4 回收角色的权限
REVOKE priviles ON table_name FROM 'role_name';
3.5 删除角色
DROP ROLE role1 [, role2]...
3.6 给用户赋予角色
GRANT role1 [, role2, ...] TO user1 [, user2 ...];
3.7 激活角色
# 1.使用set default role
SET DEFAULT ROLE ALL TO 'kang'@'localhost';
# 2.将active_all_roles_on_login设置为ON
SET GLOBAL active_all_roles_on_login=ON;
# 对所有角色永久激活，用户拥有了赋予角色的所有权限
3.8 撤销用户的角色
REVOKE role from user;