MySQL administrator helps you understand CREATE and REVOKE statements _ MySQL

The MySQL administrator should know how to set up the MySQL User account and specify which user can connect to the server, where to connect, and what to do after the connection. MySQL3.22.11 introduces two statements to make this work easier: GRANT statements create MySQL Users and specify their permissions, while REVOKE statements delete permissions. The two statements assume the front-end role of the MySQL database and extract the MySQL statements.

The MySQL administrator should know how to set up the MySQL User account and specify which user can connect to the server, where to connect, and what to do after the connection. MySQL 3.22.11 introduces two statements to make this work easier: GRANT statements create MySQL Users and specify their permissions, while REVOKE statements delete permissions. The two statements assume the front-end role of the MySQL database and provide a different method than directly operating the contents of these tables. The CREATE and REVOKE statements affect four tables:

Authorization table content

Users who can connect to the server and have

Any global permissions

Database-level permissions

Tables_priv table-level permissions

Columns_priv column-level permission

There are 5th other authorization tables (hosts), but they are not affected by GRANT and REVOKE.

When you issue a GRANT statement to a user, create a record for the user in the user table. If the statement specifies any global permissions (administrative permissions or permissions applicable to all databases), these are also recorded in the user table. If you specify database, table, and column-level permissions, they are recorded in db, tables_priv, and columns_priv tables respectively.

Using GRANT and REVOKE is easier than directly modifying the authorization table. however, read MySQL Security Guide. These tables are exceptionally important, and as an administrator, you should understand how they go beyond the functional level of GRANT and REVOKE statements.

In the following sections, we will introduce how to set up and authorize a MySQL User account. We also involve how to revoke permissions and delete users from the authorization table.

You may also want to consider using MySQL Access and mysql_setpermission scripts, which are part of MySQL distribution. they are Perl scripts and provide another option to set user accounts for GRANT statements. DBI support is required for mysql_setpermission.

1. create and authorize a user

The syntax of the GRANT statement looks like this:

GRANT privileges (columns) ON what TO user identified by "password" with grant option to use this statement, you need TO fill in the following parts: privileges.

The following table lists the permissions that can be used for GRANT statements:

Operation permitted by the permission specified character

ALTER table and index

CREATE database and table creation

DELETE existing records in the table

DROP discard (delete) databases and tables

Create or discard an INDEX

INSERT a new row into the table

REFERENCE unused

SELECT to retrieve records in a table

UPDATE modify existing table records

FILE: reads or writes files on the server.

PROCESS: view information about the thread executed on the server or kill the thread.

RELOAD: RELOAD the authorization table or clear logs, host caches, or table caches.

SHUTDOWN the server

ALL; all privileges synonyms

USAGE special "no permission" permission

The table above shows that the permission specifiers in the first group apply to databases, tables, and columns, and the second group manages permissions. Generally, these are relatively strictly authorized because they allow users to affect server operations. The third group has special permissions. "ALL" means "ALL permissions", and "UASGE" means "no permissions", that is, creating users, but not granting permissions.

Columns:

The permission column is optional, and you can only set specific permissions for the column. If the command has more than one column, separate them with commas.

What:

Permission usage level. Permissions can be global (applicable to all databases and tables), specific databases (applicable to all tables in a database), or specific tables. You can specify a columns statement to indicate that the permission is column-specific.

User:

The user authorized by the permission, which consists of a user name and host name. In MySQL, you not only specify who can connect, but also where to connect. This allows two users with the same name to connect from different places. MySQL allows you to differentiate them and grant them permissions independently.

A user name in MySQL is the user name specified when you connect to the server. it does not need to be associated with your Unix or Windows name. By default, if you do not specify a specific name, the customer program uses your login name as the MySQL User name. This is just an agreement. You can change the name to nobody in the authorization table, and then use the nobody connection to perform operations that require superuser permissions.

Password:

The password assigned to the user. it is optional. If you do not specify the identified by clause for a new user, the user is not assigned a password (insecure ). For existing users, any password you specify will replace the old password. If you do not specify a password, the old password remains unchanged. when you use identified by, the password string uses the literal meaning of the password, and GRANT will encode the password for you, do not use the PASSWORD () function as you use SET password.

The with grant option clause is optional. If you include it, you can GRANT permissions to other users through the GRANT statement. You can use this clause to grant permissions to other users.