Revoke All Privileges Fails

[jls-appfire]

We successfully created a mysql_grant resource in MySQL 8 (AWS Aurora) with permissions of all / grant = true. However when deleting said mysql_grant resource, the provider returns an error:

Error: error revoking ALL (REVOKE ALL, GRANT OPTION ON `mydatabase`.* FROM 'myuser'@'%'): Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON `mydatabase`.* FROM 'myuser'@'%'' at line 1

Reading the MySQL 8 documentation at https://dev.mysql.com/doc/refman/8.0/en/revoke.html it seems that the correct revoke statement when using ALL is:

REVOKE ALL, GRANT OPTION FROM 'myuser'@'%'

http://doc.docs.sk/mysql-refman-5.5/revoke.html for MySQL 5.5 (although it is not the official URL) indicates the same.

I find it hard to believe no one else reported this TBH, so perhaps I've missed something in my config? Looking at the code however, it seems that the function to generate the revoke statement does indeed generate only one output which includes. the ON ... component.

Terraform Version

Run terraform -v to show the version. If you are not running the latest version of Terraform, please upgrade because your issue may have already been fixed.

1.6.5

Affected Resource(s)

Please list the resources as a list, for example:

• mysql_grant

If this issue appears to affect multiple resources, it may be an issue with Terraform's core, so please mention this.

Terraform Configuration Files

resource "mysql_user" "myuser" {
  user = "myuser"
  host               = "%"
  plaintext_password = random_password.myuser_password.result

  provider = mysql.virginia
}

resource "mysql_database" "mydatabase" {
  name = "mydatabase"

  provider = mysql.virginia
}

resource "mysql_grant" "my_database" {
  user     = "myuser"
  host     = "mysql-host"
  database = "mydatabase"

  privileges = [
    "ALL"
  ]

  grant = true

  depends_on = [
    mysql_database.mydatabase,
    mysql_user.myuser
  ]

  provider = mysql.virginia
}

Debug Output

Please provider a link to a GitHub Gist containing the complete debug output: https://www.terraform.io/docs/internals/debugging.html. Please do NOT paste the debug output in the issue; just paste a link to the Gist. N/A

Panic Output

If Terraform produced a panic, please provide a link to a GitHub Gist containing the output of the crash.log. N/A

Expected Behavior

What should have happened?

The mysql_grant should be revoked without error.

Actual Behavior

What actually happened?

The revoke statement throws the error

Error: error revoking ALL (REVOKE ALL, GRANT OPTION ON `mydatabase`.* FROM 'myuser'@'%'): Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON `mydatabase`.* FROM 'myuser'@'%'' at line 1

Steps to Reproduce

Please list the steps required to reproduce the issue, for example:

1. terraform apply
2. remove the mysql_grant
3. terraform apply

Important Factoids

Are there anything atypical about your accounts that we should know? For example: Running in EC2 Classic? Custom version of OpenStack? Tight ACLs?

We're using MySQL 8 on AWS Aurora.

References

Are there any other GitHub issues (open or closed) or Pull Requests that should be linked here? For example:

[petoju]

Which version of provider did you use? This should be fixed in 3.0.49 and newer versions.

I find it hard to believe no one else reported this TBH, so perhaps I've missed something in my config? Looking at the code however, it seems that the function to generate the revoke statement does indeed generate only one output which includes. the ON ... component.

It needs the ON ... component. That's because your grant granted the user/host all privileges to database mydatabase. Had you granted the very same user/host privileges to anotherdatabase, revoke without ON would revoke too much.

The issue is combining ALL PRIVILEGES with revoking GRANT OPTION like this. And Derek fixed it here https://github.com/petoju/terraform-provider-mysql/pull/121

[jls-appfire]

Looks like we are on 3.0.43 @petoju. I tried searching for a previous REVOKE issue but my query skills must be lacking. I'll try updating the module and let you know if it works for us.

[jls-appfire]

This is perhaps then a duplicate of https://github.com/petoju/terraform-provider-mysql/issues/120. However I did look at that code, but thought it was part of the issue (I could be reading the code wrong too, I'm not a Go expert). According to the MySQL Docs I was reading, it is the ON ... syntax that is the issue.

But before anything else, let me upgrade and validate as requested.

[petoju]

@jls-appfire does it work for you now?

[jls-appfire]

Yes @petoju it has been working. Apologies that I didn't first check the versions. Thank you for the help.