|
|
External Security: Preventing Unauthorized Network AccessThe MySQL security system is flexible. It allows you to set up user access privileges in many different ways. Normally, you do this by using the GRANT and REVOKE statements, which modify on your behalf the grant tables that control client access. However, you might find that user privileges don't seem to be working the way you want. For such situations, it's helpful to understand the structure of the MySQL grant tables and how the server uses them to determine access permissions. Such an understanding allows you to add, remove, or modify user privileges by modifying the grant tables directly. It also allows you to diagnose privilege problems when you examine the tables. I assume that you've read "Managing MySQL User Accounts," in Chapter 11, and that you understand how the GRANT and REVOKE statements work. GRANT and REVOKE provide a convenient way for you to set up MySQL user accounts and associate privileges with them, but they are just a front end. All the real action takes place in the MySQL grant tables. (In fact, the section "Setting Up MySQL Accounts Without GRANT Statements" later in this chapter discusses how to modify the grant tables directly to achieve the same results that you get by issuing GRANT statements.) Structure and Contents of the MySQL Grant TablesAccess to MySQL databases by clients that connect to the server over the network is controlled by the contents of the grant tables. These tables are located in the mysql database and are initialized during the process of installing MySQL on a machine for the first time (as described in Appendix A, "Obtaining and Installing Software," for example). These tables are named user, db, tables_priv, columns_priv, and host. They are used as follows:
The structure of each grant table is shown in the next several tables, broken down by type of column. All grant tables contain two primary kinds of columns: scope-of-access columns that determine when an entry applies, and privilege columns that determine which privileges an entry grants. The privilege columns can be subdivided further into columns for administrative operations and those that are related to database and table operations. The user table has additional columns for SSL connections and resource management; these are present only in the user table because they apply globally. Some of the grant tables contain other miscellaneous columns, but they don't concern us here because they have no bearing on account management.
The grant table system includes tables_priv and columns_priv tables for setting up table-specific and column-specific privileges. However, there is no analogous rows_priv table, because MySQL doesn't provide record-level privileges. For example, you cannot restrict a user's access to just those rows in a table that contain a particular value in some column. If you need this capability, you must implement it within your own applications. (One way to implement cooperative record-level locking is to use advisory locking functions such as GET_LOCK() and RELEASE_LOCK(). The procedure for this is described in "Advisory Locking Functions," in Appendix C, "Operator and Function Reference.") New releases of MySQL sometimes add new privileges. When upgrading an existing MySQL installation to such a version, it's necessary to update the grant tables before you can use the new privileges. The procedure for doing this is given in "Dealing with Changes to Grant Table Structure," in Chapter 11. Grant Table Scope-of-Access ColumnsThe grant table scope columns are used to determine which rows to use when a given account attempts to perform a given operation. Each grant table entry contains Host and User columns to indicate that the entry applies to connections from a given host by a particular user. For example, a user table record with localhost and bill in the Host and User columns would be used for connections from the local host by bill, but not for connections by betty. (The host table is an exception; it's used in a special way that we won't get into just yet.) The other tables contain additional scope columns. The db table contains a Db column to indicate which database the entry applies to. Similarly, rows in the tables_priv and columns_priv tables contain scope columns that further narrow their scope to a particular table in a database or column in a table. Grant Table Privilege ColumnsThe grant tables also contain privilege columns. These indicate which privileges are held by the user who matches the values listed in the scope columns. The privileges supported by MySQL are shown in the following lists, which describe the administrative privileges and the privileges that control database and table access. Each list uses the privilege names that are used for the GRANT statement. For the most part, these privilege names bear an obvious resemblance to the names of privilege columns in the user, db, and host tables. For example, the SELECT privilege corresponds to the Select_priv column. Administrative PrivilegesThe following privileges apply to administrative operations that control the operation of the server or a user's ability to grant privileges:
Database and Table PrivilegesThe following privileges apply to operations on databases and tables:
Some operations require a combination of privileges. For example, REPLACE may implicitly cause a DELETE followed by an INSERT, so it requires both the DELETE and INSERT privileges. How the Grant Tables Represent PrivilegesIn the user, db, and host tables, each privilege is specified as a separate column. These columns are all defined to have a type of ENUM('N','Y'), with a default value of 'N' (off). For example, the Select_priv column is defined like this:
Select_priv ENUM('N','Y') NOT NULL DEFAULT 'N'
Privileges in the tables_priv and columns_priv tables are represented by a SET, which allows any combination of privileges to be stored in a single column. The Table_priv column in the tables_priv table is defined like this:
SET('Select','Insert','Update','Delete','Create','Drop',
'Grant','References','Index','Alter') NOT NULL DEFAULT ''
The Column_priv column in the columns_priv table is defined like this:
SET('Select','Insert','Update','References') NOT NULL DEFAULT ''
The reason there are fewer column privileges than table privileges is that fewer operations make sense at the column level. For example, you can delete a row from a table to remove it, but you can't delete individual columns of a row. Note that INSERT exists at the column level. If you have the INSERT privilege only for some columns in a table, you can specify values only for those columns when inserting new records; the other columns will be set to their default values. The tables_priv and columns_priv tables are newer than the other three, which is why they use the more efficient SET representation to list multiple privileges in a single column. (It's possible that the user, db, and host tables may be reorganized in the future to represent privileges by SET columns as well.) The user table contains several administrative privilege columns that are not present in any of the other grant tables, such as File_priv, Process_priv, Reload_priv, and Shutdown_priv. Such privileges are present only in the user table because they are global privileges that are not associated with any particular database or table. It doesn't make sense to allow or not allow a user to shut down the server based on what the current database is, for example. Grant Table SSL-Related ColumnsSeveral columns in the user table apply to authentication of secure connections over SSL. The primary column is ssl_type, which indicates whether and what type of secure connection is required for an account. ssl_type is represented as an ENUM with four possible values:
ssl_type ENUM('','ANY','X509','SPECIFIED') NOT NULL
The ssl_type values have the following meanings:
For all ssl_type values except 'SPECIFIED', the server ignores the values in the other SSL-related columns when validating client connection attempts. For 'SPECIFIED', the server checks the other columns, and for any that have non-empty values, the client must supply matching information. These other columns are as follows:
ssl_cipher, x509_issuer, and x509_subject all are represented in the user table as BLOB columns. More information about using SSL for secure connections is given later in the chapter, in "Setting Up Secure Connections." Grant Table Resource Management ColumnsThe following columns in the user table allow you to limit the extent to which any given MySQL account can consume server resources:
If the server restarts, the current counters are reset to zero. A reset also occurs, except for the max_user_connections value, if you reload the grant tables or issue a FLUSH USER_RESOURCES statement. More information about setting account limits is given in "Limiting an Account's Resource Consumption," in Chapter 11. How the Server Controls Client AccessThere are two stages of client access control when you use MySQL. The first stage occurs when you attempt to connect to the server. The server looks at the user table to see if it can find an entry that matches the host you're connecting from, your name, and the password you supplied. If there is no match, you can't connect. If there is a match, the server also checks the user table SSL and resource management columns:
If everything checks out okay, the server establishes the connection and you proceed to the second stage. If you are making a secure connection, your client and the server encrypt the traffic between them. In the second stage, the server checks two things for each statement you issue. First, it checks your statements-per-hour and updates-per-hour limits. Second, the server checks the grant tables to verify that you have sufficient privileges to perform the statement. The limits are checked before your access privileges because if you've reached the limits, there is little point in checking your privileges. The second stage continues until you disconnect from the server. The following discussion describes in some detail the rules that the MySQL server uses to match grant table entries to incoming client connection requests and to statements. This includes the types of values that are legal in the grant table scope columns, how privilege values from different grant tables are combined, and the order in which the server searches entries from a given grant table. Scope Column ContentsEach scope column is governed by rules that define what kinds of values are legal and how the server interprets those values. Some of the scope columns require literal values, but most of them allow wildcard or other special values.
Some scope columns are treated by the server as case sensitive, whereas others are not, as summarized in Table 12.4. Note in particular that Db and Table_name values are always treated as case sensitive, even though treatment of database and table names in statements depends on the case sensitivity of the filesystem on which the server runs (typically case sensitive under Unix, and not case sensitive under Windows).
Statement Access VerificationEach time you issue a statement, the server determines whether you've reached your statement resource limits. These limits are given by the max_questions and max_updates values stored in the user table. If you have not reached your limits, the server also checks whether you have sufficient privileges to execute the statement. It determines your access privileges by checking, in order, the privileges from the user, db, tables_priv, and columns_priv tables, until the server either verifies that you have proper access or it has searched all the tables in vain. More specifically:
In boolean terms, the privileges from the grant tables are combined by the server as follows: user OR db OR tables_priv OR columns_priv I see that you're wondering why the preceding description refers to only four grant tables when there are five grant tables. Okay, you caught me. The server really checks access permissions like this: user OR (db AND host) OR tables_priv OR columns_priv I showed the simpler expression first because the more complex expression will never come into play for most MySQL installations. That's because the host table that appears in the more complex expression is completely unaffected by the GRANT and REVOKE statements. The host table is affected only if you manipulate it directly with INSERT, UPDATE, and so forth. This means that if you adopt the usual administrative policy of managing user accounts with GRANT and REVOKE, your host table will never be used and you can forget about it entirely. I have on occasion inquired on the MySQL mailing lists to find out how many people actually use the host table. The response always has been rather minimal, so it appears that this table sees very little use in practice. Nevertheless, if you do want to use the host table, here's how it works:
The preceding description no doubt makes access checking sound like a rather complicated process, especially when you consider that the server checks privileges for every single statement that clients issue. However, the process is quite fast because the server doesn't actually look up information from the grant tables for every statement. Instead, it reads the contents of the tables into memory when it starts, and then verifies statements using the in-memory copies. This gives a performance boost to access-checking operations. Furthermore, if you keep your privilege specifications simple, you can ensure that access checking is as fast as possible. When the server reads the grant tables into memory, it notices whether any accounts have resource limits, and whether any have table-level or column-level privileges. If not, it knows that it need not check any of those types of information when checking privileges for statements issued by clients. This means the server can omit certain steps from the full access-checking procedure. The use of in-memory copies of the grant tables for access checking has an important side effect: If you change the contents of the grant tables directly, the server won't notice the privilege change. For example, if you add a new MySQL user by using an INSERT statement to add a new record to the user table, that in itself will not allow the user named in the entry to connect to the server. This is something that often confuses new administrators (and sometimes more-experienced ones!), but the solution is quite simple: Tell the server to reload the contents of the grant tables after you change them. You can do this by issuing a FLUSH PRIVILEGES statement or by executing mysqladmin flush-privileges or mysqladmin reload. There is no need to tell the server to reload the grant tables when you use GRANT, REVOKE, SET PASSWORD, CREATE USER, DROP USER, or RENAME USER to set up or modify user accounts. The server maps those statements onto operations that modify the grant tables, and then refreshes the in-memory copies of the tables automatically. Scope Column Matching OrderThe MySQL server sorts entries from the grant tables in a particular way, and then tries to match incoming connections by looking through the entries in order. The first match found determines the entry that is used. It's important to understand the sort order that MySQL uses, especially for the user table. This seems to trip up a lot of people in their attempts to understand MySQL security. When the server reads the contents of the user table, it sorts entries according to the values in the Host and User columns. The Host column is dominant, so entries with the same Host value are sorted together, and then ordered according to the User value. However, sorting is not lexical, or rather, it's only partially so. The principle to keep in mind is that literal values are preferred over patterns, and more-specific patterns are preferred over less-specific patterns. This means that if you're connecting from boa.snake.net and there are entries with Host values of boa.snake.net and %.snake.net, the first entry will be preferred. Similarly, %.snake.net is preferred over %.net, which in turn is preferred over %. Matching for IP numbers works that way, too. For a client connecting from a host with an IP number of 192.168.3.14, enTRies with the following Host values all match, but are preferred in the order shown: 192.168.3.14 192.168.3.% 192.168.% 192.% % Another principle to remember is that when the server tries to match user table entries, it looks for a Host value match first and a User value match second. It's a common error to think that the server looks for a User match first. A Privilege PuzzleThis section describes a particular scenario that demonstrates why it's useful to understand the order in which the server searches user table entries when validating connection attempts. It also shows how to solve a problem that seems to be fairly common with new MySQL installations, at least judged by the frequency with which it comes up on the MySQL mailing lists: A MySQL administrator sets up a new installation, including the default root and anonymous-user entries in the user table. A good administrator will assign passwords for the root accounts, but it's common to leave the anonymous users as is, with no passwords. Now, suppose that the administrator wants to set up a new account for a user who will be connecting from several different hosts. The easiest way to allow this is by creating the account with % as the host part of the account name in the GRANT statement so that the user can connect from anywhere: GRANT ALL ON sampdb.* TO 'fred'@'%' IDENTIFIED BY 'cocoa'; The intent here is to grant the user fred all privileges for the sampdb database and to allow him to connect from any host he likes. Unfortunately, the probable result is that fred will be able to connect from any host, except the server host itself! Suppose that the server host is named cobra.snake.net. When fred tries to connect from boa.snake.net, the attempt succeeds: % mysql -p -u fred -h cobra.snake.net sampdb Enter password: cocoa mysql> But if fred logs in on the server host cobra.snake.net and tries to connect, the attempt fails, even though fred supplies his password correctly: % mysql -p -u fred -h localhost sampdb Enter password: cocoa ERROR 1045 (28000): Access denied for user 'fred'@'localhost' (using password: YES) This problem occurs if your user table contains the default anonymous-user entries (the entries with blank usernames). These entries are created by the mysql_install_db initialization script under Unix and are present in the pre-initialized user table included with Windows distributions. The reason the second connection attempt fails is that when the server attempts to validate fred, one of the anonymous-user entries takes precedence over fred's entry in the matching order. The anonymous-user entry requires the user to connect with no password (rather than with the password cocoa), so a password mismatch results. Why does this happen? To understand what's going on, it's necessary to consider both how MySQL's grant tables are set up initially and how the server uses user table entries when it validates client connections. For example, under Unix, when you run the mysql_install_db script on cobra.snake.net to initialize the grant tables, the resulting user table contains rows with Host and User values that look like this:[1]
+-----------------+------+ | Host | User | +-----------------+------+ | localhost | root | | cobra.snake.net | root | | localhost | | | cobra.snake.net | | +-----------------+------+ The first two entries allow root to connect to the server on the local host by specifying either localhost or the host's actual name. The second two entries allow users to connect anonymously to the local server. After the administrator sets up the account for fred with the GRANT statement shown earlier, the user table contains these entries: +-----------------+------+ | Host | User | +-----------------+------+ | localhost | root | | cobra.snake.net | root | | localhost | | | cobra.snake.net | | | % | fred | +-----------------+------+ But the order of the entries as shown is not the order that the server uses when validating connection requests. Instead, it sorts entries by host first and then by user within host, putting more-specific values first and less-specific values last: +-----------------+------+ | Host | User | +-----------------+------+ | localhost | root | | localhost | | | cobra.snake.net | root | | cobra.snake.net | | | % | fred | +-----------------+------+ The two entries with localhost in the Host column sort together, with the entry for root first because that's a more specific username than the blank value. The entries with cobra.snake.net sort together in a similar way. Furthermore, all four of these entries have a literal Host value without any wildcard characters, so they all sort ahead of the entry for fred, which does use a wildcard character in its Host value. In particular, both of the anonymous-user entries take precedence over fred's entry in the sort order. The result is that when fred attempts to connect from the local host, one of the entries with a blank username matches before the entry containing % in the Host column. The blank password in the anonymous-user entry doesn't match fred's password of cocoa, so the connection fails. One implication of this phenomenon is that it is possible for fred to connect from the local host, but only if he specifies no password. Unfortunately, then he will be validated as an anonymous user and won't have the privileges associated with the fred@% account. What all this means is that although it's very convenient to use wildcards when you set up an account for a user who will connect from multiple hosts, the user may have problems connecting from the local host due to the anonymous entries in the user table. What is the solution to this problem? Actually, you can solve it two ways. First, you can set up another account for fred that explicitly lists localhost as the host value: GRANT ALL ON sampdb.* TO 'fred'@'localhost' IDENTIFIED BY 'cocoa'; If you do that, the server will sort the entries from the user table as follows: +-----------------+------+ | Host | User | +-----------------+------+ | localhost | fred | | localhost | root | | localhost | | | cobra.snake.net | root | | cobra.snake.net | | | % | fred | +-----------------+------+ Now when fred connects from the local host, the entry with localhost and fred will match ahead of the anonymous-user entries. When he connects from any other host, the entry with % and fred will match. The downside of having two entries for fred is that whenever you want to change his privileges or password, you'll have to make the change twice. The second solution is much easier: Delete the anonymous accounts from the user table. To do this, you cannot use REVOKE, because that only revokes privileges; it won't remove account entries from the user table. Instead, use DELETE and then flush the privileges to tell the server to reread the grant tables: % mysql -p -u root mysql> DELETE FROM mysql.user WHERE User = ''; mysql> FLUSH PRIVILEGES; The sort order of the remaining entries becomes: +-----------------+------+ | Host | User | +-----------------+------+ | localhost | root | | cobra.snake.net | root | | % | fred | +-----------------+------+ Now when fred attempts to connect from the local host, he'll succeed, because there won't be any user table entries that will match ahead of his. In general, I recommend that if you want to make your life easier as an administrator, you should delete the anonymous-user entries that are present in the initial grant tables. (In my view, these entries are not very useful, and they tend to cause more problems than they're worth.) The puzzle presented in this section addresses a specific situation, but contains a more general lesson. If privileges for a given account don't work the way you expect, look in the grant tables to see if there's some entry containing Host values that are more specific than the entry for the user in question and that will match connection attempts by that user. If so, that may explain the problem. You might need to make the user's entry more specific, or add another entry to cover the more specific case. The SHOW GRANTS statement can be useful for checking what privileges accounts have. Grant Table Risks to AvoidThis section describes precautions to observe when you grant privileges, and the attendant risks of unwise choices. Avoid creating anonymous-user accounts. Even if they don't have sufficient privileges to cause damage directly, allowing a user to connect still may provide access to that user to look around and gather information such as what databases and tables you have, or to monitor the server with SHOW STATUS and SHOW VARIABLES. Find accounts that have no passwords and either remove them or assign passwords. To find such accounts, use this query:
mysql> SELECT Host, User FROM mysql.user WHERE Password = '';
Find accounts that have password hash values in the older pre-MySQL 4.1 format and change them to the more secure password hash format that is used as of MySQL 4.1. Values in the older format have a length of 16 and do not begin with the '*' character, so you can identify them using either of these statements: mysql> SELECT Host, User FROM mysql.user WHERE LENGTH(Password) = 16; mysql> SELECT Host, User FROM mysql.user WHERE Password NOT LIKE '*%'; Note, however, that you cannot institute this security measure unless all client programs that connect to your server are from MySQL 4.1 or later and know how to authenticate using the newer password mechanism. Assuming that you can use newer passwords for all clients, you need only make sure that the server was not started with the --old-passwords option, and then use SET PASSWORD to set the password for each account that has an old-format password. The new password format will be used for each account. For additional security, start the server with the --secure-auth option. Otherwise, a client can reset its password to the old format with OLD_PASSWORD() and then connect using that password. --secure-auth prevents clients from connecting unless they have a new-format password. Unless you really need to use patterns in hostname specifiers, avoid doing so when setting up accounts. Broadening the range of hosts from which a given user can connect also broadens the range from which an imposter claiming to be that user can try to break in. Grant superuser privileges sparingly. That is, don't enable privileges in user table entries. Those privileges are global and allow the user to affect the operation of your server or to access any table in any database. Grant privileges at a more specific level instead, to restrict user access to particular databases, tables, or columns. Don't grant privileges for the mysql database, because it contains the grant tables. A user with privileges for that database may be able to modify its tables to acquire privileges on any other database as well. In effect, granting privileges that allow a user to modify the mysql database tables gives that user a global GRANT OPTION privilege. (After all, if the user can modify the tables directly, that's equivalent to being able to issue any GRANT statement you can think of.) Be careful with the GRANT OPTION privilege. Two users with different privileges that both have the GRANT OPTION privilege can make each other's access rights more powerful. The FILE privilege is particularly dangerous; don't grant it lightly. Here's an example of something a user with the FILE privilege can do: CREATE TABLE etc_passwd (pwd_entry TEXT); LOAD DATA INFILE '/etc/passwd' INTO TABLE etc_passwd; After executing those statements, the user has access to the contents of your server host's password file just by issuing a SELECT: SELECT * FROM etc_passwd; The name of any publicly readable file on the server host may be substituted for /etc/passwd in the LOAD DATA statement. If the user has connected from a remote host, the effect is that granting the FILE privilege gives that user network access to potentially a large portion of your server host's filesystem. The FILE privilege also can be exploited to compromise databases on systems that aren't set up with sufficiently restrictive data directory permissions. This is one reason why you should set the data directory contents to be readable only by the server. If files corresponding to database tables are world-readable, not only can any user with an account on the server host read them, but any client user with the FILE privilege can connect over the network and read them, too! The following procedure demonstrates how:
To avoid having someone attack your users' tables in the same way, set the permissions on your data directory contents according to the instructions in given earlier in "Securing Your MySQL Installation." As an additional measure, avoid granting the SHOW DATABASE privilege and run the server with the --skip-show-database option. This prevents users from using SHOW DATABASES and SHOW TABLES for databases to which they have no access, and helps to keep users from finding out about databases and tables they shouldn't be accessing. The dangers of the FILE privilege are amplified if you run the MySQL server as root. That's inadvisable in the first place, and is particularly so when combined with FILE. Because root can create files anywhere in the filesystem, a user with the FILE privilege can do so as well, even a user who has connected from a remote host. The server won't create a file that already exists, but it's sometimes possible to create new files that will alter the operation of the server host or compromise its security. For example, if any of the files /etc/resolv.conf, /etc/hosts.equiv, /etc/hosts.lpd, or /etc/sudoers do not already exist, a user who is able to cause the MySQL server to create them can drastically change the way your server host behaves. To avoid these problems, don't run mysqld as root. (See "Running the Server Using an Unprivileged Login Account," in Chapter 11.) The PROCESS and SUPER privileges should be granted only to trusted MySQL accounts. With PROCESS, a user can use SHOW PROCESSLIST to see the text of statements being executed by the server. This allows a user to snoop on other users and possibly see information that should remain private. With SUPER, the user can kill threads that belong to other users, disrupting their activities. SUPER also allows a user to purge log files and perform other actions that can compromise server operation. Don't give the RELOAD privilege to people who don't need it. RELOAD allows a user to issue FLUSH and RESET statements, which can be abused in several ways:
The ALTER privilege can be used in ways you may not intend. Suppose that you want one user to be able to access table1 but not table2. Another user with the ALTER privilege may be able to subvert your intent by using ALTER TABLE to rename table2 to table1. Setting Up MySQL Accounts Without GRANT StatementsNormally, the GRANT and REVOKE statements suffice to manage MySQL accounts and access privileges. However, it's also possible to modify the contents of the grant tables directly using statements such as INSERT and DELETE. It's easier to do that if you understand how the GRANT statement modifies the grant tables because you'll know what kind of INSERT and DELETE statements correspond to various GRANT and REVOKE statements. GRANT acts to modify the grant tables as follows:
If you keep the preceding principles in mind, you should be able to do anything GRANT does without using GRANT itself. But remember that after modifying the grant tables directly, you must tell the server to reload them or it won't notice your changes. If you forget to do that, you'll be wondering why your changes seem to have no effect. You can force a reload by issuing a FLUSH PRIVILEGES statement or by executing a mysqladmin flush-privileges command. The following GRANT statement uses ON *.* to specify global privileges. It creates an account for a superuser who has all privileges, including the ability to grant privileges to other users:
GRANT ALL ON *.* TO 'ethel'@'localhost' IDENTIFIED BY 'coffee'
WITH GRANT OPTION;
The statement will create an entry for ethel@localhost in the user table. It also will turn on all the privileges there because that's where superuser (global) privileges are stored. To do the same thing with INSERT, the statement looks like this:
INSERT INTO user VALUES('localhost','ethel',PASSWORD('coffee'),
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
That's one ugly INSERT statement! You may even find that it doesn't work, depending on your version of MySQL. This statement assumes that the user table has 21 privilege columns, which is the number present in MySQL 4.1 as I write. Because the structure of the grant tables has changed on occasion, you might have a different number. Use SHOW COLUMNS to find out just what privilege columns your user table contains, and adjust the INSERT statement accordingly. Note too that although the GRANT statement encrypts the password for you, INSERT does not; it's necessary to use the PASSWORD() function to encrypt passwords in your INSERT statements. The following GRANT statement creates another account with superuser status, but for only a single privilege: GRANT RELOAD ON *.* TO 'flush'@'localhost' IDENTIFIED BY 'flushpass'; You may remember this statement from Chapter 11, where we created an account for log file maintenance purposes that has privileges for flushing the server logs. The equivalent INSERT statement for this GRANT statement is a bit simpler than for the preceding one, so it's easier to list the column names and specify only the one privilege column. Each of the other privilege columns will be set to its default value ('N'):
INSERT INTO user (Host,User,Password,Reload_priv)
VALUES('localhost','flush',PASSWORD('flushpass'),'Y');
Database-level privileges are granted with an ON db_name.* clause rather than ON *.*: GRANT ALL ON sampdb.* TO 'boris'@'localhost' IDENTIFIED BY 'ruby'; These privileges are not global, so they won't be stored in the user table. However, to duplicate the effect of this GRANT statement, it's necessary to create a record in the user table so that the user can connect (if there is not already such a record). This means that a user table record is needed along with a db table record that indicates the database-level privileges:
INSERT INTO user (Host,User,Password)
VALUES('localhost','boris',PASSWORD('ruby'));
INSERT INTO db VALUES('localhost','sampdb','boris',
'Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y');
The 'N' value in the second statement is for the GRANT OPTION privilege. Setting the column to 'Y' instead would duplicate the effect of a database-level GRANT statement that includes a WITH GRANT OPTION clause. To set table-level or column-level privileges, use INSERT statements for the tables_priv or columns_priv tables. However, for these tables, you don't enable privileges using individual columns. You set either the tables_priv.Table_priv or columns_priv.Column_priv column to a SET value consisting of the privileges you want to enable. For example, to enable SELECT and INSERT privileges for a table, you'd set the Table_priv column to a value of 'Select,Insert' in the relevant tables_priv enTRy. To modify privileges for a MySQL account that already exists, use UPDATE rather than INSERT. This is true whether you are adding or revoking privileges. To remove an account entirely, use DELETE to remove entries from each grant table in which the account appears. For example, to remove an account for mike@%.snake.net, issue these statements: DELETE FROM user WHERE User = 'mike' AND Host = '%.snake.net'; DELETE FROM db WHERE User = 'mike' AND Host = '%.snake.net'; DELETE FROM host WHERE User = 'mike' AND Host = '%.snake.net'; DELETE FROM tables_priv WHERE User = 'mike' AND Host = '%.snake.net'; DELETE FROM columns_priv WHERE User = 'mike' AND Host = '%.snake.net';  | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|