1 / 30

Chapter 12 Continued

Chapter 12 Continued. Assigning Global privileges. CREATE USER ' ethel'@'localhost ' IDENTIFIED BY 'coffee'; GRANT ALL ON *.* TO ' ethel'@'localhost ' WITH GRANT OPTION;. Assigning Database Level privileges. To grant privileges at this level, use an ON db_name.* clause:

chaman
Download Presentation

Chapter 12 Continued

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 Continued Mysql developer's Library - Paul Dubois 4th Edition

  2. Mysql developer's Library - Paul Dubois 4th Edition

  3. Assigning Global privileges • CREATE USER 'ethel'@'localhost' IDENTIFIED BY 'coffee'; • GRANT ALL ON *.* TO 'ethel'@'localhost' WITH GRANT OPTION; Mysql developer's Library - Paul Dubois 4th Edition

  4. Assigning Database Level privileges • To grant privileges at this level, use an ON db_name.* clause: • CREATE USER 'bill'@'racer.snake.net' IDENTIFIED BY 'rock'; • GRANT ALL ON sampdb.* TO 'bill'@'racer.snake.net'; • CREATE USER 'reader'@'%' IDENTIFIED BY 'dirt'; • GRANT SELECT ON menagerie.* TO 'reader'@'%'; Mysql developer's Library - Paul Dubois 4th Edition

  5. Database Level • name only the specific privileges to be enabled: • CREATE USER 'jennie'@'%' IDENTIFIED BY 'boron'; • GRANT SELECT,INSERT,DELETE,UPDATE ON sampdb.* TO 'jennie'@'%'; Mysql developer's Library - Paul Dubois 4th Edition

  6. At the database level • you can grant privileges for individual tables, or even for individual columns in tables. • Column-specific privileges are useful when there are parts of a table you want to hide from a user, • when you want a user to be able to modify only particular columns. Mysql developer's Library - Paul Dubois 4th Edition

  7. Specific Columns at Database level • CREATE USER 'assistant'@'localhost' IDENTIFIED BY 'officehelp'; • GRANT SELECT, UPDATE (expiration,street,city,state,zip) ON sampdb.member TO 'assistant'@'localhost'; Mysql developer's Library - Paul Dubois 4th Edition

  8. Secure Connections • MySQL enables clients to establish secure connections using the SSL (Secure Sockets Layer) protocol • X509 can be used as a means for the client to provide identification information over SSL connections. Mysql developer's Library - Paul Dubois 4th Edition

  9. Secure Connections • CREATE USER 'eladio'@'%.snake.net' IDENTIFIED BY 'flint'; • GRANT ALL ON sampdb.* TO 'eladio'@'%.snake.net' REQUIRE SSL; Mysql developer's Library - Paul Dubois 4th Edition

  10. Present a valid X509 certificate • GRANT ALL ON sampdb.* TO 'eladio'@'%.snake.net' REQUIRE X509; Mysql developer's Library - Paul Dubois 4th Edition

  11. X509 certificate • REQUIRE X509 imposes no constraints on the certificate's contents other than that it be valid. • To be even more strict, REQUIRE enables you to indicate that the client's X509 certificate must have certain characteristics. • These characteristics are given with ISSUER or SUBJECT options in the REQUIRE clause. • ISSUER and SUBJECT refer to the certificate issuer and recipient. Mysql developer's Library - Paul Dubois 4th Edition

  12. Limiting an Account's Resource Consumption • CREATE USER 'spike'@'localhost' IDENTIFIED BY 'pyrite'; GRANT ALL ON sampdb.* TO 'spike'@'localhost' WITH MAX_CONNECTIONS_PER_HOUR 10 MAX_QUERIES_PER_HOUR 200 MAX_UPDATES_PER_HOUR 50; Mysql developer's Library - Paul Dubois 4th Edition

  13. Resource Consumption • The default value for each option is zero, which means "no limit." Mysql developer's Library - Paul Dubois 4th Edition

  14. Displaying Account Privileges • SHOW GRANTS FOR 'sampadm'@'localhost'; • SHOW GRANTS FOR CURRENT_USER(); Mysql developer's Library - Paul Dubois 4th Edition

  15. Revoking Privileges and Removing Users • REVOKE privileges [(columns)] ON what FROM account; Mysql developer's Library - Paul Dubois 4th Edition

  16. Revoke Privileges • GRANT ALL ON sampdb.* TO 'boris'@'localhost'; • REVOKE DELETE,UPDATE ON sampdb.* FROM 'boris'@'localhost'; Mysql developer's Library - Paul Dubois 4th Edition

  17. Changing Passwords or Resetting Lost Passwords • mysql> UPDATE user SET Password=PASSWORD('silicon') -> WHERE User='boris' AND Host='localhost'; • mysql> FLUSH PRIVILEGES; Mysql developer's Library - Paul Dubois 4th Edition

  18. SET PASSWORD • mysql> SET PASSWORD FOR 'boris'@'localhost' = PASSWORD('silicon'); • You can always change your own password with SET PASSWORD • To change the password for another account, you must have the UPDATE privilege for the mysql database. Mysql developer's Library - Paul Dubois 4th Edition

  19. Change Password with Grant Usage • Another way to change a password is to use GRANT USAGE with an IDENTIFIED BY clause. • mysql> GRANT USAGE ON *.* TO 'boris'@'localhost' IDENTIFIED BY 'silicon'; Mysql developer's Library - Paul Dubois 4th Edition

  20. Maintaining Logs Mysql developer's Library - Paul Dubois 4th Edition

  21. Error Log • This log contains a record of server startups and shutdowns, as well as messages about problems or exceptional conditions. • This is the place to look if the server fails to start. • the server writes a message to the error log before it terminates to indicate what problem occurred. Mysql developer's Library - Paul Dubois 4th Edition

  22. The general query log • Contains a record of client connections, • SQL statements received from clients, and various other miscellaneous events. • Useful for monitoring server activity: who is connecting, from where, and what they are doing. • Useful for troubleshooting or debugging. Mysql developer's Library - Paul Dubois 4th Edition

  23. The slow-query log • Helps you identify statements that may be in need of being rewritten for better performance. • The server maintains a long_query_time system variable that defines "slow" queries (10 seconds by default). • If a query takes more than that many seconds of real time, it is considered slow and is recorded in the slow-query log. • The slow-query log also can be used to log queries for which no indexes were used. Mysql developer's Library - Paul Dubois 4th Edition

  24. The binary log and the binary log index file • Contain a record of modifications performed by UPDATE, DELETE, INSERT, CREATE TABLE, DROP TABLE, GRANT, and so forth. • Binary log contents are written as data modification "events" encoded in binary format. • The binary log files are accompanied by an index file that lists which binary log files exist on the server. Mysql developer's Library - Paul Dubois 4th Edition

  25. The binary log has two purposes: • Used in conjunction with your backups to restore tables after a crash. • Restore databases from your backup files, and then use mysqlbinlog to convert the binary log contents to text statements. • The data modification events stored in the binary log are transmitted to replication slave servers. Mysql developer's Library - Paul Dubois 4th Edition

  26. The relay log and the relay log index file • If the server acts as a replication slave, it maintains a relay log that contains a record of data-modification events received from the master that need to be executed. • Relay log files have the same format as binary log files, and there is an index file that lists which relay log files exist on the slave. Mysql developer's Library - Paul Dubois 4th Edition

  27. Tuning the Server • System variables can exist at two levels: global and session-specific. • Global variables affect the operation of the server as a whole. • Session-specific variables affect only how the server treats a given client connection. Mysql developer's Library - Paul Dubois 4th Edition

  28. System Variables • For variables that exist at both levels, the global values are used to initialize the corresponding session variables. • This happens only when a new client connection begins • Changing a global variable during a connection does not affect the current value of the client's corresponding session variable. Mysql developer's Library - Paul Dubois 4th Edition

  29. System Variables • It is possible for a system variable to have • both global and session forms, • only a global form, • or only a session form. Mysql developer's Library - Paul Dubois 4th Edition

  30. Checking System Variable Values • mysql> SHOW VARIABLES; • mysql> SHOW VARIABLES LIKE 'key%'; Mysql developer's Library - Paul Dubois 4th Edition

More Related