Shulou(Shulou.com)06/01 Report--

This paper gives an example to describe the principle and implementation method of Mysql account management. Share with you for your reference, the details are as follows:

Account management

When operating a database in a production environment, you can never use a root account to connect. Instead, create a specific account, grant the account specific operation permissions, and then connect to operate. The main operation is the crud of the data.

MySQL account system: according to the authority of the account, MySQL accounts can be divided into the following categories

Service instance-level account: a mysqld is launched, that is, a database instance; if a user, such as root, has the permission assigned at the service instance level, then the account can delete all databases, along with the tables in these libraries

Database-level account: perform all operations to add, delete, modify and query a specific database

Data table-level account: perform all operations such as additions, deletions, modifications and queries on a specific table

Field-level permissions: operate on specific fields of some tables

Stored program level account: add, delete, modify and query the stored program

The operation of account mainly includes creating account, deleting account, changing password, authorizing authority and so on.

Note:

To operate an account, you need to log in using a root account, which has the highest instance-level permissions.

Database-level operation permissions are usually used

Grant authority

You need to log in with an instance-level account. Take root as an example.

The main actions include:

View all users

Modify the password

Delete user

1. View all users

All user and permission information is stored in the user table of the mysql database

View the structure of the user table

Desc user

Main field description:

Host indicates the host to which access is allowed

User represents the user name

Authentication_string represents the password and is the encrypted value

View all users

Select host,user,authentication_string from user

Result

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) 2. Create accounts, authorizations

You need to log in with an instance-level account. Take root as an example.

Common permissions include: create, alter, drop, insert, update, delete, select

If you assign all permissions, you can use all privileges

2.1 create account & authorize grant permission list on database to 'username' @ 'access host' identified by 'password; 2.2 example 1

Create an laowang account with a password of 123456, which can only be accessed locally, and can only be read to all tables in the jing_dong database

Step1: logging in using root

Mysql-uroot-p enter the password, and then enter

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 by: jing_dong.*

Access hosts usually use the% sign to indicate that this account can use any ip host login to access this database

The access host can be set to localhost or specific ip, indicating that only local or specific hosts are allowed to access

Check what permissions the user has

Show grants for laowang@localhost

Step3: login to log out of root

Quit

Step4: log in using your laowang account

Mysql-ulaowang-p enter the password, and then enter

The effect after login is as follows

2.3 example 2

Create a laoli account with a password of 12345678, which can be linked to any computer and have all permissions on all tables in the jing_dong database.

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

Account operation 1. Modify permissions grant permission name on database to account @ host with grant option

two。 Modify the password

Use root login to modify the user table of the mysql database

Password encryption using the password () function

Update user set authentication_string=password ('new password') where user=' user name'; example: update user set authentication_string=password ('123') where user='laowang'

Note that permissions need to be refreshed after the modification is completed.

Refresh permission: flush privileges3. Remote login (dangerous and prudent use)

If you use the msyql command in one Ubuntu to remotely connect to another mysql server, you can do it in the following ways, but this method is only good to understand and should not be used in the actual production environment.

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

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

Then restart msyql

Service mysql restart

Test the connection in another Ubuntu

If you still can't connect, the possible reasons are:

The network is not connected.

Through ping xxx.xxx.xx.xxx, you can find out whether the network is normal.

2) check whether the database is configured with bind_address parameters

Log in to the database locally to view the my.cnf file and the current database parameter show variables like 'bind_address'

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

3) check whether the database has set the skip_networking parameter

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

4) whether the port is specified correctly

4. Delete an account

Syntax 1: log in using root

Drop user 'user name' @ 'host'; for example: drop user 'laowang'@'%'

Syntax 2: log in using root to delete the data in the user table of the mysql database

Delete from user where user=' user name'; for example: refresh permission flush privileges after the end of delete from user where user='laowang';-- operation

It is recommended to use Syntax 1 to delete users. If you fail to delete users using Syntax 1, use Syntax 2.

3. Forget the root account password how to do!

Usually it's not our turn to manage the root account, so don't sell it blindly.

Solution for forgetting mysql root user password (skip-grant-tables):

Skip-grant-tables

As the name implies, when the database starts, jump permissions table restrictions, do not need to verify the password, directly log in.

Note:

This situation is only used if you forget the root password and have to restart the database. The current network environment is used with caution, the database needs to be restarted, and the security is difficult to guarantee.

1. Modify configuration parameters

/ etc/my.cnf

In

[mysqld] add the following:

Skip-grant-tables

Configuration item.

two。 Restart mysql

Make the parameter take effect:

Service mysqld restart

3. Matters needing attention

At this time, all users login to the current database is password-free, so the security of the database is very low.

4. Modify the password

Specific methods:

Https://www.jb51.net/article/169143.htm

5. Remove the parameters

a. Remove skip-grant-tables from the configuration file after the password has been changed

b. Restart the database again.

More readers who are interested in MySQL-related content can check out this site topic: "MySQL query skills Collection", "MySQL Common function Summary", "MySQL Log Operation skills Collection", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection" and "MySQL Database Lock related skills Summary"

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.