用户与权限管理
????用户管理
MySQL用户分为普通用户和root用户，提供了许多语句来管理包括登录、退出MySQL服务器、创建用户、删除用户、密码管理和权限管理等内容。
⛵登录MySQL服务器
mysql -h hostName|hostIP -P port -u userName -p DataBaseName -e "SQL语句"
创建用户
查看当前用户：
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select host, user from user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | root |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
+-----------+------------------+
4 rows in set (0.00 sec)
创建命令：
create user '
mysql> create user 'hikaru'@'%' identified by 'password';
Query OK, 0 rows affected (0.04 sec)
mysql> use mysql;
Database changed
mysql> select host, user from user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | hikaru |
| % | root |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
+-----------+------------------+
5 rows in set (0.00 sec)
-
mysql数据库的user表是由host和user组成的联合主键
所以再创建一个限制本地连接的用户也是ok的：
mysql> create user -> 'hikaru'@'localhost' identified by 'password'; Query OK, 0 rows affected (0.00 sec) mysql> select host, user from user; +-----------+------------------+ | host | user | +-----------+------------------+ | % | hikaru | | % | root | | localhost | hikaru | | localhost | mysql.infoschema | | localhost | mysql.session | | localhost | mysql.sys | +-----------+------------------+ 6 rows in set (0.00 sec)
修改用户
mysql> update user set user='tod4' where user='hikaru' and host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select host, user from user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | hikaru |
| % | root |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | tod4 |
+-----------+------------------+
6 rows in set (0.00 sec)
❓相当于直接对表的记录进行修改，属于DDL语句，需要刷新权限：
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
删除用户
Drop
mysql> drop user 'tod4';
ERROR 1396 (HY000): Operation DROP USER failed for 'tod4'@'%'
mysql> drop user 'tod4'@'localhost';
Query OK, 0 rows affected (0.00 sec)
Delete（DDL语句需要刷新权限）
delete from user where user = xxx and host = xxx;
flush privileges;
不推荐使用Delete删除用户，因为创建的用户不仅在user表中也在其他表中添加了记录，因此只删除user表会有信息残留
修改用户密码
修改自己的密码
① 使用Alter语句修改普通用户密码（推荐）
ALTER USER USER() IDENTITY BY 'new_password';
② 使用set语句修改普通用户密码
SET PASSWORD='new_password'
修改其他用户的密码
① 使用Alter语句修改普通用户密码（推荐）
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password'
② 使用set语句修改普通用户密码
SET PASSWORD FOR 'username'@'host' IDENTIFIED BY 'new_password'
????权限管理
允许做MySQL赋予权限以内的事情，不可以越界。比如只可以执行select、只允许从某一台机器上登录MySQL等等。
权限列表
mysql> show privileges;
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege | Context | Comment |
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Alter | Tables | To alter the table |
| Alter routine | Functions,Procedures | To alter or drop stored functions/procedures |
| Create | Databases,Tables,Indexes | To create new databases and tables |
| Create routine | Databases | To use CREATE FUNCTION/PROCEDURE |
| Create temporary tables | Databases | To use CREATE TEMPORARY TABLE |
| Create view | Tables | To create new views |
| Create user | Server Admin | To create new users |
| Delete | Tables | To delete existing rows |
| Drop | Databases,Tables | To drop databases, tables, and views |
| Event | Server Admin | To create, alter, drop and execute events |
| Execute | Functions,Procedures | To execute stored routines |
| File | File access on server | To read and write files on the server |
| Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess |
| Index | Tables | To create or drop indexes |
| Insert | Tables | To insert data into tables |
| Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) |
| Process | Server Admin | To view the plain text of currently executing queries |
| Proxy | Server Admin | To make proxy user possible |
| References | Databases,Tables | To have references on tables |
| Reload | Server Admin | To reload or refresh tables, logs and privileges |
| Replication client | Server Admin | To ask where the slave or master servers are |
| Replication slave | Server Admin | To read binary log events from the master |
| Select | Tables | To retrieve rows from table |
| Show databases | Server Admin | To see all databases with SHOW DATABASES |
| Show view | Tables | To see views with SHOW CREATE VIEW |
| Shutdown | Server Admin | To shut down the server |
| Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. |
| Trigger | Tables | To use triggers |
| Create tablespace | Server Admin | To create/alter/drop tablespaces |
| Update | Tables | To update existing rows |
| Usage | Server Admin | No privileges - allow connect only |
+-------------------------+---------------------------------------+-------------------------------------------------------+
31 rows in set (0.00 sec)
| 权限分布 | 可能设置的权限 |
|---|---|
| 表权限 | |
| 列权限 | |
| 过程权限 |
查看授权
看自己的：
show grants;
看别人的：
show grants for '...'@'...';
授予权限
授予权限分为两种：角色授予用户和直接给用户授权
直接给用户授权
grant select, update on dbtest.* to 'test'@'%';
grant all privileges on *.* to '';
all privileges并不意味着和root等同的权限，如赋予别人权限的权限就没有给
需要使用WITH GRANT OPTION选项
收回权限
revoke all privileges on *.* from '...'@'...'
权限表
有缘再补。。。
????️访问控制
有缘再补。。
角色管理 8.0新特性
创建角色
create role 'rolename'@'host';
不写登录主机名默认%
给角色赋予权限
grant * on * to 'rolename'@'host';
查看角色权限
show grants for 'rolename'@'host';
收回角色权限
revoke * on * from 'rolename'@'host';
删除角色
drop role 'rolename';
向用户授予角色
grant 'rolename'@'host' to 'username'@'host';
Mysql创建了角色之后都是没有被激活的，也就是不能用，必须要手动激活才能获得相应的权限
激活角色
激活角色有两种方式
①set default role
set default role all to 'username'@'host';
②将active_all_roles_on_login设置为ON
show variables like 'activate';
set global active_all_roles_on_login ON;
撤销角色
revoke 'rolename'@'host' from 'username'@'host';
