如何设置权限
我现在有一个数据库lunch我需要建立一个本地访问的账号 对lunch库的表都可以读写
建立一个外部可以访问的一个账号 对该库的两张表可写，其他都为只读
请问我在mysql中如何设置 最简单的使用phpMyAdmin点击记下鼠标就Ok了
如果想知道得backwards and forwards
请看官方文档的
5.7.5. Access Control, Stage 1: Connection Verification
When you attempt to connect to a MySQL server, the server accepts or rejects the connection based on your identity and whether you can verify your identity by supplying the correct password. If not, the server denies access to you completely. Otherwise, the server accepts the connection, then enters Stage 2 and waits for requests.
Your identity is based on two pieces of information:
The client host from which you connect
Your MySQL username
Identity checking is performed using the three user table scope columns (Host, User, and Password). The server accepts the connection only if the Host and User columns in some user table record match the client hostname and username, and the client supplies the password specified in that record.
Host values in the user table may be specified as follows:
A Host value may be a hostname or an IP number, or 'localhost' to indicate the local host.
You can use the wildcard characters ‘%’ and ‘_’ in Host column values. These have the same meaning as for pattern-matching operations performed with the LIKE operator. For example, a Host value of '%' matches any hostname, whereas a value of '%.mysql.com' matches any host in the mysql.com domain.
For Host values specified as IP numbers, you can specify a netmask indicating how many address bits to use for the network number. For example:
mysql> GRANT ALL PRIVILEGES ON db.*
-> TO david@'192.58.197.0/255.255.255.0';
This allows david to connect from any client host having an IP number client_ip for which the following condition is true:
client_ip & netmask = host_ip
That is, for the GRANT statement just shown:
client_ip & 255.255.255.0 = 192.58.197.0
IP numbers that satisfy this condition and can connect to the MySQL server are those that lie in the range from 192.58.197.0 to 192.58.197.255.
Note: The netmask can only be used to tell the server to use 8, 16, 24, or 32 bits of the address, for example:
192.0.0.0/255.0.0.0 (anything on the 192 class A network)
192.168.0.0/255.255.0.0 (anything on the 192.168 class B network)
192.168.1.0/255.255.255.0 (anything on the 192.168.1 class C network)
192.168.1.1 (only this specific IP)
The following netmask (28 bits) will not work:
192.168.0.1/255.255.255.240
A blank Host value in a db table record means that its privileges should be combined with those in the row in the host table that matches the client hostname. The privileges are combined using an AND (intersection) operation, not OR (union). You can find more information about the host table in Section 5.7.6, “Access Control, Stage 2: Request Verification”.
A blank Host value in the other grant tables is the same as '%'.
Because you can use IP wildcard values in the Host column (for example, '144.155.166.%' to match every host on a subnet), someone could try to exploit this capability by naming a host 144.155.166.somewhere.com. To foil such attempts, MySQL disallows matching on hostnames that start with digits and a dot. Thus, if you have a host named something like 1.2.foo.com, its name never matches the Host column of the grant tables. An IP wildcard value can match only IP numbers, not hostnames.
In the User column, wildcard characters are not allowed, but you can specify a blank value, which matches any name. If the user table row that matches an incoming connection has a blank username, the user is considered to be an anonymous user with no name, not a user with the name that the client actually specified. This means that a blank username is used for all further access checking for the duration of the connection (that is, during Stage 2).
The Password column can be blank. This is not a wildcard and does not mean that any password matches. It means that the user must connect without specifying a password.
Non-blank Password values in the user table represent encrypted passwords. MySQL does not store passwords in plaintext form for anyone to see. Rather, the password supplied by a user who is attempting to connect is encrypted (using the PASSWORD() function). The encrypted password then is used during the connection process when checking whether the password is correct. (This is done without the encrypted password ever traveling over the connection.) From MySQL's point of view, the encrypted password is the REAL password, so you should not give anyone access to it! In particular, don't give non-administrative users read access to the tables in the mysql database!
MySQL 5.0 employs the stronger authentication method (first implemented in MySQL 4.1) that has better password protection during the connection process than in earlier versions. It is secure even if TCP/IP packets are sniffed or the mysql database is captured. Password encryption is discussed further in Section 5.7.9, “Password Hashing in MySQL 4.1”.
The following examples show how various combinations of Host and User values in the user table apply to incoming connections:
Host Value User Value Connections Matched by Entry
'thomas.loc.gov' 'fred' fred, connecting from thomas.loc.gov
'thomas.loc.gov' '' Any user, connecting from thomas.loc.gov
'%' 'fred' fred, connecting from any host
'%' '' Any user, connecting from any host
'%.loc.gov' 'fred' fred, connecting from any host in the loc.gov domain
'x.y.%' 'fred' fred, connecting from x.y.net, x.y.com, x.y.edu, and so on. (this is probably not useful)
'144.155.166.177' 'fred' fred, connecting from the host with IP address 144.155.166.177
'144.155.166.%' 'fred' fred, connecting from any host in the 144.155.166 class C subnet
'144.155.166.0/255.255.255.0' 'fred' Same as previous example
It is possible for the client hostname and username of an incoming connection to match more than one row in the user table. The preceding set of examples demonstrates this: Several of the entries shown match a connection from thomas.loc.gov by fred.
When multiple matches are possible, the server must determine which of them to use. It resolves this issue as follows:
Whenever the server reads the user table into memory, it sorts the entries.
When a client attempts to connect, the server looks through the entries in sorted order.
The server uses the first row that matches the client hostname and username.
To see how this works, suppose that the user table looks like this:
+-----------+----------+-
| Host | User | …
+-----------+----------+-
| % | root | …
| % | jeffrey| …
| localhost | root | …
| localhost | | …
+-----------+----------+-
When the server reads in the table, it orders the entries with the most-specific Host values first. Literal hostnames and IP numbers are the most specific. The pattern '%' means “any host” and is least specific. Entries with the same Host value are ordered with the most-specific User values first (a blank User value means “any user” and is least specific). For the user table just shown, the result after sorting looks like this:
+-----------+----------+-
| Host | User | …
+-----------+----------+-
| localhost | root | … ...
| localhost | | … ...
| % | jeffrey| … ...
| % | root | … ...
+-----------+----------+-
When a client attempts to connect, the server looks through the sorted entries and uses the first match found. For a connection from localhost by jeffrey, two of the entries in the table match: the one with Host and User values of 'localhost' and '', and the one with values of '%' and 'jeffrey'. The 'localhost' row appears first in sorted order, so that is the one the server uses.
Here is another example. Suppose that the user table looks like this:
+----------------+----------+-
| Host | User | …
+----------------+----------+-
| % | jeffrey| …
| thomas.loc.gov | | …
+----------------+----------+-
The sorted table looks like this:
+----------------+----------+-
| Host | User | …
+----------------+----------+-
| thomas.loc.gov | | …
| % | jeffrey| …
+----------------+----------+-
A connection by jeffrey from thomas.loc.gov is matched by the first row, whereas a connection by jeffrey from whitehouse.gov is matched by the second.
It is a common misconception to think that, for a given username, all entries that explicitly name that user are used first when the server attempts to find a match for the connection. This is simply not true. The previous example illustrates this, where a connection from thomas.loc.gov by jeffrey is first matched not by the row containing 'jeffrey' as the User column value, but by the row with no username. As a result, jeffrey is authenticated as an anonymous user, even though he specified a username when connecting.
If you are able to connect to the server, but your privileges are not what you expect, you probably are being authenticated as some other account. To find out what account the server used to authenticate you, use the CURRENT_USER() function. It returns a value in user_name@host_name format that indicates the User and Host values from the matching user table record. Suppose that jeffrey connects and issues the following query:
mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| @localhost |
+----------------+
The result shown here indicates that the matching user table row had a blank User column value. In other words, the server is treating jeffrey as an anonymous user.
Another thing you can do to diagnose authentication problems is to print out the user table and sort it by hand to see where the first match is being made. See also Section 12.9.3, “Information Functions”.
5.7.6. Access Control, Stage 2: Request Verification
Once you establish a connection, the server enters Stage 2 of access control. For each request that comes in on the connection, the server determines what operation you want to perform, then checks whether you have sufficient privileges to do so. This is where the privilege columns in the grant tables come into play. These privileges can come from any of the user, db, host, tables_priv, or columns_priv tables. (You may find it helpful to refer to Section 5.7.2, “How the Privilege System Works”, which lists the columns present in each of the grant tables.)
The user table grants privileges that are assigned to you on a global basis and that apply no matter what the current database is. For example, if the user table grants you the DELETE privilege, you can delete rows from any table in any database on the server host! In other words, user table privileges are superuser privileges. It is wise to grant privileges in the user table only to superusers such as database administrators. For other users, you should leave the privileges in the user table set to 'N' and grant privileges at more specific levels only. You can grant privileges for particular databases, tables, or columns.
The db and host tables grant database-specific privileges. Values in the scope columns of these tables can take the following forms:
The wildcard characters ‘%’ and ‘_’ can be used in the Host and Db columns of either table. These have the same meaning as for pattern-matching operations performed with the LIKE operator. If you want to use either character literally when granting privileges, you must escape it with a backslash. For example, to include the underscore character (‘_’) as part of a database name, specify it as ‘\_’ in the GRANT statement.
A '%' Host value in the db table means “any host.” A blank Host value in the db table means “consult the host table for further information” (a process that is described later in this section).
A '%' or blank Host value in the host table means “any host.”
A '%' or blank Db value in either table means “any database.”
A blank User value in either table matches the anonymous user.
The server reads in and sorts the db and host tables at the same time that it reads the user table. The server sorts the db table based on the Host, Db, and User scope columns, and sorts the host table based on the Host and Db scope columns. As with the user table, sorting puts the most-specific values first and least-specific values last, and when the server looks for matching entries, it uses the first match that it finds.
The tables_priv and columns_priv tables grant table-specific and column-specific privileges. Values in the scope columns of these tables can take the following form:
The wildcard characters ‘%’ and ‘_’ can be used in the Host column of either table. These have the same meaning as for pattern-matching operations performed with the LIKE operator.
A '%' or blank Host value in either table means “any host.”
The Db, Table_name, and Column_name columns cannot contain wildcards or be blank in either table.
The server sorts the tables_priv and columns_priv tables based on the Host, Db, and User columns. This is similar to db table sorting, but simpler because only the Host column can contain wildcards.
The request verification process is described here. (If you are familiar with the access-checking source code, you may notice that the description here differs slightly from the algorithm used in the code. The description is equivalent to what the code actually does; it differs only to make the explanation simpler.)
For requests that require administrative privileges such as SHUTDOWN or RELOAD, the server checks only the user table row because that is the only table that specifies administrative privileges. Access is granted if the row allows the requested operation and denied otherwise. For example, if you want to execute mysqladmin shutdown but your user table row doesn't grant the SHUTDOWN privilege to you, the server denies access without even checking the db or host tables. (They contain no Shutdown_priv column, so there is no need to do so.)
For database-related requests (INSERT, UPDATE, and so on), the server first checks the user's global (superuser) privileges by looking in the user table row. If the row allows the requested operation, access is granted. If the global privileges in the user table are insufficient, the server determines the user's database-specific privileges by checking the db and host tables:
The server looks in the db table for a match on the Host, Db, and User columns. The Host and User columns are matched to the connecting user's hostname and MySQL username. The Db column is matched to the database that the user wants to access. If there is no row for the Host and User, access is denied.
If there is a matching db table row and its Host column is not blank, that row defines the user's database-specific privileges.
If the matching db table row's Host column is blank, it signifies that the host table enumerates which hosts should be allowed access to the database. In this case, a further lookup is done in the host table to find a match on the Host and Db columns. If no host table row matches, access is denied. If there is a match, the user's database-specific privileges are computed as the intersection (not the union!) of the privileges in the db and host table entries; that is, the privileges that are 'Y' in both entries. (This way you can grant general privileges in the db table row and then selectively restrict them on a host-by-host basis using the host table entries.)
After determining the database-specific privileges granted by the db and host table entries, the server adds them to the global privileges granted by the user table. If the result allows the requested operation, access is granted. Otherwise, the server successively checks the user's table and column privileges in the tables_priv and columns_priv tables, adds those to the user's privileges, and allows or denies access based on the result.
Expressed in boolean terms, the preceding description of how a user's privileges are calculated may be summarized like this:
global privileges
OR (database privileges AND host privileges)
OR table privileges
OR column privileges
It may not be apparent why, if the global user row privileges are initially found to be insufficient for the requested operation, the server adds those privileges to the database, table, and column privileges later. The reason is that a request might require more than one type of privilege. For example, if you execute an INSERT INTO ... SELECT statement, you need both the INSERT and the SELECT privileges. Your privileges might be such that the user table row grants one privilege and the db table row grants the other. In this case, you have the necessary privileges to perform the request, but the server cannot tell that from either table by itself; the privileges granted by the entries in both tables must be combined.
The host table is not affected by the GRANT or REVOKE statements, so it is unused in most MySQL installations. If you modify it directly, you can use it for some specialized purposes, such as to maintain a list of secure servers. For example, at TcX, the host table contains a list of all machines on the local network. These are granted all privileges.
You can also use the host table to indicate hosts that are not secure. Suppose that you have a machine public.your.domain that is located in a public area that you do not consider secure. You can allow access to all hosts on your network except that machine by using host table entries like this:
+--------------------+----+-
| Host | Db | ...
+--------------------+----+-
| public.your.domain | %| ... (all privileges set to 'N')
| %.your.domain | %| ... (all privileges set to 'Y')
+--------------------+----+-
Naturally, you should always test your entries in the grant tables (for example, by using SHOW GRANTS or mysqlaccess) to make sure that your access privileges are actually set up the way you think they are.
5.7.7. When Privilege Changes Take Effect
When mysqld starts, all grant table contents are read into memory and become effective for access control at that point.
When the server reloads the grant tables, privileges for existing client connections are affected as follows:
Table and column privilege changes take effect with the client's next request.
Database privilege changes take effect at the next USE db_name statement.
Changes to global privileges and passwords take effect the next time the client connects.
If you modify the grant tables using GRANT, REVOKE, or SET PASSWORD, the server notices these changes and reloads the grant tables into memory again immediately.
If you modify the grant tables directly using statements such as INSERT, UPDATE, or DELETE, your changes have no effect on privilege checking until you either restart the server or tell it to reload the tables. To reload the grant tables manually, issue a FLUSH PRIVILEGES statement or execute a mysqladmin flush-privileges or mysqladmin reload command.
If you change the grant tables directly but forget to reload them, your changes have no effect until you restart the server. This may leave you wondering why your changes don't seem to make any difference!
页:
[1]