How to implement user creation, Modification, deletion and Authorization by mysql 03/01 Update SLTechnology News&Howtos

How to implement user creation, Modification, deletion and Authorization by mysql

2025-03-01 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

This article mainly introduces mysql how to achieve user creation, modification, deletion and authorization operations, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

1. If the mysql command connects without-h, it means that user@localhost connects to the local localhost database server, and the ip after-h is the IP of the database server.

2. To make all ip connected, add @'%'or no @ after the user name when create user (the default is @'% 'without @)

3. Mysql executes grant for authorization, and the permissions are appended, so don't worry about overwriting the previous ones.

4. Grant can create users, authorize and change passwords at the same time

5. It is recommended to use create to create users, because create can only create users, not authorize them. Mysql does not have connect create session permissions like oracle. Mysql automatically has connect create session permissions as long as users create.

6. It is recommended to use alter user to change the password, because alter does not involve permissions. Although grant can change the password, the grant command should have permission options.

7. If the user name is the same, the corresponding network segment of the host will be the same. The user whose name is the same shall prevail.

That is to say, the following two rules: who executes first and who takes effect first? that is to say, whose password is used after mysql-u test1-p to log in.

Grant select on test1.* to test123@'192.168.0.0/255.255.0.0' identified by "12345678"

Grant select on test1.* to test123@'192.168.10.0/255.255.255.0' identified by "1234567"

8. Authorization executed by grant does not require flush privileges to take effect. Authorization executed by update mysql.user table requires flush privileges to take effect.

Create a user

Mysql > help create user

Mysql > help grant

Mysql > create user 'user1'@'192.168.10.0/255.255.255.0' identified by' 123456'

Mysql > grant select,update on db1.* to 'user2'@'192.168.10.0/255.255.255.0' identified by' 123456 authorization-created and authorized at the same time

Modify the password

Mysql > help alter user

Mysql > help grant

Mysql > help set password

Mysql > alter user 'user1'@'192.168.10.0/255.255.255.0' identified by' 666666'

Mysql > grant select on mysql.user to 'user1'@'192.168.10.0/255.255.255.0' identified by' 777777 authorization was granted while changing the password

Mysql > set password for 'user1'@'192.168.10.0/255.255.255.0'=password (' 888888')

SET PASSWORD is deprecated as of MySQL 5.7.6 and will be removed in a future MySQL release. ALTER USER is now the preferred statement for assigning passwords.

Starting with MySQL 5.7.6, SET PASSWORD is not recommended and will be discarded in future versions of MySQL. ALTER USER is now the preferred declaration for changing passwords.

Authorization

Mysql > help grant

Mysql > grant select,insert,update on *. * to 'user1'@'192.168.10.0/255.255.255.0';-- is only authorized and does not involve changing the password

Delete user

Mysql > help drop user

Mysql > drop user 'user1'@'192.168.10.0/255.255.255.0'

Password-related parameters

Mysql > SHOW VARIABLES LIKE'% password%'

Mysql > CREATE USER 'mytest'@'192.168.20.0/255.255.255.0' identified by' 123456'

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

Mysql > show variables like'% validate_password_policy%'

+-+ +

| | Variable_name | Value |

+-+ +

| | validate_password_policy | MEDIUM |

+-+ +

1 row in set (0.01 sec)

Mysql > set global validate_password_policy=0

Mysql > show variables like'% validate_password_length%'

+-+ +

| | Variable_name | Value |

+-+ +

| | validate_password_length | 8 |

+-+ +

1 row in set (0.00 sec)

Mysql > set global validate_password_length=1

In the following experiment, the DB server ip is 192.168.10.101. Using mysql login locally without adding-h means using user@localhost login, and the DB server ip must be followed by-h.

If all ip logins are allowed, the user name after create user does not add @ or the user name after create user is added @'%'.

[root@mydb ~] # ifconfig | grep 'inet addr'

Inet addr:192.168.10.101 Bcast:192.168.10.255 Mask:255.255.255.0

Inet addr:127.0.0.1 Mask:255.0.0.0

Inet addr:192.168.122.1 Bcast:192.168.122.255 Mask:255.255.255.0

Mysql > CREATE USER 'mytest01'@'192.168.20.0/255.255.255.0' identified by' 123456'

Query OK, 0 rows affected (0.01 sec)

Mysql > CREATE USER 'mytest02'@'192.168.10.0/255.255.255.0' identified by' 123456'

Query OK, 0 rows affected (0.00 sec)

Mysql > CREATE USER 'mytest03'@'*' identified by' 123456'

Query OK, 0 rows affected (0.00 sec)

[root@mydb] # mysql-u mytest01-p123456