Basic knowledge of user account management in MySQL database

Guide: MySQLThe Administrator should know how to set the MySQL user account by specifying which users can connect to the server, where to connect, and what to do during connection. MySQL3.22.11 introduces two statements that are easier to perform this work: GRANT statement to create a MySQL user and specify its permissions, and REVOKE statement to delete permissions. These two statements act as the front-end of the authorization table in the mysql database and provide an alternative method for directly manipulating the table content. The GRANT and REVOKE statements affect the following four tables:

Authorization Table content

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

When you publish a GRANT statement for a user, you should create an item for the user in the user table. If this statement specifies all global privileges (administrative permissions or permissions for all databases), these are also recorded in the user table. If database, table, or column permissions are specified, they are recorded in db, tables_priv, and columns_priv tables.

Using GRANT and REVOKE statements is easier than directly modifying the authorization table. However, it is recommended that you read chapter 12th to add the content of this chapter. Chapter 12th details the authorization table. These tables are very important. As an administrator, you should understand how these tables work at the GRANT and REVOKE statement levels.

The following sections describe how to set the account and authorization of a MySQL user. They also describe how to cancel permissions and delete all users from the authorization table, in addition, we will consider a problem that has plagued many new MySQL administrators.

You must also consider using mysqlaccess and mysql_setpermission scripts, which are components of the MySQL distribution package. These are Perl scripts that provide substitutes for GRANT statements for setting user accounts. Mysql_setpermission requires a DBI support environment.

Create new user and authorize

The syntax of the GRANT statement is as follows:

GRANT privileges (columns)

ON what

TO user IDENTIFIEDBY "password"

WITH GRANT OPTION

To use this statement, enter the following:

The permissions that privileges assigns to the user. The following table lists the permission specifiers that can be used in a GRANT statement:

Permitted operations

The first set of permission specifiers shown in the table above applies to databases, tables, and columns. The second set of specifiers is the management privilege. In general, these permissions are quite conservative because they affect server operations (for example, the SHUTDOWN privilege is not distributed on a daily basis ). The third set of specifiers is special. ALL means ALL permissions, while USAGE means creating a user without permissions, but not granting any permissions.

Columns.This is optional. You can only set the column-specific permissions. If you name more than one column, separate them with commas.

What permission application level.The permission can be global (applicable to all databases and tables), Database proprietary (applicable to all tables in a database), or table proprietary. You can grant permissions to specific columns by specifying a c o l u m n s clause.

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

The MySQL user name is the name you specify when you connect to the server. This name is not necessarily related to your UNIX registration name or Windows Name. By default, the client uses the name you registered as the MySQL user name (if you do not specify a name explicitly), but this is just a convention. This is also a convention about using root as the Super User Name that can operate on all MySQL databases. You can also change this name to nobody in the authorization table and connect it as a nobody user to perform operations that require the superuser privilege.

The password assigned to the user. This is optional. If you do not specify an IDENTIFIEDBY clause for a new user, the user will not be assigned a password (Insecure ). For existing users, any specified password will replace the old password. If no new password is specified, the old password remains unchanged. When you really want to use id e n t I f I E DBY, the password string should be a direct amount, and GRANT will encode the password. Do not use the PASSWORD () function when using the set pa s w o r d statement.

The with grant option clause is optional. If this clause is included, the user can GRANT any permissions granted by the GRANT statement to other users. You can use this clause to grant the authorization capability to other users.

These are all the basic knowledge of MySQL database user account management that I want to introduce to you. To learn how to operate MySQL Databases well, we should first start from the basics, this article is a good choice for many beginners.