Repository: crossplane-contrib/provider-sql (go)

Issue:
GRANTs Failing for Several Grant Types
### What happened?
I'm attempting to create several GRANTs in MySQL. A few succeed, but several fail with `cannot create grant: Error 1221: Incorrect usage of DB GRANT and GLOBAL PRIVILEGES`. It seems to be related to the specific GRANT types I am trying to create.

`PROCESS, REPLICATION CLIENT, REPLICATION SLAVE` in particular.

I can create the failing GRANTs from the MySQL CLI without any issue, using the same username/password that Crossplane is using.

Other GRANT objects with different sets of GRANT types work correctly and create the grants. e.g. `"SELECT","INSERT","UPDATE",
"DELETE","EXECUTE","CREATE TEMPORARY TABLES"`


```
NAME                                              READY   SYNCED   AGE    ROLE                DATABASE               PRIVILEGES
grant.mysql.sql.crossplane.io/datadog-perf        True    True     2d6h   datadog             performance_schema.*   ["SELECT"]
grant.mysql.sql.crossplane.io/datadog-repl        False   False    2d6h   datadog             *.*                    ["REPLICATION CLIENT","PROCE
SS"]
grant.mysql.sql.crossplane.io/somegrantfoo     True    True     2d7h   someuser     *.*                    ["SELECT","INSERT","UPDATE",
"DELETE","EXECUTE","CREATE TEMPORARY TABLES"]
```

### How can we reproduce it?
``` yaml
apiVersion: mysql.sql.crossplane.io/v1alpha1
kind: Grant
metadata:
  name: datadog-repl
spec:
  providerConfigRef:
    name: foo
  forProvider:
    privileges:
      - REPLICATION CLIENT
      - PROCESS
    userRef:
      name: datadog
    database: "*.*"
```

### What environment did it happen in?
Crossplane version:  

`Image:         crossplane/crossplane:v1.7.0`
`Image:         crossplane/provider-sql-controller:v0.4.1`

* Cloud: Azure MySQL Flexible Server v8.0
* Kubernetes version:
``` sh
> kubectl version
Client Version: version.Info{Major:"1", Minor:"23", GitVersion:"v1.23.5", GitCommit:"c285e781331a3785a7f436042c65c5641ce8a9e9", GitTreeState:"clean", BuildDate:"2022-03-16T15:51:05Z", GoVersion:"go1.17.8", Compiler:"gc", Platform:"darwin/amd64"}
Server Version: version.Info{Major:"1", Minor:"22", GitVersion:"v1.22.6", GitCommit:"5963a7a5570a1b8c977c31ced503cb20f0ef6e8c", GitTreeState:"clean", BuildDate:"2022-03-10T07:59:42Z", GoVersion:"go1.16.12", Compiler:"gc", Platform:"linux/amd64"}
```
* Kubernetes distribution: Azure AKS
* OS (e.g. from /etc/os-release)
``` sh
> # cat /etc/os-release
NAME="Ubuntu"
VERSION="18.04.6 LTS (Bionic Beaver)"
```
* Kernel (e.g. `uname -a`)
``` sh 
 uname -a
Linux aks-lab90-23705758-vmss000001 5.4.0-1069-azure #72~18.04.1-Ubuntu SMP Mon Feb 7 11:12:24 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux
```

### Debug Log
```
provider-sql 2022-04-04T03:21:03.853Z	DEBUG	provider-sql	Cannot create external resource	{"controller": "managed/grant.mysql.sql.crossplane.io", "request": "/datadog-repl", "uid": "1a84a73b-e5d0-4958-92b1-07d82e0992b1", "version": "185563119", "external-name": "datadog-repl", "error": "cannot create grant: Error 1221: Incorrect usage of DB GRANT and GLOBAL PRIVILEGES", "errorVerbose": "Error 1221: Incorrect usage of DB GRANT and GLOBAL PRIVILEGES\ncannot create grant\ngithub.com/crossplane-contrib/provider-sql/pkg/controller/mysql/grant.(*external).Create\n\t/home/runner/work/provider-sql/provider-sql/pkg/controller/mysql/grant/reconciler.go:249\ngithub.com/crossplane/crossplane-runtime/pkg/reconciler/managed.(*Reconciler).Reconcile\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/github.com/crossplane/crossplane-runtime@v0.13.0/pkg/reconciler/managed/reconciler.go:670\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller).reconcileHandler\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/sigs.k8s.io/controller-runtime@v0.8.0/pkg/internal/controller/controller.go:293\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller).processNextWorkItem\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/sigs.k8s.io/controller-runtime@v0.8.0/pkg/internal/controller/controller.go:248\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller).Start.func1.1\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/sigs.k8s.io/controller-runtime@v0.8.0/pkg/internal/controller/controller.go:211\nk8s.io/apimachinery/pkg/util/wait.JitterUntilWithContext.func1\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/k8s.io/apimachinery@v0.20.1/pkg/util/wait/wait.go:185\nk8s.io/apimachinery/pkg/util/wait.BackoffUntil.func1\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/k8s.io/apimachinery@v0.20.1/pkg/util/wait/wait.go:155\nk8s.io/apimachinery/pkg/util/wait.BackoffUntil\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/k8s.io/apimachinery@v0.20.1/pkg/util/wait/wait.go:156\nk8s.io/apimachinery/pkg/util/wait.JitterUntil\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/k8s.io/apimachinery@v0.20.1/pkg/util/wait/wait.go:133\nk8s.io/apimachinery/pkg/util/wait.JitterUntilWithContext\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/k8s.io/apimachinery@v0.20.1/pkg/util/wait/wait.go:185\nk8s.io/apimachinery/pkg/util/wait.UntilWithContext\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/k8s.io/apimachinery@v0.20.1/pkg/util/wait/wait.go:99\nruntime.goexit\n\t/opt/hostedtoolcache/go/1.17.7/x64/src/runtime/asm_amd64.s:1581"}
```

