Copy from http://comp4.com/
Mysql User Management (2) Add Time: 19 May 2004 01:09 Views: 550
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 that you can connect 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 "All tables of SAMP_DB Database) The other extreme is that you may have a frequent travel and need user Max from the host around the world. In this case You can allow him to connect, no matter where: Grant All on Samp_DB. * To max @% identified by "diamond" "%" font from the wildcard function, the meaning of the LIKE mode match is the same. 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 least unsafe. Take it, you can allow a user to access from a limited host. For example, Allow MARY to 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, user identify the word The host section can be given by the IP address instead of a host name. You can specify an IP address or a location containing the mode character, and you can also specify from MySQL 3.23, you can also specify the network number IP number of the number of network masks: 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 Rex@192.168.128.0/17 Identified by "Ruby" The first example indicates the specific host that the user can connect from its connection, the second specified IP mode for Class Class 192.168.128, and In the three statements, 192.168.128.0 / 17 Specify a 17-bit network number and match the IP address with 192.168.128 heads. If Mysql complained that you specified user values, you may need to use quotation marks (only username and The host name part is separately added to the quotation number) .GRANT All on samp_db.president to "my friends" @ "BOA.SNAKE.NET" 1.2 What level of license should be available and what should they apply? You can authorize licenses 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 the authorized to other users, send the following statement: grant all on *. * To Ethel @ localhost identified by "coffee" with the Grant Option on clause of *. * Means "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 permission (File, Process, Reload, and Shutdown are managed permission and can only authorize the "on *. *" Global permission specified. If you like, you can authorize these permissions without authorizing the library 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 relumb on *. * To flushl @ localhost Identified by "flushpass" Generally, you want to authorize management permissions, 点, because of their users It can affect your server's operation. Database grade permissions apply to all tables in a specific database, they can be granted by using on db_name. * Clause: GRANT ALL ON SAMP_DB TO BILL@racer.snake.net indetified by "Rock" Grant SELECT ON SAMP_DB TO RO_USER @% indetified by "ROCK" first statement to the list of all tables in the SAMP_DB database, the second creation of a strict restricted user RO_USER (only the user) can only access the SAMP_DB database All tables, but only read, that is, the user can only issue a SELECT statement. You can list all permissions to grant a series of simultaneous grants. 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 permissions: 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 of the tables. When you want to hide a table of a table, or if you want a user to modify a specific column, column specific permissions is very useful. Such as: 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 permissions for the entire Member table and set a password, The second statement adds Update permissions when only the expiration column. 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, for the Update permission of the address field of the Member table for Assistant users, use the following statement, the new permissions will be added to the user's license: Grant Update (Street, City, State, Zip) on Samp_DB To Assistant @localhost usually, you don't want to grant any permissions whispered than users' permissions. However, when you want users to create a temporary table to save the intermediate results, you don't want them to have a relative to a database in a database in a database in a database that contains them in a database. Loose permission. You can do it by establishing a separate repository (such as TMP) and grant all permissions on the database.
