Feature request: Revoking access privileges

[BojanKomazec]

It would be useful if having an empty list for privileges in mysql_grant would revoke all privileges (if exist) for a given user, database and table.

resource "mysql_grant" {
   ...
   privileges = []
}

Alternatively, a new resource named like e.g. mysql_revoke should be introduced to complement the existing mysql_grant.

Provider version

3.0.65

MySQL version and settings

8.0

Terraform Configuration Files

I tried to grant user some privileges on all tables apart from one:

resource "mysql_grant" "my-user" {
  user       = mysql_user.my-user.user
  host       = mysql_user.my-user.host
  database   = "%"
  privileges = ["SELECT", "INSERT", "UPDATE", "DELETE", "CREATE"]
}

resource "mysql_grant" "revoke_my-user_access_to_table-a" {
  user       = mysql_user.my-user.user
  host       = mysql_user.my-user.host
  database   = "my-db"
  table      = "table-a"
  privileges = []
}

Expected Behavior

mysql provider grants my-user listed privileges on all tables apart from table-a.

Actual Behavior

terraform apply fails with error:

mysql_grant.revoke_my-user_access_to_table-a: Creating...
╷
│ Error: Error running SQL (GRANT  ON `my-db`.`table-a` TO 'my-user'@'%'): 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 `my-db`.`table-a` TO 'my-user'@'%'' at line 1

Steps to Reproduce

terraform apply

References

To achieve this currently I'd need to create a list of all tables and create "mysql_grant" for each of them (like in #179), with customized privileges for the exceptional table.

[petoju]

What we should do is handle this properly - warn soon about the issue.

However, we're trying to enforce import everywhere. Without import, running provider may be dangerous as it may remove some already existing privileges not managed by provider yet.

For now, I believe the best idea is to import resources with grants for all the tables (like in #179).