Pull request:
Fixed database by passing a default value
### Description of your changes
Since the docs says the database is optional, I made it in the code to option, because actually if you don't pass a value this following error occurs:
```
Observed a panic: "invalid memory address or nil pointer dereference" (runtime error: invalid memory address or nil pointer dereference)
...
/home/runner/work/provider-sql/provider-sql/pkg/controller/mysql/grant/reconciler.go:143 +0x98
```
This also will fix #79 and #84 
Also the documentation was updated to reflect that

I have:

- [x] Read and followed Crossplane's [contribution process].
- [x] Run `make reviewable` to ensure this PR is ready for review.
I am not a go guys but when running the `make reviewable` I got this error:
```
Can't run linter unused: buildir: failed to load package goarch: could not load export data: cannot import "internal/goarch" (unknown iexport format version 2), export data is newer version - update tool
ERRO Running error: buildir: failed to load package goarch: could not load export data: cannot import "internal/goarch" (unknown iexport format version 2), export data is newer version - update tool
```
I installed go1.17.11 as is stated in the docs and go1.13.15 as is stated in `go.mod`

### How has this code been tested

[contribution process]: https://git.io/fj2m9

### Update
1. The problem was that I was just aliasing the go1.17.11 to go and that does not work, you really need to update your PATH variable



