1 / 62

Chapter 12

Chapter 12. MySql Administration. Securing a new MySQL Installation. The MySQL installation procedure sets up the server's data directory and populates it with two databases: A mysql database containing the grant tables that control access by clients to the server

taylor-dillard
Download Presentation

Chapter 12

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 12 MySql Administration Mysql Developer's Library, Pual Dubios. 4th Edition

  2. Securing a new MySQL Installation • The MySQL installation procedure sets up the server's data directory and populates it with two databases: • A mysql database containing the grant tables that control access by clients to the server • A test database that can be used for testing purposes Mysql Developer's Library, Pual Dubios. 4th Edition

  3. Securing a new MySQL Installation • The initial installation is not secure, so you should assign passwords to these accounts. Mysql Developer's Library, Pual Dubios. 4th Edition

  4. The book uses examples of running MySQL server on a machine with a hostname of cobra.snake.net and that you will be connecting to the server from that same machine Mysql Developer's Library, Pual Dubios. 4th Edition

  5. Passwords for Initial MySQL Accounts • The grant tables in the mysql database are set up during the MySQL installation procedure with two kinds of accounts. Mysql Developer's Library, Pual Dubios. 4th Edition

  6. Passwords for Initial MySQL Accounts • Accounts that have a username of root. • These are superuser accounts intended for administrative purposes. • The root accounts have all privileges and can do anything, including deleting all your databases and shutting down the server. Mysql Developer's Library, Pual Dubios. 4th Edition

  7. Passwords for Initial MySQL Accounts • Accounts that have a blank username. • These are "anonymous" accounts; • they enable Users to connect to the server without having accounts explicitly set up for them in advance. • Anonymous users usually are given very few privileges, to limit the scope of what they can do. Mysql Developer's Library, Pual Dubios. 4th Edition

  8. User table • Each entry (row) in the user table contains a Host value that indicates • the host from which a user can connect • User • Password values that indicate the name and password the user must give when connecting from that host. Mysql Developer's Library, Pual Dubios. 4th Edition

  9. User table SELECT Host, User, Password FROM mysql.user; Mysql Developer's Library, Pual Dubios. 4th Edition

  10. The Windows user table entries look like those in the following table. Mysql Developer's Library, Pual Dubios. 4th Edition

  11. User table – root access • These user table account entries enable connections by client programs as follows: • You can connect as root from the local host. As root, you have all privileges and can perform any operation. Mysql Developer's Library, Pual Dubios. 4th Edition

  12. User table – anonymous access • You can connect anonymously with no username from the local host. • For current versions of MySQL, this account has no superuser privileges. • Before MySQL 5.0.36/5.1.16, the account has the same superuser privileges as root and can do anything. • Revoke privileges to anonymous account in addition to assigning a password to the account, or perhaps just delete the account entirely. Mysql Developer's Library, Pual Dubios. 4th Edition

  13. Db Table • Another grant table contains privilege information that enables anonymous users to use a particular database on the mysql server. Mysql Developer's Library, Pual Dubios. 4th Edition

  14. password-assignment • Connect to the server as root, determine which accounts have no password • use a SET PASSWORD statement for each one. • SELECT Host, User FROM mysql.user WHERE Password = ''; Mysql Developer's Library, Pual Dubios. 4th Edition

  15. SET Password Statement • mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('rootpass'); • mysql> SET PASSWORD FOR 'root'@'cobra.snake.net' = PASSWORD('rootpass'); • mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('rootpass'); • mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('anonpass'); • mysql> SET PASSWORD FOR ''@'cobra.snake.net' = PASSWORD('anonpass'); Mysql Developer's Library, Pual Dubios. 4th Edition

  16. SET PASSWORD – with UPdate • An alternative to SET PASSWORD is to modify the user table directly with UPDATE. • This method can be used to specify a password for all accounts with a given User value, regardless of their Host value, and thus modify multiple accounts simultaneously. • To set the password for all root accounts and all anonymous-user accounts, use these statements. Mysql Developer's Library, Pual Dubios. 4th Edition

  17. mysql> UPDATE mysql.user SET Password=PASSWORD('rootpass') WHERE User='root'; • mysql> UPDATE mysql.user SET Password=PASSWORD('anonpass') WHERE User=''; mysql> FLUSH PRIVILEGES; Mysql Developer's Library, Pual Dubios. 4th Edition

  18. SET PASSWORD • SET PASSWORD • the server notices that you've made a change to the grant tables and automatically re-reads them to refresh its in-memory copy of the tables. • UPDATE • it's necessary to explicitly tell the server to reload the tables. • Use FLUSH PRIVILEGES statement following the UPDATE statements. Mysql Developer's Library, Pual Dubios. 4th Edition

  19. Check privileges • mysql> SHOW GRANTS for ''@'localhost'; Mysql Developer's Library, Pual Dubios. 4th Edition

  20. no superuser privileges, the output will look like this Mysql Developer's Library, Pual Dubios. 4th Edition

  21. account has superuser privileges, you'll see this instead Mysql Developer's Library, Pual Dubios. 4th Edition

  22. revoke the account's privileges • mysql> REVOKE ALL ON *.* FROM ''@'localhost'; mysql> REVOKE GRANT OPTION ON *.* FROM ''@'localhost'; Mysql Developer's Library, Pual Dubios. 4th Edition

  23. Delete a user • mysql> DROP USER ''@'localhost'; • mysql> DROP USER ''@'cobra.snake.net'; Mysql Developer's Library, Pual Dubios. 4th Edition

  24. For REVOKE and DROP USER, the server automatically re-reads the grant tables and no FLUSH PRIVILEGES statement is needed. Mysql Developer's Library, Pual Dubios. 4th Edition

  25. Running the MySQL Server On Windows • Windows servers provide two types of connections Mysql Developer's Library, Pual Dubios. 4th Edition

  26. Running the Server as a Windows Service • On Windows, any MySQL server can be installed as a Windows service using this command: • C:\> C:\mysql\bin\mysqld --install Mysql Developer's Library, Pual Dubios. 4th Edition

  27. Start or Stop Mysql • To start or stop the service from the command line, use the following commands: • C:\> net start MySQL • C:\> net stop MySQL Mysql Developer's Library, Pual Dubios. 4th Edition

  28. Stop Service from Localhost • Use a host value of 127.0.0.1 rather than localhost: • % mysqladmin -p -u root --protocol=tcp shutdown • % mysqladmin -p -u root -h 127.0.0.1 shutdown Mysql Developer's Library, Pual Dubios. 4th Edition

  29. Stop Service from Localhost • 127.0.0.1 is an IP number (it refers to the local host's loopback interface) • it explicitly forces a TCP/IP connection to be used rather than a socket connection. Mysql Developer's Library, Pual Dubios. 4th Edition

  30. Controlling How the Server Listens for Connections • The default port number is 3306 Mysql Developer's Library, Pual Dubios. 4th Edition

  31. Named Pipes • By default the MySQL server will allow connections via TCP/IP from any host (but may reject a connection based on the user's remote hostname/IP address). • In many cases TCP/IP connectivity is not required and can be disabled to prevent remote access to the MySQL server. • If you are using MySQL locally for development or for use with a web server, you should disable TCP/IP networking. Mysql Developer's Library, Pual Dubios. 4th Edition

  32. Named Pipes – Windows Server • Named-pipe connections are disabled by default. • To enable this capability, start the server with the --enable-named-pipe option. • This enables local clients to connect through the named pipe by specifying the --protocol=pipe option or by connecting to the special hostname "." (period). • By default, the pipe name is MySQL (not case sensitive). • To specify a different name, use the --socket option. Mysql Developer's Library, Pual Dubios. 4th Edition

  33. Shared memory • MySQL supports shared-memory connections on Windows, but this capability is disabled by default Mysql Developer's Library, Pual Dubios. 4th Edition

  34. Shared Memory • The following table shows the allowable --protocol option values and indicates the platforms on which each value may be used. The values are not case sensitive. • On Windows, the shared-memory name to use, for connections made via shared memory to a local server. The default value is MYSQL. The shared-memory name is case sensitive. • The server must be started with the --shared-memory option to enable shared-memory connections. Mysql Developer's Library, Pual Dubios. 4th Edition

  35. Bind the TCP/IP Address • In some situations it is not possible to disable TCP/IP networking even when the server will only be needed for requests from localhost. • add the following to the [mysqld] section of your server configuration file: • bind-address=127.0.0.1 • This will cause the MySQL server to respond only to requests from localhost, and ignore all requests from the machine's network interfaces. • http://dev.mysql.com/tech-resources/articles/securing_mysql_windows.html Mysql Developer's Library, Pual Dubios. 4th Edition

  36. Managing MySQL User Accounts • Grant tables in the mysql database • CREATE USER, DROP USER, and RENAME USER create, remove, and rename MySQL accounts. • GRANT specifies account privileges (and creates accounts if they do not exist). • REVOKE removes privileges from existing MySQL accounts. • SET PASSWORD assigns passwords to existing accounts. • SHOW GRANTS displays the privileges held by existing accounts. Mysql Developer's Library, Pual Dubios. 4th Edition

  37. Grant Table Mysql Developer's Library, Pual Dubios. 4th Edition

  38. Grant Table • Manipulate the contents of the grant tables directly by issuing SQL statements like INSERT and UPDATE. • GRANT and REVOKE make it easier to manage user accounts by acting as a front end to the grant tables. Mysql Developer's Library, Pual Dubios. 4th Edition

  39. High-Level MySQL Account Management • CREATE USER creates a new account and optionally assigns a password: CREATE USER account [IDENTIFIED BY 'password']; CREATE USER grants no privileges • DROP USER removes an existing account DROP USER account; • RENAME USER changes the name of an existing account: • RENAME USER from_account TO to_account; Mysql Developer's Library, Pual Dubios. 4th Edition

  40. CREATE USER • asking these questions: • What is the user's name? • From which host or hosts should the user be able to connect? • What is the user's password? Mysql Developer's Library, Pual Dubios. 4th Edition

  41. Specifying Account Names • CREATE USER consists of a username and hostname in 'user_name'@'host_name' format • You specify not only who can connect but from where. • This enables you to set up separate accounts for two users who have the same name but that connect from different locations. • MySQL lets you distinguish between them and assign privileges to each one independent of the other. • The server stores the user_name and host_name values in the User and Host columns of the user table row for the account, and in any other grant table rows associated with the account. Mysql Developer's Library, Pual Dubios. 4th Edition

  42. Root access • There also is nothing special about the name root that is used for the MySQLsuperuser that can do anything. Mysql Developer's Library, Pual Dubios. 4th Edition

  43. User access • At one extreme, you can limit access to a single host if you know users will be connecting only from that host: • CREATE USER 'boris'@'localhost' IDENTIFIED BY 'frost'; • CREATE USER 'fred'@'ares.mars.net' IDENTIFIED BY 'steam'; Mysql Developer's Library, Pual Dubios. 4th Edition

  44. User Access • Enabling a user to connect only from a single host is the strictest form of access you can allow. • At the other extreme, you might have a user who travels a lot and needs to be able to connect from hosts all over the world. • If the user's name is max, you can enable him to connect from anywhere like this: • CREATE USER 'max'@'%' IDENTIFIED BY 'mist'; Mysql Developer's Library, Pual Dubios. 4th Edition

  45. User access • The '%' character functions as a wildcard with the same meaning as in a LIKE pattern match. • a hostname specifier, % means "any host." • This is the easiest way to set up a user, but it's also the least secure. Mysql Developer's Library, Pual Dubios. 4th Edition

  46. User access • To take a middle ground, you can enable a user to connect from a limited set of hosts. • For example, to enable mary to connect from any host in the snake.net domain, • use a host specifier of %.snake.net: • CREATE USER 'mary'@'%.snake.net' IDENTIFIED BY 'fog'; Mysql Developer's Library, Pual Dubios. 4th Edition

  47. User access • The host part of the account value can be given using an IP number rather than a hostname Mysql Developer's Library, Pual Dubios. 4th Edition

  48. User access • CREATE USER 'joe'@'192.168.128.3' IDENTIFIED BY 'water'; • CREATE USER 'ardis'@'192.168.128.%' IDENTIFIED BY 'snow'; • CREATE USER 'rex'@'192.168.128.0/255.255.255.0' IDENTIFIED BY 'ice'; Mysql Developer's Library, Pual Dubios. 4th Edition

  49. User access • The first of the statements indicates a specific single address, 192.168.128.3, from which the user can connect. • The second specifies an IP pattern for the 192.168.128 Class C subnet. • In the third statement, 192.168.128.0/255.255.255.0 specifies a netmask that has the first 24 bits turned on. Mysql Developer's Library, Pual Dubios. 4th Edition

  50. User access • On Windows, the user can connect by specifying a host value of localhost or 127.0.0.1. • Both of these connections are made using TCP/IP, except that if the server supports shared-memory connections, a connection to localhost is made using shared memory by default. Mysql Developer's Library, Pual Dubios. 4th Edition

More Related