MySQL User management

MySQL Account management

• Before we logged in to MySQL we were all directly using the root user, the root user belongs to the Super Administrator in the database system, and has the right to do whatever you want to do with MySQL.

• If you are working with the database in a production environment, it is all directly connected to the root account, which is similar to dancing on the cliff side. So create a specific account, grant this account specific permissions to operate, and then connect to operations such as regular crud is the right path.

• MySQL account system: Depending on the permissions the account has, the MySQL account can be divided into the following

  • Service Instance level account:, start a mysqld, that is, a database instance, if a user, such as root, has the service instance level assignment permissions, then the account can delete all the databases, together with the tables in these libraries
  • Database level account: Perform all actions for adding and removing changes to a specific database
  • Data table level account: Perform additions and deletions to a specific table and all actions
  • Field-level permissions: Manipulating specific fields of some tables
  • Stored program-level accounts: operations for adding and checking stored programs

Note: When doing an account operation, you need to log in with the root account, which has the highest instance-level permissions. Account operations mainly include creating accounts, deleting accounts, changing passwords, authorizing permissions, and so on.

granting permissions

Need to use instance-level account login operation, take root for example

The main operations include:

• View All Users
• Change Password
• Delete User

View All Users

• All user and permission information is stored in the user table in the MySQL database
• View the structure of the user table

desc user;

• Main Field Description:
  • Host indicates which hosts are allowed to access
  • User indicates username
  • Authentication_string represents the password, the value after the encryption

View All Users

select host,user,authentication_string from user;

Results

mysql> select host,user,authentication_string from user;+-----------+------------------+-------------------------------------------+| host      | user             | authentication_string                     |+-----------+------------------+-------------------------------------------+| localhost | root             | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA || localhost | mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE || localhost | debian-sys-maint | *EFED9C764966EDB33BB7318E1CBD122C0DFE4827 |+-----------+------------------+-------------------------------------------+3 rows in set (0.00 sec)

Create an account, authorize

• Need to use instance-level account login operation, take root for example
• Common permissions include: Create, Alter, DROP, INSERT, UPDATE, delete, select
• If you assign all permissions, you can use the all privileges

Create Accounts & authorizations

grant 权限列表 on 数据库 to ‘用户名‘@‘访问主机‘ identified by ‘密码‘;

Example 1

Create an laowang account with a password 123456 that can only be accessed locally, and can only jing_dong operate on all tables in the database 读

Step1: Log in with Root

mysql -uroot -p回车后写密码，然后回车

Step2: Create an account and grant all permissions

grant select on jing_dong.* to ‘laowang‘@‘localhost‘ identified by ‘123456‘;

Description

• You can manipulate all tables in the Python database in the following ways:jing_dong.*
• The access host typically uses percent% to indicate that this account can access the database using any IP host login
• The access host can be set to localhost or a specific IP, which means that only native or specific hosts are allowed access

• See what permissions users have

show grants for [email protected];

Step3: Log out of root

quit

Step4: Login with Laowang account

mysql -ulaowang -p回车后写密码，然后回车

• Post-logon effects such as

Example 2

Create an laoli account with a password that 12345678 can be linked to any computer and jing_dong have all permissions on all tables in the database

grant all privileges on jing_dong.* to "laoli"@"%" identified by "12345678"

Account Operation Modification Permissions

grant 权限名称 on 数据库 to 账户@主机 with grant option;

Change Password

Using root login, modify the user table of the MySQL database

• Use the password () function for password encryption

   update user set authentication_ String=password ( ' new password ') where Span class= "Hljs-keyword" >user= ' username '; Example: update user set authentication_ String=password ( ' 123 ') where Span class= "Hljs-keyword" >user= ' Laowang ';        

• Note The Refresh permission is required after the modification is completed

  刷新权限：flush privileges

Remote Login (Danger-caution)

If you are using MySQL command in one Ubuntu to connect to another MySQL server remotely, you can do it in the following way, but this method only understands, do not use in the actual production environment

Modify the/etc/mysql/mysql.conf.d/mysqld.cnf file

vim /etc/mysql/mysql.conf.d/mysqld.cnf

Then restart MSYQL

service mysql restart

Connect test in another Ubuntu

If you are still not connected, the probable cause:

1) network does not pass

Ping xxx.xxx.xx.xxx to see if the network is healthy

2) See if the database is configured with the bind_address parameter

Local login database view my.cnf files and database current parameters show variables like ' bind_address ';

If bind_address=127.0.0.1 is set, you can only log on locally

3) See if the database has the skip_networking parameter set

If you set this parameter, you can only log in to the MySQL database locally

4) port designation is correct

Delete Account

• Syntax 1: Log in with Root

drop user ‘用户名‘@‘主机‘;例：drop user ‘laowang‘@‘%‘;

• Syntax 2: Log in with ROOT to delete data from the user table in the MySQL database

delete from user where user=‘用户名‘;例：delete from user where user=‘laowang‘;-- 操作结束之后需要刷新权限flush privileges

MySQL User management