puppetlabs/puppetlabs-mysql — Issue #24: Providers do not correctly handle per-database permissions
Author: dominics
Created: 2011-11-17 22:33:21+00:00
State: open

If you have a single `database_user` with multiple `database_grant`s, that user's permissions on *all* databases is set to the permissions of the last grant declared.

class mysql::test {
database {
"foo":;
"bar":;
}

database_user {
"john@localhost":;
}

database_grant {
"john@localhost/foo":
privileges => ['all'];
"john@localhost/bar":
privileges => ['select_priv'];
}
}

In this configuration, john does not have write access to the foo database, because the permissions have been set to the same as the bar database.

Here are the commands the provider runs in this setup:

```
debug: Puppet::Type::Database_grant::ProviderMysql: Executing '/usr/bin/mysql mysql -Be update db set select_priv = 'Y', insert_priv = 'Y', update_priv = 'Y', delete_priv = 'Y', create_priv = 'Y', drop_priv = 'Y', grant_priv = 'Y', references_priv = 'Y', index_priv = 'Y', alter_priv = 'Y', create_tmp_table_priv = 'Y', lock_tables_priv = 'Y', create_view_priv = 'Y', show_view_priv = 'Y', create_routine_priv = 'Y', alter_routine_priv = 'Y', execute_priv = 'Y' where user="blah" and host="localhost"'
[...]
debug: Puppet::Type::Database_grant::ProviderMysql: Executing '/usr/bin/mysql mysql -Be update db set select_priv = 'Y', insert_priv = 'N', update_priv = 'N', delete_priv = 'N', create_priv = 'N', drop_priv = 'N', grant_priv = 'N', references_priv = 'N', index_priv = 'N', alter_priv = 'N', create_tmp_table_priv = 'N', lock_tables_priv = 'N', create_view_priv = 'N', show_view_priv = 'N', create_routine_priv = 'N', alter_routine_priv = 'N', execute_priv = 'N' where user="blah" and host="localhost"'
```

Note that neither query mentions the {db} column of the {db} table, so each updates two rows instead of one. The offending code seems to be this chunk in providers/database_grant/mysql.rb (~line 133):

```ruby
when :user
stmt = 'update user set '
where = ' where user="%s" and host="%s"' % [ name[:user], name[:host] ]
all_privs = MYSQL_USER_PRIVS
when :db
stmt = 'update db set '
where = ' where user="%s" and host="%s"' % [ name[:user], name[:host] ]
all_privs = MYSQL_DB_PRIVS
end
```