Unable to manage static and dynamic privileges at the same time in MySQL 8.0

[kei500]

In MySQL 8.0, static privilege and dynamic privilege are represented as separate grants as below.

> show grants for test;
+-------------------------------------------------------------------------+
| Grants for test@%                                                       |
+-------------------------------------------------------------------------+
| GRANT ALTER ON *.* TO `test`@`%` WITH GRANT OPTION                      |
| GRANT APPLICATION_PASSWORD_ADMIN ON *.* TO `test`@`%` WITH GRANT OPTION |
+-------------------------------------------------------------------------+

Currently, mysql_grant's resource id is consisted of user, host, database and table. So, in this case, Terraform cannot manage those grants because resource id is duplicated.

Farther more, about dynamic privileges, please note that grant is separated if WITH GRANT OPTION is specified or not.

> show grants for test;
+-------------------------------------------------------------------------+
| Grants for test@%                                                       |
+-------------------------------------------------------------------------+
| GRANT ALTER ON *.* TO `test`@`%` WITH GRANT OPTION                      |
| GRANT CONNECTION_ADMIN ON *.* TO `test`@`%`                             |
| GRANT APPLICATION_PASSWORD_ADMIN ON *.* TO `test`@`%` WITH GRANT OPTION |
+-------------------------------------------------------------------------+

Terraform Version

Terraform v1.4.2
on darwin_arm64
+ provider registry.terraform.io/hashicorp/aws v4.59.0
+ provider registry.terraform.io/petoju/mysql v3.0.32

Affected Resource(s)

Please list the resources as a list, for example:

• mysql_grant

Terraform Configuration Files

resource "mysql_user" "test" {
  provider           = mysql.main
  user               = "test"
  host               = "%"
  plaintext_password = "password"
}

resource "mysql_grant" "test" {
  provider = mysql.main
  user     = mysql_user.test.user
  host     = mysql_user.test.host
  database = "*"
  grant    = true
  privileges = [
    "ALTER", # Static Privilege
    "APPLICATION_PASSWORD_ADMIN", # Dynamic Privileges
  ]
}

Debug Output

N/A

Panic Output

N/A

Expected Behavior

We expect that there aren't resources which will be changed after applying Terraform once.

Actual Behavior

mysql_grant.test will be changed again.

Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following symbols:
  ~ update in-place

Terraform will perform the following actions:

  # mysql_grant.test will be updated in-place
  ~ resource "mysql_grant" "test" {
        id         = "test@%:*"
      ~ privileges = [
          + "ALTER",
            # (1 unchanged element hidden)
        ]
        # (7 unchanged attributes hidden)
    }

Plan: 0 to add, 1 to change, 0 to destroy.

Steps to Reproduce

1. terraform apply
2. terraform plan

Important Factoids

N/A

References

N/A

[petoju]

You're right, this is a bug. But it has an unintended side effect: as some grants may have grant option while other don't have it, we cannot manage all sets of resources using terraform. This was not considered.

I don't have a lot of time to fix it, but we should:

1. Add "grant option" into ID and everywhere. That would work well, I believe.
2. Make sure we merge all grants represented in more resources.

[petoju]

I believe this has been fixed some time ago. Please try with the latest version of provider and comment here / open another issue if it persists.