Repository: chanzuckerberg/terraform-provider-snowflake
Pull request #969 (2022-04-10 21:06:03)

feat: snowflake_user_ownership_grant resource
<!-- Feel free to delete comments as you fill this in -->

<!-- summary of changes -->

## Test Plan
<!-- detail ways in which this PR has been tested or needs to be tested -->
* [x] acceptance tests

```console
$ make test
goimports -w -d $(find . -type f -name '*.go' -not -path "./vendor/*" -not -path "./dist/*")
go mod tidy -compat=1.17
CGO_ENABLED=1 go test -race -coverprofile=coverage.txt -covermode=atomic  ./...
?       github.com/chanzuckerberg/terraform-provider-snowflake  [no test files]
ok      github.com/chanzuckerberg/terraform-provider-snowflake/pkg/datasources  0.199s  coverage: 4.1% of statements
?       github.com/chanzuckerberg/terraform-provider-snowflake/pkg/db   [no test files]
ok      github.com/chanzuckerberg/terraform-provider-snowflake/pkg/provider     0.367s  coverage: 30.4% of statements
ok      github.com/chanzuckerberg/terraform-provider-snowflake/pkg/resources    10.882s coverage: 55.2% of statements
ok      github.com/chanzuckerberg/terraform-provider-snowflake/pkg/snowflake    0.104s  coverage: 59.1% of statements
?       github.com/chanzuckerberg/terraform-provider-snowflake/pkg/testhelpers  [no test files]
ok      github.com/chanzuckerberg/terraform-provider-snowflake/pkg/validation   0.045s  coverage: 93.1% of statements
?       github.com/chanzuckerberg/terraform-provider-snowflake/pkg/version      [no test files]

$ make test-acceptance TESTARGS='-run=TestAccUserOwnershipGrant_defaults'
goimports -w -d $(find . -type f -name '*.go' -not -path "./vendor/*" -not -path "./dist/*")
go mod tidy -compat=1.17
SKIP_MANAGED_ACCOUNT_TEST=1 TF_ACC=1 go test -v -coverprofile=coverage.txt -covermode=atomic -run=TestAccUserOwnershipGrant_defaults ./...
?       github.com/chanzuckerberg/terraform-provider-snowflake  [no test files]
testing: warning: no tests to run
PASS
coverage: 0.0% of statements
ok      github.com/chanzuckerberg/terraform-provider-snowflake/pkg/datasources  0.033s  coverage: 0.0% of statements [no tests to run]
?       github.com/chanzuckerberg/terraform-provider-snowflake/pkg/db   [no test files]
testing: warning: no tests to run
PASS
coverage: 0.0% of statements
ok      github.com/chanzuckerberg/terraform-provider-snowflake/pkg/provider     0.059s  coverage: 0.0% of statements [no tests to run]
=== RUN   TestAccUserOwnershipGrant_defaults
=== PAUSE TestAccUserOwnershipGrant_defaults
=== CONT  TestAccUserOwnershipGrant_defaults
--- PASS: TestAccUserOwnershipGrant_defaults (10.58s)
PASS
coverage: 4.2% of statements
ok      github.com/chanzuckerberg/terraform-provider-snowflake/pkg/resources    10.636s coverage: 4.2% of statements
testing: warning: no tests to run
PASS
coverage: 0.0% of statements
ok      github.com/chanzuckerberg/terraform-provider-snowflake/pkg/snowflake    0.016s  coverage: 0.0% of statements [no tests to run]
?       github.com/chanzuckerberg/terraform-provider-snowflake/pkg/testhelpers  [no test files]
testing: warning: no tests to run
PASS
coverage: 0.0% of statements
ok      github.com/chanzuckerberg/terraform-provider-snowflake/pkg/validation   0.013s  coverage: 0.0% of statements [no tests to run]
?       github.com/chanzuckerberg/terraform-provider-snowflake/pkg/version      [no test files]
```

## References
<!-- issues documentation links, etc  -->

* #956

