Mysql your visual blueprint for creating open source databases- P12

Chia sẻ: Cong Thanh | Ngày: | Loại File: PDF | Số trang:20

Thêm vào BST

Báo xấu

77
lượt xem 5
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Mysql your visual blueprint for creating open source databases- P12:The show tables command displays a list of tables available in the currently selected database. You can use this command when you are unsure of the exact name of a table within the database.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Mysql your visual blueprint for creating open source databases- P12

CONFIGURE MYSQL SECURITY 11 MYSQL SECURITY BASICS ySQL includes a sophisticated security system. You M can use MySQL commands to create users and grant them privileges for a database or table. The Grant Tables The Authentication Process Internally, the MySQL server stores its usernames, When you attempt to connect to a MySQL server, the passwords, and privileges in several tables within the client encrypts your password and sends a request mysql database. This database is created when you including the username you specified to the server. The install the MySQL server. The user table within this server checks whether the username is listed in the user database stores a row for each user and a number of table and whether the password matches the encrypted fields that define the basic privileges granted to the user. password stored in that table. If they match, you are allowed to connect. The other tables in the mysql database include the host table, which stores privileges specific to particular After this initial authentication, the MySQL client hostnames, and the db table, which stores privileges authenticates each command the client sends to the granted to users for a specific database. The tables_priv server, and checks the user, db, and other tables to table stores privileges granted for specific table names, determine whether the username has the right and the columns_priv table stores privileges granted for privileges for the command being issued. only specific columns of a table. Security Commands Default Users MySQL includes three basic commands for working When you install the MySQL server, the root user is with security. The first, GRANT, grants one or more created by default. This user is granted all privileges for all privileges to a user for a database or table. If the user databases and tables, and can create additional users. The does not already exist, it is created. root user does not have a password by default, and this is a major security hole. Be sure to change the root password The REVOKE command removes one or more privileges before allowing users to access the MySQL server. from a username. It can leave a user without privileges, but does not delete users from the user table. The installation also creates an anonymous user, which allows users on the local host to connect without The SHOW GRANTS command displays the privileges specifying a username and password. This user is granted to a particular user. These are displayed as restricted to a database named test or with a name GRANT statements and can be used to recreate or beginning with test_, so this does not represent a duplicate the user's privileges. serious security risk. MySQL Users and Privileges A user in MySQL is actually the combination of a You must specify a username when you use MySQL username and hostname. If a username is set up with a client programs, such as mysql or mysqladmin. If you specific host, the user can only connect from that host. are the administrator of the MySQL server, you can Users can also be configured to allow multiple hosts or create usernames and control the privileges, or all hosts. permissions, of each user. The privileges you can grant to a user include most of You use the GRANT command in MySQL to grant one or the different things that can be done with SQL queries, more privileges to a user. If the username you specify including SELECT, INSERT, and DELETE. The complete does not exist, it is created. The REVOKE command is list of privileges is included later in this chapter. the opposite. This command removes one or more privileges from a user. 207
MySQL GRANT PRIVILEGES TO USERS You can specify a database name with the * symbol, meaning M ySQL uses its own system of usernames and passwords, unrelated to the underlying operating all tables under that database, a table name under the system. You can use the GRANT command from current database selected with the USE command, or the MySQL to create a username and assign one or more wildcard *.*, meaning all databases on the server. You can privileges to the user. You can assign privileges for all optionally specify a list of columns in parentheses before databases, a single database, a table, or even a single column. the ON keyword, and the user will have the privileges you specify for only those columns. The basic syntax of the GRANT command specifies a privilege type, a table or database name, a username, and The IDENTIFIED BY clause in the GRANT statement allows a password. The username can be an existing MySQL user. you to specify a password for the user. The password will be If it is a new user, the user is added. The following GRANT encrypted and stored in the MySQL user table. If the user command grants all privileges to the user nancy for the has already been created with a previous GRANT statement, testdb database: you do not need to use the IDENTIFIED BY clause again. GRANT ALL ON testdb.* In order to grant privileges to a user, you must be logged in TO nancy IDENTIFIED BY 'thepass'; as a user with those privileges and the ability to grant. If you specify WITH GRANT OPTION at the end of the GRANT Usernames on MySQL can be a simple name like the command, the user will have the ability to grant any above, or a combination of a username, the @ symbol, privileges they have to other users. and hostname. If you specify a hostname, the user can only access MySQL from that host. If you do not specify The REVOKE command allows you to revoke one or more a hostname, the username will work from any host. You privileges from a user. To use this command, specify REVOKE, can use the wildcard character, %, as the hostname to the privilege type or ALL, the ON keyword, the table or explicitly indicate that the user can connect from any host. database name, the FROM keyword, and the username. GRANT PRIVILEGES TO USERS Note: This example uses the testdb ⁄ From the MySQL monitor, ¤ Type GRANT ALL ON ‹ Type TO nancy IDENTIFIED database and the quotes table, which type USE testdb; and press testdb.* and press Enter. BY 'thepass'; and press Enter. you can import from the CD-ROM. Enter. s You are prompted for the s The user is now created. s The database is now next line. This user has all privileges for selected. the entire database. 208
CONFIGURE MYSQL SECURITY 11 Most of the examples here use the ALL keyword as the privilege type. This keyword assigns all available privileges. You can also assign the specific privileges listed in the table below. PRIVILEGE ALLOWS ALTER Use ALTER TABLE command CREATE Use CREATE TABLE command DELETE Use DELETE command DROP Use DROP TABLE command FILE Use SELECT INTO OUTFILE and LOAD DATA INFILE INDEX Use CREATE INDEX or DROP INDEX INSERT Use INSERT command LOCK TABLES Use LOCK TABLES command PROCESS Use SHOW PROCESSLIST and mysqladmin processlist RELOAD Use the FLUSH command SELECT Use SELECT queries SHOW DATABASES Show all databases SHUTDOWN Shut down the server with mysqladmin shutdown SUPER Various administrative privileges including mysqladmin kill UPDATE Use UPDATE queries › Type GRANT ALL ON s This creates another user. Á Type REVOKE DELETE, s This removes the DELETE quotes and press Enter. This one has access to the DROP ON quotes and press and DROP privileges, leaving quotes table only. Enter. the user with the remaining ˇ Type TO fred IDENTIFIED privileges. BY 'other'; and press Enter. ‡ Type FROM fred; and press Enter. 209
MySQL MODIFY USER PASSWORDS You can also change a user's password using the A fter you have created a user and granted privileges with GRANT, you can change the user's password mysqladmin password command at the command using the SET PASSWORD command within the prompt. In this case, you do not need to use the PASSWORD MySQL monitor. For example, the following command function. For example, the following command changes the changes the password for the user fred: password for the current user: SET PASSWORD FOR fred = PASSWORD('newpass'); mysqladmin password 'newpass' MySQL stores passwords in an encrypted form. When you If you specify the -u option with mysqladmin, you can set change a password with the SET PASSWORD command, the password for the specified user. However, this option you must use the PASSWORD function to encrypt the new requires the user's current password. If you need to set a password. MySQL expects the new password to be in password and do not know the user's current password, use encrypted form. the SET PASSWORD command. In order to change a user's password, you must either be When MySQL is first installed, the root user may be set up logged in as that user or as a user with the GRANT OPTION with no password or a default password. To secure the privilege. This allows you to change the password for any MySQL server, you should immediately change the user. You can also assign passwords by using the password for this user using SET PASSWORD or IDENTIFIED BY clause when creating users or adding mysqladmin password. privileges using the GRANT command, as explained in the previous section. MODIFY USER PASSWORDS Note: This example uses the users ¤ Type fred = ‹ Type SET PASSWORD = Note: If you change your password, you created in the previous section. PASSWORD('newpass'); PASSWORD('newpass'); and be sure not to use the default value You must be connected to MySQL as and press Enter. press Enter. given here, and be sure to remember the root user or another user that can the password you have chosen. grant privileges. s This sets the user's s This sets the password for password. the current user. ⁄ From the MySQL monitor, type SET PASSWORD FOR and press Enter. 210
CONFIGURE MYSQL SECURITY 11 MySQL uses its own system of usernames and passwords. Usernames in MySQL are limited to a length of 16 characters. There is no limit to password length in MySQL, but some systems limit the length to eight characters. While the username and password can be the same as a UNIX or Windows user account, they are separate and do not need to be the same. When you choose a password, be sure to make it difficult to guess. Names and words that appear in the dictionary are bad choices for passwords. The ideal choice is a combination of random letters mixed with numbers, although truly random passwords are not easy for users to remember. Because MySQL stores passwords encrypted using the PASSWORD function, knowing the encrypted password for a user is as good as knowing the real password. Do not allow users to view the grant tables, described later in this chapter, as the encrypted passwords would be displayed. When users specify a password on the command line to mysql or other client programs, other users may be able to see the password in the system's process list. A better strategy is to store the password in a .my.cnf file in each user's home directory. This file is explained in Chapter 10. › Type SET PASSWORD FOR s This sets another user's Á Type SELECT s This demonstrates the and press Enter. password. PASSWORD('newpass'); PASSWORD function and and press Enter. displays an encrypted result. ˇ Type nancy = PASSWORD('pass2'); and press Enter. 211
MySQL VIEW A USER'S PRIVILEGES user's privileges. In this case, when you use SHOW GRANTS, Y ou can use the VIEW GRANTS command from the MySQL monitor to find out what privileges have been the results show a GRANT USAGE statement. USAGE is a granted to a particular user. This is useful if you need special privilege meaning "no privileges." In other words, to check what abilities have been given to a user. For the user can connect to the MySQL server but cannot example, the following statement displays the privileges access any databases or tables. granted to the user fred: When using SHOW GRANTS, remember that MySQL stores SHOW GRANTS FOR fred; users as a combination of username and hostname. If a username is configured with a specific host, you must The results for SHOW GRANTS are presented in the form specify the hostname to view their privileges. If you have of one or more GRANT statements. You can copy these created the user ted@localhost, for example, no privileges statements and use them to restore the user's privileges will be shown if you use this command: in the event of data loss, or use them to create another user with the same privileges. The password in the GRANT SHOW GRANTS FOR ted; statement is shown in encrypted form. Because no hostname is specified, this command looks for In some cases a user is configured in MySQL but does not a user with access from all hosts, and no user is found. To have any privileges. This can happen if you create a user show the privileges for the correct user, specify the manually in the users table, or if you have revoked all of a hostname with the @ symbol. VIEW A USER'S PRIVILEGES Note: The users referred to in this s The privileges for the user ¤ Type SHOW GRANTS FOR s This user's privileges are example were created in the section are displayed. fred; and press Enter. displayed. “Grant Privileges to Users.” Note: You must be connected to ⁄ From the MySQL monitor, MySQL as the root user or another type SHOW GRANTS FOR user that can grant privileges to use nancy; and press Enter. this command. 212
CONFIGURE MYSQL SECURITY 11 In order to use SHOW GRANTS, your username must have the GRANT OPTION in its list of privileges. When you display the privileges for a user, the encrypted password is shown in the GRANT statements, and this could be used to gain access to the user's resources. When you change a user's privileges using GRANT or REVOKE, the changes take effect immediately and are shown in subsequent SHOW GRANTS commands. The privileges are checked both when a user attempts to connect to the MySQL server and when they issue each command after connecting. You cannot use wildcards with SHOW GRANTS to display the privileges of multiple users. To display a list of users or quickly view privileges for multiple users, you can access the grant tables directly, as described in the next section. The GRANT statements shown when you use SHOW GRANTS are a summary of the user's privileges. While they can be used to recreate the user's privileges, they are not necessarily the same commands you used to assign the privileges and create the user. ‹ Type REVOKE ALL ON s This revokes all of the › Type SHOW GRANTS FOR s The user's privileges now testdb.quotes FROM fred; user's privileges. fred; and press Enter. include only the USAGE and press Enter. privilege, which allows access but no privileges. 213
MySQL VIEW SECURITY TABLES Because the output of this command includes encrypted M ySQL stores the users and privileges you assign in a set of tables under the mysql database, which was passwords, be sure not to let anyone other than an created when you installed the server. You can view administrator view the list. these tables directly to find out detailed information about The db table stores a row for each user that has privileges a user or to view the complete lists of users and privileges. for a specific database on the server. For each row, the The mysql database is accessible only to the root user username, hostname, and database name are stored along by default. Because this database contains usernames, with flags indicating various privileges specific to the passwords, and privileges for all users, access to it database for that user. effectively allows you to view or modify any user's The host table stores information for specific hostnames, privileges on the server. and is used when a user is given access from multiple hosts. The user table within the mysql database stores the list of The tables_priv and columns_priv tables are used to store usernames and their basic privileges. This table is used by any privileges that have been granted to users specific to a the MySQL server to determine whether to allow access table or one or more columns of a table. when a user attempts to connect. Various columns of this table store values of "Y" or "N" to indicate whether a privilege is granted. You can use the following command to view the complete list of users: SELECT * FROM user; VIEW SECURITY TABLES ⁄ From the MySQL monitor, s The database is now ¤ Type SELECT * FROM user s The user's entry in the user type USE mysql; and press selected. and press Enter. table is displayed. Enter. Note: Usually you must be logged in ‹ Type WHERE User = as the root user to access this "fred"; and press Enter. 214 database.
CONFIGURE MYSQL SECURITY 11 You can manipulate the tables in the mysql database directly. For example, you can use the following UPDATE query to change a user's password rather than using the SET PASSWORD command. Example: UPDATE user SET Password=PASSWORD('newpass') WHERE user='fred'; You can also use INSERT queries to add users or DELETE queries to delete users from the user table. You can also modify the other tables to add or remove privileges. While this is rarely necessary, it gives you more complete access to the various settings stored in the tables and may be more practical than using GRANT and REVOKE in some cases. When you have made changes to users or other tables in the mysql database, they are not automatically reloaded by the server. You can use the command FLUSH PRIVILEGES from the MySQL monitor, or mysqladmin flush-privileges from the command prompt, to force the tables to be reloaded. They will also be reloaded if you restart the MySQL server. While modifying these tables directly is powerful, it can also be dangerous: You could easily delete the root username, for example, and lose root access to the server. Use these tables with caution, or use the GRANT and REVOKE commands instead. Also, be sure that you do not give any other users access to view or modify the tables in the mysql database. › Type DESCRIBE user; and s This displays a summary of ˇ Type DESCRIBE db; and s This displays the columns press Enter. the columns of the user table. press Enter. of the db table. 215
MySQL CONTROL NETWORK ACCESS hen you created users on the MySQL server earlier you have not previously granted privileges to the same W in this chapter, you did not specify a hostname in the GRANT command. This allows the user to connect to the MySQL server from any host on the username without specifying a hostname. If you have done this, use REVOKE to remove the privileges for the original user before adding a user with a specified hostname. network. While this is often what you need, when a user will only be connecting from the local host or a specific You can specify a hostname or IP address that the user can host, you can give them access only from certain hosts. This connect from instead of using localhost. For example, greatly reduces the possibility of the user account being the following GRANT command creates a username, sue, used maliciously across the network. that can connect only from a host called example.com: To specify the hostname a user can connect from, use the GRANT ALL ON testdb.* TO sue@example.com @ symbol to combine the user name and hostname. For IDENTIFIED BY 'password'; example, the following GRANT command creates a username, henry, that can be used to connect only from the machine If you need to allow access for a user from more than running MySQL server: one host, simply repeat the GRANT command for each hostname. You can use the wildcard character % in the GRANT ALL ON testdb.* TO henry@localhost hostname to allow a set of host names or IP addresses. IDENTIFIED BY 'password'; When you do this, you must enclose the username and hostname in quotation marks: MySQL allows multiple users with the same name in the user table, as long as their hostnames are different. For this GRANT ALL ON testdb.* TO reason, limiting the user to the local host will only work if 'user1'@'192.168.%'; CONTROL NETWORK ACCESS Note: This example uses the testdb ¤ Type IDENTIFIED BY ‹ Type GRANT ALL ON s This creates a user that can database. You must be connected to 'password'; and press Enter. testdb.* TO sue@example.com connect to MySQL from the MySQL as the root user or another and press Enter. example.com host only. user that can grant privileges. s This creates a user that can access MySQL from the local › Type IDENTIFIED BY Note: For security, choose a different ⁄ From the MySQL monitor, host only. 'password'; and press Enter. password. type GRANT ALL ON testdb.* TO henry@localhost and Note: For security, choose your own press Enter. password rather than using the one given here. 216
CONFIGURE MYSQL SECURITY 11 ADVANCED MYSQL SECURITY M ySQL includes SSL AND MYSQL a number of more advanced security options. You When you connect to a MySQL server using a client, the traffic between the two is not can use various startup encrypted. This means that a machine on the network may be able to scan network traffic options when you start and discover passwords or data being transferred to and from the MySQL server. the MySQL server to control specific aspects To prevent this type of network vulnerability, MySQL supports the SSL (Secure Sockets of security. You can Library) protocol, the same system used by Web servers to provide encrypted access to also use the secure clients. Setting up SSL on MySQL requires re-compiling the server, if it was not initially set SSL protocol when up with this option. For details on using SSL with MySQL, see the MySQL documentation connecting clients to at www.mysql.com. a MySQL server. USING STARTUP OPTIONS The program that handles the MySQL server, mysqld, --skip-show-database includes a variety of startup options. This section describes several options that you can use to manage the security of If this option is used, the SHOW DATABASES command the MySQL server. To use these options, add one or more is not allowed at all, except for users who have been of them to the command line when you start the MySQL explicitly given the SHOW DATABASES privilege. server. See Chapter 1 for information on starting the server. --skip-grant-tables --local-infile If this option is specified, the grant tables are not used at This option should be followed by the = symbol and all — in other words, MySQL is running with no security a value of zero or one. If the value is zero, the LOAD at all. You should never use this option unless you have DATA LOCAL INFILE command is not permitted. This a special situation that requires MySQL security to be command may be a security risk because it allows files turned off. on the local system to be read and sent to the MySQL One use for this command is for recovering access to the server. See Chapter 8 for details on using the LOAD server when you have accidentally changed or deleted DATA command. the root user. You can start the server with the --skip- grant-tables option, use GRANT to restore the root Example: user, and then restart the server to turn security back on. mysqld –local-infile=0 --skip-name-resolve --safe-show-database If this option is used, MySQL will not use DNS (domain Normally, any MySQL user can use the SHOW DATABASES name system) to convert hostnames to IP addresses. command to display a complete list of databases on the This effectively means that you cannot use a hostname server. If you use the --safe-show-database option, when granting privileges to users, and you must users are only shown the databases for which they have explicitly use an IP address instead. This can increase been granted one or more privileges. security if your DNS server is not secure. --safe-user-create --skip-networking If this option is included, users are not allowed to use If this option is specified, MySQL does not allow GRANT to create a new user unless they have been TCP/IP connections to the server across the network. explicitly granted the INSERT privilege for the user Connections can only use UNIX sockets, which means table in the mysql database. Users who have the that the local host is usually the only machine that can GRANT OPTION privilege can still grant any privileges connect. This option will increase security if you do not they have to any existing user. require access to the server from other machines. 217
MySQL INTRODUCING PHP This is only a brief introduction to PHP. PHP is a project of P HP is one of the most popular Web scripting languages, and one of the most popular languages for creating the Apache Software Foundation, and you can find out applications to work with MySQL. PHP is a scripting more or download software from the PHP Web site: language that is interpreted by Web servers. In particular, the www.php.net. popular open source Apache server can support PHP as a module, which allows for efficient execution of scripts. PHP and HTML Using Variables To use PHP, you embed a script within an HTML PHP supports variables, containers that can store document and save the document with the .php or numbers, text strings, or other data. PHP variables do not .php3 extension. The Web server looks for PHP scripts need to be defined before you use them, and they can in files with these extensions and interprets them store any type of data. The following example assigns a before sending the document to the Web browser. string value and a numeric value to two variables: Thus, the browser does not have access to the PHP script — only to the output of the script and the ?> tags. Anything between these two tags is interpreted as PHP rather than HTML. Anything outside these tags PHP also supports arrays, or variables with indexes. An should be valid HTML. array is basically a number of variables with a common name. The indexes for an array can be either numbers Create Output or string values. The following example assigns a value to an array element: PHP supports a number of commands. One basic example is the echo command, which sends output to semicolon. The following example displays a message using PHP: PHP variables can be included in string values. This is useful when you need to combine a variable's value PHP and MySQL PHP includes support for MySQL with a number of functions, described throughout this chapter. These allow you to connect to a MySQL server, submit a query to the server and retrieve the results, and perform other MySQL tasks. Virtually anything you can do with the MySQL monitor can also be done from a PHP script. 218
USING MYSQL WITH PHP 12 Using Functions Operators You can combine several statements into a group with a PHP supports a number of standard operators for function in PHP. To define a function, you use the working with numbers and strings. You can use these in function keyword and surround the statements with any expression to work with constants or variables. The braces. The following example defines a function called operators available in PHP are similar to those of print_bold: MySQL. The following table summarizes some of the basic PHP operators: - Subtraction The values in parentheses are the arguments, or * Multiplication parameters, of the function. To call a function, you / Division specify its name and the arguments in parentheses. The following statement calls the print_bold function: % Modulo (remainder) . Concatenation (combines strings) print_bold("This is a test."); Functions can also return a value using the return Conditional Statements keyword. When you call such a function, you can store You can use the if statement in PHP to perform one or its result in a variable. more statements conditionally. This statement uses a conditional expression followed by a single PHP Loops statement, or a block of statements enclosed in braces. PHP supports loops to perform a statement or a block For example, this if statement displays a message if the of statements multiple times. The while statement $num variable has a value greater than 30: defines a simple type of loop that repeats as long as a condition is true. The following is an example of a if ($num > 30) echo "It's bigger than while loop: 30."; while ($num < 30) { The condition in an if statement can use one or more $num = $num + 1; conditional operators. These are similar to the echo "The number is $num."; conditional operators in MySQL, and are summarized in } the table below. PHP also supports for loops. This type of loop starts OPERATOR MEANING with a for statement specifying a beginning value, a condition, and an increment expression for a variable. == Is equal to The following for loop is equivalent to the previous != Is not equal to while example: > Is greater than for ($num=0; $num= Is greater than or equal to }
MySQL TEST THE PHP INSTALLATION created into a Web browser, then PHP is installed. Next, I f you are running your own Web server and are not already running PHP, you can install PHP on the server. check the Configure Command entry for the with-mysql Depending on your system, this can be a simple or option. If this is present, your PHP installation is set up to complicated process. Most Linux distributions include PHP, work with MySQL. so you may not need to do anything. If PHP does not work or is not configured to work with If you are using a shared hosting provider, there is a good MySQL, you may need to install PHP. If you are running chance it supports PHP. In particular, most installations using Linux, first check with the vendor of your Linux distribution the Apache Web server are set up to run PHP. Consult your to see if they have a prepackaged binary version of PHP host's instructions to find out how to use PHP on the server. available. This provides an easy way to install PHP. If a binary version is not available, you will need to install PHP To test whether your server already supports PHP, create a from source code. file with a .php extension and include the following text in the file: You can download the PHP source code from the PHP Web site at www.php.net. This site also provides detailed can use the tar zxf command to expand the files from the archive. After the files are extracted, you will need to The phpinfo command displays a detailed list of use a configure command to choose how PHP will be information about the current PHP installation. If this configured, and then compile PHP following the instructions. information is displayed when you load the .php file you TEST THE PHP INSTALLATION ⁄ From the UNIX command Note: Use the appropriate command ¤ Type
USING MYSQL WITH PHP 12 For Windows servers, a binary version of PHP is available from the PHP Web site at www.php.net. PHP for Windows is available in two packages: one as a .zip archive, and a second as an .exe file that automatically begins the installation. Both packages include built-in support for MySQL, so you do not need to change the configuration to support MySQL. If you are using Microsoft's IIS (Internet Information Server) or PWS (Personal Web Server), included with some versions of Windows, the .exe version of the PHP binary will automatically configure your server to work with PHP. If you are using the Apache Web server under Windows, download the .zip version of the PHP package. This includes everything you need to support PHP as an Apache module. At this writing, this feature only works reliably with PHP 1.x, and does not yet support the new PHP 2.x versions. The www.php.net site includes links to external sites where you can find binary versions of PHP for Mac OS X, Novell NetWare, and a variety of other systems. › Type ?> to end the PHP Note: Use the appropriate command Á Type the URL for the test s If the PHP information is script. for the editor you are using. file into a Web browser. displayed, PHP is working on your server. ˇ Press Ctrl-X and then Y to save the file and exit the editor. 221
MySQL INSTALL AND TEST PHPMYADMIN best work on your server. In most cases, the version with P hpMyAdmin is a free, open source application that gives you a user-friendly interface to a MySQL PHP extensions will work best. Downloads are available in database or an entire MySQL server. While this various archive formats, including .tar.gz, typically used on program is written in PHP and requires PHP, you can use UNIX systems, and .zip, usually used under Windows. it to manage your databases and tables even if you do not know how to program in PHP. This utility allows you to To install phpMyAdmin, you can simply install the files from create databases and tables, browse through data, and the archive in a directory on your Web server. After the files perform most MySQL queries from a Web interface. are in place, you will need to edit the config.inc.php file and specify a username, password, and hostname for the To use phpMyAdmin, you must first download and install MySQL server. it. Because it is written in PHP, it should work on most Web servers that support PHP. You can download the After you install phpMyAdmin, you can use it to perform phpMyAdmin installation files from the project's Web most of the same functions as the MySQL monitor from page at www.phpmyadmin.net. any Web browser. This is very useful for beginners to MySQL and even for experienced users who want a more The installation files are available in two formats, with the convenient interface to the database. PHP or PHP3 extension. You can use whichever version will INSTALL AND TEST PHPMYADMIN ⁄ Type cd followed by the ¤ Type tar zxf filename, ‹ Type cd phpmyadmin- Note: Use the appropriate editor name of a directory on your replacing filename with the version, replacing version with command on your system. The file Web server and press Enter. name of the .tar.gz file, and the version you downloaded, may end in .php3, depending on the press Enter. and press Enter. version you downloaded. Note: This assumes you have downloaded the phpMyAdmin s The files are extracted into › Type pico config.inc.php installation file to the same directory. a new directory. and press Enter. 222
USING MYSQL WITH PHP 12 You can use phpMyAdmin to perform virtually any task on the MySQL server, including dangerous tasks like dropping a database or running a DELETE query. Because you have specified a MySQL username and password in phpMyAdmin's configuration, anyone who can access the Web page can access the database. Because of this, you should secure the directory where you have installed phpMyAdmin in some way. Most Web servers allow you to create an .htaccess file that requires a username and password. You can secure the page in this way, or place it on a private Web server or a secure directory you have already configured. In addition to securing the Web location where phpMyAdmin is installed, you should also use a username and password in the configuration file that allow only a minimum of access. You can create a special user in MySQL that has access to only a certain database, and this way other databases are inaccessible from the phpMyAdmin utility. See Chapter 11 for information on creating users in MySQL and assigning privileges. The phpMyAdmin Web page at www.phpmyadmin.net/ has more information on security and authentication. ˇ From the editor, specify ° Specify the hostname of ‚ Type the URL where you Note: See the security notes in the the URL where you have the MySQL server. installed phpMyAdmin into a Extra section. installed phpMyAdmin. Web browser. · Press Ctrl-X and then Y Note: You may want to create Á Specify the username to to save the file and exit the s You can now access a user specifically for running access the MySQL server. editor. the databases and tables phpMyAdmin. See Chapter 11 permitted for the username for details. ‡ Specify the password for you specified. the MySQL user. 223
MySQL MANAGE DATA WITH PHPMYADMIN The SQL option for a table allows you to enter your own A fter you have installed phpMyAdmin, you can use it as an alternative to the MySQL monitor and MySQL queries. A default SELECT query is displayed, and general-purpose MySQL client. The phpMyAdmin you can edit the query and click the Go button. The results screen is divided into two frames. The left frame displays of the query, if any, are displayed on the next screen. a list of the databases you have access to and can list the The Select option for the table displays a friendly interface tables within each database. The right frame displays the that allows you to create a SELECT query. You can enter a data with which you are currently working. WHERE clause, select a value for the LIMIT clause, and To work with a table in phpMyAdmin, click its entry in the enter text values with wildcards. left frame. The right frame displays a summary of the table's The Insert option for a table allows you to add a row to structure and several additional options. After you have the table using a Web form. Fields are displayed for each of selected a table, you can select several options. the columns of the table, and you can enter a value for a The Browse option displays the data from the table in column or select a function such as ENCRYPT to encrypt a pages, using a series of SELECT queries. An Edit link is string or NOW for the current date. displayed to the left of each row of data. You can click this to edit the row in a Web form. The Delete link displays a confirmation prompt and allows you to delete a row from the table. MANAGE DATA WITH PHPMYADMIN ⁄ From the main Note: This example shows the s The structure of the ¤ Click the Browse link to phpMyAdmin screen, select quotes table in the testdb database. selected table is displayed. display the contents of the the table with which to work table. on the left side of the screen. 224
USING MYSQL WITH PHP 12 The phpMyAdmin utility includes several other options for working with tables. The Export page allows you to export data from the table. You can choose to export the table data, the table structure, or both. An option is included to format the data in a CSV format that can be imported into Microsoft Excel, or you can customize the formatting. You can also choose to limit the number of rows of data that will be included in the exported file. The Operations page includes an interface to several MySQL options, including options to sort the table data by a particular column value, rename the table, move it to a different database or table, or copy it to another table. There are also options to optimize or repair the table. The Options page allows you to modify the table options. This includes the table type, the comment associated with the table, and a number of other options. Two final options are included for tables: Empty deletes all of the rows of a table, and Drop deletes the table completely. Both of these prompt you for confirmation before deleting anything. s The first ten rows of the ‹ Click the Edit button next s The columns of the s From this screen, you can table are displayed. to a row to edit the row. selected row are displayed modify the row and save it, or in a form. create a new table row based on the same data. 225
MySQL MANAGE THE MYSQL SERVER WITH PHPMYADMIN option opens the user table in the mysql database, and I n addition to working with data in MySQL tables, you can use phpMyAdmin to manage various aspects of the allows you to manage users. MySQL server. This allows you to display information The Database Statistics option displays a summary of the about the server's current status, manage users, and create size of each database, and the total amount of disk storage and drop databases. used by all tables. This is useful to keep track of which In order to use these features, you must specify a user with tables are using the most space. root access in the config.inc.php file. If the user has access, Finally, the Create new database option allows you to phpMyAdmin includes a number of extra features on the create a new database, assuming your username has the main page. The first of these are interfaces to various SHOW correct privileges. You can also delete a database using commands in MySQL. phpMyAdmin. To drop a database, first select it from the The Show MySQL runtime information option displays status list in the left column, and then click the Drop link at the information, similar to SHOW STATUS. The Show MySQL top of the page. You will be asked to confirm this action. system variables option is equivalent to SHOW VARIABLES. Because using a user with root access for phpMyAdmin The Show processes option displays a list of current threads, allows it to perform some powerful and potentially the same as the SHOW PROCESSLIST command. dangerous functions, be sure you are running it on a The Reload MySQL option executes a FLUSH command to secure Web server or behind a firewall. Otherwise it reload the security tables and configuration files. The Users represents a serious security risk. MANAGE THE MYSQL SERVER WITH PHPMYADMIN ⁄ From a Web browser, type s The main phpMyAdmin s The MySQL extended status › Click the Show MySQL the URL where you have page is displayed. values are displayed. system variables link. installed phpMyAdmin. ¤ Click the Show MySQL ‹ Click the Home link runtime information link. to return to the main phpMyAdmin page. 226