mysql+用户server,Mysql 用户管理

用户创建

create user ‘user01‘@‘%‘ identified by ‘user01‘;

select host,user,authentication_string from mysql.user where user=‘user01‘;

+------+--------+-------------------------------------------+

| host | user | authentication_string |

+------+--------+-------------------------------------------+

| % | user01 | *C8B27DECB94F864D2395C39D43F5FCA5F82CD447 |

+------+--------+-------------------------------------------+

授权用户

grant select on db1.* to ‘user01‘@‘%‘  [WITH GRANT OPTION];

show privileges;

+----------------------------+---------------------------------------

| Privilege | Context

+----------------------------+---------------------------------------

| Alter | Tables

| Alter routine | Functions,Procedures

| Create | Databases,Tables,Indexes

| Create routine | Databases

| Create role | Server Admin

| Create temporary tables | Databases

| Create view | Tables

| Create user | Server Admin

| Delete | Tables

| Drop | Databases,Tables

| Drop role | Server Admin

| Event | Server Admin

| Execute | Functions,Procedures

| File | File access on server

| Grant option | Databases,Tables,Functions,Procedures

| Index | Tables

| Insert | Tables

| Lock tables | Databases

| Process | Server Admin

| Proxy | Server Admin

| References | Databases,Tables

| Reload | Server Admin

| Replication client | Server Admin

| Replication slave | Server Admin

| Select | Tables

| Show databases | Server Admin

| Show view | Tables

| Shutdown | Server Admin

| Super | Server Admin

| Trigger | Tables

| Create tablespace | Server Admin

| Update | Tables

| Usage | Server Admin

| XA_RECOVER_ADMIN | Server Admin

| SET_USER_ID | Server Admin

| ROLE_ADMIN | Server Admin

| RESOURCE_GROUP_USER | Server Admin

| RESOURCE_GROUP_ADMIN | Server Admin

| BINLOG_ADMIN | Server Admin

| SYSTEM_VARIABLES_ADMIN | Server Admin

| GROUP_REPLICATION_ADMIN | Server Admin

| CONNECTION_ADMIN | Server Admin

| REPLICATION_SLAVE_ADMIN | Server Admin

| ENCRYPTION_KEY_ADMIN | Server Admin

| BACKUP_ADMIN | Server Admin

| PERSIST_RO_VARIABLES_ADMIN | Server Admin

+----------------------------+---------------------------------------

file：允许用户在主机中读取和写入文件。5.7有个参数控制目录，8.0没有。

process：允许用户使用show processlist 。

super：允许用户中止其他客户端连接，更改服务器配置。kill，set

all：授权所有权限。

查看授权

show grants for ‘user01‘@‘%‘;

+-----------------------------------------+

| Grants for user01@% |

+-----------------------------------------+

| GRANT USAGE ON *.* TO `user01`@`%` |

| GRANT SELECT ON `db1`.* TO `user01`@`%` |

+-----------------------------------------+

回收权限

revoke select on db1.* from ‘user01‘@‘%‘;

忘记root密码

在配置文件中添加 mysqld_safe --skip-grant-tables

登录后修改user表

mysql> update user set authentication_string=password(‘xx‘) where user=‘root‘

mysql>flush privileges

重新登录后强制修改密码

mysql>alter user ‘root‘@‘localhost‘ identified by ‘root123‘;

8.0以下版本可以拷贝 其他user表覆盖。

角色

创建角色：create role ‘role01‘;

角色授权：grant select,insert,delete on db1.* to ‘role01‘;

用户授权：grant ‘role01‘ to ‘user01‘@‘%‘;

启用角色：set default role role01 to ‘user01‘@‘%‘;

Mysql 用户管理

标签：drop   启用   tab   所有权限   for   proxy   OLE   dex   temporary

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：https://www.cnblogs.com/emmm233/p/9951010.html