Summary and sharing of user creation and permission management in MySQL

This article brings you relevant knowledge about mysql. It mainly introduces user creation and permission management in MySQL. The article introduces the content in detail around the topic, which has certain reference value. , friends who need it can refer to it.

Recommended learning: mysql video tutorial

1. User management

atmysqlThere is a user table in the libraryYou can view the created users

1. Create a MySQL user

Note: Not available in MySQL To describe the user simply by username, the host must be added. Such as hhy@10.1.1.1

Basic syntax:

mysql> create user '用户名'@'被允许连接的主机名称或主机的IP地址' identified by '用户密码';
mysql> select user,host from mysql.user;

Case: Create a MySQL account, Username: hhy, user password: 123

mysql> create user 'hhy'@'localhost' identified by '123';
/*或*/
mysql> create user 'hhy'@'127.0.0.1' identified by '123';

Case: Create a MySQL account (requires opening a remote connection), host IP address: 192.1668.44.110, username: test, user password :123

mysql> create user 'test'@'192.1668.44.110' identified by '123';

Test:On the host with IP address 192.168.44.110

# yum install mysql -y
# mysql -h 192.168.44.110 -P 3306 -uharry -p
Enter password:123

Option description:192.168.44.110: MySQL server side IP address

yum installation mysql: represents the MySQL client installed
yum installation mysql-server: represents the installation of MySQL Server side

Case:Create a MySQL account (requires opening a remote connection), host IP network segment: 10.1.1.0, user name: jack, user password: 123

create user 'jack'@'192.168.44.%' identified by '123'

Case: Create a MySQL account (requires opening a remote connection), which is required to be open to all hosts, user name: root, user password: 123

create user 'root'@'%' identified by '123';

2. Delete MySQL user

Basic user:

mysql> drop user 'username'@'host name or IP address of the host';

Special Note:

If you do not specify the name of the host or the IP address of the host when deleting a user, all information about this account will be deleted by default.

Case: Delete the account hhy

drop user 'hhy'@'localhost';

Case: Delete the account jack

drop user 'jack'@'192.168.44.%';

Case:Create two harry accounts (localhost/10.1.1.23), and then delete one of them

mysql> create user 'harry'@'localhost' identified by '123';
mysql> create user 'harry'@'192.168.44.110' identified 

mysql> drop user 'harry'@'192.168.44.110';

Another way to delete the MySQL account

mysql> delete from mysql.user where user='root' and host='%';
mysql> flush privileges;

3. Modify the MySQL user

Special Note: MySQL user renaming can usually change two parts, one is the user's name, and the other is the host name or IP address of the host that is allowed to access.

Basic syntax:

mysql> rename user 旧用户信息 to 新用户信息;

Case: Change user 'root'@'%' to 'root'@'10.1.1. %'

mysql> rename user 'root'@'%' to 'root'@'10.1.1.%';

Case: Rename 'harry'@'localhost' to 'hhy'@'localhost'

mysql> create user 'tom'@'localhost' identified by '123';
mysql> rename user 'tom'@'localhost' to 'hhy'@'localhost';

Use update statement to update user information

mysql> update mysql.user set user='hhy',host='localhost' where user='tom' and host='localhost';

mysql> flush privileges;

2. Permission management

1. Permission description

All permission description

USAGE	无权限,只有登录数据库,只可以使用test或test_*数据库
ALL		所有权限
以下权限为指定权限
select/update/delete/super/replication slave/reload...
with grant option 选项表示允许把自己的权限授予其它用户或者从其他用户收回自己的权限

By default, if the with grant option is not specified when assigning permissions , means that this user cannot grant permissions to other users, but this permission allocation cannot exceed its own permissions.

2. Permission storage location (understand)

• mysql.user:The account number and password of all mysql users, as well as the user's access to the entire database Table permissions (*.*)
• mysql.db:Authorizations for non-mysql libraries are stored here (db.*)
• mysql.table_priv: Authorization of a certain table in a certain database (db.table)
• mysql.columns_priv :Authorization of a certain column in a certain table in a certain database (db.table.col1)
• mysql.procs_priv :Authorization of stored procedures in a certain library

3. Authorize users

Create database table:

create database java;
use java;
create table tb_student(
	id mediumint not null auto_increment,
	name varchar(20),
	age tinyint unsigned default 0,
	gender enum('男','女'),
	address varchar(255),
	primary key(id)
) engine=innodb default charset=utf8;

insert into tb_student values (null,'刘备',33,'男','湖北省武汉市');
insert into tb_student values (null,'貂蝉',18,'女','湖南省长沙市');
insert into tb_student values (null,'关羽',32,'男','湖北省荆州市');
insert into tb_student values (null,'大乔',20,'女','河南省漯河市');
insert into tb_student values (null,'赵云',25,'男','河北省石家庄市');
insert into tb_student values (null,'小乔',18,'女','湖北省荆州市');

Basic syntax:

mysql> grant 权限1,权限2 on 库.表 to 用户@主机
mysql> grant 权限(列1,列2,...) on 库.表 to 用户@主机

Library.Table representation method: *.* represents all data tables in all databases, db_itheima.* represents all data in the db_itheima database Table, db_itheima.tb_admin, represents the tb_admin table in the db_itheima database

Case: Assign query permissions to the java database to the thy account

mysql> grant select on java.* to 'hehanyu'@'192.168.44.%';

mysql> flush privileges;

Case : Assign permissions to the java.tb_student data table to the hehanyu account (required to only change the age field)

mysql> grant update(age) on java.tb_student to 'hehanyu'@'192.168.44.%';

mysql> flush privileges;

Case: Add a root@% account, and then assign all permissions

create user 'root'@'%' identified by '123';
grant all on *.* to 'root'@'%';
flush privileges;

4. Query user permissions

Query current user permissions:

mysql> show grants;

Query other user permissions:

mysql> show grants for '用户名称'@'授权的主机名称或IP地址';

5. with grant option option

mysql> grant all on *.* to 'amy'@'10.1.1.%' identified by '123' with grant option;
mysql> grant all on *.* to 'harry'@'10.1.1.%' identified by '123';

As shown in the above command: amy has the function of granting permissions, but harry does not have the function of granting permissions.

If grant authorization does not have the with grant option option, it cannot authorize other users.

6.revoke to recover permissions

Basic syntax:

revoke 权限 on 库.表 from 用户;
查看hehanyu用户权限
mysql> show grants for 'hehanyu'@'192.168.44.%';
撤消指定的权限
mysql> revoke update on java.tb_student from 'tom'@'192.168.44.%';
撤消所有的权限
mysql> revoke select on java.* from 'tom'@'192.168.44.%';

推荐学习：mysql视频教程

The above is the detailed content of Summary and sharing of user creation and permission management in MySQL. For more information, please follow other related articles on the PHP Chinese website!