MySQL user management
Source: PHP Chinese User 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: Authorization form User can connect to the server users and what they have any global permissions DB database level permissions TABLES_PRIV table-level permissions columns_priv column-level permissions also have the 5th authorization table (Host), but it 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 privileges (color "on what to user identified by" password "with grant option To use this statement, you need to fill out the following: privileges granted the user's permissions, the following table List permission specifiers available for a GRANT statement: Permissions Specifies the Operations Alter Code and Index Create Create Database and Table Delete Delete Database DROP Discard (Delete) Database and Table INDEX Create or Abandon Index Insert Insert a new line Reference Reference Update in the table Update to modify the current save table Record File Record File Read or write the file processs on the server Process View the thread information executed in the server or kill the thread RELOAD Overload authorization form or empty the log, the host Cache or table caching. Shutdown Close Server All; All privilege synonyms USAGE special "no right" permissions The above table shows the permissions specified in the first group Suitable for databases, tables, and columns, the second set of data. 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. Column permission uses columns, which 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. The level of WHAT permission is used.
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 gives the user password, which 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 from hosts around the world. In this case, You can allow him to connect to where: Grant All On Samp_DB. * To max @% identified by "diamond" "%" characters from the wildcard function, the meaning of the LIKE mode match. In the above statement, it means "any Host ". So Max and Max @% equivalence. This is the easiest way to build users, but it is also the least unsecured. Take it, you can allow a user to access from a limited host. For example, to allow Mary Connect from any host from the snake.net field, with a%. Snake.Net host specifier: Grant All on SAMP_DB. * To Mary @ .snake.net Identified by "quartz"; if you like, the host part of the user identifier You can use the IP address instead of a host name.
You can specify an IP address or an address containing the mode character, and from MySQL 3.23, you can also specify an IP number that has a network mask indicating the number of bits for the network number: GRANT All on Samp_DB. * To boris @ 192.168.128.3 Identified by "ruby" grant all on samp_db. * To fred@192.168.128.% Identified by "quartz" grant all on samp_db. * To recmp_192.168.128.0/17 Identified by "Ruby" first example It is pointed out that the user can connect the specific host from which it is connected to the IP mode of Class 192.168.128, and in the third statement, 192.168.128.0 / 17 specifies a 17-digit network number and matches 192.168.128 The first 17-bit IP address. If mysql complained that you specified user values, you may need to use quotation marks (just separate the user name and hostname part). Grant All on Samp_DB.PRESIDENT to "My Friend" @ "Boa.snake.Net" 1.2 What level of permissions should be available and what should they apply? You can authorize the permissions of different levels, global permissions are the most powerful because they apply to any database. To make ETHEL a super user who can do anything, including authorization to other users, issues the following statements: grant all on *. * To ethere @ localhost identified by "coffee" with the Grant Option ON clause of *. * Means Holding "all databases, all tables". From safe consideration, we specify that ETHEL can only be connected locally. The host that limits a super user can be connected is usually wise because it limits the host attempting to crack the password. Some privileges (file, process, reload, and shutdown) are administrative privileges and can only be authorized by "on *. *" Global permission. If you like, you can authorize these privileges without authorizing database permissions. For example, the following statement sets a Flush user, he can only issue a FLUSH statement. This may be useful in the management scripts you need to perform, such as emptying logs: grant reluxt on *. * To flushl @ localhost Identified by "flushpass" Generally, you want to authorize administrative privileges, 点, because of their users Affect the operation of your server. Database level permissions apply to all tables in a specific database, they can be granted using on db_name. * Clause: GRANT ALL ON SAMP_DB TO BILL@racer.snake.net indeetified by "Rock" Grant SELECT ON SAMP_DB TO RO_USER @% Indet by "ROCK" first statement to the Bill authorizes the permissions of all tables in the SAMP_DB database, the second creation of a strict restricted user RO_USER (read-only user), only access to all tables in the SAMP_DB database, but only read Take, that is, the user can only issue a SELECT statement. You can list all permissions granted simultaneously.
For example, if you want the user to read and modify the content of the existing database, you can't create a new table or delete table, grant these privileges as follows: Grant SELECT, INSERT, DELETE, UPDATE ON SAMP_DB TO BILL@snake.net Indetified BY "ROCK" For more refined access control, you can authorize in each table, or even on each column of the table. When you want to hide a table of a table, or if you want a user to modify a specific column, column specific permissions are very useful. For example: grant select on samp_db.member to bill @ localhost indetified by "Rock" Grant Update (expected) on SAMP_DB. Member to bill @ localhost 1 statement grants read rights to the entire Member table and set a password, second Scriptures have added UPDATE privileges only to the expedition. There is no need to specify your password because the first statement has been specified. If you want to grant the permissions for multiple columns, specify a list of separated by commas. For example, an UPDATE permission to increase the address field of the Member table for Assistant users, use the following statement, new permissions will be added to the user's existing permissions: Grant Update (street, city, state, zip) on samp_db to assistant @ localhost usually, You don't want to give any permissions wide than users do need. However, when you want users to create a temporary table to save the intermediate results, but you don't want them to do this in a database that contains them should not modify the content, there is a relatively loosely on a database. Permission. You can do it by establishing a separate database (such as TMP) and grant all permissions on the database. For example, if you want any user from the host from the MARS.NET domain to use the TMP database, you can send such a GRANT statement: grant all on tmp. * To ""@mars.net After you, users can create The TMP.TBL_NAME is used to creating an anonymous user in the TMP ("" in the user specified ", and any user matches the blank username). 1.3 Users should be allowed to manage privileges? You can allow the owner of a database to control database access by granting all owner permissions on the database, specifying with grant option when authorization. For example: If you want Alicia to connect from any host from a BIG.CORP.COM domain and have administrator privileges in the Sales database, you can use the following GRANT ALL on Sales. * To alicia@%.big . corp.com indetified by "Applejuice" with grant option in the effect of the Grant Option clause allowing you to grant access authorization to another user. Note that two users with GRANT privileges can authorize each other. If you only give the first user SELECT permission, and another user has a GRANT plus SELECT permissions, then the second user can be the first user more "power". 2 Withdrawal and delete the user to cancel a user's permissions, use the revoke statement.
