Revision 68 of common_schema is out, and includes some interesting features: eval(): Evaluates the queries generated by a given query match_grantee(): Match an existing account based on user+host processlist_grantees: Assigning of GRANTEEs for connected processes candidate_keys: Listing of prioritized candidate keys: keys which are UNIQUE, by order of best-use. easter_day(): Returns DATE of easter day [...]
A MySQL account is a user/host combination. A MySQL connection is done by a user connecting from some host. However, the user/host from which the connection is made are not the same as the user/host as specified in the account. For example, the account may be created thus: CREATE USER 'temp'@'10.0.0.%' IDENTIFIED BY '123456'; The [...]
Today I have released common_schema, a utility schema for MySQL which includes many views and functions, and is aimed to be installed on any MySQL server. What does it do? There are views answering for all sorts of useful information: stuff related to schema analysis, data dimensions, monitoring, processes & transactions, security, internals... There are [...]
Following up on MySQL security: inconsistencies, and on MySQL bug #61596, I was thinking it may take a long time till the non-existent ROUTINE_PRIVILEGES view is implemented. Here's my own implementation of the view. I've followed the somewhat strange conventions used in the *_PRIVILEGES tables in INFORMATION_SCHEMA, where the IS_GRANTABLE is a separate column, although [...]
Doing some work with MySQL security, I've noticed a few inconsistencies. They're mostly not-too-terrible for daily work, except they get in my way right now. The ALL PRIVILEGES inconsistency The preferred way of assigning account privileges in MySQL is by way of using GRANT. With GRANT, one assigns one or more privileges to an account, [...]
I asked this during my presentation on the MySQL Conference this year. And I got a unanimous answer from the crowd. Take a moment to think this over, before coming up with the answer. Meanwhile, an intermezzo. Tam dam dam... Pom pom Pom pom Pom pom... If your answer is that the most basic privilege [...]
Have just read Darren Cassar's Recovering a MySQL `root` password – Three solutions. There's a fourth solution: using an init-file, which leads to just one restart of the database instead of two. It also avoids the security issue involved with using skip-grant-tables. I've written all about it before on Dangers of skip-grant-tables. Darren's 1st advice [...]
Question asked by a student: is there a way to limit a table's quote on disk? Say, limit a table to 2GB, after which it will refuse to grow? Note that the requirement is that rows are never DELETEd. The table must simply refuse to be updated once it reaches a certain size. There is [...]
You have old_passwords=1 in your my.cnf. I'm guessing this is because you used one of the my-small.cnf, my-large.cnf etc. templates provided with your MySQL distribution. These files can easily win the "most outdated sample configuration file contest". Usually it's no big deal: if some parameter isn't right, you just go and change it. Some variables, [...]
Maybe I'm like an old replication server, lagging way behind, but a couple of weeks ago I found autossh, which is a wrapper around ssh, that keeps reconnecting the session if it breaks. With public key encryption, I am now able to work out pretty reliable SSH tunneling among servers, which doesn't break. It seems [...]