Note To use the GRANT statement, you must have the GRANT privilege...

Strona startowa Ludzie pragną czasami się rozstawać, żeby móc tęsknić, czekać i cieszyć się z powrotem.? provide summaries for tables ("summary" attribute) or use the LONGDESC attribute to link to the description or data? in all tables that lay out text in parallel,...Chapter 7: Using JDBC to Interact with SQL Databases place the information onto the PDA (for example), let the user use it over some period of time, and then have the user...accessibility "hooks" for developers, and also there are alsoworld-wide efforts to develop training and support to facilitateeffective use of the GUI by blind...Chapter 15: Discovering problems 945 This test system has been reasonably useful, and the exercise of creating it and putting it into use has been...One thing you’ll notice is the awkwardness of the use of Structure1...uważałbym taką notę za wypowiedzenie wojny! Wszystkie dwory mi to przyznały! Cesarz począł krążyć ogromnymi krokami po pokoju, a Bałaszow musiał...Sometimes when you execute the statement you may not know whether you are making an update or a query...ORACLE statement...Contains all system privileges with ADMIN OPTION, and the SYSOPER system privilege; permits CREATE DATABASE and time-based recovery...Przeżywałem jedno z najbardziej nieoczekiwanych rozstań w życiu... 
 

Ludzie pragną czasami się rozstawać, żeby móc tęsknić, czekać i cieszyć się z powrotem.


164932-4 ch12.F 5/29/02 3:51 PM Page 328
328
Part III ✦ Administration
In my time as a database administrator, I’ve most frequently used the USAGE macro to initially create the user and then gone back and granted individual privileges for the user at a later time. With the USAGE macro the user will be added to the grants database as normal except no privileges will be given to the user. The USAGE macro can be helpful if the actual database hasn’t been created yet (or tables haven’t been created within the database).
As you can see by the syntax listing, the GRANT statement accepts wildcards for the database and also wildcards within the database context to indicate all tables. For example, to grant privileges on a specific table within a database, you can use databasename. tablename (or simply tablename if you are in the database at the time). Contrast this with granting privileges on all tables within a database (which you call with the databasename.* syntax, or simply with * from within the database). Also, granting privileges to all databases and tables is possible with the use of the *.* wildcard syntax. Examples of these grants are shown in Figure 12-17.
Figure 12-17: Examples of GRANT statements in differing scenarios With the GRANT statement you can specify that the privilege will only apply to certain columns within a given table. You can also specify more than one privilege within a statement and apply that to the same or different columns within the same table or database. table structure. In addition, you can give the same privileges to multiple users at the same time if you separate the users/host/password portions with commas. Examples of these grants are shown in Figure 12-18.
164932-4 ch12.F 5/29/02 3:51 PM Page 329
Chapter 12 ✦ Security
329
Figure 12-18: Some variations of the GRANT statement as useful syntax examples
The value for <host> can contain any valid hostname, IP address, or localhost. In addition, the wildcards % and _ are valid as are netmask values. For all hosts, the %
wildcard can be used. When using a wildcard or netmask value, the <host> portion must be quoted. For example, username’192.168.1.%’ would grant access to username from any address within the 192.168.1.0/24 range. This is the same as username’192.168.1.0/255.255.255.0’. If given username’%’ then username at any host would be allowed.
You can require the user connect only via a secure connection such as Secure Sockets Layer (SSL) or with X509. This functionality is achieved by adding the REQUIRE modifier to the GRANT statement.
If you wish for the user to have the ability to add, delete, and alter privileges within the database you must add the WITH GRANT OPTION modifier to the end of the GRANT
statement. This is true even if you use the ALL PRIVILEGES macro to enable all privileges for the user. The GRANT privilege is not included with the ALL PRIVILEGES
macro.
Caution
Use care when issuing GRANT statements so as not to give too many privileges or give them to unintended users.
As with all database administration, you should be careful when issuing GRANT
statements. Since MySQL uses a user/host combination for authentication, there can be multiple users in the database with the same username. Ensuring that you are granting access to the correct user or users is very important.
164932-4 ch12.F 5/29/02 3:51 PM Page 330
330
Part III ✦ Administration
If you are unsure of the grants that a given user has, you can issue the SHOW
GRANTS FOR statement. For example, to find out the grants and privileges given to Webuser (from a previous example in this chapter), issue the following statement: SHOW GRANTS FOR Webuser@localhost;
Deleting users and revoking privileges
The REVOKE statement is used to remove privileges from a user. The syntax for the REVOKE statement is as follows:
REVOKE privilege [(< columnlist>)] [, privilege [(< columnlist>)]
...]
ON [< tablename> | * | *.* | < databasename>.*]
FROM username [, username ...]
The syntax is quite similar to that of the GRANT statement. The REVOKE statement can apply to a column or columns, databases and tables can be specified and wildcarded and multiple users can be revoked simultaneously.
The ALL PRIVILEGES macro works with the REVOKE statement the same as with the GRANT statement. This is important to know because if you have granted the GRANT privilege to the user and use a REVOKE ALL PRIVILEGES statement, the GRANT option will still be there! Therefore, you must perform a separate statement of REVOKE GRANT OPTION ... for this occasion.
Issuing a REVOKE statement does not delete the user from the MySQL grants database. To delete a user from the grants database you must specifically issue a DELETE statement to remove them from the grants database.
Caution
Use caution when issuing any REVOKE or DELETE statements as you can easily revoke all privileges from all users or even delete all users from the database, including the root user!