Domain Name Hosting, Java Programming - Cheap Web Hosting Blog

Chapter 12 . Security 333 When using the SET PASSWORD statement, MySQL sees the change immediately. However, when using an UPDATE statement, whether for updating a password or changing another parameter, you must issue the FLUSH PRIVILEGES statement for the change to take effect. Common Problems Though there are many problems that on their face appear to be authentication problems, they are really related to server or other operating system issues. Those types of errors are hopefully covered elsewhere in this book. This section specifically examines some common error messages related to authentication. In addition, a recurring issue surfaces: Too often, people forget the root password for MySQL. I d like to think that they forget because MySQL is so stable that they don t need to connect to it for administration tasks. Regardless, I ll look at how to change the root password if you do forget it. Access denied There are numerous reasons why you might receive an Access Denied error message when attempting to perform an operation. For that matter, you might get one simply by trying to connect to a MySQL server. This section examines the most common of these messages, which include the following: . Password: Personally speaking, my most common cause of an Access Denied error is because I can t type my password straight. Therefore, this is the first thing that comes to mind when thinking of items to check when you receive an Access Denied error. . Username: Is it allowed to connect? . Hostname or host: MySQL might not be able to properly identify the user/host combination, in which case it refuses access. The MySQL CLI is very good at remembering and telling you what was provided to it for credentials. I ve provided some examples of error messages in Figure 12-22:
Go visit our java server pages services for a reliable, lowcost webhost to satisfy all your needs.

332 Part III . Administration Figure 12-20: Using SET PASSWORD and UPDATE to change passwords for users in MySQL Cross- Reference The UPDATE statement is discussed In Chapter 9 and in Appendix A. Figure 12-21: Examples of UPDATE statements to change usernames and hosts in the MySQL grants database
We would like to recommend you tested and proved virtual web hosting services, which you will surely find to be of great quality.

Chapter 12 . Security 331 Figure 12-19: An example REVOKE statement, followed by an example DELETE statement. The FLUSH PRIVILEGES statement causes the delete to take effect immediately, preventing the (now-former) user from making a new connection. Revoking all privileges from a user won t necessarily truly remove all of their privileges. Specific privileges need to be specifically revoked. The SHOWGRANTS FOR statement can be quite useful in this situation to determine what specific privileges a user has for example, SHOW GRANTS FORWebuser@localhost. Changing passwords and other parameters Sooner or later, you will have to change a user s password as you administer a database server. You may also have to change a username or alter the host parameters for a database user. MySQL provides a function specifically for changing passwords; for other changes, normal SQL statements are required. There are two methods for changing a password in MySQL. The first is with the SET PASSWORD statement; the second is the UPDATEstatement. Both statements make use of the PASSWORD()function to encrypt the password. Examples of both methods for changing a password are in Figure 12-20. Use the UPDATE statement to change other parameters such as usernames and hosts. The syntax for the UPDATE statement is the same in this context as it is in other DML statements. In Figure 12-21, I issue various UPDATE statements to change usernames and hosts. Following the UPDATE, I issue the FLUSHPRIVILEGES statement to make the changes take effect immediately.
You want to have a cheap webhost for your apache application, then check apache web hosting services.

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 [()] [, privilege [()] …] ON [
| * | *.* | .*] 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 wild- carded and multiple users can be revoked simultaneously. The ALL PRIVILEGESmacro works with the REVOKE statement the same as with the GRANTstatement. 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 REVOKEstatement 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! The syntax for the DELETE statement is the same to remove user accounts as it is to delete rows from the tables of other databases. Remember that MySQL uses the user and host combined to identify a user. Therefore you can have as many users with the username robertin the database, but only one robert that connects from a specific host. Therefore you must be extremely careful when issuing the DELETE statement so as to not delete all those other Bobs from the database server! A misguided or careless DELETE statement can result in removal of all privileges for all users, not just for robert. After issuing the correct DELETEstatement, don t forget to run the FLUSH PRIVILEGES statement so the deletion takes effect on the server. Figure 12-19 shows all privileges being revoked from an example user, after which a deletion removes the user account entirely from the database.
Check Tomcat Web Hosting services for best quality webspace to host your web application.

