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: authorization
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 Connect HOL content posted
Just a quick post to note that the content from my hands-on lab at MySQL Connect (“MySQL Enterprise Features in Practice”) has been uploaded to the content catalog, and can be found here. This includes the 36-page lab manual and example commands and programs (mostly in Java; the package includes both compiled and source code). For those who attended the lab, this is an opportunity to complete the exercises we didn’t get to in the 2.5 hours, and for those who missed it, an opportunity to learn more about the features and capabilities of key MySQL Enterprise products and features such as MySQL Enterprise Audit plugin, MySQL Enterprise Monitor and MySQL Enterprise Security (PAM plugin). I hope to expand on the lab content to demonstrate other features such as MySQL Enterprise Backup, MySQL Enterprise High Availability and MySQL Enterprise Scalability, and will post updates via my blog.
Implementing a host blacklist with MySQL privileges
When I saw Shlomi’s recent post which asked (in part) for blacklist support in MySQL, I started thinking about ways in which this could be done using the tools we have today. Here’s the example requirements Shlomi noted:
Speaking of whitelist, it would be great to have a host blacklist. If I wanted to grant access to ‘gromit’@’192.168.%’ except for ‘192.168.10.%’ — well, I would have to whitelist all the possible subnets. I can’t exclude a set of hosts.
I think that’s entirely possible without the overhead of whitelisting all possible subnets – let’s give it a go!
Continue reading Implementing a host blacklist with MySQL privileges
Speaking at MySQL Connect
The MySQL Connect content catalog is published, and I’ll be leading a hands-on lab on MySQL Enterprise Features in Practice [HOL9787]. If you have wondered how to get the most out of the features of MySQL Enterprise subscriptions – whether you are an existing Enterprise customer or not – this lab is for you. We’ll help you understand the benefits of the various components of the MySQL Enterprise subscription as you install, configure, demonstrate and use the features. You’ll learn how best practices and helpful tips, and work through sample customization exercises illustrating how tools such as MySQL Enterprise Monitor, MySQL Enterprise Backup and Security, Audit and Scalability components of MySQL Server can be applied to your MySQL use cases. I’ll be joined by Engineering staff responsible for several of these key products/features, so it’s a great opportunity to learn more about features that can make your life easier directly from the experts!
It’s also very likely I will be found at the Application Development with MySQL, Java, PHP, and Python [BOF4743] if you want to talk Java with me.
To only slightly misquote one of the greatest movies of all times:
You keep using that option. I do not think it means what you think it means.
Perhaps like many users, I had certain assumptions about what max_connect_errors really does – but in looking closely as part of investigating the new PERFORMANCE_SCHEMA.HOST_CACHE table in MySQL 5.6, I learned that some very fundamental elements had escaped my notice. I’m writing this blog post to help others who hold similar misconceptions of what this option does.
MySQL 5.6 users – prevent host blocked errors
The much-improved PERFORMANCE_SCHEMA in MySQL 5.6 provides visibility into MySQL’s host cache, including the ability to monitor for impending blocked hosts. You can do this with the following query:
mysql> SELECT -> ip, -> host, -> host_validated, -> sum_connect_errors -> FROM performance_schema.host_cache\G *************************** 1. row *************************** ip: 192.168.2.4 host: TFARMER-MYSQL.wh.oracle.com host_validated: YES sum_connect_errors: 3 1 row in set (0.02 sec)
That’s helpful information, and allows DBAs to identify problematic hosts before they are blocked. Due to Bug#69807, it’s also something MySQL 5.6 users will want to do. This bug causes the counter maintained in the host cache for failed connections – against which max_connect_errors is compared – to never be reset by a valid connection. The end result is that over time, hosts may reach the max_connect_errors threshold and be blocked.
Continue reading MySQL 5.6 users – prevent host blocked errors
Improved password policy utility for MySQL 5.6
I previously published stored programs to help implement a (more) comprehensive password policy in MySQL 5.6, building on the password complexity plugin now available in MySQL 5.6. This proof-of-concept has been expanded recently, and the updated package is available here. There’s a few notable changes to the earlier version: Continue reading Improved password policy utility for MySQL 5.6
Notes on ALTER USER … PASSWORD EXPIRE
I’ve been looking at the new ALTER USER … PASSWORD EXPIRE command as I try to implement a comprehensive password policy for MySQL 5.6. There’s a few aspects of this feature that I found interesting, and thought others might benefit from what I’ve learned. Here’s a quick summary:
System user authentication plugin
I’ve been working on revising my password policy scripts, and in the process, thought about the privileges required. My first draft added tables to the
mysql system database and leveraged the
root@localhost account. I’m looking to lock that down for the next iteration. It’s easy to move the tables and procedures out of the
mysql system database into a new
password_policy database, but what to do about the use of the root account? Continue reading System user authentication plugin