Southeast Linuxfest -- MySQL User Admin Tips & Tricks

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.2 The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decision. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle. Safe Harbor Statement

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.4 MySQL Manual 6.3.1. User Names and Passwords MySQL stores accounts in the user table of the mysql database. An account is defined in terms of a user name and the client host or hosts from which the user can connect to the server. The account may also have a password. Thus speaketh the manual

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.5 MySQL login ≠ User Login • Many folks do use Unix Login as their MySQL login ● For convenience only ● Easily overridden ● -u or –user option ● MySQL user names can be upto 16 characters long ● Passwords encrypted by own algorithm ● Alternative character sets and collations supported

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.6 You can create MySQL accounts in two ways: By using statements intended for creating accounts, such as CREATE USER or GRANT. These statements cause the server to make appropriate modifications to the grant tables. By manipulating the MySQL grant tables directly with statements such as INSERT, UPDATE, or DELETE. The preferred method is to use account-creation statements because they are more concise and

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.7 Example of adding users shell> mysql --user=root mysql mysql> CREATE USER ‘joe'@'localhost' IDENTIFIED BY 'some_pass'; mysql> GRANT ALL PRIVILEGES ON *.* TO ‘joe'@'localhost' -> WITH GRANT OPTION; mysql> CREATE USER ‘joe'@'%' IDENTIFIED BY 'some_pass'; mysql> GRANT ALL PRIVILEGES ON *.* TO ‘joe'@'%' -> WITH GRANT OPTION; mysql> CREATE USER 'admin'@'localhost';

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.8 When Joe is not Joe ‘joe’@’localhost’ may or not have the same permissions as ‘joe’@’168.10.%’ Usually discovery of this occurs at worst possible times Network reconfiguration can cause problems

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.9 Anonymous Accounts  Mysql.user User column is blank – Generally a bad idea – Often used Click to edit Master text styles Second level Third level Fourth level

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.10 So now we know mysql.user has user, host & password – what else is in there  Privileges – Select, Insert, Update, Delete, Create, Drop, Reload, Shutdown, Process, File, Grant, References, Index, Alter, Show, Super, Create_tmp_table, Lock_tables, Execute, Repl_slave, Repl_client, Create_view, Show_view, Create_routine, Alter_routine, Create_user, Event, Trigger, Create_tablespace  Encryption – SSL_type, SSL_cipher, x509_issuer, X509_subject  Limits – Max_questions, Max_updates, Max_connections, Max_user_connections  New – Plugin, authentication_string, password_expired

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.11 Plugins -- 6.3.7. Pluggable Authentication  When a client connects to the MySQL server, the server uses the user name provided by the client and the client host to select the appropriate account row from the mysql.user table. It then uses this row to authenticate the client.  In MySQL 5.6, the server authenticates clients using plugins, as follows:  The server determines from the account row which authentication plugin applies for the client:  If the account row specifies no plugin name, the server uses native authentication.  If the account row specifies a plugin, the server invokes it to authenticate the user. If the server cannot find the plugin, an error occurs.  The plugin returns a status to the server indicating whether the user is permitted to connect.  Pluggable authentication enables two important capabilities:  External authentication: Pluggable authentication makes it possible for clients to connect to the MySQL server with credentials that are appropriate for authentication methods other than native authentication based on passwords stored in the mysql.user table. For example, plugins can be created to use external authentication methods such as PAM, Windows login IDs, LDAP, or Kerberos.  Proxy users: If a user is permitted to connect, an authentication plugin can return to the server a user name different from the name of the connecting user, to indicate that the connecting user is a proxy for another user. While the connection lasts, the proxy user is treated, for purposes of access control, as having the privileges of a different user. In effect, one user impersonates another.

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.13 Proxy Users  When authentication to the MySQL server occurs by means of an authentication plugin, the plugin may request that the connecting (external) user be treated as a different user for privilege-checking purposes. This enables the external user to be a proxy for the second user; that is, to have the privileges of the second user. In other words, the external user is a “proxy user” (a user who can impersonate or become known as another user) and the second user is a “proxied user” (a user whose identity can be taken on by a proxy user). CREATE USER 'empl_external'@'localhost' IDENTIFIED WITH auth_plugin AS 'auth_string'; CREATE USER 'employee'@'localhost' IDENTIFIED BY 'employee_pass'; GRANT PROXY ON 'employee'@'localhost' TO 'empl_external'@'localhost';

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.15 Examples of table/column permissions  GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost';  GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';FLUSH PRIVILEGES  GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.19 ● Error on the side of too few than too many ● Grant, Super, and Process privs are dangerous ● Temp files can fill up disk drives, SANs ● Shutdown priv can get very messy ● Consider audit vaule. First Rule on handing out privs Be Stingy!!!

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.20 ● Look for who has Grant, File, Shutdown, Drop, Create User, Create Index, Create Temp Files, Alter and Event ● Do you TRUST them ● Are they worth a job/vacation/weekend/evening ● Do you HAVE TO trust them ● Triggers, logs, and Backups can be your friend ● Setup replication accordingly ● Time Delay ● Certain Schemas /tables ● Paranoia is not necessarily bad Second Rule Audit the privs

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.22  Optimized for Web, Cloud-based, Embedded use cases  Simplified, Pluggable architecture – Maintainability, more extensible – More NoSQL options (HTTP, JSON, JavaScript, etc.)  Refactoring – Data Dictionary in InnoDB – Optimizer/Parser/Protocol  InnoDB – Optimized for SSD – GIS  Easy HA, Replication and Sharding MySQL Database Development Priorities

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.23  mysql.com ● MySQL Products, Editions, Training, Consulting ● TCO calculator ● Customer use cases and success stories  dev.mysql.com ● Downloads, Documentation ● Forums - PlanetMySQL  eDelivery.oracle.com ● Download and evaluate all MySQL products Learn More

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.24 New MySQL 5.6 Training Learn about the world’s most popular open-source database oracle.com/education/mysql Learn MySQL From Oracle ● Expert-led training to help you install, configure, and administer MySQL 5.6. ● Extensive hands-on practices guide you through each concept ● Explore real-world problems and discover best practices as you work with the tools and techniques used by professional MySQL database administrators ● Content developed in collaboration with product engineering. • Available in traditional or virtual classroom as well as self-study formats. • Custom training solutions to match your organization’s specific business needs • Backed by Oracle University’s 100% Satisfaction Program

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.25 MySQL Connect MySQL Engineers, Twitter, Percona, Google, Facebook, Tumblr, Paypal, Census Bureau, Ticketmaster, Amazon, Verizon, Codership and more presenting  September 21st – 23rd  San Francisco Union Square Hotel  Learn from the best – Customers  Tutorials on Advanced Subjects

Copyright © 2013, Oracle and/or its affiliates. All rights reserved.26 MySQL Marinate! -- Great way to learn MySQL  Virtual self-study of MySQL through the Boston MySQL Users Group (http://www.meetup.com/mysqlbos/)  http://www.meetup.com/Virtual-Tech-Self-Study/events/84103332/

Southeast Linuxfest -- MySQL User Admin Tips & Tricks

More Related Content

What's hot

Similar to Southeast Linuxfest -- MySQL User Admin Tips & Tricks

More from Dave Stokes

Recently uploaded

Southeast Linuxfest -- MySQL User Admin Tips & Tricks