Shulou(Shulou.com)05/31 Report--

This article is to share with you about how to achieve access control in mysql, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Overview

Mysql permission control can be controlled in different contexts and at different levels of operation, including the following

* * Administrative permissions allow users to manage mysql server operations. These permission controls are global and not specific to a particular database.

* * Database permissions correspond to a database and its objects. These permissions are used to control specific databases or globally, and they can be applied to all databases.

* * the permissions of database objects correspond to database tables, indexes, views, and stored procedures, and can be authorized to control an object within the database.

Storage location of permissions

* * account permission information is stored in the user,db,tables_priv, columns_priv and procs_priv tables of the mysql database.

* * read this information into memory when mysql starts, or re-read it into memory when the permission change takes effect.

Mysql account management

The mysql account name consists of a user name and a host name. This allows the same user to control permissions separately on different hosts. This section describes how to assign account names, including specific values and ambiguities.

Match rules, which are used in sql statements when create user, grant, set password:

-'user_name'@'host_name' is the grammatical rule for account names.

-an account that contains only a user name is equivalent to 'username'@'%'

-if the user name is a legal character, the user name and host name do not need to be enclosed in quotation marks. When your user name contains characters like'-', you need to use quotation marks. Or the host name has

Wildcards need to be quoted.

-quotation marks can be single or double quotation marks.

-the host and user name must be enclosed in quotation marks, if necessary.

Mysql uses two columns in the mysql database to store the user name and hostname:

-the user table contains one record for each account. This table also shows what global permissions the account has.

-other authorization tables represent the database owned by the account and the objects in the database. These tables have user name and host columns. The account information of each line and the account information of the user table are Yiyang.

Usernames and hostnames can contain special characters or wildcards. Here are the rules:

The user name is either a non-empty string that literally must match the user name specified in the connection string, or an empty string matches the user name. The empty user name is an anonymous user.

Hostnames can also take many forms, or wildcards:

-the hostname can be an IP address or a localhost-like string.

-% has the same effect as like in sql. For example,'%. Mysql.com 'will match a.mysql.com.

Access control phase 1: connection verification

When you try to connect to a mysql server, the server accepts or rejects the connection based on the following two conditions:

-your ID and the correct password

-is your account locked?

The server first verifies that the password is correct, and then verifies that it is locked. If any of the steps fail, the server will reject the connection. If the verification is passed, the server accepts the connection, then enters the second phase and waits for the request.

The authentication check first checks the three columns of the user table, host,user,password. The lock status is recorded in the account_locked column of the user table. The account lock status can be changed through the alter user statement.

Your identity consists of two parts, the client host you connect to and your mysql user name. If the user name is not empty, you must match all characters, and if the user name is empty, match any user. If the user table matches an empty user name, the user is considered to be an anonymous user and is not actually provided by the customer. This means that the empty user name is used for further inspection, that is, stage 2.

The password can be empty, which is not a wildcard and does not mean to match any password. It means that the user must connect without a password. If the service authorizes a customer to use a plug-in, so

The authentication system implemented by the plug-in may not use password columns. In such cases, the external password can also be used to authenticate the mysql server.

Non-empty passwords are encrypted and mysql does not store any plaintext passwords. And the password provided by the user is also encrypted. The encrypted password is later checked during the connection process to see if the password is correct.

From mysql's point of view, the encrypted password is the real password, so don't tell anyone the encrypted password.

If multiple rows are matched in the user table, the server must decide which one to use, and its rule is as follows:

-whenever the server reads the user table into memory, it sorts.

-servers checked by servers are checked in line order

-the server uses the first matching line

The rules for server sorting are wildcard-free at the front and unified at the back.

Access control phase 2: request verification

When you pass the connection verification and you establish a connection with the server, the server enters the second phase of access control. For each of your requests, the server decides what you want to do and then checks to see if you have

Permission to do it. At this point, the privilege column of the grant table should come out. These columns can come from user,db,tables_priv,columns_priv,procs_priv. I won't elaborate on these watches any more.

When will the permission change take effect?

When the server starts, the data from the grant table is read into memory. If you use the account management statement to update the access control, the server will know about the changes and re-read them. If you

Update these tables directly with SQL statements, and your changes will not take effect until you restart the server or you tell the server to rebuild the cache.

You can notify the server to rebuild the cache by performing a refresh permission operation. The following three statements can achieve the effect:

-flush_privileges

-mysqladmin_flush-privileges

-mysqladmin_reload

If the startup parameter is-skip-grant-tables, then the server no longer verifies any permissions, which is very insecure.

The above is how to achieve access control in mysql. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.