Graphical examples analyzing MySQL user management

This article brings you relevant knowledge about mysql, which mainly introduces related content about user management, including creating new users, viewing user information, renaming users, deleting users, etc. Let’s take a look at the content below, I hope it will be helpful to everyone.

Recommended learning: mysql video tutorial

1. User management

##1.1 Create a new user

create user '用户名'@'主机名' [identified by [password] '密码'];

Copy after login

The user name needs no explanation, it is the account we use when logging in
• The host name specifies which hosts the user we create can be on To log in, you can use the IP address, network segment, and host name. Local users can use localhost. Allowing login from any host can be represented by the wildcard character %.
• identified by is translated as verification by..., which is the password. The password is divided into plain text and cipher text. The plain text is directly identified by plus 'password', and the cipher text is identified by password 'password' ', the password keyword is added.
• If you use a clear text password, enter the 'password' directly, and it will be automatically encrypted by Mysql when inserted into the database; if you use an encrypted password, you need to use SELECT PASSWORD('password'); to obtain the ciphertext first, and then add it in the statement Add PASSWORD 'Private text'; if the "IDENTIFIED BY" part is omitted, the user's password will be empty.

1. Create user in clear text

   
   
   

##Create user in ciphertext

2. 
   

View the user table in the mysql library. It should be emphasized that only the root user can view this library. Normally Users do not have permission to query this library.

If we want an ordinary user to view this mysql library, we need to use grant for authorization. Note that authorization work can only be performed by the root user.

Remote login, that is, remote login to the local database from other hosts

View the mysql.user table

rename user '旧用户名'@'旧主机名' to '新用户名'@'新主机名';

Copy after login

drop user '用户名'@'主机名';

Copy after login

1. Modify current user password

set password = password('新密码')

2. Change the password of other users

set password for '用户名'@'主机名' = password('新密码');普通用户是无法修改其他用户密码的

##1.6 What to do if you forget your password

vim /etc/my.cnf   #修改mysql配置文件
[mysqld]
......
skip-grant-tables   #在mysqld模块下添加该配置
wq保存退出
systemctl restart mysqld   #重启mysql服务
mysql   #mysql直接登录
update mysql.user set authentication_string = password('新密码') where User='root';
flush privileges;
退出重新登陆
切记：修改完后一定将配置文件的skip-grant-tables注释或者删掉，不然再执行一次重启数据库，还是可以无密码登录，很危险

Copy after login

二、授权

2.1 用户授权

grant，授权，通常用于root用户授予普通用户一些执行权限，比如select,insert,update。

grant 权限列表（select|insert|delete|drop|update等等）on 数据库名.表名（*表示所有） to '用户名'@'主机名' [identified by '密码'];
若授权的用户不存在，mysql会先创建一个用户，然后进行授权操作

2.2 查看所授予的权限

show grants for '用户名'@'主机名';   #查看指定用户的权限show grants;   #查看当前用户权限

2.3 撤销权限

revoke 权限列表 on 库名.表名 from '用户名'@'主机名';   #从用户XXX撤销XX库.XX表的XX操作的权限

推荐学习：mysql视频教程

The above is the detailed content of Graphical examples analyzing MySQL user management. For more information, please follow other related articles on the PHP Chinese website!