Client Access Control with MySQL - 12.3.2 Statement Privilege Checking
(Page 3 of 5 )
Each time the server receives a statement from a client, it checks the client's privileges to see whether it's allowed to execute the statement. For example, if you issue an UPDATE statement, you must possess the UPDATE privilege for each of the columns to be updated.
The server checks privileges in an additive fashion from the global level to the column- specific level. To check a statement, the server determines which privileges the statement requires, and then assesses whether the client possesses them by proceeding successively through the grant tables.
First, the server checks the client's global privileges in the user table. If these are sufficient, the server executes the statement. If the global privileges are not sufficient, the server adds any database-specific privileges indicated for the client in the db table and checks again. If the combined privileges are sufficient, the server executes the statement. Otherwise, it continues as necessary, checking the table-specific and column-specific privileges in the tables_priv and columns_priv tables. If, after checking all the grant tables, the client does not have sufficient privileges, the server refuses to execute the statement.
12.3.3 Resource Limit Checking
For an account that has resource limits, the server applies them to access control as follows:
If the client has a limit on the number of times per hour it can connect to the server, that limit applies in the first stage of access control, when the server determines whether to accept the client connection.
If the client has a limit on the number of queries or updates per hour it can issue, those limits apply in the second stage of access control. The server checks the limits for each query received before checking whether the client has the proper privileges to execute it.
12.3.4 Disabling Client Access Control
The --skip-grant-tables option tells the server not to use the grant tables to control client access. This option has the following effects:
You can connect from anywhere with no password, and you have full privileges to do anything. That's convenient if you've forgotten the root password and need to reset it because you can connect without knowing the password. On the other hand, because anyone else can connect, running the server in this mode is dangerous. To prevent remote clients from connecting over TCP/IP, you might want to use the --skip-networking option as well. Clients then can connect only from the local host using a Windows-named pipe or a Unix socket file.
--skip-grant-tables disables the GRANT, REVOKE, and SET PASSWORD statements. These statements require the in-memory copies of the grant tables, which aren't set up when you skip use of the tables. To make a change to the grant tables while those statements are inoperative, you must update them directly. Alternatively, when you connect to the server, you can issue a FLUSH PRIVILEGES statement to cause the server to read the tables. That will enable GRANT, REVOKE, and SET PASSWORD. (Note that if you also started the server with the --skip-networking option, you'll still need to restart it without that option to cause it to listen for TCP/IP connections again.)
Next: 12.4 Exercises >>
More MySQL Articles
More By Sams Publishing
|
 This article is excerpted from chapter 12 of the MySQL 5.0 Certification Guide, written by Paul Dubois et al. (Sams, 2005; ISBN: 0672328127). Check it out today at your favorite bookstore. Buy this book now.
|
|
/ 6 
