Unable to grant proxy privilege using mysql provider.

[maver1ck]

Terraform Version

Terraform v1.5.7

Affected Resource(s)

mysql_grant

Terraform Configuration Files

resource "mysql_user" "pUser1" {
  user = "pUser1"
  host = "%"
  plaintext_password = "xxx"
}

resource "mysql_user" "proxy" {
  user = "proxy"
  host = "%"
  auth_plugin = "authentication_oci"
  auth_string_hashed = "{\"tenancy\": \"ocid1.tenancy.oc1..xxx\",\"group_mapping\": {\"ocid1.group.oc1..xxx\": \"pUser1\"}}"
}

resource "mysql_grant" "proxy_pUser1" {
  user = mysql_user.proxy.user
  host = mysql_user.proxy.host
  privileges = ["PROXY"]
  database = mysql_user.pUser1.user
  table =  ""
}

Debug Output

│ Error: Error running SQL (GRANT PROXY ON `pUser1`.* TO 'proxy'@'%'): 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 '.* TO 'proxy'@'%'' at line 1

Expected Behavior

We should be able to add PROXY privilege to the user.

Actual Behavior

Error appears.

References

https://docs.oracle.com/en-us/iaas/mysql-database/doc/connecting-mapped-proxy-user1.html

[petoju]

@maver1ck can you run that command using CLI outside this provider? How does the correct MySQL call look like?

Knowing this could make it easier to fix it - I don't see any clear indication of what's wrong.

[maver1ck]

Correct SQL looks like this GRANT PROXY ON 'pUser1'@'%' TO 'proxy'@'%'"

[maver1ck]

PS. This is working terraform script where I used mysql_sql resource

terraform {
  required_providers {
    mysql = {
      source = "petoju/mysql"
      version = "3.0.43"
    }
  }
}

provider "mysql" {
  endpoint = "localhost:3308"
  username = "admin"
  password = "xxx!"
}

# Create a Database
resource "mysql_database" "test" {
  name = "test_123"
}

resource "mysql_user" "pUser1" {
  user = "pUser1"
  host = "%"
  plaintext_password = "xxx!"
}

resource "mysql_user" "proxy" {
  user = ""
  host = "%"
  auth_plugin = "authentication_oci"
  auth_string_hashed = "{\"tenancy\": \"ocid1.tenancy.oc1..xxx\",\"group_mapping\": {\"ocid1.group.oc1..xxx\": \"pUser1\"}}"
}

resource "mysql_grant" "test_pUser1" {
  user = mysql_user.pUser1.user
  host = mysql_user.pUser1.host
  privileges = ["ALL"]
  database = mysql_database.test.name
}

resource "mysql_sql" "test" {
  name = "test"
  create_sql = "GRANT PROXY ON '${mysql_user.pUser1.user}'@'${mysql_user.pUser1.host}' TO '${mysql_user.proxy.user}'@'${mysql_user.proxy.host}'"
  delete_sql = "SELECT 1"
}