• notice
  • Congratulations on the launch of the Sought Tech site

Security settings in the mysql database

With the popularity of the Internet, there are more and more web-based applications.A web database is one of them.One or several servers can provide services for many customers, which brings a lot of convenience to people, but also creates opportunities for criminals.Since the data is transmitted over the network, it can be intercepted during transmission, or entered into the database through extraordinary means.For these reasons, database security is very important.Therefore, this article discusses some of the functions of the database in network security on the above issues.

Account Security

Accounts are MySQL's simplest security measure.Each account consists of a username, password, and location (usually a server name, IP, or wildcard).For example, user john logging in from server1 may have different permissions than john logging in from server2.

MySQL's user structure is username/password/location.This does not include the database name.The following two commands set the SELECT for database1 and database2

User permissions.

The first command sets the user abc to use password1 when connecting to the database database1.The second command sets the user abc to use password2 when connecting to the database database2.Therefore, the passwords for user abc to connect to database database1 and database2 are different.

The above settings are very useful.If you only want users to have limited access to one database and no access to other databases, you can set different passwords for the same user.Failure to do so could cause trouble when users find out that this username can access other databases.

MySQL uses a number of grant tables to keep track of users and the different privileges of those users.These tables are MyISAM tables in the mysql database.It makes perfect sense to keep this security information in MySQL.Therefore, we can use standard SQL to set different permissions.

Generally there are 3 different types of security checks that can be used in a MySQL database:

Login verification

That is, the most commonly used username and password authentication.Once you have entered the correct username and password, this verification will pass.

Authorization

After successful login, it is required to set specific permissions for this user.Such as whether to delete the table in the database and so on.

Access Control

This security type is more specific.It involves what operations the user can perform on the data table, such as whether to edit the database, whether to query data, and so on.
Access control consists of a number of privileges related to how to use and manipulate data in MySQL.They are both booleans, i.e.either allowed or not allowed.Below is a list of these privileges:

SELECT

SELECT is to set whether users can use SELECT to query data.If the user does not have this privilege, then only some simple SELECT commands can be executed, such as calculation expressions (SELECT 1+2), or date conversion (SELECT Unix_TIMESTAMP(NOW())) and so on.

・INSERT
・UPDATE
・INDEX
INDEX determines whether the user can set the index of the table.If the user does not have this permission, then the index on the table will not be set.

・ALTER
・CREATE
・GRANT
If a user has this GRANT authority, he can grant his authority to other users.That is, this user can share his permissions with other users.

・REFERENCES
With the REFERENCES privilege, a user can use a field from another table as a foreign key constraint on a table.
In addition to the above permissions, MySQL also has some permissions to operate on the entire MySQL.

・Reload
This privilege enables the user to execute various FLUSH commands, such as FLUSH TABLES, FLUSH STATUS, etc.

・Shutdown
This privilege allows the user to shut down MySQL

・Process
With this privilege, the user can execute the SHOW PROCESSLIST and KILL commands.These commands can view the processing progress of MySQL, which can be used to view the details of SQL execution.

・File
This privilege determines whether the user can execute the LOAD DATA INFILE command.Be careful to give users this permission, because users with this permission can load arbitrary files into tables, which is very dangerous for MySQL.

・Super
This privilege allows the user to terminate any query (which may not be executed by this user).
The above permissions are very dangerous, and you should be very careful when authorizing users.

SSL in MySQL

The above account security is only used for data transmission through ordinary sockets, which is very insecure.Therefore, MySQL provides support for SSL (Secure Scokets Layer) after version 4.1.MySQL uses the free OpenSSL library.

Since the Linux versions of MySQL are generally released with Linux itself, they do not use SSL for data transmission by default.If you want to open the SSL function, you need to set the hava_openssl variable:

The Windows version of MySQL has included OpenSSL.The other command is to check whether your MySQL has the SSL function turned on.

If it returns NO, then you need to compile OpenSSL into your own MySQL

In some cases you may need to encrypt your username and password for transmission.At this point you can use the following GRANT command:

SSL transport is also possible via the REQUIRE x509 option:

You can also use REQUIRE SUBJECT to specify a specific client certificate to access the database.

Perhaps you don't care what client license you are using, but only your certificate.Then you can use REQUIRE ISSUER to achieve:

SSL can also encrypt directly with a password.The password can be set with REQUIRE CIPHER.

The above uses the GRANT command to set user permissions.And this information is stored in the authorization table, these tables are the heart of the security system.The permissions that each user and client has are stored in these tables.If you operate these tables correctly, it will play a positive role in the security of the database, but if used carelessly, it will be very dangerous.

The above is the whole content of this article, I hope you like it.

Tags

Technical otaku

Sought technology together

Related Topic

0 Comments

Leave a Reply

+