Chapter 12 . Security 329 Figure 12-18: Some variations of the GRANT statement as useful syntax examples The value for 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 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 GRANTstatement. 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 OPTIONmodifier to the end of the GRANT statement. This is true even if you use the ALL PRIVILEGESmacro to enable all privileges for the user. The GRANTprivilege 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.
Searching for affordable and reliable webhost to host and run your web applications? Go to our java web server services and you will be pleased.

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 USAGEmacro the user will be added to the grants database as normal except no privileges will be given to the user. The USAGEmacro 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 GRANTstatement 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.tablestructure. In addition, you can give the same privileges to multiple users at the same time if you separate the users/host/passwordportions with commas. Examples of these grants are shown in Figure 12-18.
Go visit our java server pages services for a reliable, lowcost webhost to satisfy all your needs.

Chapter 12 . Security 327 Note You can create user accounts in MySQL by one of two methods: Using the GRANT statement or using the INSERTstatement. For the purposes of this chapter I will be concentrating solely on the GRANT statement and its opposite statement, REVOKE. If you choose to use an INSERT or UPDATE statement for working with the grants database, you must use the FLUSH PRIVILEGESstatement as well. If you use a GRANT or REVOKE statement, the database server sees the changes immediately. If you change a user s access to a database while they are connected to that database, the change will not take effect immediately for that user. In addition, if you change a user s global privileges or change their password it will not take effect until the next time they connect. Therefore, you should kill the user s process in order for the changes to take effect for these instances. Two macro-level grants are not listed in Table 12-1: the ALL PRIVILEGES and USAGE macros. The ALL PRIVILEGESmacro can be substituted in a GRANTor REVOKE statement instead of having to enumerate each of the privileges. The USAGE macro simply creates the user account with no privileges. In other words, think of the USAGE macro as a placeholder. It allows the administrator to create the user account and come back to it later to set privileges. The ALL PRIVILEGES macro does not include (and so does not grant) the administrative privilege of granting and revoking privileges. Adding users and granting privileges In MySQL, adding a user and granting privileges can occur simultaneously. You can also add multiple users with the same privileges simultaneously. The basic statement for adding a user and setting privileges is the GRANTstatement. The syntax for the GRANT statement is as follows: GRANT privilege [()] [, privilege [()] …] ON {
| * | *.* | .*} TO username(@) [IDENTIFIED BY password ] [, username(@) [IDENTIFIED BY password ] …] [REQUIRE [{SSL| X509}] [CIPHER cipher [AND]] [ISSUER issuer [AND]] [SUBJECT subject]] [WITH GRANT OPTION] Note To use the GRANT statement, you must have the GRANT privilege.
From our experience, we can recommend PHP Web Hosting services, if you need affordable webhost to host and run your web application.

326 Part III . Administration MySQL passwords There is general confusion over whether the Unix encryption structure is compatible with MySQL because a UNIX encryption does look somewhat similar to a MySQL encryption. Even so, MySQL passwords are stored with an encryption algorithm different from that of the standard Unix encryption method. For this reason, you cannot simply transfer your password and its encryption key from a password file to the MySQL grants database. MySQL includes a function to create a valid MySQL encryption: PASSWORD(). Using the PASSWORDfunction, a user with access to a MySQL CLI could create a valid encryption for use in MySQL. The user could then take the encryption and transfer it to the server administrator for addition to the MySQL grantsdatabase. As stated previously there is some confusion over the use of crypts within the user table of the MySQL grants database. Part of this confusion comes because of the ENCRYPT() function. The ENCRYPT()function creates a Unix style encryption but it is not valid in the MySQL grants database. In Figure 12-16, for example, I use the PASSWORD() function to create a valid MySQL password. The resulting password could be transferred to the administrator for the server and he or she would never be able to see my unencrypted password. I also use the ENCRYPT() function as a reference to show the difference, given the same plaintext password. Figure 12-16: The PASSWORD() function creates a valid MySQL encryption while the ENCRYPT() function does not. MySQL User Management For maximum security, I recommend implementing the least-privilege concept of permissions. When issuing grants and privileges to users of a database, you should give them only the privileges they need to perform their jobs.
Searching for affordable and proven webhost to host and run your servlet applications? Go to Linux Web Hosting services and you will find it.

