Knowing which privileges a given account has is easy – just issue SHOW GRANTS FOR user@host. But what about when you need visibility into privileges from the other direction – which accounts can access specific data? If you’re a DBA – or perform DBA duties, regardless of your title – you may have been asked this question. It’s an important question to ask in an audit or compliance review – but it can be a difficult question to answer. This post will walk through how to assess this, but if you’re impatient and need answers to this question immediately, jump to the end – there’s a simple shortcut. Continue reading Which accounts can access this data?
Tag Archives: MySQL 5.6
Practical P_S: Find Client JRE Version Using SQL
MySQL Connector/Java supports connection attributes since version 5.1.25. This projects useful metadata about the client environment into the database, where MySQL administrators can query PERFORMANCE_SCHEMA tables to remotely survey application deployment environments. One useful piece of information exposed is the version and vendor of the JVM in use by the client. This very short blog demonstrates how to get this information from PERFORMANCE_SCHEMA.
Continue reading Practical P_S: Find Client JRE Version Using SQL
Protecting MySQL passwords with sha256_password plugin
Over the years, MySQL has used three different mechanisms for securing passwords both for storage and for transmission across networks. This blog post aims to provide a brief history of the various mechanisms and highlight reasons to migrate accounts to use the sha256_password mechanism introduced in MySQL Server 5.6. Continue reading Protecting MySQL passwords with sha256_password plugin
SSL/TLS Connections to Recent MySQL Servers in Java
Recent changes to support better security by increasing strength of Diffie-Hellman cipher suites from 512-bit to 2048-bit were introduced to MySQL Server 5.7. While this change enhances security, it is an aggressive change in that 2048-bit DH ciphers are not universally supported. This has become a problem specifically for Java users, as only Java 8 JRE (currently) supports DH ciphers greater than 1024 bits. Making the problem more acute, this change was back-ported from MySQL Server 5.7 to the recent 5.6.26 and 5.5.45 releases in response to a community bug report. This blog post will identify affected applications, existing workarounds, and our plans to provide a more permanent solution in upcoming maintenance releases.
Continue reading SSL/TLS Connections to Recent MySQL Servers in Java
SSL/TLS in 5.6 and 5.5 – oCERT Advisory
Today, oCERT published advisory 2015-003 describing a TLS vulnerability in MySQL and derivative products. The content isn’t exactly news – it is documented legacy behavior and the subject of an earlier blog post describing how MySQL Server 5.7 solves the problem. That said, the efforts of Duo Security are certainly appreciated and welcomed – it provides a meaningful context to discuss how to properly harden existing MySQL 5.5 and 5.6 deployments, as well as frame a discussion on potential changes in these versions to increase security.
MySQL High Availability with Oracle Clusterware
MySQL has an extensive range of high-availability solutions to suit many different use cases and deployment needs. This list spans from the time-tested – yet continuously-improved – MySQL replication to the just-released MySQL Fabric, giving users many certified solutions for highly available MySQL deployments. The list is growing yet again, with Oracle Clusterware adding support for MySQL.
Oracle’s Clusterware product is the foundation for the Oracle RAC, and has been battle-tested for high availability support for Oracle database, as well as other Oracle applications. This technology is now available as part of the MySQL Enterprise subscription, and – like all Oracle commercial products – is freely available for evaluation purposes. This post will explain Oracle Clusterware architecture and the benefits to MySQL users, and will be followed by a later post focusing on how to deploy Clusterware agents with MySQL.
A very flexible architecture gives Oracle Clusterware the ability to support various consistency mechanisms. The initial release of the Clusterware agent for MySQL uses a shared resource approach, where essential resources – such as the data directory – are deployed on a shared disk. A similar strategy is employed in other high-availability solutions (OVM High Availability Template for MySQL, Oracle Solaris Clustering, MySQL with Windows Cluster Failover). The flexibility of Clusterware doesn’t dictate a specific shared resource implementation – anything from a simple NFS mount to a high-performance SAN may be used. The recommended and tested solution leverages the Oracle ACFS filesystem. As with other shared-disk high availability solutions for MySQL, an Oracle Clusterware-based solution requires only one MySQL instance be using a shared MySQL data directory at any one time.
While no high availability solution for MySQL is truly transparent, the Clusterware system provides useful infrastructure to minimize downtime. The agent performs periodic health checks of the running MySQL Server using mysqladmin, and applications connect through a managed virtual IP address. The use of a managed virtual IP address directs application traffic to a failover host without requiring configuration changes at the application layer. Failover time is bounded by the interval of agent health checks (every second by default) plus the time required to start the MySQL Server on the failover host (including any necessary crash recovery processing).
A big thanks goes out to the Oracle Clusterware team who did the heavy lifting in adding MySQL support!
Batch mode and expired passwords
A series of related discussions triggered by difficulty in setting passwords via scripts using the mysql command-line client when an account has an expired password caused me to look into the interaction between expired passwords and batch mode, and this blog post resulted. I hope it’s a useful explanation of the behavior and the workaround to those troubled by it, and amplifies the excellent documentation in the user manual. Continue reading Batch mode and expired passwords
MySQL 5.6.17 – now with better encryption
Joro wrote recently about MySQL 5.6.17‘s new support for AES-256 encryption, and it’s a great improvement for people need to encrypt their data at rest. The new session block_encryption_mode variable controls what variant of AES is used, and for backwards compatibility, the default behavior remains 128-bit key length with ECB block cipher mode. If you’re happy with that level of encryption, nothing changes – your existing code will work the same on 5.6.17 as it has on earlier versions (note that users of statement-based replication will experience new warnings). There are good examples of how to use the new functionality in the updated public test cases, but this blog post is intended to help orient users to the new options as well.
Continue reading MySQL 5.6.17 – now with better encryption
MySQL 5.6 Certification Now GA!
The MySQL 5.6 certification exams (Developer and DBA) are now GA! A big thanks to all those who sat for the extended length beta exams – and subsequently waited for several months for your results. Your feedback and the data collected from many beta testers helped ensure the final exams are high quality. A huge thanks also goes to the MySQL staff at Oracle who produced, edited and verified exam questions, in addition to their normal job responsibilities. In particular, these exams would have never materialized without a massive investment from the MySQL Support Team – thanks for the great job!
If you missed the reduced-fee beta period, the exams are now available for $245 (USD) each. While not as inexpensive as the beta exams, this is still a substantial savings from earlier certification offerings, as each certification has collapsed content from two exams (and two fees) into a single exam. And you’ll get the benefits of a GA test – quick feedback on results and a shorter exam with higher-quality, beta-vetted questions.
The exams are tough – I wasn’t sure whether I would pass (like Dave Stokes, happy to report I did). The passing percentage for both exams is set at 64%. If you’re feeling unsure about whether you’re prepared, consider a MySQL Training course to assist you.
Now that I know that I passed both exams, I’ll try to complete the promised study guide blog posts.
My Favorite Hidden Docs Page
There’s a lot to love about the MySQL product documentation – a lot of hard work from a number of very talented Oracle staff goes into it (not to mention the helpful suggestions and feedback from the community). There is, however, one page I find myself coming back to again and again, despite the fact that it’s somewhat hard to find. This is the MySQL Server options/variable reference page. It’s a helpful table that lists every MySQL Server option or variable, what version it was introduced or deprecated in, whether it’s dynamic or not, whether variables have session/global/both scope, and links to the version-specific documentation for that variable (useful to know when default values changed, for example).
It always takes me a while to find this page again when I’m looking for it, as it’s outside of the version-specific Server manual. Maybe you already know about this page and have it bookmarked; if not, give it a look – I find it very useful. That said, I wish it had filtering capabilities, so I wrote this bug report. I like being able to quickly answer questions like:
- Which options were introduced in MySQL 5.6?
- Which options and variables contain “ssl”?
- Which options cannot be set dynamically?
There’s a functional but ugly form and working JavaScript code attached to the bug report, if you ever have similar needs. All it needs is JQuery and the <div> contents that surround the table.