The method of mysql user creation and authorization

This article focuses on "how to create and authorize mysql users". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the method of mysql user creation and authorization.

Preface

When mysql is installed by default, there is a root user who has the highest permissions. If you want to open the data in the database to others, generally, you will not open root users to others. Normally, you will create new users for others to use, and this user's permissions can be controlled by yourself.

I. users create users

Command

CREATE USER 'username'@'host' IDENTIFIED BY' password'

Parameter description:

(1) username: the user name you want to create

(2) host: the host address that can connect to this data. If you want any host to connect to this server, you can use the wildcard%.

(3) password: user's login password

Case

Create user: zs, password: 123, host: 196.168.64.12

CREATE USER 'zs'@'196.168.64.12' IDENTIFIED BY' 123'

Create user: zs, password: 123. any host can log in to the database

CREATE USER 'zs'@'%' IDENTIFIED BY' 123'

Note: if you want to set up a user to have multiple ip login access, you only need to repeat the practice in case (1). The account number and password are the same, but the host ip is different.

Edit user

After you have created the user, you can do the following if you want to change the user's password

Command

SET PASSWORD FOR 'username'@'host' = PASSWORD (' newpassword')

For example

SET PASSWORD FOR 'zs'@'196.168.64.12' = PASSWORD ("123456")

If you are currently logged in, you can use the following command

SET PASSWORD = PASSWORD ("123456"); delete user

If you want to delete the created user, you can use the following command

Drop user 'user name' @ 'ip'

For example:

Drop user 'zs'@'192.168.92.14'; II. Authorization

Command

GRANT privileges ON databasename.tablename TO 'username'@'host'

Note:

(1) privileges: the user's operation permission, such as SELECT,INSERT,UPDATE,DELETE, etc. If you want to grant the permission, use ALL

(2) databasename: database name

(3) tablename: database table, which can be expressed as * if you want to grant the user the corresponding operation permissions on all databases and tables, such as *. *

(4) username: the user to be authorized

(5) host:ip

Examples

Give the user zs, and grant the permission to add, delete, modify and query the user table user in the database test1.

GRANT ALL ON test1.user TO 'ZS'@'192.169.12.12'

Give the user zs, and grant the permission to check and modify the user table user in the database test1.

GRANT SELECT,UPDATE ON test1.user TO 'ZS'@'192.169.12.12'

Give the user zs and grant all permissions to all tables in the database

GRANT ALL ON *. * TO 'ZS'@'192.169.12.12'; revokes permissions

Command

REVOKE privilege ON databasename.tablename FROM 'username'@'host'

Note:

(1) privileges: the user's operation permission, such as SELECT,INSERT,UPDATE,DELETE, etc. If you want to grant the permission, use ALL

(2) databasename: database name

(3) tablename: database table, which can be expressed as * if you want to grant the user the corresponding operation permissions on all databases and tables, such as *. *

(4) username: the user to be authorized

(5) host:ip

Examples

After granting the user zs the permission to check and modify the user table user in the database test1, you want to revoke this permission.

REVOKE SELECT,UPDATE ON test1.user TO 'ZS'@'192.169.12.12'; III. Other commands

View all users in the database

SELECT DISTINCT CONCAT ('User:'', user,'''@''',host,''';') AS query FROM mysql.user

View the permissions of a specific user in the database

Show grants for 'cactiuser'@'%'

Set and change user password

SET PASSWORD FOR 'username'@'host' = PASSWORD (' newpassword')

If it is used by the current login user:

SET PASSWORD = PASSWORD ("newpassword")

Mysql > SET PASSWORD FOR 'finley'@'%' = PASSWORD ("123456"); now that you have a better understanding of "how to create and authorize mysql users", you might as well do it in practice! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!