mysql implementation of adding users and authorizing

Query all users

Method 1:

mysql> select host, user, password from mysql.user;               -- 5.7版本之前的
mysql> select host, user, authentication_string from mysql.user;  -- 5.7版本之后的，包括5.7

Method 2:

mysql> select distinct concat('User: ''',user,'''@''',host,''';') as query from mysql.user;

Query user permissions

all means all permissions

select means only checking permissions

update means only changing permissions

delete means only Delete permissions, etc.

(Recommended online learning video tutorial: mysql video tutorial)

Method 1:

mysql> show grants for "user"@"host";
mysql> show grants for "root"@"localhost";

Method 2:

mysql> select * from mysql.user where user='root'\G;

Add authorized user(Newly created users do not have any permissions by default): Use the root user to log in to the database

The command format is as follows ：

mysql> create user "用户名"@"IP地址" identified by "密码";

mysql> create user "haidon" identified by "123456";       -- 此时密码为123456，host值为%。
mysql> create user "haidon"@"%" identified by "123456";   -- 此时密码为123456

Assign user permissions(Authorize users)

The command format is as follows:

mysql> grant 权限类型 on 数据库名.表名 to '用户名'@'ip地址' identified by '用户密码' with grant option;

Commonly used The permission types are as follows:

all privileges: all permissions.
select: Read permission.
create: Create permission.
delete: Delete permissions.
update: Update permissions.
drop: Delete database and data table permissions.

Allow access to all tables under all databases

mysql> grant all privileges on *.* to '用户名'@'指定ip' identified by '用户密码' ;

Allow access to all tables under the specified database

mysql> grant all privileges on test.* to '用户名'@'指定ip' identified by '用户密码' ;

Allow access to the specified table under the specified database

mysql> grant all privileges on test.test to '用户名'@'指定ip' identified by '用户密码' ;
 
mysql> grant all privileges on tornado.* to 'haidon'@'%' identified by '123456';

Revoke user permissions(Use root user operation)

mysql> revoke select on tornado.* from "haidon"@"%";
mysql> revoke all on tornado.* from "haidon"@"%";

Delete authorized users

mysql> drop user "haidon"@"%";                      -- 删除方法1
mysql> delete from mysql.user where user="haidon";  -- 删除方法2

RefreshPermissions

mysql> flush privileges;

Recommended related articles and tutorials: mysql tutorial

The above is the detailed content of mysql implementation of adding users and authorizing. For more information, please follow other related articles on the PHP Chinese website!