# Mysql your visual blueprint for creating open source databases- P12

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

0
44
lượt xem
4

## Mysql your visual blueprint for creating open source databases- P12

Mô tả tài liệu

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)

Lưu

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

2. 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
3. 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
6. 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
7. 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
8. 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.
9. 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
10. 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
11. 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
12. 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
13. 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 }
14. 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
15. 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