aidanmelen: @alldoami this should be ready for review. tests pass and generated docs.
alldoami: /ok-to-test sha=fb8f4a4
github-actions[bot]: Integration tests success for [fb8f4a4](https://github.com/chanzuckerberg/terraform-provider-snowflake/actions/runs/2150795605)

Patch:
diff --git a/docs/resources/user_ownership_grant.md b/docs/resources/user_ownership_grant.md
new file mode 100644
index 00000000..0c060508
--- /dev/null
+++ b/docs/resources/user_ownership_grant.md
@@ -0,0 +1,28 @@
+---
+# generated by https://github.com/hashicorp/terraform-plugin-docs
+page_title: "snowflake_user_ownership_grant Resource - terraform-provider-snowflake"
+subcategory: ""
+description: |-
+  
+---
+
+# snowflake_user_ownership_grant (Resource)
+
+
+
+
+
+<!-- schema generated by tfplugindocs -->
+## Schema
+
+### Required
+
+- **on_user_name** (String) The name of the user ownership is granted on.
+- **to_role_name** (String) The name of the role to grant ownership. Please ensure that the role that terraform is using is granted access.
+
+### Optional
+
+- **current_grants** (String) Specifies whether to remove or transfer all existing outbound privileges on the object when ownership is transferred to a new role.
+- **id** (String) The ID of this resource.
+
+
diff --git a/go.mod b/go.mod
index f4b17644..80d0316b 100644
--- a/go.mod
+++ b/go.mod
@@ -16,7 +16,7 @@ require (
 	github.com/stretchr/testify v1.7.0
 	github.com/youmark/pkcs8 v0.0.0-20201027041543-1326539a0a0a
 	golang.org/x/crypto v0.0.0-20220307211146-efcb8507fb70
-	golang.org/x/tools v0.1.9
+	golang.org/x/tools v0.1.10
 )
 
 require (
@@ -111,7 +111,7 @@ require (
 	github.com/vmihailenco/tagparser v0.1.2 // indirect
 	github.com/zclconf/go-cty v1.10.0 // indirect
 	go.opencensus.io v0.23.0 // indirect
-	golang.org/x/mod v0.5.1 // indirect
+	golang.org/x/mod v0.6.0-dev.0.20220106191415-9b9b3d81d5e3 // indirect
 	golang.org/x/net v0.0.0-20220225172249-27dd8689420f // indirect
 	golang.org/x/oauth2 v0.0.0-20220223155221-ee480838109b // indirect
 	golang.org/x/sys v0.0.0-20220307203707-22a9840ba4d7 // indirect
diff --git a/go.sum b/go.sum
index 9118cb97..1392bd93 100644
--- a/go.sum
+++ b/go.sum
@@ -642,8 +642,8 @@ golang.org/x/mod v0.3.0/go.mod h1:s0Qsj1ACt9ePp/hMypM3fl4fZqREWJwdYDEqhRiZZUA=
 golang.org/x/mod v0.4.0/go.mod h1:s0Qsj1ACt9ePp/hMypM3fl4fZqREWJwdYDEqhRiZZUA=
 golang.org/x/mod v0.4.1/go.mod h1:s0Qsj1ACt9ePp/hMypM3fl4fZqREWJwdYDEqhRiZZUA=
 golang.org/x/mod v0.4.2/go.mod h1:s0Qsj1ACt9ePp/hMypM3fl4fZqREWJwdYDEqhRiZZUA=
-golang.org/x/mod v0.5.1 h1:OJxoQ/rynoF0dcCdI7cLPktw/hR2cueqYfjm43oqK38=
-golang.org/x/mod v0.5.1/go.mod h1:5OXOZSfqPIIbmVBIIKWRFfZjPR0E5r58TLhUjH0a2Ro=
+golang.org/x/mod v0.6.0-dev.0.20220106191415-9b9b3d81d5e3 h1:kQgndtyPBW/JIYERgdxfwMYh3AVStj88WQTlNDi2a+o=
+golang.org/x/mod v0.6.0-dev.0.20220106191415-9b9b3d81d5e3/go.mod h1:3p9vT2HGsQu2K1YbXdKPJLVgG5VJdoTa1poYQBtP1AY=
 golang.org/x/net v0.0.0-20180530234432-1e491301e022/go.mod h1:mL1N/T3taQHkDXs73rZJwtUhF3w3ftmwwsq0BUmARs4=
 golang.org/x/net v0.0.0-20180724234803-3673e40ba225/go.mod h1:mL1N/T3taQHkDXs73rZJwtUhF3w3ftmwwsq0BUmARs4=
 golang.org/x/net v0.0.0-20180811021610-c39426892332/go.mod h1:mL1N/T3taQHkDXs73rZJwtUhF3w3ftmwwsq0BUmARs4=
@@ -861,8 +861,8 @@ golang.org/x/tools v0.1.2/go.mod h1:o0xws9oXOQQZyjljx8fwUC0k7L1pTE6eaCbjGeHmOkk=
 golang.org/x/tools v0.1.3/go.mod h1:o0xws9oXOQQZyjljx8fwUC0k7L1pTE6eaCbjGeHmOkk=
 golang.org/x/tools v0.1.4/go.mod h1:o0xws9oXOQQZyjljx8fwUC0k7L1pTE6eaCbjGeHmOkk=
 golang.org/x/tools v0.1.5/go.mod h1:o0xws9oXOQQZyjljx8fwUC0k7L1pTE6eaCbjGeHmOkk=
-golang.org/x/tools v0.1.9 h1:j9KsMiaP1c3B0OTQGth0/k+miLGTgLsAFUCrF2vLcF8=
-golang.org/x/tools v0.1.9/go.mod h1:nABZi5QlRsZVlzPpHl034qft6wpY4eDcsTt5AaioBiU=
+golang.org/x/tools v0.1.10 h1:QjFRCZxdOhBJ/UNgnBZLbNV13DlbnK0quyivTnXJM20=
+golang.org/x/tools v0.1.10/go.mod h1:Uh6Zz+xoGYZom868N8YTex3t7RhtHDBrE8Gzo9bV56E=
 golang.org/x/xerrors v0.0.0-20190717185122-a985d3407aa7/go.mod h1:I/5z698sn9Ka8TeJc9MKroUUfqBBauWjQqLJ2OPfmY0=
 golang.org/x/xerrors v0.0.0-20191011141410-1b5146add898/go.mod h1:I/5z698sn9Ka8TeJc9MKroUUfqBBauWjQqLJ2OPfmY0=
 golang.org/x/xerrors v0.0.0-20191204190536-9bdfabe68543/go.mod h1:I/5z698sn9Ka8TeJc9MKroUUfqBBauWjQqLJ2OPfmY0=
diff --git a/pkg/provider/provider.go b/pkg/provider/provider.go
index f50d868b..74bd9b92 100644
--- a/pkg/provider/provider.go
+++ b/pkg/provider/provider.go
@@ -220,6 +220,7 @@ func getResources() map[string]*schema.Resource {
 		"snowflake_tag":                        resources.Tag(),
 		"snowflake_task":                       resources.Task(),
 		"snowflake_user":                       resources.User(),
+		"snowflake_user_ownership_grant":       resources.UserOwnershipGrant(),
 		"snowflake_user_public_keys":           resources.UserPublicKeys(),
 		"snowflake_view":                       resources.View(),
 		"snowflake_warehouse":                  resources.Warehouse(),
diff --git a/pkg/resources/grant_helpers.go b/pkg/resources/grant_helpers.go
index c08b94f0..a600a536 100644
--- a/pkg/resources/grant_helpers.go
+++ b/pkg/resources/grant_helpers.go
@@ -137,7 +137,7 @@ func grantIDFromString(stringID string) (*grantID, error) {
 	} else if len(lines[0]) == 5 && lines[0][4] == "true" {
 		grantOption = true
 	}
-    
+
 	schemaName := ""
 	objectName := ""
 	privilege := ""
diff --git a/pkg/resources/role_ownership_grants_test.go b/pkg/resources/role_ownership_grant_test.go
similarity index 100%
rename from pkg/resources/role_ownership_grants_test.go
rename to pkg/resources/role_ownership_grant_test.go
diff --git a/pkg/resources/user_ownership_grant.go b/pkg/resources/user_ownership_grant.go
new file mode 100644
index 00000000..3dc6b400
--- /dev/null
+++ b/pkg/resources/user_ownership_grant.go
@@ -0,0 +1,152 @@
+package resources
+
+import (
+	"database/sql"
+	"fmt"
+	"log"
+	"strings"
+
+	"github.com/chanzuckerberg/terraform-provider-snowflake/pkg/snowflake"
+	"github.com/hashicorp/terraform-plugin-sdk/v2/helper/schema"
+	"github.com/hashicorp/terraform-plugin-sdk/v2/helper/validation"
+)
+
+var userOwnershipGrantSchema = map[string]*schema.Schema{
+	"on_user_name": {
+		Type:        schema.TypeString,
+		Elem:        &schema.Schema{Type: schema.TypeString},
+		Required:    true,
+		Description: "The name of the user ownership is granted on.",
+		ValidateFunc: func(val interface{}, key string) ([]string, []error) {
+			return snowflake.ValidateIdentifier(val)
+		},
+	},
+	"to_role_name": {
+		Type:        schema.TypeString,
+		Elem:        &schema.Schema{Type: schema.TypeString},
+		Required:    true,
+		Description: "The name of the role to grant ownership. Please ensure that the role that terraform is using is granted access.",
+		ValidateFunc: func(val interface{}, key string) ([]string, []error) {
+			return snowflake.ValidateIdentifier(val)
+		},
+	},
+	"current_grants": {
+		Type:        schema.TypeString,
+		Elem:        &schema.Schema{Type: schema.TypeString},
+		Optional:    true,
+		Description: "Specifies whether to remove or transfer all existing outbound privileges on the object when ownership is transferred to a new role.",
+		Default:     "COPY",
+		ValidateFunc: validation.StringInSlice([]string{
+			"COPY",
+			"REVOKE",
+		}, true),
+	},
+}
+
+func UserOwnershipGrant() *schema.Resource {
+	return &schema.Resource{
+		Create: CreateUserOwnershipGrant,
+		Read:   ReadUserOwnershipGrant,
+		Delete: DeleteUserOwnershipGrant,
+		Update: UpdateUserOwnershipGrant,
+		Schema: userOwnershipGrantSchema,
+		Importer: &schema.ResourceImporter{
+			StateContext: schema.ImportStatePassthroughContext,
+		},
+	}
+}
+
+func CreateUserOwnershipGrant(d *schema.ResourceData, meta interface{}) error {
+	db := meta.(*sql.DB)
+	user := d.Get("on_user_name").(string)
+	role := d.Get("to_role_name").(string)
+	currentGrants := d.Get("current_grants").(string)
+
+	g := snowflake.UserOwnershipGrant(user, currentGrants)
+	err := snowflake.Exec(db, g.Role(role).Grant())
+	if err != nil {
+		return err
+	}
+
+	d.SetId(fmt.Sprintf(`%s|%s|%s`, user, role, currentGrants))
+
+	return ReadUserOwnershipGrant(d, meta)
+}
+
+func ReadUserOwnershipGrant(d *schema.ResourceData, meta interface{}) error {
+	db := meta.(*sql.DB)
+	log.Println(d.Id())
+	user := strings.Split(d.Id(), "|")[0]
+	currentGrants := strings.Split(d.Id(), "|")[2]
+
+	stmt := fmt.Sprintf("SHOW USERS LIKE '%s'", user)
+	row := snowflake.QueryRow(db, stmt)
+
+	grant, err := snowflake.ScanUserOwnershipGrant(row)
+	if err == sql.ErrNoRows {
+		// If not found, mark resource to be removed from statefile during apply or refresh
+		log.Printf("[DEBUG] user (%s) not found", d.Id())
+		d.SetId("")
+		return nil
+	}
+	if err != nil {
+		return err
+	}
+
+	if user != grant.Name.String {
+		return fmt.Errorf("no user found like '%s'", user)
+	}
+
+	grant.Name.String = strings.TrimPrefix(grant.Name.String, `"`)
+	grant.Name.String = strings.TrimSuffix(grant.Name.String, `"`)
+	err = d.Set("on_user_name", grant.Name.String)
+	if err != nil {
+		return err
+	}
+
+	grant.Owner.String = strings.TrimPrefix(grant.Owner.String, `"`)
+	grant.Owner.String = strings.TrimSuffix(grant.Owner.String, `"`)
+	err = d.Set("to_role_name", grant.Owner.String)
+	if err != nil {
+		return err
+	}
+
+	err = d.Set("current_grants", currentGrants)
+	if err != nil {
+		return err
+	}
+
+	return nil
+}
+
+func UpdateUserOwnershipGrant(d *schema.ResourceData, meta interface{}) error {
+	db := meta.(*sql.DB)
+	user := d.Get("on_user_name").(string)
+	role := d.Get("to_role_name").(string)
+	currentGrants := d.Get("current_grants").(string)
+
+	d.SetId(fmt.Sprintf(`%s|%s|%s`, user, role, currentGrants))
+
+	g := snowflake.UserOwnershipGrant(user, currentGrants)
+	err := snowflake.Exec(db, g.Role(role).Grant())
+	if err != nil {
+		return err
+	}
+
+	return ReadUserOwnershipGrant(d, meta)
+}
+
+func DeleteUserOwnershipGrant(d *schema.ResourceData, meta interface{}) error {
+	db := meta.(*sql.DB)
+	user := d.Get("on_user_name").(string)
+	currentGrants := d.Get("current_grants").(string)
+
+	g := snowflake.UserOwnershipGrant(user, currentGrants)
+	err := snowflake.Exec(db, g.Role("ACCOUNTADMIN").Revoke())
+	if err != nil {
+		return err
+	}
+
+	d.SetId("")
+	return nil
+}
diff --git a/pkg/snowflake/user_ownership_grant.go b/pkg/snowflake/user_ownership_grant.go
new file mode 100644
index 00000000..3edb2b54
--- /dev/null
+++ b/pkg/snowflake/user_ownership_grant.go
@@ -0,0 +1,76 @@
+package snowflake
+
+import (
+	"database/sql"
+	"fmt"
+
+	"github.com/jmoiron/sqlx"
+)
+
+type UserOwnershipGrantBuilder struct {
+	user          string
+	currentGrants string
+}
+
+type UserOwnershipGrantExecutable struct {
+	role          string
+	granteeType   granteeType
+	grantee       string
+	currentGrants string
+}
+
+func UserOwnershipGrant(user string, currentGrants string) *UserOwnershipGrantBuilder {
+	return &UserOwnershipGrantBuilder{user: user, currentGrants: currentGrants}
+}
+
+func (gb *UserOwnershipGrantBuilder) Role(role string) *UserOwnershipGrantExecutable {
+	return &UserOwnershipGrantExecutable{
+		role:          role,
+		granteeType:   "USER",
+		grantee:       gb.user,
+		currentGrants: gb.currentGrants,
+	}
+}
+
+func (gr *UserOwnershipGrantExecutable) Grant() string {
+	return fmt.Sprintf(`GRANT OWNERSHIP ON %s "%s" TO ROLE "%s" %s CURRENT GRANTS`, gr.granteeType, gr.grantee, gr.role, gr.currentGrants) // nolint: gosec
+}
+
+func (gr *UserOwnershipGrantExecutable) Revoke() string {
+	return fmt.Sprintf(`GRANT OWNERSHIP ON %s "%s" TO ROLE "%s" %s CURRENT GRANTS`, gr.granteeType, gr.grantee, gr.role, gr.currentGrants) // nolint: gosec
+}
+
+type userOwnershipGrant struct {
+	Name                  sql.NullString `db:"name"`
+	CreatedOn             sql.NullString `db:"created_on"`
+	LoginName             sql.NullString `db:"login_name"`
+	DisplayName           sql.NullString `db:"display_name"`
+	FirstName             sql.NullString `db:"first_name"`
+	LastName              sql.NullString `db:"last_name"`
+	Email                 sql.NullString `db:"email"`
+	MinsToUnlock          sql.NullString `db:"mins_to_unlock"`
+	DaysToExpiry          sql.NullString `db:"days_to_expiry"`
+	Comment               sql.NullString `db:"comment"`
+	Disabled              sql.NullString `db:"disabled"`
+	MustChangePassword    sql.NullString `db:"must_change_password"`
+	SnowflakeLock         sql.NullString `db:"snowflake_lock"`
+	DefaultWarehouse      sql.NullString `db:"default_warehouse"`
+	DefaultNamespace      sql.NullString `db:"default_namespace"`
+	DefaultRole           sql.NullString `db:"default_role"`
+	DefaultSecondaryRoles sql.NullString `db:"default_secondary_roles"`
+	ExtAuthnDuo           sql.NullString `db:"ext_authn_duo"`
+	ExtAuthnUid           sql.NullString `db:"ext_authn_uid"`
+	MinsToBypass_mfa      sql.NullString `db:"mins_to_bypass_mfa"`
+	Owner                 sql.NullString `db:"owner"`
+	LastSuccessLogin      sql.NullString `db:"last_success_login"`
+	ExpiresAtTime         sql.NullString `db:"expires_at_time"`
+	LockedUntilTime       sql.NullString `db:"locked_until_time"`
+	HasPassword           sql.NullString `db:"has_password"`
+	HasRsaPublicKey       sql.NullString `db:"has_rsa_public_key"`
+}
+
+func ScanUserOwnershipGrant(row *sqlx.Row) (*userOwnershipGrant, error) {
+	uog := &userOwnershipGrant{}
+	err := row.StructScan(uog)
+	return uog, err
+}


Test patch:
diff --git a/pkg/resources/grant_helpers_internal_test.go b/pkg/resources/grant_helpers_internal_test.go
index 53f4d0a9..4209f295 100644
--- a/pkg/resources/grant_helpers_internal_test.go
+++ b/pkg/resources/grant_helpers_internal_test.go
@@ -46,7 +46,6 @@ func TestGrantIDFromString(t *testing.T) {
 	_, err = grantIDFromString(id)
 	r.Equal(fmt.Errorf("1 to 6 fields allowed in ID"), err)
 
-
 	// 0 lines
 	id = ""
 	_, err = grantIDFromString(id)
@@ -169,4 +168,3 @@ func TestGrantIDFromStringRoleGrant(t *testing.T) {
 	r.Equal([]string{"role3", "role4"}, grant.Roles)
 	r.Equal(false, grant.GrantOption)
 }
-
diff --git a/pkg/resources/helpers_test.go b/pkg/resources/helpers_test.go
index 515044ff..a2ff4728 100644
--- a/pkg/resources/helpers_test.go
+++ b/pkg/resources/helpers_test.go
@@ -192,6 +192,14 @@ func roleGrants(t *testing.T, id string, params map[string]interface{}) *schema.
 	return d
 }
 
+func userOwnershipGrant(t *testing.T, id string, params map[string]interface{}) *schema.ResourceData {
+	r := require.New(t)
+	d := schema.TestResourceDataRaw(t, resources.UserOwnershipGrant().Schema, params)
+	r.NotNil(d)
+	d.SetId(id)
+	return d
+}
+
 func roleOwnershipGrant(t *testing.T, id string, params map[string]interface{}) *schema.ResourceData {
 	r := require.New(t)
 	d := schema.TestResourceDataRaw(t, resources.RoleOwnershipGrant().Schema, params)
diff --git a/pkg/resources/user_ownership_grant_acceptance_test.go b/pkg/resources/user_ownership_grant_acceptance_test.go
new file mode 100644
index 00000000..315129d5
--- /dev/null
+++ b/pkg/resources/user_ownership_grant_acceptance_test.go
@@ -0,0 +1,57 @@
+package resources_test
+
+import (
+	"fmt"
+	"testing"
+
+	"github.com/hashicorp/terraform-plugin-sdk/v2/helper/acctest"
+	"github.com/hashicorp/terraform-plugin-sdk/v2/helper/resource"
+)
+
+func TestAccUserOwnershipGrant_defaults(t *testing.T) {
+	user := acctest.RandStringFromCharSet(10, acctest.CharSetAlpha)
+	role := acctest.RandStringFromCharSet(10, acctest.CharSetAlpha)
+
+	resource.ParallelTest(t, resource.TestCase{
+		Providers: providers(),
+		Steps: []resource.TestStep{
+			{
+				Config: userOwnershipGrantConfig(user, role),
+				Check: resource.ComposeTestCheckFunc(
+					resource.TestCheckResourceAttr("snowflake_user_ownership_grant.grant", "on_user_name", user),
+					resource.TestCheckResourceAttr("snowflake_user_ownership_grant.grant", "to_role_name", role),
+					resource.TestCheckResourceAttr("snowflake_user_ownership_grant.grant", "current_grants", "COPY"),
+				),
+			},
+		},
+	})
+}
+
+func userOwnershipGrantConfig(user, role string) string {
+	return fmt.Sprintf(`
+
+resource "snowflake_user" "user" {
+	name = "%v"
+}
+
+resource "snowflake_role" "role" {
+	name = "%v"
+}
+
+resource "snowflake_role_grants" "grants" {
+	role_name = snowflake_role.role.name
+
+	roles = [
+		"ACCOUNTADMIN",
+	]
+}
+
+resource "snowflake_user_ownership_grant" "grant" {
+	on_user_name = snowflake_user.user.name
+
+	to_role_name = snowflake_role.role.name
+
+	current_grants = "COPY"
+}
+`, user, role)
+}
diff --git a/pkg/resources/user_ownership_grant_test.go b/pkg/resources/user_ownership_grant_test.go
new file mode 100644
index 00000000..4f86cdbe
--- /dev/null
+++ b/pkg/resources/user_ownership_grant_test.go
@@ -0,0 +1,100 @@
+package resources_test
+
+import (
+	"database/sql"
+	"testing"
+
+	sqlmock "github.com/DATA-DOG/go-sqlmock"
+	"github.com/chanzuckerberg/terraform-provider-snowflake/pkg/provider"
+	"github.com/chanzuckerberg/terraform-provider-snowflake/pkg/resources"
+	. "github.com/chanzuckerberg/terraform-provider-snowflake/pkg/testhelpers"
+	"github.com/stretchr/testify/require"
+)
+
+func TestUserOwnershipGrant(t *testing.T) {
+	r := require.New(t)
+	err := resources.UserOwnershipGrant().InternalValidate(provider.Provider().Schema, true)
+	r.NoError(err)
+}
+
+func TestUserOwnershipGrantCreate(t *testing.T) {
+	r := require.New(t)
+
+	d := userOwnershipGrant(t, "user1", map[string]interface{}{
+		"on_user_name":   "user1",
+		"to_role_name":   "role1",
+		"current_grants": "COPY",
+	})
+
+	WithMockDb(t, func(db *sql.DB, mock sqlmock.Sqlmock) {
+		mock.ExpectExec(`GRANT OWNERSHIP ON USER "user1" TO ROLE "role1" COPY CURRENT GRANTS`).WillReturnResult(sqlmock.NewResult(1, 1))
+		expectReadUserOwnershipGrant(mock)
+		err := resources.CreateUserOwnershipGrant(d, db)
+		r.NoError(err)
+	})
+}
+
+func TestUserOwnershipGrantRead(t *testing.T) {
+	r := require.New(t)
+
+	d := userOwnershipGrant(t, "user1|role1|COPY", map[string]interface{}{
+		"on_user_name":   "user1",
+		"to_role_name":   "role1",
+		"current_grants": "COPY",
+	})
+
+	WithMockDb(t, func(db *sql.DB, mock sqlmock.Sqlmock) {
+		expectReadUserOwnershipGrant(mock)
+		err := resources.ReadUserOwnershipGrant(d, db)
+		r.NoError(err)
+	})
+}
+
+func expectReadUserOwnershipGrant(mock sqlmock.Sqlmock) {
+	rows := sqlmock.NewRows([]string{
+		"name",
+		"created_on",
+		"login_name",
+		"display_name",
+		"first_name",
+		"last_name",
+		"email",
+		"mins_to_unlock",
+		"days_to_expiry",
+		"comment",
+		"disabled",
+		"must_change_password",
+		"snowflake_lock",
+		"default_warehouse",
+		"default_namespace",
+		"default_role",
+		"default_secondary_roles",
+		"ext_authn_duo",
+		"ext_authn_uid",
+		"mins_to_bypass_mfa",
+		"owner",
+		"last_success_login",
+		"expires_at_time",
+		"locked_until_time",
+		"has_password",
+		"has_rsa_public_key",
+	}).AddRow("user1", "_", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "role1", "", "", "", "", "")
+	mock.ExpectQuery(`SHOW USERS LIKE 'user1'`).WillReturnRows(rows)
+}
+
+func TestUserOwnershipGrantDelete(t *testing.T) {
+	r := require.New(t)
+
+	d := userOwnershipGrant(t, "user1|role1|COPY", map[string]interface{}{
+		"on_user_name":   "user1",
+		"to_role_name":   "role1",
+		"current_grants": "COPY",
+	})
+
+	WithMockDb(t, func(db *sql.DB, mock sqlmock.Sqlmock) {
+
+		mock.ExpectExec(`GRANT OWNERSHIP ON USER "user1" TO ROLE "ACCOUNTADMIN" COPY CURRENT GRANTS`).WillReturnResult(sqlmock.NewResult(1, 1))
+		err := resources.DeleteUserOwnershipGrant(d, db)
+		r.NoError(err)
+	})
+}
diff --git a/pkg/snowflake/user_ownership_grant_test.go b/pkg/snowflake/user_ownership_grant_test.go
new file mode 100644
index 00000000..14a696b4
--- /dev/null
+++ b/pkg/snowflake/user_ownership_grant_test.go
@@ -0,0 +1,26 @@
+package snowflake_test
+
+import (
+	"testing"
+
+	"github.com/chanzuckerberg/terraform-provider-snowflake/pkg/snowflake"
+	"github.com/stretchr/testify/require"
+)
+
+func TestUserOwnershipGrantQuery(t *testing.T) {
+	r := require.New(t)
+	copy := snowflake.UserOwnershipGrant("user1", "COPY")
+	revoke := snowflake.UserOwnershipGrant("user1", "REVOKE")
+
+	g1 := copy.Role("role1").Grant()
+	r.Equal(`GRANT OWNERSHIP ON USER "user1" TO ROLE "role1" COPY CURRENT GRANTS`, g1)
+
+	r1 := copy.Role("ACCOUNTADMIN").Revoke()
+	r.Equal(`GRANT OWNERSHIP ON USER "user1" TO ROLE "ACCOUNTADMIN" COPY CURRENT GRANTS`, r1)
+
+	g2 := revoke.Role("role1").Grant()
+	r.Equal(`GRANT OWNERSHIP ON USER "user1" TO ROLE "role1" REVOKE CURRENT GRANTS`, g2)
+
+	r2 := revoke.Role("ACCOUNTADMIN").Revoke()
+	r.Equal(`GRANT OWNERSHIP ON USER "user1" TO ROLE "ACCOUNTADMIN" REVOKE CURRENT GRANTS`, r2)
+}