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

This article is to share with you about how to create and manage users in MySQL. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article.

The MySQL administrator should know how to set up the MySQL user account and indicate which user can connect to the server, where to connect, and what to do after connecting. MySQL 3.22.11 began by introducing two statements to make this easier: the GRANT statement creates the MySQL user and specifies its permissions, and the REVOKE statement removes the permissions. The two statements play the front-end role of the mysql database and provide another way to directly manipulate the contents of these tables.

The CREATE and REVOKE statements affect the contents of four authorization tables:

Users who user can connect to the server and any global permissions they have

Db database-level permissions

Tables_priv table-level permissions

Columns_priv column-level permissions

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

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

It is easier to use GRANT and REVOKE than to modify the authorization table directly. However, it is recommended that you read the MySQL Security Guide. These tables are extremely 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 describe how to set up a MySQL user account and authorize it. We also cover how to revoke rights and remove users from the authorization table.

You may also want to consider using mysqlaccess and mysql_setpermission scripts, which are part of the MySQL distribution, which are Perl scripts that provide another option for GRANT statements to set up user accounts. Mysql_setpermission requires DBI support to be installed.

MySQL creates users and authorizes them

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 sections:

Privileges

Permissions granted to the user, the following table lists the permission specifiers that can be used for GRANT statements:

Actions allowed by permission specifier permissions

ALTER modifies tables and indexes

CREATE creates databases and tables

DELETE deletes existing records from the table

DROP discards (deletes) databases and tables

INDEX creates or discards indexes

INSERT inserts a new row into the table

REFERENCE not used

Records in the SELECT search table

UPDATE modifies existing table records

FILE reads or writes files on the server

PROCESS views the thread information executed on the server or kills the thread

RELOAD reloads the authorization table or empties the log, host cache, or table cache.

SHUTDOWN shuts down the server

Owned by ALL; synonymous with ALL PRIVILEGES.

USAGE's special "No permission" permission

The permission specifiers shown in the first group in the above table apply to databases, tables and columns, and the second group manages permissions. In general, these are relatively strictly authorized because they allow users to influence the operation of the server. The third set of permissions is special, ALL means "all permissions" and UASGE means no permissions, that is, users are created but no permissions are granted.

Columns

The column in which permissions are applied, which is optional, and you can only set column-specific permissions. If the command has more than one column, you should separate them with a comma.

What

The level at which permissions are used. Permissions can be global (applicable to all databases and all tables), specific databases (applicable to all tables in a database), or specific tables. You can specify that a columns statement is column-specific.

User

The user to whom permission is granted, which consists of a user name and a host name. In MySQL, you specify not only who can connect, but also where to connect. This allows you to connect two users with the same name from different places. MySQL lets you distinguish between them and grant permissions independently of each other.

A user name in MySQL is the user name you specify when you connect to the server, and this name does not have to be associated with your Unix login or Windows name. By default, if you do not specify a name explicitly, the client will use your login name as the MySQL user name. It's just a pact. You can change the name to nobody in the authorization table, and then use the nobody connection to perform operations that require superuser privileges.

Password

The password given to the user, it is optional. If you do not specify an 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 the same, when you use IDENTIFIED BY, the password string uses the literal meaning of the password, GRANT will code 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, the user can grant permissions to other users through the GRANT statement. You can use this clause to authorize other users.

User names, passwords, databases, and table names are case-sensitive in authorization table records, while host and column names are not.

In general, you don't want to grant any permissions that are wider than the permissions the user really needs. However, when you want users to create a temporary table to hold intermediate results, but you don't want them to do so in a database that contains what they should not modify, a relatively loose permission to be granted on a database occurs. You can create a separate data.

The above is how to create and manage users 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.