Detailed explanation of how to authorize MySQL database

Preface

Friends who have read the blogger's previous article can get a knowledge point: data authorization (eg: grant xx to yy). By the way, data is so sensitive that we must follow the principle of "dividing and conquering, minimal claiming". In this era of big data prevalence, data has obviously become the Nth means of production for mankind. Without it, it seems that we "stand not too high, see clearly, and hear far enough." So how to manage it is becoming more and more important.

Today, the blogger continues to talk about MySQL to help you understand how it completes authorization. Let’s Go~

Q: How to authorize and manage MySQL databases

Tip: All the examples in this article have MySQL 5.7 as an example.

1. MySQL's "Privacy"

Before learning how to authorize, let's take a look at what MySQL has"Authorized Power"。

1. Permission level

2. Permission list

To further illustrate the privileges that MySQL can provide, the blogger provides an official list:

For each authorization item, we can refer to the official guidance document for learning and jump directlyhere, check it.

2. Authorized operation

Understand the above"privilege"After that, we can freely rule them and allocate them to the right people.

1. View permissions

For example, if you want to view the permissions of the specified user (user), you can complete it through the following statement:

SHOW GRANTS FOR [USER]@[HOST];

To view the user's non-privileged attributes, you can complete it by following statements:

SHOW CREATE USER [USER]@[HOST];

2. Assign permissions

Assign permissions and use the following statements to complete:

grant xx privileges on [DB_NAME].[TABLE_NAME] to [USER]@[HOST] identified by [PASSWORD] with grant option;

Here, it is necessary for the blogger to give a special introduction to this statement:

• grant xx privileges
  The scope of authorization, such as all, select, create, etc.
• on [DB_NAME].[TABLE_NAME]
  Authorized objects, such as which library and which table.
• to [USER]@[HOST]
  Authorized user access control, such as test users accessing the database through 127.0.0.1.
• identified by [PASSWORD]
  Authorized user access password.

3. Recycling permissions

Since there is distribution, then there is recycling. How to operate the recycling can be completed through the following statement:

revoke xx privileges on [DB_NAME].[TABLE_NAME] from [USER]@[HOST];

Here, the blogger will also give a special introduction to this statement:

• revoke xx privileges
  The scope of revocation/recycling of authorization, such as all, select, create, etc.
• on [DB_NAME].[TABLE_NAME]
  Undo/recycled objects, such as which library and which table.
• from [USER]@[HOST]
  Revoked/recycled user access control, such as test users accessing the database through 127.0.0.1.

Conclusion

This article introduces the permission operations of MySQL. I hope it can provide some help to friends in need.

This is the article about how to authorize and manage MySQL databases in detail. For more related MySQL data authorization content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!