MYSQL user account management Author: Unknown Updated: Finishing Source: ddvip.net Updated: 2004.9.19 Contributor mail: tg * ddvip.com MySQL administrator should know how to be connected to the server through which the user specifies, from where the connection, And what to do when connecting, to set the mysql user account. MySQL3.22.11 introduces two statements that are more easier to perform this work: GRANT statement creates a mysql user and specifies its permissions, and the REVOKE statement deletes permissions. These two statements act as the front end of the authorization table in the mysql database and provide a selectable way to directly manipulate these table content. GRANT and REVOKE statements affect the following four tables: Authorization form User can connect to the server users and any global privileged DB database level privilege table _ Priv table-level privilege col UM ns _ Priv list privilege also has fifth An authorization form (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 the statement specifies all global privileges (administrative privileges or permissions for all databases), these specified are also recorded in the USER table. If you specify a database, table, or column permissions, they will be recorded in DB, TABLES_PRIV, and COLUMNS_PRIV tables. It is easier to use the Grant and REVOKE statements than the direct modification authorization table. However, it is recommended that you better discuss the authorization form in detail in Chapter 12. These tables are very important, as a administrator should understand how these tables work in Grant and Revoke statements. The following sections will discuss how to set up the account and authorization of MySQL users, and will show how to cancel permissions and remove all users from the license list, and will consider a problem that plagues many new MySQL administrators. You have to consider using the MySQLACCESS and MySQL_SETPERMISSION scripts, which are components of the MySQL distribution package. These are Perl scripts that provide use of the use of the GRANT statement of the user account. MySQL_SETPERMISITION requires a support environment with DBI. Creating a new user and authorized grant statement 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, you need to fill in the following: Privileg assigned to the user's permissions. The following table lists the permission specifiers that can be used in a GRANT statement: Permission Description Permission Allowed Operations The first set of permissions for the table displayed for databases, tables, and columns. The second set of specifiers are manage privileges. Typically, these privileges are quite conservative because they affect the operation of the server (for example, the shutdown privilege is not permission to distribute daily). The third set of specifiers are special. All means "all permissions", and usage means "no permissions" - creates users, but does not grant any permissions. Columns permissions apply to columns. This is optional, only sets the proprietary permissions. If you are named more than one column, separate it by comma. The level of what permission application. Permissions can be globally (for all databases and all tables), database exclusive (for all tables in a database), or table proprietary. The permissions can be granted a specific column by specifying a C O L u m n s clause. User uses users. It consists of username and hostname. In mysql, not only specify who is connected, but also to specify where to connect.
It allows you to have two users connected to different locations with the same name. MySQL allows you to distinguish between them and assign permissions independently. MySQL username is the name you specified when you connect to the server. The name is not necessarily connected to your UNIX registration name or Windows name. When the default settings, the client program will use the name you registered as the username of MySQL (if you don't specify a name), it is just a convention. This is also the case that the super user name that uses root can operate all MySQL is the same. You can also modify this name into N O B O D Y and then connect this name to NOBODY users to perform operations that require superuser privileges. Password assigns a password to the user. This is optional. If you do not specify the IdentifiedBy clause for the new user, the user does not assign a password (not safe). For existing users, any specified password will replace the old password. If you do not specify a new password, the old password of the user remains unchanged. When you do use ID E N T I f i e dby, the password should be a direct amount, and GRANT will encode the password. When using the Set PA S W W O R D statement, do not use the Password () function. The with grant Option clause is optional. If the 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 authorized ability to other users. The name, password, and database and table are case sensitive in the authorization entries, and the host name and column name are not. By querying some questions, you can usually infer the type of GRANT statement required: Who can connect, where is it connected? What level of permissions should be applied? Allow users to manage privileges? Let's ask questions, and see some examples of using the GRANT statement to set the mysql user account. 1. Who can connect, where to connect you can allow users to connect in a particular host or a wide group of hosts involving a wide range. In one extreme, if you know that the user will only connect from that host, you can define access to a single host: grant all on samp_db. * To boris @ localhost identfiedby "ruby" grant all on samp_db. * To fred @ ares. Mars.Net Identfiedby "Quartz" (Symbol SAMP_DB. * Meaning is "All Table in SAMP_DB Database") in another extreme, you may have a user max, he travels around the world and needs to be available from the host around the world connection. In this case, no matter where you connect you, you will allow: Grant All on Samp_DB. * To max @% identfiedby "diamond" '%' character starts the function of wildcard, the same meaning as the LIKE mode, in the previous In the statement, it means "any host". If you do not give the hostname part at all, it is the same as the meaning of the specified "%". Therefore, MAX and MAX @% are equivalent. This is the easiest way to set up a user, but the security is minimal. To take a compromise, users can allow users to connect in a limited host.
For example, to connect Mary from any host in the snake.net field, you can use the%. Snake.Net Host specifier: Grant All on SAMP_DB. * To Mary@%.snake.Net IdentFiedby "Topaz" user identifier The host part can be given by IP address instead of the host name (if you want). You can specify a direct IP address or an address that contains a mode character. Similarly, from MySQL3.23, you can use a network mask to specify the IP number. The network mask indicates the number of binary bits for the network number: GRANT ALL ON SAMP_DB. * To joe@192.168.0.3 Identifiedby "Water" Grant All on Samp_DB. * To ardis@192.168.128.% Identifiedby "Snow" Grant All on SAMP_DB. * To REX@192.168.128.0/17 Identifiedby "ICE" first statement indicates a specific host that the user can connect. The second statement specifies the IP mode of the 129.168.128 Class C subnet. In a third statement, 192.168.128.0/17 specifies a 17-bit binary network number and matchs any host to 192.168.128.0/17 in the first 17 binary bits of its IP address. If mysql complained that the user value you specified, you may need to use quotation marks (but add quotes for the username and hostname): grant all on samp_db.president to "my friends" @ "boa.snake.net" 2. The user should have What level of permissions, these permissions apply to what you can grant different levels. The global permissions are the strongest because they apply to any database. In order to make ETHEL a super user that can perform all operations (which includes other users authorized), publish the following statement: grant all on *. * To Ethel @ localhost identifiedby "coffee" with grant option on clauses *. * The meaning of "all databases, all tables", for insurance, we have specified ETHEL only to connect from the local host. Limiting Super Users on which hosts are usually wise because it limits other users to test the password. Some privileges (F I l E, P R O C E S, RELOAD and S H U T D O w N) are administrative privileges, and can only be granted with no *. * Global permission specifier. If you want, you can also grant these privileges without granting the permissions of the database level. For example, the following statements have established a Flush user, which does not do anything else except for the release of a FLUSH statement. This may be useful in the management script, as you need to perform actions such as refreshing the log during the log file cycle: Grant Reload on *. * To flush @ localhost iDentifiedby "flushpass" usually granting administrative privileges should be conservative Because users with these privileges may affect the operation of the server. Database level permissions apply to all tables in a specific database.
These privileges are granted using an on db_name. * Clause: grant all on samp_db. * To bill@racer.snake.net identifiedby "Rock" Grant Select on menagerie. * To ro_user @% identifiedifiedby "dirt" first statement will Bill All permissions grant any table for the SAMP_DB database. The second statement creates a restricted user R o _ user, which can access all tables in the Menager Database, but only read. That is, the user can only release the SELECT statement. How to specify local hostnames in an authorization form If you use the server's host name and non-localhost, there is usually a problem that is connected from the server host. This may be due to the error match between the method and the name reslover routine method of the name reslover routine in the authorization list. If the resolver reports an unlimited name (such as PIT - VI PER), the authorization table contains items with fully qualified names (such as Pit - Vi PER. Snake. Net, and vice versa), error match occurs . In order to determine if this situation is in the system, you can try to connect to the local server with the -H option, which specifies the name of the host. Then check the regular log file of the server. How did it report the host name? Is it in a non-qualified form or a limited form? Regardless of whether it is, you will tell you how to specify the host name part of the user's specification when you publish your GRANT statement. Many granted single permissions can be listed at the same time. For example, if you want the user to read and modify the contents of the already table, you don't allow you to create a new table or delete table, you can authorize: Grant SELECT, INSERT, DELETE, UPDATE ON SAMP_DB. * To Jennie @% Identifiedby "Boron" For smaller particle size (Fine - grained) access control, you can authorize on a single table, even on a single column of the table. When there is a table to be hidden to the user, or when only the user is allowed to modify the particular column, the exclusive permissions are useful. Assuming that there are some volunteers in the History League meeting to help you work as the responsibilities of the League Secretary should perform. This is a good news, but you decided to first grant the new assistant to the permission to the Member table read-only (this table contains information about membership), then adds the column of the expedition of the expedition of the table with Update Permission. That is, your assistant can make a change of the deadline when people update their membership. Setting this mysql user's statement as follows: The first statement grants read access to the entire Member table and set the password. The second statement adds UPDATE privileges, but just columns to the expiration. There is no need to specify your password again because it has been completed in the first statement. If you want to grant column proprietary permissions for multiple columns, you can specify a list of lists and divide them with a comma. For example, in order to increase the UPDATE permissions for the Member table address column to the Assistant user, you can use the following statements. New permissions will be added to all columns already existing on the user: Grant Update (Street, City, State, Zip) on Samp_Db.member to assistant @ localhost usually, do not give users more needed than actual needs Permission.
However, when you want to enable the user to create a temporary table of the memory intermediate results, it is not allowed to do so in the database containing the data they cannot modify, there is a lot of permissions to grant on the database. Reason. You can create a separate database (the author calls it T m P) and grants all permissions of the user. For example, if any of the users who want the Mars.NET domain can use the TMP database, you can release the following GRANT statement: grant all on tmp. * To ""@%.mars.net After completing these, users can use The name of the Tmp.TBL_NAME format creates and references the table in the TMP database ("" in the user specifier "to create an anonymous user item, and any user matches the blank username). 3. Allow users to manage privileges? You can allow database owners to control access to the database by granting all permissions of the database owner database and specifying with Grant Option when operating. For example, if you want Alicia to connect to all hosts in a BIG.CORP.COM domain and manage all tables in the Sales database, you should use the following GRANT statement: In fact, the WITH GRANT OPTION clause allows access to the right to access Give another user. To know, two users with GRANT privileges can give their own permissions. If only gives SELECT permissions, the second user can "power" the first user "strong" will be "strong" to another user to grant GRANT and other permissions except SELECT. Cancel permissions and delete users can use the REVOKE statement in order to recover the permissions of a user. In addition to replacing TO with from and without IdentifiedBy or with Grant Option clauses, Revoke's syntax and GRANT statement are very similar: Revoke Privies (Columns) On What from User User section must with the original GRANT you want to cancel your permissions The USER section of the statement matches. The PRIV I L E G e s sections do not need to match, you can use the GRANT statement to be authorized, then part of the part of the REVOKE statement. The REVOKE statement deletes permissions and does not delete the user. The user's item is still kept in the User table, even if you cancel all permissions of the user. This means that the user can still connect to the server. To delete the entire user, you must use the DELETE statement to delete the user's record from the USER table: DELETE statement deletes the user's item, the FLUSH statement tells the server to reload the authorization table (when using the GRANT or REVOKE statement, not direct modification These tables will be automatically reloaded when authorization forms. Section 11.4 will discuss why the User entries will be discussed. One permission problem, the first part is a case in which a new MySQL administrator adds an item to a user, using the host name part, which is specified in a mode. For example: Grant All on Samp_DB. * To Fred@%.snake.Net Identifiedby "Cocoa" The intent here is to allow the user Fred to connect from all hosts of the snake.net field and have all permissions to the SAMP_DB database. In fact, FRED can connect from those hosts (except for the server host itself). When Fred tries to connect from the server host, the attempt is made with "access rejection" messages. This is true even if the user specifies the correct password.
