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

What are the effects of GRANT and REVOKE statements in MySQL database? I believe many inexperienced people are at a loss about this. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

MySQL administrators should know how to set up MySQL user accounts by specifying which users can connect to the server, where to connect, and what to do when connecting. MySQL3.22.11 introduces two statements that make it easier to do this: the GRANT statement creates MySQL users and specifies their permissions, and the REVOKE statement removes permissions. These two statements act as the front end of the authorization tables in the mysql database and provide alternative methods for directly manipulating the contents of those tables. The following editor to explain the impact of MySQL database GRANT and REVOKE statements?

What are the effects of MySQL database GRANT and REVOKE statements

Authorization form content

User users who can connect to the server and any global privileges they have db database-level privileges tables_priv table-level privileges columns_priv column-level privileges

There is also a fifth authorization table (host), but it is not affected by GRANT or REVOKE.

When you issue a GRANT statement for a user, you should create an item in the user table for that user. If the statement specifies all global privileges (administrative privileges or permissions for all databases), these assignments are also recorded in the user table. If permissions for databases, tables, or columns are specified, they are recorded in the db, tables_priv, and columns_ private tables.

It is easier to use GRANT and REVOKE statements than to modify the authorization table directly. However, it is recommended that you supplement this chapter by reading Chapter 12, which discusses the authorization table in detail. These tables are very important, and an administrator should understand how they work at the GRANT and REVOKE statement level.

The following sections of this section discuss how to set up accounts and authorizations for MySQL users, remove permissions and remove all users from the authorization table, and consider a challenge that bothers many new MySQL administrators.

You should also consider using mysqlaccess and mysql_setpermission scripts, which are part of the MySQL distribution. These are Perl scripts that provide a substitute for GRANT statements that set up a user's account. Mysql_setpermission requires a supporting environment with DBI.

What are the effects of MySQL database GRANT and REVOKE statements

Create new users and authorizations

The syntax of the GRANT statement is as follows:

GRANTprivileges (columns)

ONwhat

TOuserIDENTIFIEDBY "password"

WITHGRANTOPTION

To use this statement, you need to complete the following sections:

The permissions assigned to the user by privileges. The following table lists the permission specifiers that can be used in GRANT statements:

Permission descriptor actions allowed by permissions

The first set of permission specifiers shown in the above table apply to databases, tables, and columns. The second set of specifiers are administrative privileges. In general, the granting of these permissions is rather conservative because they affect the operation of the server (for example, SHUTDOWN privileges are not distributed on a daily basis). The third set of specifiers is special. ALL means all permissions, while USAGE means that users are created without permissions, but no permissions are granted.

The column to which the columns permission applies. This is optional and only sets column-specific permissions. If you name more than one column, separate it with a comma.

The level at which what permissions are applied. Permissions can be global (for all databases and all tables), database-specific (for all tables in a database), or table-specific. Permissions can be granted to specific columns by specifying a COLUMNS clause.

The user with the permission to use user. It consists of a user name and a host name. In MySQL, you specify not only who to connect, but also where to connect. It allows you to have two users with the same name connected from different locations. MySQL allows you to distinguish between them and assign permissions independently of each other.

The user name of MySQL is the name you specify when you connect to the server. This name is not necessarily associated with your UNIX registration name or Windows name. By default, the client program will use the name you registered as the user name of the MySQL (if you don't specify a name explicitly), but this is just a convention. The same is true for using root as a super user name that can manipulate all MySQL, which is a convention. You can also change this name to nobody in the authorization table, and then connect as a nobody user to perform operations that require superuser privileges.

The password assigned to the user by password. This is optional. If you do not assign an IDENTIFIEDBY clause to a new user, the user does not assign a password (which is not secure). For existing users, any specified password replaces the old password. If you do not specify a new password, the user's old password remains the same. When you do use IDENTIFIEDBY, the password string should be direct, and GRANT will encode the password. When using the SETPASSWORD statement, do not use the PASSWORD () function.

The WITHGRANTOPTION clause is optional. If you include this clause, the user can grant any permissions granted by the GRANT statement to other users. You can use this clause to grant authorized capabilities to other users.

After reading the above, have you mastered the influence of GRANT and REVOKE statements in MySQL database? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.