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

Issue:
Support mysql grant option
<!--
Thank you for helping to improve Crossplane!

Please be sure to search for open issues before raising a new one. We use issues
for bug reports and feature requests. Please find us at https://slack.crossplane.io
for questions, support, and discussion.
-->

### What problem are you facing?
<!--
Please tell us a little about your use case - it's okay if it's hypothetical!
Leading with this context helps frame the feature request so we can ensure we
implement it sensibly.
--->
Sql provider does not allow creating users with grant option in mysql databases

### How could Crossplane help solve your problem?
<!--
Let us know how you think Crossplane could help with your use case. 
-->

Adding an optional boolean field to **user.mysql.sql.crossplane.io crd** `WithGrantOption` and if that field is set to true add `WITH GRANT OPTION` to the query string in: https://github.com/crossplane-contrib/provider-sql/blob/master/pkg/controller/mysql/grant/reconciler.go#L299

I would like to work in a pr if this feature is considered as desirable


Pull request:
Support MySQL `WITH GRANT OPTION` (cherry-picks #132)
<!--
Thank you for helping to improve Crossplane!

Please read through https://git.io/fj2m9 if this is your first time opening a
Crossplane pull request. Find us in https://slack.crossplane.io/messages/dev if
you need any help contributing.
-->

### Description of your changes

<!--
Briefly describe what this pull request does. Be sure to direct your reviewers'
attention to anything that needs special consideration.

We love pull requests that resolve an open Crossplane issue. If yours does, you
can uncomment the below line to indicate which issue your PR fixes, for example
"Fixes #500":

-->
Fixes #128

This PR cherry-picks the commits from #132 (all credit goes to the author) and fixes the merge conflicts with master.

I have:

- [x] Read and followed Crossplane's [contribution process].
- [x] Run `make reviewable` to ensure this PR is ready for review.

### How has this code been tested

<!--
Before reviewers can be confident in the correctness of this pull request, it
needs to tested and shown to be correct. Briefly describe the testing that has
already been done or which is planned for this change.
-->

[contribution process]: https://git.io/fj2m9

Quoting from https://github.com/crossplane-contrib/provider-sql/pull/132

> Added unit tests to grant controller:
> 
> * Observe method:
>   * SuccessGrantOptionNoDatabase
>   * SuccessGrantOptionWithDatabase
> * Create method:
>   * SuccessGrantOption
> * Update method:
>   * SuccessGrantOption



Patch:
diff --git a/pkg/controller/mysql/grant/reconciler.go b/pkg/controller/mysql/grant/reconciler.go
index 966f57f..8618b8f 100644
--- a/pkg/controller/mysql/grant/reconciler.go
+++ b/pkg/controller/mysql/grant/reconciler.go
@@ -59,7 +59,7 @@ const (
 )
 
 var (
-	grantRegex = regexp.MustCompile(`^GRANT (.+) ON (.+)\.(.+) TO .+`)
+	grantRegex = regexp.MustCompile(`^GRANT (.+) ON (\S+)\.(\S+) TO \S+@\S+?(\sWITH GRANT OPTION)?$`)
 )
 
 // Setup adds a controller that reconciles Grant managed resources.
@@ -172,9 +172,16 @@ func defaultIdentifier(identifier *string) string {
 
 func parseGrant(grant, dbname string, table string) (privileges []string) {
 	matches := grantRegex.FindStringSubmatch(grant)
-	if len(matches) == 4 && matches[2] == dbname && matches[3] == table {
-		return strings.Split(matches[1], ", ")
+	if len(matches) == 5 && matches[2] == dbname && matches[3] == table {
+		privileges := strings.Split(matches[1], ", ")
+
+		if matches[4] != "" {
+			privileges = append(privileges, "GRANT OPTION")
+		}
+
+		return privileges
 	}
+
 	return nil
 }
 
@@ -252,13 +259,13 @@ func (c *external) Create(ctx context.Context, mg resource.Managed) (managed.Ext
 	table := defaultIdentifier(cr.Spec.ForProvider.Table)
 
 	privileges := strings.Join(cr.Spec.ForProvider.Privileges.ToStringSlice(), ", ")
+	grantOption := hasGrantOption(cr)
 	binlog := cr.Spec.ForProvider.BinLog
-	query := createGrantQuery(privileges, dbname, username, table)
+	query := createGrantQuery(privileges, dbname, username, table, grantOption)
 
 	if err := mysql.ExecWithBinlogAndFlush(ctx, c.db, mysql.ExecQuery{Query: query, ErrorValue: errCreateGrant}, mysql.ExecOptions{Binlog: binlog}); err != nil {
 		return managed.ExternalCreation{}, err
 	}
-
 	return managed.ExternalCreation{}, nil
 }
 
@@ -278,6 +285,7 @@ func (c *external) Update(ctx context.Context, mg resource.Managed) (managed.Ext
 	observed := cr.Status.AtProvider.Privileges
 	desired := cr.Spec.ForProvider.Privileges.ToStringSlice()
 	toGrant, toRevoke := diffPermissions(desired, observed)
+	grantOption := hasGrantOption(cr)
 
 	if len(toRevoke) > 0 {
 		sort.Strings(toRevoke)
@@ -300,14 +308,7 @@ func (c *external) Update(ctx context.Context, mg resource.Managed) (managed.Ext
 
 	if len(toGrant) > 0 {
 		sort.Strings(toGrant)
-		query := fmt.Sprintf("GRANT %s ON %s.%s TO %s@%s",
-			strings.Join(toGrant, ", "),
-			dbname,
-			table,
-			mysql.QuoteValue(username),
-			mysql.QuoteValue(host),
-		)
-
+		query := createGrantQuery(strings.Join(toGrant, ", "), dbname, username, table, grantOption)
 		if err := mysql.ExecWithBinlogAndFlush(ctx, c.db,
 			mysql.ExecQuery{
 				Query: query, ErrorValue: errCreateGrant,
@@ -316,11 +317,20 @@ func (c *external) Update(ctx context.Context, mg resource.Managed) (managed.Ext
 			return managed.ExternalUpdate{}, err
 		}
 	}
-
 	return managed.ExternalUpdate{}, nil
 }
 
-func createGrantQuery(privileges, dbname, username string, table string) string {
+// hasGrantOption returns true if the privileges has a grant option item
+func hasGrantOption(cr *v1alpha1.Grant) bool {
+	for _, p := range cr.Spec.ForProvider.Privileges {
+		if string(p) == "GRANT OPTION" {
+			return true
+		}
+	}
+	return false
+}
+
+func createGrantQuery(privileges, dbname, username string, table string, grantOption bool) string {
 	username, host := mysql.SplitUserHost(username)
 	result := fmt.Sprintf("GRANT %s ON %s.%s TO %s@%s",
 		privileges,
@@ -330,6 +340,10 @@ func createGrantQuery(privileges, dbname, username string, table string) string
 		mysql.QuoteValue(host),
 	)
 
+	if grantOption {
+		result = fmt.Sprintf("%s WITH GRANT OPTION", result)
+	}
+
 	return result
 }
 


Test patch:
diff --git a/pkg/controller/mysql/grant/reconciler_test.go b/pkg/controller/mysql/grant/reconciler_test.go
index aa38c35..8db7ec8 100644
--- a/pkg/controller/mysql/grant/reconciler_test.go
+++ b/pkg/controller/mysql/grant/reconciler_test.go
@@ -320,6 +320,79 @@ func TestObserve(t *testing.T) {
 				observedPrivileges: []string{allPrivileges},
 			},
 		},
+		"SuccessGrantOptionNoDatabase": {
+			reason: "We should return no error if we can successfully show our grants",
+			fields: fields{
+				db: mockDB{
+					MockQuery: func(ctx context.Context, q xsql.Query) (*sql.Rows, error) {
+						return mockRowsToSQLRows(
+							sqlmock.NewRows(
+								[]string{"Grants"},
+							).AddRow("GRANT INSERT, SELECT ON *.* TO 'success-user'@% WITH GRANT OPTION"),
+						), nil
+					},
+				},
+			},
+			args: args{
+				mg: &v1alpha1.Grant{
+					Spec: v1alpha1.GrantSpec{
+						ForProvider: v1alpha1.GrantParameters{
+							User:       pointer.StringPtr("success-user"),
+							Privileges: v1alpha1.GrantPrivileges{"INSERT", "SELECT", "GRANT OPTION"},
+						},
+					},
+				},
+			},
+			want: want{
+				o: managed.ExternalObservation{
+					ResourceExists:   true,
+					ResourceUpToDate: true,
+				},
+				err: nil,
+				observedPrivileges: []string{
+					"GRANT OPTION",
+					"INSERT",
+					"SELECT",
+				},
+			},
+		},
+		"SuccessGrantOptionWithDatabase": {
+			reason: "We should return no error if we can successfully show our grants",
+			fields: fields{
+				db: mockDB{
+					MockQuery: func(ctx context.Context, q xsql.Query) (*sql.Rows, error) {
+						return mockRowsToSQLRows(
+							sqlmock.NewRows(
+								[]string{"Grants"},
+							).AddRow("GRANT INSERT, SELECT ON `success-db`.* TO 'success-user'@% WITH GRANT OPTION"),
+						), nil
+					},
+				},
+			},
+			args: args{
+				mg: &v1alpha1.Grant{
+					Spec: v1alpha1.GrantSpec{
+						ForProvider: v1alpha1.GrantParameters{
+							Database:   pointer.StringPtr("success-db"),
+							User:       pointer.StringPtr("success-user"),
+							Privileges: v1alpha1.GrantPrivileges{"INSERT", "SELECT", "GRANT OPTION"},
+						},
+					},
+				},
+			},
+			want: want{
+				o: managed.ExternalObservation{
+					ResourceExists:   true,
+					ResourceUpToDate: true,
+				},
+				err: nil,
+				observedPrivileges: []string{
+					"GRANT OPTION",
+					"INSERT",
+					"SELECT",
+				},
+			},
+		},
 		"SuccessDiffGrants": {
 			reason: "We should return no error if different grants exist for the provided database",
 			fields: fields{
@@ -635,8 +708,59 @@ func TestCreate(t *testing.T) {
 				mg: &v1alpha1.Grant{
 					Spec: v1alpha1.GrantSpec{
 						ForProvider: v1alpha1.GrantParameters{
-							Database: pointer.StringPtr("test-example"),
-							User:     pointer.StringPtr("test-example"),
+							Database:   pointer.StringPtr("test-example"),
+							User:       pointer.StringPtr("test-example"),
+							Privileges: v1alpha1.GrantPrivileges{"INSERT", "SELECT"},
+						},
+					},
+				},
+			},
+			want: want{
+				err: nil,
+			},
+		},
+		"SuccessNoDatabase": {
+			reason: "No error should be returned when we successfully create a grant with no database",
+			fields: fields{
+				db: &mockDB{
+					MockExec: func(ctx context.Context, q xsql.Query) error { return nil },
+				},
+			},
+			args: args{
+				mg: &v1alpha1.Grant{
+					Spec: v1alpha1.GrantSpec{
+						ForProvider: v1alpha1.GrantParameters{
+							User:       pointer.StringPtr("test-example"),
+							Privileges: v1alpha1.GrantPrivileges{"INSERT", "SELECT"},
+						},
+					},
+				},
+			},
+			want: want{
+				err: nil,
+			},
+		},
+		"SuccessGrantOption": {
+			reason: "No error should be returned when we successfully create a grant with grant option",
+			fields: fields{
+				db: &mockDB{
+					MockExec: func(ctx context.Context, q xsql.Query) error {
+						if strings.HasPrefix(q.String, "GRANT") &&
+							!strings.HasSuffix(q.String, "WITH GRANT OPTION") {
+							return errBoom
+						}
+
+						return nil
+					},
+				},
+			},
+			args: args{
+				mg: &v1alpha1.Grant{
+					Spec: v1alpha1.GrantSpec{
+						ForProvider: v1alpha1.GrantParameters{
+							Database:   pointer.StringPtr("test-example"),
+							User:       pointer.StringPtr("test-example"),
+							Privileges: v1alpha1.GrantPrivileges{"GRANT OPTION", "ALL"},
 						},
 					},
 				},
@@ -857,6 +981,35 @@ func TestUpdate(t *testing.T) {
 				c:   managed.ExternalUpdate{},
 			},
 		},
+		"SuccessGrantOption": {
+			reason: "No error should be returned when we successfully create a grant with grant option",
+			fields: fields{
+				db: &mockDB{
+					MockExec: func(ctx context.Context, q xsql.Query) error {
+						if strings.HasPrefix(q.String, "GRANT") &&
+							!strings.HasSuffix(q.String, "WITH GRANT OPTION") {
+							return errBoom
+						}
+
+						return nil
+					},
+				},
+			},
+			args: args{
+				mg: &v1alpha1.Grant{
+					Spec: v1alpha1.GrantSpec{
+						ForProvider: v1alpha1.GrantParameters{
+							Database:   pointer.StringPtr("test-example"),
+							User:       pointer.StringPtr("test-example"),
+							Privileges: v1alpha1.GrantPrivileges{"GRANT OPTION", "ALL"},
+						},
+					},
+				},
+			},
+			want: want{
+				err: nil,
+			},
+		},
 	}
 
 	for name, tc := range cases {