Conflicting grant definitions (database-specific SELECT vs. global SHOW_ROUTINE)

[maximmasiutin]

This bug report highlights an issue in the MySQL Terraform provider where defining separate grants (one restricting SELECT to a specific database and another allowing SHOW_ROUTINE on all databases) erroneously triggers a duplication error.

This is a refinement of the https://github.com/petoju/terraform-provider-mysql/issues/214

After the investigation, I narrowed down the problem to the reproducible result. problem is that with MySQL I can have SELECT limited to particular tables together with SHOW_ROUTINE which can only be set to *.*:

Therefore, the following are valid grants for MySQL:

GRANT SELECT ON mydatabase.* TO `myrole`@`%`
GRANT SHOW_ROUTINE ON *.* TO `myrole`@`%`

However, with the MySQL terraform provider I cannot create two different grants: one for SELECT for a particular database and another grant for SHOW_ROUTINE on *.*. The MySQL terraform provider gives error that the grant already exist. I can only create a grant for SELECT, SHOW_ROUTINE on *.*. In other words, if I add SHOW_ROUTINE privilege, I cannot limit SELECT for particular databases in the MySQL terraform provider. Should I create a test case for that? Problem is that your test instances do not seem to all using SHOW_ROUTINE at all.

In the above example, the grants should not conflict because in the first block it is for the database "mydatabase" while in the second block it is for the database "*". But the provider reports error on the second block that the grant already exist. To fix it, I have to combine two blocks into one, but in this case I will have to remove the database limitation for "SELECT" which is not intended.

resource "mysql_grant" "select" {
  provider   = mysql.myprovider
  role       = "role_analyst"
  database   = "mydatabase"
  table      = "*"
  privileges = ["SELECT"]
}

resource "mysql_grant" "showroutine" {
  provider   = mysql.myprovider
  role       = "role_analyst"
  database   = "*"
  table      = "*"
  privileges = ["SHOW_ROUTINE"]
}

Addressing this flaw to support distinct privilege resources will enable precise, granular permission management that aligns with MySQL's intended operational behavior.

[petoju]

Do they conflict? I believe two grants like you wrote should be just created. If they are not, please provide also log.

[maximmasiutin]

Does any of the databases in your test cases fully support SHOW_ROUTINE? I couldn't find one. I wanted to create a test case to illustrate the issue.

[petoju]

@maximmasiutin we already test most of this with basic things like SELECT / UPDATE. But you could use for example FLUSH_TABLES, that's also a dynamic privilege, that needs to be applied to all tables.

[maximmasiutin]

I added FLUSH_TABLES and SHOW_ROUTINE and all of them worked on Oracle MySQL and Percona, see https://github.com/petoju/terraform-provider-mysql/pull/217

Therefore, I could not reproduce the issue so far with the existing test cases at CircleCI.

My last idea is that terraform that I used in my environment is version 1.11.4, while in the make file it is 0.14.7: https://github.com/petoju/terraform-provider-mysql/blob/5be87ac13fd36be1b0d5855786ac08b69f311f44/GNUmakefile#L5

[maximmasiutin]

I made tests with indexed resources created with "for_each" and with resources in different providers (different database instances) at https://github.com/petoju/terraform-provider-mysql/pull/217 to figure out that the mysql provider works properly. It was an error in my configuration that in .tf files that referred to different providers one provider reference was incorrect, so that two resources referred to the same provider instead of on each own's one.

@petoju Should we also display the provider name when the privilege already exist so that users could have been able to trace such issues easier that I did? If the provider name would have been displayed in the error message, it would have been a good hint.

[petoju]

@maximmasiutin for showing provider name: should we add it to debug outputs?

Maybe, but I don't think it's that helpful. If you know there's something wrong with MySQL, you know the provider...