As per http://dev.mysql.com/doc/refman/5.5/en/identifiers.html , MySQL
allows for more than '\w-'. This commit improves the check to ensure
that:
- if username only contains [0-9a-zA-Z$_], it might be quoted. It is
not a requirement though
- if username contains anything else, it MUST be quoted
I kept 2 checks, but the 2nd one can probably be removed (I can't find a
username which match the 2nd one but not the first.)
* Mysql uses the underscore character to represent a single character
wildcard.
* A grant on table `the_database`.* would match `theAdatabase`.*, so
underscores must be escaped to avoid this match.
* The output from mysql escapes special characters (\n, \t, \0, and \\),
but the input does not need to be escaped.
* In order for the provider to compare the tables, the output of
mysql -NBe <query> must have \\ substituted with \.
Old regex is : /^GRANT\s(.+)\sON\s(.+)\sTO\s(.*)@(.*?)(\s.*)$/ . The
last part (\s.*)$ means "a space followed by anything". The issue is
that when user has no GRANT privileges, the "SHOW GRANTS FOR #{user_string}" returns
"GRANT SELECT ON `database`.* TO 'user'@'%'" which does not match (\s.*)$ .
This small patch fixes this making last bloc optional (thanks to '?').
mysql_grant has an autorequire()'d dependency on the .my.cnf file used
by the provider to talk to the database.
I've added this to mysql_database and mysql_user too since logically
these also need the file to be in place.
I've hit this bug because of a slightly unusual edge case in our own
manifests, but I think this fix belongs upstream regardless.
On MySQL v5.5.38, creating a database such as:
CREATE DATABASE `mydb` CHARACTER SET binary COLLATE binary;
seems to hit a parser bug. A workaround is simply to quote COLLATE
`binary`. As the quoting is harmless, and for aesthetics, quote both
the CHARACTER SET and COLLATE arguments.
Check for database existence when dropping to prevent
ERROR 1008 (HY000): Can't drop database 'test'; database doesn't exist
Signed-off-by: Ray Lehtiniemi <rayl@mail.com>
If database grant has backslash in database name (for example: example\_dev), then puppet will try to apply same resource every run because MySQL reports that table name with double backslash (for example: example\\_dev). By global replace of double backslash with single one, this issue is fixed.
In the grant provider users are fetched by querying mysql.user table. Grants
for those users are fetched using show grants for... syntax. This can lead to
errors, when some of the users in mysql.user table do not have currently
active grants.
This happens at least when MySQL is started with --skip-name-resolve option,
when there are users with the hostname part specified as a FQDN. Such users are
created by mysql_install_db. This leads to problems if mysql::account_security
is included for the node and skip-name-resolve is specified in override_options
hash for mysql::server.
Includes acceptance test for the change.
This addresses https://tickets.puppetlabs.com/browse/MODULES-1040.
The user parameter is required to have the form username@host. A grant
is identified in the instances method by a name of the form
username@host/table. The resource will fail to be identified as already
existing if the name given to the resource does not match this form.
MySQL/MariaDB automatically downcase hostnames:
MariaDB [mysql]> create user 'testuser'@'HOSTNAME';
MariaDB [mysql]> select user,host from user where host = 'hostname';
+----------+----------+
| user | host |
+----------+----------+
| testuser | hostname |
+----------+----------+
This causes problems when a mysql_user or datbase_user has an hostname
with non-lowercase characters:
database_user { "root@HOSTNAME":
ensure => absent,
}
The SELECT statements used to determine if the user exists will fail
because the comparisons use "HOSTNAME" but the database has "hostname".
This patch forces the hostname part of "user@hostname" to lower case in
the custom type definitions.
A prior commit accidently broke this, meaning that mysql_database
was querying the mysql defaults instead of each individual database
when trying to determine the current collate settings.
this should avoid errors like:
ERROR 1007 (HY000): Can't create database 'MyDB'; database exists
This error can cause a multi-master replication to stop due to conflicting
commands between nodes. For example, if the command create DB is run in
different nodes and then they will send it in the replication logs to each
other and then they will try to run them second time and fail.
Added "require" to the global mysql.rb file like in the other provider files.
defaults-file changed to defaults-extra-file in all the database_* (old) providers, the same as in the mysql_* providers.
Changed defaults-file to defaults-extra-file in all test files
Should load the .my.cnf file with "--defaults-extra-file" instead of "--defaults-file". This is necessary if we have global my.cnf file but we want to use both of them.
Because arrays are ordered lists, Puppet compares the list of retrieved
privileges against the defined privilege list. This causes it to
reapply privilege if the ordering differs. We now forcibly order in
the type and the provider to make sure we never falsely reapply
privileges.
The quote is need for username and host in mysql grant. revoke and grant function is already doing it with cmd_user(). not sure why the constructor didn't do it. This patch fixed#261 and #262.
Handful of changes here, such as removing flush (so that mysql_user
can be used for root password changes) and other tweaks here.
Add time option to mysql::backup.
This provider has undergone the largest set of changes and currently
just accepts a full SQL grant string as the name and then applies it,
making things easier for DBAs and removes the awkward attempts at
modelling grants into Puppet.
This work adds max_connections_per_hour, max_queries_per_hour, and
max_updates_per_hour support to the provider and extends self.instances to add
in the new parameters when checking existing users. It also adds
self.prefetch in order to speed up Puppet runs.
Provider is also switched to using mk_resource_methods to generate
all the resource readers, and exists? and other methods now use the
property_hash where appropriate.
Tests rewritten to handle changes and extend code coverage.
Add collate as a new managable parameter, and extend self.instances to
add in all parameters when checking existing databases. It also adds
self.prefetch in order to speed up Puppet runs.
Provider is also switched to using mk_resource_methods to generate
all the resource readers, and exists? and other methods now use the
property_hash where appropriate.
Tests rewritten to handle changes and extend code coverage.
If the /root/.my.cnf file does not exist but is specified by the
`--defaults-file` argument, the mysql calls will fail. The
`mysql::config` class creates this file, but if the custom resources are
used without including our classes then it will still break.
This allows users to use our custom resources without having to use our
classes.
Previous regex matched COLLATE value instead of CHARACTER SET. For
example:
> CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */
Returned utf8_bin instead of utf8 causing an unfortunate database refresh in my
configuration. Fixed the regex by adding the optional presence of the COLLATE
keyword.
This is necessary when running puppet as root using sudo because mysql
will still look in the user's home directory in that case unless told
otherwise.
The mysql database_grant provider currently has what is arguably a heinous
design flaw. At present:
1. The 'privileges' parameter for the database_grant type, mysql provider,
does not expect the same syntax as the mysql Grant command ('SELECT',
'UPDATE', 'DELETE', etc). Rather, it expects the user to supply column
names used to store raw grants in the mysql.db or mysql.user tables
internally ('Select_priv', 'Update_priv', 'Delete_priv', etc).
2. If a user supplies `privileges => [ 'SELECT', 'INSERT' ]` instead of
`privileges => [ 'Select_priv', 'Insert_priv' ]`, the provider fails
silently and will continuously attempt to update the privileges with
each successive puppet run. In the specific example provided, all privs
for the user/db will be set to false since e.g. 'INSERT' does not match
any valid privilege.
Unfortunately it doesn't look simple to modify the provider such that the
intuitive SELECT, DELETE, etc. keywords can be used without changing
existing behavior. Leaving that alone for now, it *is* pretty simple to add
a validation function that will at least fail cleanly if non-functional
privilege values are supplied that don't mean anything to the provider. If
the user is trying to use valid MySQL Grant syntax, the new validation
procedure will recognize this and suggest a correction. Hopefully giving
users this kind of warning will clue them in to what kind of input the
provider expects.
The incorrect regex did not all for the anonymous mysql users to be
removed via the mysql::server::account_security class. The regex is now
increased to cover for @localhost and @%.
This commit fixes an issue in self.instances of
database_user where none of the users were actually
being detected.
There was a accidental '\' in front of the '.' which
means that it will only consider users that have
one or more '.' in front of the '@'.
This commit removes the '\' so that all users are
returned that have one or more characters in from
of an '@'.
This is a major change to the module and would be released as a new
version.
* Add self.instances to database and database_user for puppet resource.
* Update database provider to use flush method.
* Update module to conform to puppet-lint recommendations.
* Cleanup some unecessary logic in mysql::db define type.
* Move mysql_restart to config class.
* Use class to class dependency instead of resource dependency.
* Change appropriate rspec-puppet tests.
* Add fixtures directory to simplify testing.
* Update raketask and spec_helper to reflect fixture changes.
* Update mysql_password function to support validation.
* Move client installation to a separate class.
* Update documentation and readme.
These were missing from the list of allowed privileges:
* event_priv
* trigger_priv
No rspec changes, as we don't even have basic coverage on these providers and
its a minor change so should be low risk.