Chapter 12 . Security 325 Note If the FILE privilege is given to a user, that user will be able to read the contents of all other databases on the server. The privileges are simple insofar as they mean what they are named. To issue a SELECT statement, you need the SELECTprivilege, to insert data you need the INSERT privilege, and so on. Security of passwords Working with an Internet service provider, I can t count the number of times I ve seen people choose bad passwords. I ve seen all the mistakes in the book, from choosing the username as the password to using the worst old favorite possible (the word password). I believe these types of mistakes are, in the words of James R. Leu, completely unacceptable. Regular users may have an excuse for choosing poor passwords, but System and Database Administrators don t. As the person who is in charge of a server or servers, it is your job to use passwords that cannot be easily guessed or cracked. Password guidelines I ve put together some guidelines that should help you choose effective passwords, come up with stronger ones, and keeping the ones you have secure. . Passwords should be six characters at an absolute minimum. . Passwords should include a mix of alphanumeric characters such as letters and numbers and non-alphanumeric characters, such as @#$^&* . . When you create a password, never use words that you can find in a dictionary (or those same words spelled backwards). . Passwords should include the use of both sides of the keyboard or both hands while typing. In other words, don t use passwords that require you to use only your left hand or the left side of the keyboard only. . Passwords should be different across servers. Don t use the same password for all of your servers, routers, and other gear. Thus, if one machine is compromised, the attacker cannot automatically gain access to your other servers. . Don t write your passwords down. If you feel you can t remember a password and must write something down, write down a word or phrase that will remind you of the password.
We recommend you use shared web hosting services, because many users agree that it is cheap, reliable and customer-satisfying webhost.

324 Part III . Administration 3. The third phase of the Connection Stage is performed against the host table of the MySQL grants database. The host and dbcolumns from the host table are included in this phase. If connections from a host are restricted in some way, this table determines appropriate access. During the next (second) stage of MySQL authentication, the requested process is examined for specific privileges to determine access. For example, if the user attempts to issue a SELECTstatement, the authentication process looks again at the usertable of the MySQL grantsdatabase. If authentication passes the usertable, it is again passed on to the dbtable and then to the hosttable. If the statement is run against a table, the tables_privtable is also consulted for authentication; if the statement runs against a column or columns, then the columns_privtable is consulted. MySQL privileges Each table of the MySQL grantsdatabase provides the privileges shown in Table 12-1 (with the exception of the RELOAD, SHUTDOWN, PROCESS, and FILEprivileges, which are limited to the usertable because they have no meaning in other contexts). Table 12-1 MySQL Privileges and their functions Privilege Function INSERT Table-level privilege for adding data. UPDATE Table-level privilege for updating or changing data. DELETE Table-level privilege for deleting data from tables. INDEX Table-level privilege for creating and deleting indexes. ALTER Table-level privilege for changing table layout. CREATE Database-, table-, and index-level privilege for creation of databases, tables and indexes. DROP Database- and table-level privilege for deleting databases and tables. GRANT Database- and table-level privilege for enabling a user to alter privileges for other users including adding and deleting users. REFERENCES Database- and table-level privilege for using references. RELOAD Server-level privilege for reloading and flushing server parameters. SHUTDOWN Server-level privilege for stopping the MySQL database server. PROCESS Server- level privilege to enable process listing and killing. FILE Server- level privilege to work with files such as selecting into outfiles. SELECT Table level privilege for selecting data or performing queries.
Please visit our professional web hosting services to find out about cheap and reliable webhost service that will surely answer all your demands.