Patch:
diff --git a/apis/mysql/v1alpha1/grant_types.go b/apis/mysql/v1alpha1/grant_types.go
index af47224..d15c6e4 100644
--- a/apis/mysql/v1alpha1/grant_types.go
+++ b/apis/mysql/v1alpha1/grant_types.go
@@ -75,13 +75,13 @@ type GrantParameters struct {
 	// +optional
 	UserSelector *xpv1.Selector `json:"userSelector,omitempty"`
 
-	// Tables this grant is for.
+	// Tables this grant is for, default *.
 	// +optional
 	Table *string `json:"table,omitempty" default:"*"`
 
-	// Database this grant is for.
+	// Database this grant is for, default *.
 	// +optional
-	Database *string `json:"database,omitempty"`
+	Database *string `json:"database,omitempty" default:"*"`
 
 	// DatabaseRef references the database object this grant it for.
 	// +immutable
diff --git a/package/crds/mysql.sql.crossplane.io_grants.yaml b/package/crds/mysql.sql.crossplane.io_grants.yaml
index e10913b..1e3fabc 100644
--- a/package/crds/mysql.sql.crossplane.io_grants.yaml
+++ b/package/crds/mysql.sql.crossplane.io_grants.yaml
@@ -72,7 +72,7 @@ spec:
                   instance.
                 properties:
                   database:
-                    description: Database this grant is for.
+                    description: Database this grant is for, default *.
                     type: string
                   databaseRef:
                     description: DatabaseRef references the database object this grant
@@ -159,7 +159,7 @@ spec:
                     minItems: 1
                     type: array
                   table:
-                    description: Tables this grant is for.
+                    description: Tables this grant is for, default *.
                     type: string
                   user:
                     description: User this grant is for.
diff --git a/pkg/controller/mysql/grant/reconciler.go b/pkg/controller/mysql/grant/reconciler.go
index 548ee77..2fe7464 100644
--- a/pkg/controller/mysql/grant/reconciler.go
+++ b/pkg/controller/mysql/grant/reconciler.go
@@ -61,7 +61,7 @@ const (
 )
 
 var (
-	grantRegex = regexp.MustCompile("^GRANT (.+) ON `(.+)`\\.(.+) TO .+")
+	grantRegex = regexp.MustCompile(`^GRANT (.+) ON (.+)\.(.+) TO .+`)
 )
 
 // Setup adds a controller that reconciles Grant managed resources.
@@ -140,8 +140,8 @@ func (c *external) Observe(ctx context.Context, mg resource.Managed) (managed.Ex
 	}
 
 	username := *cr.Spec.ForProvider.User
-	dbname := *cr.Spec.ForProvider.Database
-	table := defaultTable(cr.Spec.ForProvider.Table)
+	dbname := defaultIdentifier(cr.Spec.ForProvider.Database)
+	table := defaultIdentifier(cr.Spec.ForProvider.Table)
 
 	privileges, result, err := c.getPrivileges(ctx, username, dbname, table)
 	if err != nil {
@@ -166,10 +166,11 @@ func (c *external) Observe(ctx context.Context, mg resource.Managed) (managed.Ex
 	}, nil
 }
 
-func defaultTable(table *string) string {
-	if !(table == nil) {
-		return mysql.QuoteIdentifier(*table)
+func defaultIdentifier(identifier *string) string {
+	if identifier != nil && *identifier != "*" {
+		return mysql.QuoteIdentifier(*identifier)
 	}
+
 	return "*"
 }
 
@@ -244,8 +245,8 @@ func (c *external) Create(ctx context.Context, mg resource.Managed) (managed.Ext
 	}
 
 	username := *cr.Spec.ForProvider.User
-	dbname := *cr.Spec.ForProvider.Database
-	table := defaultTable(cr.Spec.ForProvider.Table)
+	dbname := defaultIdentifier(cr.Spec.ForProvider.Database)
+	table := defaultIdentifier(cr.Spec.ForProvider.Table)
 
 	privileges := strings.Join(cr.Spec.ForProvider.Privileges.ToStringSlice(), ", ")
 
@@ -264,8 +265,8 @@ func (c *external) Update(ctx context.Context, mg resource.Managed) (managed.Ext
 	}
 
 	username := *cr.Spec.ForProvider.User
-	dbname := *cr.Spec.ForProvider.Database
-	table := defaultTable(cr.Spec.ForProvider.Table)
+	dbname := defaultIdentifier(cr.Spec.ForProvider.Database)
+	table := defaultIdentifier(cr.Spec.ForProvider.Table)
 
 	privileges := strings.Join(cr.Spec.ForProvider.Privileges.ToStringSlice(), ", ")
 	username, host := mysql.SplitUserHost(username)
@@ -276,7 +277,7 @@ func (c *external) Update(ctx context.Context, mg resource.Managed) (managed.Ext
 	// Using a transaction is unfortunately not possible because a GRANT triggers
 	// an implicit commit: https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html
 	query := fmt.Sprintf("REVOKE ALL ON %s.%s FROM %s@%s",
-		mysql.QuoteIdentifier(dbname),
+		dbname,
 		table,
 		mysql.QuoteValue(username),
 		mysql.QuoteValue(host),
@@ -297,7 +298,7 @@ func createGrantQuery(privileges, dbname, username string, table string) string
 	username, host := mysql.SplitUserHost(username)
 	result := fmt.Sprintf("GRANT %s ON %s.%s TO %s@%s",
 		privileges,
-		mysql.QuoteIdentifier(dbname),
+		dbname,
 		table,
 		mysql.QuoteValue(username),
 		mysql.QuoteValue(host),
@@ -313,15 +314,15 @@ func (c *external) Delete(ctx context.Context, mg resource.Managed) error {
 	}
 
 	username := *cr.Spec.ForProvider.User
-	dbname := *cr.Spec.ForProvider.Database
-	table := defaultTable(cr.Spec.ForProvider.Table)
+	dbname := defaultIdentifier(cr.Spec.ForProvider.Database)
+	table := defaultIdentifier(cr.Spec.ForProvider.Table)
 
 	privileges := strings.Join(cr.Spec.ForProvider.Privileges.ToStringSlice(), ", ")
 	username, host := mysql.SplitUserHost(username)
 
 	query := fmt.Sprintf("REVOKE %s ON %s.%s FROM %s@%s",
 		privileges,
-		mysql.QuoteIdentifier(dbname),
+		dbname,
 		table,
 		mysql.QuoteValue(username),
 		mysql.QuoteValue(host),


Test patch:
diff --git a/pkg/controller/mysql/grant/reconciler_test.go b/pkg/controller/mysql/grant/reconciler_test.go
index a8e5cf5..cedb57b 100644
--- a/pkg/controller/mysql/grant/reconciler_test.go
+++ b/pkg/controller/mysql/grant/reconciler_test.go
@@ -381,6 +381,36 @@ func TestObserve(t *testing.T) {
 				err: nil,
 			},
 		},
+		"SuccessGrantNoDatabaseNoTable": {
+			reason: "We should return no error if no database and table were provided",
+			fields: fields{
+				db: mockDB{
+					MockQuery: func(ctx context.Context, q xsql.Query) (*sql.Rows, error) {
+						return mockRowsToSQLRows(
+							sqlmock.NewRows([]string{"Grants"}).
+								AddRow("GRANT CREATE, DROP ON *.* TO 'success-user'@%"),
+						), nil
+					},
+				},
+			},
+			args: args{
+				mg: &v1alpha1.Grant{
+					Spec: v1alpha1.GrantSpec{
+						ForProvider: v1alpha1.GrantParameters{
+							User:       pointer.StringPtr("success-user"),
+							Privileges: v1alpha1.GrantPrivileges{"DROP", "CREATE"},
+						},
+					},
+				},
+			},
+			want: want{
+				o: managed.ExternalObservation{
+					ResourceExists:   true,
+					ResourceUpToDate: true,
+				},
+				err: nil,
+			},
+		},
 		"SuccessGrantWithTables": {
 			reason: "We should see the grants in sync when using a table",
 			fields: fields{