mysql security management details

1. Introduce according to the order

The security of a MySQL server is based on the principle that users should have just the right amount of access to the data they need, no more and no less.

In other words, users cannot have too much access to too much data.

• Most users only need to read and write tables, but a few users even need to be able to create and delete tables;
• Some users need to read the table but may not need to update the table;
• You might want to allow users to add data, but not to allow them to delete data;
• Some users (administrators) may need to handle user account permissions, but most users do not;
• You may want to allow users to access data through stored procedures, but not allow them to access the data directly;
• You may want to restrict access to certain features based on where the user is logged in from.

These are just examples, but they help illustrate the important fact that you need to give users the access they need, and only the access they need.

This is called access control, and managing access control requires creating and managing user accounts.

Preventing Unintentional Errors It is important to note that the purpose of access control is not just to prevent malicious intent by users.

Data nightmares are more often the result of unintentional mistakes, such as mistyping a MySQL statement, operating on an inappropriate database, or some other user error.

Access controls help prevent these situations by ensuring that users cannot execute statements they should not. Do not use root The use of root logins should be taken seriously. Only use it when absolutely necessary (perhaps when you can't log into another administrative account).

You should not use root in day-to-day MySQL operations.
MySQL user accounts and information are stored in a MySQL database named mysql . You generally don't need direct access to mysql databases and tables (you'll understand this later), but sometimes you do. One of the times you would need to access it directly is when you need to get a list of all user accounts.

To do this, use the following code:

use mysql;
SELECT USER FROM user;

The mysql database has a table called user , which contains all user accounts.

The user table has a column called user which stores the user login name. A newly installed server may have only one user (as shown here), while a server built in the past may have many users.

Experimenting with Multiple Clients The best way to experiment with changes to user accounts and permissions is to open multiple database clients (such as multiple copies of the mysql command-line utility), logging in one as the administrative user and the others as the user being tested.

2. Create a user

CREATE USER ben IDENTIFIED by 'ben123456';

This creates a user.

Specifying a hashed password IDENTIFIED BY specifies the password as plain text. MySQL
It will be encrypted before being saved to the user table. To specify the password as a hash value, use IDENTIFIED BY PASSWORD .
You can also create user accounts using the GRANT or INSERT GRANT statements (described later), but CREATE USER is generally the clearest and simplest statement. In addition, you can also add users by directly inserting rows into the user table, but for security reasons, this is generally not recommended.

The tables (and table schemas, etc.) that MySQL uses to store user account information are extremely important, and any damage to them may seriously harm the MySQL server. Therefore, it is better to use tags and functions to process these tables rather than directly process them.

To rename a user account, use the RENAME USER statement as follows:

RENAME USER ben to zhangsan;

MySQL 5 and later versions only support RENAME USER. To rename a user in MySQL in the past, you could use UPDATE to directly update the user table.

3. Delete user account

DROP USER zhangsan;

Notice:

Before MySQL 5 Since MySQL 5, DROP USER removes a user account and all associated account privileges. Prior to MySQL 5, DROP USER could only be used to delete user accounts, not associated permissions. Therefore, if you use an older version of MySQL, you need to first use REVOKE to delete the permissions associated with the account, and then use DROP USER to delete the account.

4. Access Rights

After creating a user account, you must then assign access rights. Newly created user accounts have no access permissions. They can log in to MySQL, but cannot see the data or perform any database operations.

CREATE USER zhangsan IDENTIFIED by 'zhang123456';

To see the permissions granted to the user account, use SHOW GRANTS FOR as follows:

SHOW GRANTS FOR 'zhangsan';

result:

GRANT USAGE ON *.* TO 'zhangsan'@'%' IDENTIFIED BY PASSWORD '*557661E2A88A816A3155408E5D15997A8C5C7D25'

It shows no permissions.

USAGE means no permissions at all (I know, not very intuitive), so this result means no permissions on anything, on any database and any table.

Users are defined as user@host. MySQL permissions are defined using a combination of username and host name. If you do not specify a hostname, the default hostname is used (grant access to the user regardless of hostname)

To set permissions, use the GRANT statement. GRANT requires you to provide at least the following information:

• The permissions to be granted;
• The database or table to which access permissions are granted;
• username.

The following example shows the usage of GRANT:

GRANT SELECT ON test.* to zhangsan;

Then:

SHOW GRANTS FOR ZHANGSAN;
GRANT SELECT ON `test`.* TO 'zhangsan'@'%'

Each GRANT adds (or updates) a privilege for a user. MySQL reads all grants and determines permissions based on them.

The inverse operation of GRANT is REVOKE, which is used to revoke specific permissions. Here is an example:

REVOKE SELECT ON test.* FROM zhangsan;

This REVOKE statement revokes the SELECT access privilege that was just granted to user bforta. The access rights being revoked must exist, otherwise an error will occur.

GRANT and REVOKE control access permissions at several levels:

• For the entire server, use GRANT ALL and REVOKE ALL;
• For the entire database, use ON database.*;
• For a specific table, use ON database.table;
• Specific columns;
• A specific stored procedure.

Example:

ALL All permissions except GRANT OPTION ALTER Use ALTER TABLE
ALTER ROUTINE Using ALTER PROCEDURE and DROP PROCEDURE
CREATE Using CREATE TABLE
CREATE ROUTINE Using CREATE PROCEDURE
CREATE TEMPORARY 
TABLES
Using CREATE TEMPORARY TABLE
CREATE USER Using CREATE USER, DROP USER, RENAME USER, and REVOKE
ALL PRIVILEGES
CREATE VIEW
DELETE Using DELETE
DROP Using DROP TABLE
EXECUTE Using CALL and stored procedure FILE Using SELECT INTO OUTFILE and LOAD DATA INFILE
GRANT OPTION Using GRANT and REVOKE
INDEX Using CREATE INDEX and DROP INDEX
INSERT Using INSERT
LOCK TABLES
PROCESS Using SHOW FULL PROCESSLIST
RELOAD using FLUSH
REPLICATION CLIENT server location access REPLICATION SLAVE by the replication slave using SELECT using SELECT
Using SHOW DATABASES
SHOW VIEW Using SHOW CREATE VIEW
SHUTDOWN Use mysqladmin shutdown (to shut down MySQL)
SUPER uses CHANGE MASTER, KILL, LOGS, PURGE, MASTER
and SET GLOBAL. Also allow mysqladmin debug login UPDATE Use UPDATE
USAGE No access rights

Using GRANT and REVOKE , you can have complete control over what users can and cannot do with your valuable data.

For future authorizations using GRANT and REVOKE, the user account must exist, but there is no such requirement for the objects involved.

This allows administrators to design and implement security measures before creating databases and tables.

A side effect of this is that when a database or table is deleted (using the DROP statement), the associated access permissions still exist. Furthermore, if you re-create the database or table in the future, these permissions will still work.

You can simplify multiple grants by stringing together multiple GRANT statements by listing each privilege and separating them with commas, as follows:

GRANT SELECT, INSERT ON test.* to zhangsan;

5. Change password

To change a user's password, use the SET PASSWORD statement. The new password must be encrypted as follows:

SET PASSWORD FOR zhangsan = PASSWORD('zhangsan');

Modify the current user's password:

SET PASSWORD = PASSWORD('root');

This is the end of this article about MySQL security management details. For more relevant MySQL security management content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!