这篇博客详细介绍了MySQL中用户权限的管理，包括创建、修改和删除用户，设置与管理用户密码，以及密码过期和重用策略。此外，还讲解了权限的授予、收回以及通过角色进行权限管理，强调了访问控制的层次，如user、db、table_priv和columns_priv表。最后提到了系统变量的全局与会话区别。
一、用户权限的创建，修改，删除：
- 用户权限的创建
create user 'liu01' identified by '32332222222';
查看结果是否成功
select host,user from user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | liu01 |
| % | root |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
+-----------+------------------+
5 rows in set (0.00 sec)
刷新权限
flush privileges;
最后看是否登录成功
- 修改用户名字
update user set user='liu03' where user='liu02';
查看结果
select host,user from user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | liu01 |
| % | liu03 |
| % | root |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
+-----------+------------------+
6 rows in set (0.00 sec)
flush privileges;
最后看是否登录成功
-
删除用户
第一种是用drop
drop user 'liu03';
或者使用delete
delete from mysql.user where user='liu03' and host='localhost';
最后查看登录是否失败
二、用户的密码设置与管理
1.修改当前用户密码
1.1 set方法修改（MySQL5版本）修改当前用户的密码（不推荐，权限表在删不干净）
set password = password('21212');
1.2 alter user 方法修改(推荐）
alter user user() identified by '92121';
1.3 set方法修改（推荐）
set password='922121';
2.修改其他用户密码（root用户可用，%表示全主机可登录）
2.1 alter user 方法修改(推荐）
alter user 'liu01'@'%' identified by '9rer';
刷新权限
flush privileges;
测试登录
2.2 set方法修改（推荐）
set password for 'liu01' ='9ewew';
刷新权限
flush privileges;
测试登录
2.3 使用update的方法修改（不推荐，权限表在删不干净）
update MySQL.user set authentication_string=password("321312") where user="liu01" and host = "hostname";
3.密码过期设置
3.1 配置文件my.cnf
[mysqld]
dedault_password_lifetime= 180 #建立全局策略，设置密码每隔180天过期
3.2 手动设置指定时间过期方式：单独设置
#设置liu01账号密码每90天过期
create user 'liu01'@'%' password expire interval 90 day;
alter user 'liu01'@'%' password expire interval 90 day;
#设置liu01账号密码永不过期
create user 'liu01'@'%' password expire never;
alter user 'liu01'@'%' password expire never;
#设置liu01账号密码使用默认的过期策略
create user 'liu01'@'%' password expire default;
alter user 'liu01'@'%' password expire default;
3.3 手动设置密码重用方式（全局）
[mysqld]
password_history=6#密码重用最近数量
password_reuse_interval=365#密码最近重用时间
3.4 手动设置密码重用方式（单独）
#密码重用最近数量
create user 'liu01'@'%' password history 6;
alter user 'liu01'@'%' password history 6;
#密码最近重用时间
create user 'liu01'@'%' password reuse interval 365 day;
alter user 'liu01'@'%' password reuse interval 365 day;
#密码最近重用时间和密码重用最近数量
create user 'liu01'@'%'
password history 6
reuse interval 365 day;
三、权限管理与访问控制
1.授予权限
分为角色赋予用户给用户授权和直接给用户授权。
1.1直接授权命令
赋予test库下dbtest01表增删改查的权限
grant select,insert,delete,update on test.dbtest01 to 'liu01'@'%';
赋予test库下dbtest01表所有权限
grant all privileges on test.dbtest01 to 'liu01'@'%';
查看权限
show grants;
+----------------------------------------------------------------------------+
| Grants for liu01@% |
+----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `liu01`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test01`.`dbtest01` TO `liu01`@`%` |
+----------------------------------------------------------------------------+
2 rows in set (0.00 sec)
赋予用户给予其他用户的权限WITH GRANT OPTION
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `test01`.`dbtest01` TO `root`@`%` WITH GRANT OPTION;
1.2 收回授权命令
revoke SELECT, INSERT, UPDATE, DELETE ON `test01`.`dbtest01` from `liu01`@`%`;
1.2创建角色授权命令
创建角色
create role 'manager'@'%';
查看是否成功
select host,user from user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | liu01 |
| % | manager |
| % | root |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
+-----------+------------------+
6 rows in set (0.00 sec)
赋予角色权限
grant select,insert,delete,update on atguigudb.* to 'manager'@'%';
Query OK, 0 rows affected (0.00 sec)
查看角色权限
mysql> show grants for 'manager'@'%';
+------------------------------------------------------------------------+
| Grants for manager@% |
+------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `manager`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `atguigudb`.* TO `manager`@`%` |
+------------------------------------------------------------------------+
2 rows in set (0.00 sec)
将角色的权限赋予给某个用户
mysql> grant 'manager' to 'liu01'@'%';
Query OK, 0 rows affected (0.00 sec)
查看是否成功
mysql> show grants for 'liu01'@'%';
+----------------------------------------------------------------------------+
| Grants for liu01@% |
+----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `liu01`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test01`.`dbtest01` TO `liu01`@`%` |
| GRANT `manager`@`%` TO `liu01`@`%` |
+----------------------------------------------------------------------------+
3 rows in set (0.00 sec)
用这个用户登录查看数据库时，会不成功，需要激活
mysql> use atguigudb;
ERROR 1044 (42000): Access denied for user 'liu01'@'%' to database 'atguigudb'
mysql>
方式一：在这个用户下激活
mysql> set default role 'manager'@'%' to 'liu01'@'%';
Query OK, 0 rows affected (0.01 sec)
退出再登录查看是否成功
mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| `manager`@`%` |
+----------------+
1 row in set (0.00 sec)
方式二：将activate_all_roles_on_login全局变量设置为ON，之后的角色会自动激活，这种以命令方式没有写进配置文件的方式会在MySQL重启或者宕机后重启时恢复配置文件的默认值，但是在没有宕机之后的其他会话中会保持其设置
mysql> show variables like 'activate_all_roles_on_login';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | OFF |
+-----------------------------+-------+
1 row in set (0.01 sec)
mysql> set global activate_all_roles_on_login=on;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'activate_all_roles_on_login';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | ON |
+-----------------------------+-------+
1 row in set (0.00 sec)
回收角色
mysql> revoke 'manager' from 'liu01'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'liu01'@'%';
+----------------------------------------------------------------------------+
| Grants for liu01@% |
+----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `liu01`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test01`.`dbtest01` TO `liu01`@`%` |
+----------------------------------------------------------------------------+
2 rows in set (0.00 sec)
销毁角色
drop role ';manager'@'%';
设置强制角色（mandatory role)是给每个创建账户的默认角色，不需要手动设置，强制角色无法被revoke或者drop；
方式一：服务启动前设置
[mysqld]
mandatory_roles='liu01'@"%';
方式二： 运行时设置,global这种设置变量都会这样session更是如此，persist可以持久化，写进磁盘
set persist mandatory_roles='liu01'@"%'; #重启系统时失效
set global mandatory_roles='liu01'@"%'; #重启系统时失效
四、访问控制
MySQL用户在登录时，首先会检查优user表，权限如果在user表被授予，那么MySQL就会在继续检查db（database）表，db表的权限是限定源数据库层级的,MySQL会继续检查table_priv（库的表）表，再一层级就是columns_priv（列）表。用show privileges命令查看。
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 role | Server Admin | To create new roles |
| 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 |
| Drop role | Server Admin | To drop roles |
| 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 |
| XA_RECOVER_ADMIN | Server Admin | |
| SHOW_ROUTINE | Server Admin | |
| SET_USER_ID | Server Admin | |
| RESOURCE_GROUP_USER | Server Admin | |
| APPLICATION_PASSWORD_ADMIN | Server Admin | |
| SYSTEM_VARIABLES_ADMIN | Server Admin | |
| AUDIT_ADMIN | Server Admin | |
| SERVICE_CONNECTION_ADMIN | Server Admin | |
| CLONE_ADMIN | Server Admin | |
| PERSIST_RO_VARIABLES_ADMIN | Server Admin | |
| FLUSH_USER_RESOURCES | Server Admin | |
| BINLOG_ADMIN | Server Admin | |
| ROLE_ADMIN | Server Admin | |
| SESSION_VARIABLES_ADMIN | Server Admin | |
| BINLOG_ENCRYPTION_ADMIN | Server Admin | |
| FLUSH_STATUS | Server Admin | |
| SYSTEM_USER | Server Admin | |
| ENCRYPTION_KEY_ADMIN | Server Admin | |
| REPLICATION_SLAVE_ADMIN | Server Admin | |
| GROUP_REPLICATION_ADMIN | Server Admin | |
| BACKUP_ADMIN | Server Admin | |
| RESOURCE_GROUP_ADMIN | Server Admin | |
| FLUSH_OPTIMIZER_COSTS | Server Admin | |
| TABLE_ENCRYPTION_ADMIN | Server Admin | |
| FLUSH_TABLES | Server Admin | |
| CONNECTION_ADMIN | Server Admin | |
| INNODB_REDO_LOG_ENABLE | Server Admin | |
| INNODB_REDO_LOG_ARCHIVE | Server Admin | |
| REPLICATION_APPLIER | Server Admin | |
+----------------------------+---------------------------------------+-------------------------------------------------------+
62 rows in set (0.01 sec)
五、系统变量
系统变量分为全局变量和会话变量，而作为变量可以是全局变量也可以是会话变量，default_storage_engine就是刚建立会话时系统给的,但是可以在会话中建表时更改，亦可以在set @session default_storage_engine=myisam更改，用这种方式更改后只会影响本次会话的建表结果，其他连接会话还是使用系统默认，set @@global default_storage_engine=myisam更改是影响后面的会话变量，前提是系统没有重启。重启之后用得还是配置文件里的默认变量，就是说这种设置只是在内存中保存，并不是持久化到磁盘中了。
扫一扫
1288
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
