See an article introduction to mysql users management, is easy to understand, so attach it to you, share with you. MySQL administrator should know how to set the mysql user account, pointing which user can connect to the server, where to connect, what to do after the connection. MySQL 3.22.11 begins to introduce two statements to make this work more easily: GRANT statement creates a mysql user and specify its permissions, and the REVOKE statement deletes permissions. Two statements play a front-end role of the MySQL database and provide another method that is different from the content of these tables. CREATE and REVOKE statements affect 4 tables: Authorized form User can connect to the server users and any global permission they own DB database level permissions TABLES_PRIV table-level permissions Column_priv column level
There is also the 5th authorization form (Host), but it is not affected by Grant and Revoke. When you send a GRANT statement to a user, create a record for the user in the User table. If the statement specifies any global permissions (administrative privileges or permissions for all databases), these are also recorded in the User table. If you specify the database, table, and column level permissions, they are recorded in DB, Tables_Priv, and ColumnS_Priv tables. It is easier to use Grant and Revoke than the direct modification of the license table, however, I suggest you read the "MySQL Security Guide". These tables are important, and as an administrator, you should understand how they go beyond the feature levels of Grant and Revoke statements. In the following chapter, we will show how to set the mysql user account and authorize. We also involve how to withdraw and remove users from the license list. You may also want to consider using mysqlaccess and mysql_setpermission scripts, which is part of MySQL distribution, which is a Perl script, and another option to provide a GRANT statement to set a user account. MySQL_SETPERMISSION needs to install DBI support. 1 Create a user and authorize
The syntax of the GRANT statement looks like this: Grant Privies (Column) on what to user identified by "Password" with grant option
To use this statement, you need to fill out the following: Privileges
Grant users, the following table lists permission specifiers that can be used for GRANT statements: Permissions Specifies the Action Alter Victory Alter Creative ALTER Modify Table and Index Create Creating Database and Table Delete Delete Table Existing Record DROP Abandon (Delete) Database and Table INDEX Create or Discard Index Insert Insert New Row Reference Reference Update in the Table Update Modifying the Retrieving Update in the SELECT Retrieving File Record File Read or Write the File Information on the server Process View the thread information executed in the server or kill threads Reload overload Authorization form or empty log, host cache or table cache. Shutdown Close Server All; All Privileges synonyms Usage Special "No Permissions" Permissions
The above table shows the permissions specified in the first group suitable for databases, tables, and columns, and the second set of management rights. Typically, these are relatively strictly authorized because they allow users to affect the operation of the server. The third group of permissions, all means "all permissions", UASGE means no authority, that is, creating users, but does not grant permission. Columns
The columns used by permissions, it is optional, and you can only set column-specific permissions. If the command is more than one column, you should separate them with a comma. WHAT
Level of permissions. Permissions can be globally (for all databases and all tables), specific databases (suitable for all tables in a database) or specific tables. It can be permissions by specifying a columns sentence. User authority is granted by a user name and host name. In MySQL, you not only specify who can connect, and where to connect. This allows you to let two co-name users connect from different locations. MySQL allows you to distinguish them and give privileges independently. One user name in MySQL is the username you specified when you connect to the server, which does not have to be linked to your UNIX login or Windows name. By default, if you don't clearly specify a name, the client will use your login name as a MySQL username. This is just a convention. You can change the name to Nobody in the license list, and then perform the operation of the superuser permissions in the Nobody connection. Password
Give the user password, it is optional. If you don't specify the Identified by clause for the new user, the user does not assign a password (insecure). For existing users, any password you specify will replace the old password. If you don't specify a password, the old password remains unchanged. When you use Identified by, the password string is used to use the literal meaning of the password, and the Grant will coding the password for you, do not use the Password () function as you use set password. The with grant Option clause is optional. If you contain it, the user can grant permissions to authorize other users via the GRANT statement. You can use this clause to give the ability to authorize other users. User name, password, database, and table name are case sensitive in authorization form records, host names and column names are not. In general, you can identify the type of the GRANT statement by querial asks a few simple questions: Who can connect, from there? What level of permissions should users apply? What do they apply? Should users allow administrative privileges?
Some examples are discussed below. 1.1 Who can connect, from there?
You can allow a user to connect from a specific or a series of hosts. There is an extreme, if you know if you are deignive from a host connection, you can limit the permissions to a single host: grant all on samp_db. * To boris @ localhost identified by "ruby" grant all on samp_db. * To fred@res.mars .com identified by "quartz"
(SAMP_DB. * Means "SAMP_DB database all tables) The other extreme is that you may have a regular travel and need users Max connected to the host around the world. In this case, you can allow him to be from Connection: grant all on samp_db. * To max @% identified by "Diamond"
"%" Characters functions together, the meaning of matching the LIKE mode. In the above statement, it means "any host". So Max and Max @% equivalent. This is the easiest way to build users, but it is also the most unsafe. Take it, you can allow a user to access from a limited host collection. For example, to allow Mary to connect from any host from the snake.net domain, use a%. Snake.Net host specifier: Grant All on Samp_DB. * To Mary @. Snake.Net Identified by "Quartz";
