Repository: chanzuckerberg/terraform-provider-snowflake
Pull request #387 (2020-12-30 20:43:20)

Add masking policy 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
<!-- add more below if you think they are relevant -->
* [x] unit tests

## References
<!-- issues documentation links, etc  -->

* 

alldoami: /ok-to-test sha=c527a98
alldoami: Looks like the acceptance test failed: https://github.com/chanzuckerberg/terraform-provider-snowflake/runs/1665872992?check_suite_focus=true
alldoami: /ok-to-test sha=8e4eb2f
alldoami: /ok-to-test sha=77136e4
github-actions[bot]: Integration tests ran for [77136e4](https://github.com/chanzuckerberg/terraform-provider-snowflake/actions/runs/470641768)
ajbosco: > Looks like the acceptance tests are failing

I think they should be passing now.
alldoami: /ok-to-test sha=267628f
ajbosco: Looks like the id generation changes reverted so some changes need to happen here unless it is going to be merged again. https://github.com/chanzuckerberg/terraform-provider-snowflake/pull/407
alldoami: > Looks like the id generation changes reverted so some changes need to happen here unless it is going to be merged again. #407

do you want to create another PR to add what you need back? 🙏 
alldoami: /ok-to-test sha=66869a3

Patch:
diff --git a/docs/resources/masking_policy.md b/docs/resources/masking_policy.md
new file mode 100644
index 00000000..ff0053c1
--- /dev/null
+++ b/docs/resources/masking_policy.md
@@ -0,0 +1,48 @@
+---
+page_title: "snowflake_masking_policy Resource - terraform-provider-snowflake"
+subcategory: ""
+description: |-
+  
+---
+
+# Resource `snowflake_masking_policy`
+
+
+
+## Example Usage
+
+```terraform
+resource "snowflake_masking_policy" "example_masking_policy" {
+  name               = "EXAMPLE_MASKING_POLICY"
+  database           = "EXAMPLE_DB"
+  schema             = "EXAMPLE_SCHEMA"
+  value_data_type    = "string"
+  masking_expression = "case when current_role() in ('ANALYST') then val else sha2(val, 512) end"
+  return_data_type   = "string"
+}
+```
+
+## Schema
+
+### Required
+
+- **database** (String, Required) The database in which to create the masking policy.
+- **masking_expression** (String, Required) Specifies the SQL expression that transforms the data.
+- **name** (String, Required) Specifies the identifier for the masking policy; must be unique for the database and schema in which the masking policy is created.
+- **return_data_type** (String, Required) Specifies the data type to return.
+- **schema** (String, Required) The schema in which to create the masking policy.
+- **value_data_type** (String, Required) Specifies the data type to mask.
+
+### Optional
+
+- **comment** (String, Optional) Specifies a comment for the masking policy.
+- **id** (String, Optional) The ID of this resource.
+
+## Import
+
+Import is supported using the following syntax:
+
+```shell
+# format is database name | schema name | policy name
+terraform import snowflake_masking_policy.example 'dbName|schemaName|policyName'
+```
diff --git a/examples/resources/snowflake_masking_policy/import.sh b/examples/resources/snowflake_masking_policy/import.sh
new file mode 100644
index 00000000..14b6d925
--- /dev/null
+++ b/examples/resources/snowflake_masking_policy/import.sh
@@ -0,0 +1,2 @@
+# format is database name | schema name | policy name
+terraform import snowflake_masking_policy.example 'dbName|schemaName|policyName'
diff --git a/examples/resources/snowflake_masking_policy/resource.tf b/examples/resources/snowflake_masking_policy/resource.tf
new file mode 100644
index 00000000..e8630ef1
--- /dev/null
+++ b/examples/resources/snowflake_masking_policy/resource.tf
@@ -0,0 +1,8 @@
+resource "snowflake_masking_policy" "example_masking_policy" {
+  name               = "EXAMPLE_MASKING_POLICY"
+  database           = "EXAMPLE_DB"
+  schema             = "EXAMPLE_SCHEMA"
+  value_data_type    = "string"
+  masking_expression = "case when current_role() in ('ANALYST') then val else sha2(val, 512) end"
+  return_data_type   = "string"
+}
diff --git a/pkg/provider/provider.go b/pkg/provider/provider.go
index 90d1ef35..541b2ef9 100644
--- a/pkg/provider/provider.go
+++ b/pkg/provider/provider.go
@@ -101,6 +101,7 @@ func getResources() map[string]*schema.Resource {
 	others := map[string]*schema.Resource{
 		"snowflake_database":                  resources.Database(),
 		"snowflake_managed_account":           resources.ManagedAccount(),
+		"snowflake_masking_policy":            resources.MaskingPolicy(),
 		"snowflake_network_policy_attachment": resources.NetworkPolicyAttachment(),
 		"snowflake_network_policy":            resources.NetworkPolicy(),
 		"snowflake_pipe":                      resources.Pipe(),
diff --git a/pkg/resources/masking_policy.go b/pkg/resources/masking_policy.go
new file mode 100644
index 00000000..cd08798c
--- /dev/null
+++ b/pkg/resources/masking_policy.go
@@ -0,0 +1,313 @@
+package resources
+
+import (
+	"bytes"
+	"database/sql"
+	"encoding/csv"
+	"fmt"
+	"strings"
+
+	"github.com/chanzuckerberg/terraform-provider-snowflake/pkg/snowflake"
+	"github.com/hashicorp/terraform-plugin-sdk/v2/helper/schema"
+	"github.com/pkg/errors"
+)
+
+const (
+	maskingPolicyIDDelimiter = '|'
+)
+
+var maskingPolicySchema = map[string]*schema.Schema{
+	"name": {
+		Type:        schema.TypeString,
+		Required:    true,
+		Description: "Specifies the identifier for the masking policy; must be unique for the database and schema in which the masking policy is created.",
+		ForceNew:    true,
+	},
+	"database": {
+		Type:        schema.TypeString,
+		Required:    true,
+		Description: "The database in which to create the masking policy.",
+		ForceNew:    true,
+	},
+	"schema": {
+		Type:        schema.TypeString,
+		Required:    true,
+		Description: "The schema in which to create the masking policy.",
+		ForceNew:    true,
+	},
+	"value_data_type": {
+		Type:        schema.TypeString,
+		Required:    true,
+		Description: "Specifies the data type to mask.",
+		ForceNew:    true,
+	},
+	"masking_expression": {
+		Type:        schema.TypeString,
+		Required:    true,
+		Description: "Specifies the SQL expression that transforms the data.",
+		ForceNew:    true,
+	},
+	"return_data_type": {
+		Type:        schema.TypeString,
+		Required:    true,
+		Description: "Specifies the data type to return.",
+		ForceNew:    true,
+	},
+	"comment": {
+		Type:        schema.TypeString,
+		Optional:    true,
+		Description: "Specifies a comment for the masking policy.",
+	},
+}
+
+type maskingPolicyID struct {
+	DatabaseName      string
+	SchemaName        string
+	MaskingPolicyName string
+}
+
+// String() takes in a maskingPolicyID object and returns a pipe-delimited string:
+// DatabaseName|SchemaName|MaskingPolicyName
+func (mpi *maskingPolicyID) String() (string, error) {
+	var buf bytes.Buffer
+	csvWriter := csv.NewWriter(&buf)
+	csvWriter.Comma = maskingPolicyIDDelimiter
+	dataIdentifiers := [][]string{{mpi.DatabaseName, mpi.SchemaName, mpi.MaskingPolicyName}}
+	err := csvWriter.WriteAll(dataIdentifiers)
+	if err != nil {
+		return "", err
+	}
+	strMaskingPolicyID := strings.TrimSpace(buf.String())
+	return strMaskingPolicyID, nil
+}
+
+/// maskingPolicyIDFromString() takes in a pipe-delimited string: DatabaseName|SchemaName|MaskingPolicyName
+// and returns a maskingPolicyID object
+func maskingPolicyIDFromString(stringID string) (*maskingPolicyID, error) {
+	reader := csv.NewReader(strings.NewReader(stringID))
+	reader.Comma = maskingPolicyIDDelimiter
+	lines, err := reader.ReadAll()
+	if err != nil {
+		return nil, fmt.Errorf("Not CSV compatible")
+	}
+
+	if len(lines) != 1 {
+		return nil, fmt.Errorf("1 line per masking policy")
+	}
+	if len(lines[0]) != 3 {
+		return nil, fmt.Errorf("3 fields allowed")
+	}
+
+	maskingPolicyResult := &maskingPolicyID{
+		DatabaseName:      lines[0][0],
+		SchemaName:        lines[0][1],
+		MaskingPolicyName: lines[0][2],
+	}
+	return maskingPolicyResult, nil
+}
+
+// MaskingPolicy returns a pointer to the resource representing a masking policy
+func MaskingPolicy() *schema.Resource {
+	return &schema.Resource{
+		Create: CreateMaskingPolicy,
+		Read:   ReadMaskingPolicy,
+		Update: UpdateMaskingPolicy,
+		Delete: DeleteMaskingPolicy,
+
+		Schema: maskingPolicySchema,
+		Importer: &schema.ResourceImporter{
+			StateContext: schema.ImportStatePassthroughContext,
+		},
+	}
+}
+
+// CreateMaskingPolicy implements schema.CreateFunc
+func CreateMaskingPolicy(d *schema.ResourceData, meta interface{}) error {
+	db := meta.(*sql.DB)
+	name := d.Get("name").(string)
+	database := d.Get("database").(string)
+	schema := d.Get("schema").(string)
+	valueDataType := d.Get("value_data_type").(string)
+	maskingExpression := d.Get("masking_expression").(string)
+	returnDataType := d.Get("return_data_type").(string)
+
+	builder := snowflake.MaskingPolicy(name, database, schema)
+
+	builder.WithValueDataType(valueDataType)
+	builder.WithMaskingExpression(maskingExpression)
+	builder.WithReturnDataType(returnDataType)
+
+	// Set optionals
+	if v, ok := d.GetOk("comment"); ok {
+		builder.WithComment(v.(string))
+	}
+
+	stmt := builder.Create()
+	err := snowflake.Exec(db, stmt)
+	if err != nil {
+		return errors.Wrapf(err, "error creating masking policy %v", name)
+	}
+
+	maskingPolicyID := &maskingPolicyID{
+		DatabaseName:      database,
+		SchemaName:        schema,
+		MaskingPolicyName: name,
+	}
+	dataIDInput, err := maskingPolicyID.String()
+	if err != nil {
+		return err
+	}
+	d.SetId(dataIDInput)
+
+	return ReadMaskingPolicy(d, meta)
+}
+
+// ReadMaskingPolicy implements schema.ReadFunc
+func ReadMaskingPolicy(d *schema.ResourceData, meta interface{}) error {
+	db := meta.(*sql.DB)
+	maskingPolicyID, err := maskingPolicyIDFromString(d.Id())
+	if err != nil {
+		return err
+	}
+
+	dbName := maskingPolicyID.DatabaseName
+	schema := maskingPolicyID.SchemaName
+	policyName := maskingPolicyID.MaskingPolicyName
+
+	builder := snowflake.MaskingPolicy(policyName, dbName, schema)
+
+	showSQL := builder.Show()
+
+	row := snowflake.QueryRow(db, showSQL)
+
+	s, err := snowflake.ScanMaskingPolicies(row)
+	if err != nil {
+		return err
+	}
+
+	err = d.Set("name", s.Name.String)
+	if err != nil {
+		return err
+	}
+
+	err = d.Set("database", s.DatabaseName.String)
+	if err != nil {
+		return err
+	}
+
+	err = d.Set("schema", s.SchemaName.String)
+	if err != nil {
+		return err
+	}
+
+	err = d.Set("comment", s.Comment.String)
+	if err != nil {
+		return err
+	}
+
+	descSQL := builder.Describe()
+	rows, err := snowflake.Query(db, descSQL)
+	if err != nil {
+		return err
+	}
+
+	var (
+		name       string
+		signature  string
+		returnType string
+		body       string
+	)
+	for rows.Next() {
+		err := rows.Scan(&name, &signature, &returnType, &body)
+		if err != nil {
+			return err
+		}
+
+		err = d.Set("masking_expression", body)
+		if err != nil {
+			return err
+		}
+
+		err = d.Set("return_data_type", returnType)
+		if err != nil {
+			return err
+		}
+
+		// format in database is `(VAL <data_type>)`
+		valueDataType := strings.TrimSuffix(strings.Split(signature, " ")[1], ")")
+		err = d.Set("value_data_type", valueDataType)
+		if err != nil {
+			return err
+		}
+	}
+
+	return err
+}
+
+// UpdateMaskingPolicy implements schema.UpdateFunc
+func UpdateMaskingPolicy(d *schema.ResourceData, meta interface{}) error {
+	db := meta.(*sql.DB)
+
+	maskingPolicyID, err := maskingPolicyIDFromString(d.Id())
+	if err != nil {
+		return err
+	}
+
+	dbName := maskingPolicyID.DatabaseName
+	schema := maskingPolicyID.SchemaName
+	policyName := maskingPolicyID.MaskingPolicyName
+
+	builder := snowflake.MaskingPolicy(policyName, dbName, schema)
+
+	if d.HasChange("comment") {
+		comment := d.Get("comment")
+		if c := comment.(string); c == "" {
+			q := builder.RemoveComment()
+			err := snowflake.Exec(db, q)
+			if err != nil {
+				return errors.Wrapf(err, "error unsetting comment for masking policy on %v", d.Id())
+			}
+		} else {
+			q := builder.ChangeComment(c)
+			err := snowflake.Exec(db, q)
+			if err != nil {
+				return errors.Wrapf(err, "error updating comment for masking policy on %v", d.Id())
+			}
+		}
+	}
+
+	if d.HasChange("masking_expression") {
+		maskingExpression := d.Get("masking_expression")
+		q := builder.ChangeMaskingExpression(maskingExpression.(string))
+		err := snowflake.Exec(db, q)
+		if err != nil {
+			return errors.Wrapf(err, "error updating masking policy expression on %v", d.Id())
+		}
+	}
+
+	return ReadMaskingPolicy(d, meta)
+}
+
+// DeleteMaskingPolicy implements schema.DeleteFunc
+func DeleteMaskingPolicy(d *schema.ResourceData, meta interface{}) error {
+	db := meta.(*sql.DB)
+	maskingPolicyID, err := maskingPolicyIDFromString(d.Id())
+	if err != nil {
+		return err
+	}
+
+	dbName := maskingPolicyID.DatabaseName
+	schema := maskingPolicyID.SchemaName
+	policyName := maskingPolicyID.MaskingPolicyName
+
+	q := snowflake.MaskingPolicy(policyName, dbName, schema).Drop()
+
+	err = snowflake.Exec(db, q)
+	if err != nil {
+		return errors.Wrapf(err, "error deleting masking policy %v", d.Id())
+	}
+
+	d.SetId("")
+
+	return nil
+}
diff --git a/pkg/resources/privileges.go b/pkg/resources/privileges.go
index 99b6f6eb..15f80639 100644
--- a/pkg/resources/privileges.go
+++ b/pkg/resources/privileges.go
@@ -38,7 +38,7 @@ const (
 	privilegeCreateTemporaryTable   Privilege = "CREATE TEMPORARY TABLE"
 	privilegeCreateMaskingPolicy    Privilege = "CREATE MASKING POLICY"
 	privilegeAddSearchOptimization  Privilege = "ADD SEARCH OPTIMIZATION"
-	privilegeApplyMaskingPolicy   	Privilege = "APPLY MASKING POLICY"
+	privilegeApplyMaskingPolicy     Privilege = "APPLY MASKING POLICY"
 
 	privilegeCreateRole        Privilege = "CREATE ROLE"
 	privilegeCreateUser        Privilege = "CREATE USER"
diff --git a/pkg/snowflake/masking_policy.go b/pkg/snowflake/masking_policy.go
new file mode 100644
index 00000000..af8ef401
--- /dev/null
+++ b/pkg/snowflake/masking_policy.go
@@ -0,0 +1,148 @@
+package snowflake
+
+import (
+	"database/sql"
+	"fmt"
+	"strings"
+
+	"github.com/jmoiron/sqlx"
+)
+
+// MaskingPolicyBuilder abstracts the creation of SQL queries for a Snowflake Masking Policy
+type MaskingPolicyBuilder struct {
+	name              string
+	db                string
+	schema            string
+	comment           string
+	valueDataType     string
+	maskingExpression string
+	returnDataType    string
+}
+
+// QualifiedName prepends the db and schema if set and escapes everything nicely
+func (mpb *MaskingPolicyBuilder) QualifiedName() string {
+	var n strings.Builder
+
+	if mpb.db != "" && mpb.schema != "" {
+		n.WriteString(fmt.Sprintf(`"%v"."%v".`, mpb.db, mpb.schema))
+	}
+
+	if mpb.db != "" && mpb.schema == "" {
+		n.WriteString(fmt.Sprintf(`"%v"..`, mpb.db))
+	}
+
+	if mpb.db == "" && mpb.schema != "" {
+		n.WriteString(fmt.Sprintf(`"%v".`, mpb.schema))
+	}
+
+	n.WriteString(fmt.Sprintf(`"%v"`, mpb.name))
+
+	return n.String()
+}
+
+// WithComment adds a comment to the MaskingPolicyBuilder
+func (mpb *MaskingPolicyBuilder) WithComment(c string) *MaskingPolicyBuilder {
+	mpb.comment = EscapeString(c)
+	return mpb
+}
+
+// WithValueDataType adds valueDataType to the MaskingPolicyBuilder
+func (mpb *MaskingPolicyBuilder) WithValueDataType(dataType string) *MaskingPolicyBuilder {
+	mpb.valueDataType = dataType
+	return mpb
+}
+
+// WithMaskingExpression adds maskingExpression to the MaskingPolicyBuilder
+func (mpb *MaskingPolicyBuilder) WithMaskingExpression(maskingExpression string) *MaskingPolicyBuilder {
+	mpb.maskingExpression = maskingExpression
+	return mpb
+}
+
+// WithReturnDataType adds returnDataType to the MaskingPolicyBuilder
+func (mpb *MaskingPolicyBuilder) WithReturnDataType(dataType string) *MaskingPolicyBuilder {
+	mpb.returnDataType = dataType
+	return mpb
+}
+
+// MaskingPolicy returns a pointer to a Builder that abstracts the DDL operations for a masking policy.
+//
+// Supported DDL operations are:
+//   - CREATE MASKING POLICY
+//	 - ALTER MASKING POLICY
+//   - DROP MASKING POLICY
+//   - SHOW MASKING POLICIES
+//   - DESCRIBE MASKING POLICY
+//
+// [Snowflake Reference](https://docs.snowflake.com/en/user-guide/security-column-ddm.html)
+func MaskingPolicy(name, db, schema string) *MaskingPolicyBuilder {
+	return &MaskingPolicyBuilder{
+		name:   name,
+		db:     db,
+		schema: schema,
+	}
+}
+
+// Create returns the SQL query that will create a masking policy.
+func (mpb *MaskingPolicyBuilder) Create() string {
+	q := strings.Builder{}
+	q.WriteString(fmt.Sprintf(`CREATE MASKING POLICY %v AS (VAL %v) RETURNS %v -> `, mpb.QualifiedName(), mpb.valueDataType, mpb.returnDataType))
+
+	q.WriteString(mpb.maskingExpression)
+
+	if mpb.comment != "" {
+		q.WriteString(fmt.Sprintf(` COMMENT = '%v'`, EscapeString(mpb.comment)))
+	}
+
+	return q.String()
+}
+
+// Describe returns the SQL query that will describe a masking policy
+func (mpb *MaskingPolicyBuilder) Describe() string {
+	return fmt.Sprintf(`DESCRIBE MASKING POLICY %v`, mpb.QualifiedName())
+}
+
+// ChangeComment returns the SQL query that will update the comment on the masking policy.
+func (mpb *MaskingPolicyBuilder) ChangeComment(c string) string {
+	return fmt.Sprintf(`ALTER MASKING POLICY %v SET COMMENT = '%v'`, mpb.QualifiedName(), EscapeString(c))
+}
+
+// RemoveComment returns the SQL query that will remove the comment on the masking policy.
+func (mpb *MaskingPolicyBuilder) RemoveComment() string {
+	return fmt.Sprintf(`ALTER MASKING POLICY %v UNSET COMMENT`, mpb.QualifiedName())
+}
+
+// ChangeMaskingExpression returns the SQL query that will update the masking expression on the masking policy.
+func (mpb *MaskingPolicyBuilder) ChangeMaskingExpression(maskingExpression string) string {
+	q := strings.Builder{}
+	q.WriteString(fmt.Sprintf(`ALTER MASKING POLICY %v SET BODY -> `, mpb.QualifiedName()))
+
+	q.WriteString(maskingExpression)
+
+	return q.String()
+}
+
+// Drop returns the SQL query that will drop a masking policy.
+func (mpb *MaskingPolicyBuilder) Drop() string {
+	return fmt.Sprintf(`DROP MASKING POLICY %v`, mpb.QualifiedName())
+}
+
+// Show returns the SQL query that will show a masking policy.
+func (mpb *MaskingPolicyBuilder) Show() string {
+	return fmt.Sprintf(`SHOW MASKING POLICIES LIKE '%v' IN SCHEMA "%v"."%v"`, mpb.name, mpb.db, mpb.schema)
+}
+
+type MaskingPolicyStruct struct {
+	CreatedOn    sql.NullString `db:"created_on"`
+	Name         sql.NullString `db:"name"`
+	DatabaseName sql.NullString `db:"database_name"`
+	SchemaName   sql.NullString `db:"schema_name"`
+	Kind         sql.NullString `db:"kind"`
+	Owner        sql.NullString `db:"owner"`
+	Comment      sql.NullString `db:"comment"`
+}
+
+func ScanMaskingPolicies(row *sqlx.Row) (*MaskingPolicyStruct, error) {
+	m := &MaskingPolicyStruct{}
+	err := row.StructScan(m)
+	return m, err
+}


Test patch:
diff --git a/pkg/resources/account_grant_test.go b/pkg/resources/account_grant_test.go
index a70f5deb..ad3ce13f 100644
--- a/pkg/resources/account_grant_test.go
+++ b/pkg/resources/account_grant_test.go
@@ -107,7 +107,6 @@ func expectReadAccountGrant(mock sqlmock.Sqlmock) {
 	mock.ExpectQuery(`^SHOW GRANTS ON ACCOUNT$`).WillReturnRows(rows)
 }
 
-
 func TestApplyMaskingPolicy(t *testing.T) {
 	r := require.New(t)
 
diff --git a/pkg/resources/helpers_test.go b/pkg/resources/helpers_test.go
index f14fef99..b5668e5e 100644
--- a/pkg/resources/helpers_test.go
+++ b/pkg/resources/helpers_test.go
@@ -97,6 +97,14 @@ func managedAccount(t *testing.T, id string, params map[string]interface{}) *sch
 	return d
 }
 
+func maskingPolicy(t *testing.T, id string, params map[string]interface{}) *schema.ResourceData {
+	r := require.New(t)
+	d := schema.TestResourceDataRaw(t, resources.MaskingPolicy().Schema, params)
+	r.NotNil(d)
+	d.SetId(id)
+	return d
+}
+
 func networkPolicy(t *testing.T, id string, params map[string]interface{}) *schema.ResourceData {
 	r := require.New(t)
 	d := schema.TestResourceDataRaw(t, resources.NetworkPolicy().Schema, params)
diff --git a/pkg/resources/masking_policy_acceptance_test.go b/pkg/resources/masking_policy_acceptance_test.go
new file mode 100644
index 00000000..f0b7f37b
--- /dev/null
+++ b/pkg/resources/masking_policy_acceptance_test.go
@@ -0,0 +1,62 @@
+package resources_test
+
+import (
+	"fmt"
+	"os"
+	"strings"
+	"testing"
+
+	"github.com/hashicorp/terraform-plugin-sdk/v2/helper/acctest"
+	"github.com/hashicorp/terraform-plugin-sdk/v2/helper/resource"
+)
+
+func TestAcc_MaskingPolicy(t *testing.T) {
+	if _, ok := os.LookupEnv("SKIP_MASKING_POLICY_TESTS"); ok {
+		t.Skip("Skipping TestAccMaskingPolicy")
+	}
+
+	accName := strings.ToUpper(acctest.RandStringFromCharSet(10, acctest.CharSetAlpha))
+
+	resource.ParallelTest(t, resource.TestCase{
+		Providers: providers(),
+		Steps: []resource.TestStep{
+			{
+				Config: maskingPolicyConfig(accName),
+				Check: resource.ComposeTestCheckFunc(
+					resource.TestCheckResourceAttr("snowflake_masking_policy.test", "name", accName),
+					resource.TestCheckResourceAttr("snowflake_masking_policy.test", "database", accName),
+					resource.TestCheckResourceAttr("snowflake_masking_policy.test", "schema", accName),
+					resource.TestCheckResourceAttr("snowflake_masking_policy.test", "comment", "Terraform acceptance test"),
+					resource.TestCheckResourceAttr("snowflake_masking_policy.test", "value_data_type", "VARCHAR"),
+					resource.TestCheckResourceAttr("snowflake_masking_policy.test", "masking_expression", "case when current_role() in ('ANALYST') then val else sha2(val, 512) end"),
+					resource.TestCheckResourceAttr("snowflake_masking_policy.test", "return_data_type", "VARCHAR(16777216)"),
+				),
+			},
+		},
+	})
+}
+
+func maskingPolicyConfig(n string) string {
+	return fmt.Sprintf(`
+resource "snowflake_database" "test" {
+	name = "%v"
+	comment = "Terraform acceptance test"
+}
+
+resource "snowflake_schema" "test" {
+	name = "%v"
+	database = snowflake_database.test.name
+	comment = "Terraform acceptance test"
+}
+
+resource "snowflake_masking_policy" "test" {
+	name = "%v"
+	database = snowflake_database.test.name
+	schema = snowflake_schema.test.name
+	value_data_type = "VARCHAR"
+	masking_expression = "case when current_role() in ('ANALYST') then val else sha2(val, 512) end"
+	return_data_type = "VARCHAR(16777216)"
+	comment = "Terraform acceptance test"
+}
+`, n, n, n)
+}
diff --git a/pkg/resources/masking_policy_test.go b/pkg/resources/masking_policy_test.go
new file mode 100644
index 00000000..a393fa7f
--- /dev/null
+++ b/pkg/resources/masking_policy_test.go
@@ -0,0 +1,84 @@
+package resources_test
+
+import (
+	"database/sql"
+	"testing"
+	"time"
+
+	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 TestMaskingPolicy(t *testing.T) {
+	r := require.New(t)
+	err := resources.MaskingPolicy().InternalValidate(provider.Provider().Schema, true)
+	r.NoError(err)
+}
+
+func TestMaskingPolicyCreate(t *testing.T) {
+	r := require.New(t)
+
+	in := map[string]interface{}{
+		"name":               "policy_name",
+		"database":           "database_name",
+		"schema":             "schema_name",
+		"comment":            "great comment",
+		"value_data_type":    "string",
+		"masking_expression": "case when current_role() in ('ANALYST') then val else sha2(val, 512) end",
+		"return_data_type":   "string",
+	}
+
+	d := maskingPolicy(t, "database_name|schema_name|policy_name", in)
+
+	WithMockDb(t, func(db *sql.DB, mock sqlmock.Sqlmock) {
+		mock.ExpectExec(
+			`^CREATE MASKING POLICY "database_name"."schema_name"."policy_name" AS \(VAL string\) RETURNS string -> case when current_role\(\) in \('ANALYST'\) then val else sha2\(val, 512\) end COMMENT = \'great comment\'$`,
+		).WillReturnResult(sqlmock.NewResult(1, 1))
+		expectReadMaskingPolicy(mock)
+		err := resources.CreateMaskingPolicy(d, db)
+		r.NoError(err)
+		r.Equal("policy_name", d.Get("name").(string))
+	})
+}
+
+func expectReadMaskingPolicy(mock sqlmock.Sqlmock) {
+	showRows := sqlmock.NewRows([]string{
+		"created_on", "name", "database_name", "schema_name", "kind", "owner", "comment",
+	}).AddRow(
+		time.Date(2000, 1, 1, 0, 0, 0, 0, time.UTC), "policy_name", "database_name", "schema_name", "MASKING_POLICY", "test", "this is a comment",
+	)
+	mock.ExpectQuery(`^SHOW MASKING POLICIES LIKE 'policy_name' IN SCHEMA "database_name"."schema_name"$`).WillReturnRows(showRows)
+
+	descRows := sqlmock.NewRows([]string{
+		"name", "signature", "return_type", "body",
+	}).AddRow(
+		"policy_name", "(VAL VARCHAR)", "VARCHAR(16777216)", "case when current_role() in ('ANALYST') then val else sha2(val, 512) end",
+	)
+	mock.ExpectQuery(`^DESCRIBE MASKING POLICY "database_name"."schema_name"."policy_name"$`).WillReturnRows(descRows)
+}
+
+func TestMaskingPolicyDelete(t *testing.T) {
+	r := require.New(t)
+
+	in := map[string]interface{}{
+		"name":               "policy_name",
+		"database":           "database_name",
+		"schema":             "schema_name",
+		"comment":            "great comment",
+		"value_data_type":    "string",
+		"masking_expression": "case when current_role() in ('ANALYST') then val else sha2(val, 512) end",
+		"return_data_type":   "string",
+	}
+
+	d := maskingPolicy(t, "database_name|schema_name|policy_name", in)
+	r.NotNil(d)
+
+	WithMockDb(t, func(db *sql.DB, mock sqlmock.Sqlmock) {
+		mock.ExpectExec(`^DROP MASKING POLICY "database_name"."schema_name"."policy_name"$`).WillReturnResult(sqlmock.NewResult(1, 1))
+		err := resources.DeleteMaskingPolicy(d, db)
+		r.NoError(err)
+	})
+}
diff --git a/pkg/snowflake/masking_policy_test.go b/pkg/snowflake/masking_policy_test.go
new file mode 100644
index 00000000..6a89ff42
--- /dev/null
+++ b/pkg/snowflake/masking_policy_test.go
@@ -0,0 +1,80 @@
+package snowflake_test
+
+import (
+	"testing"
+
+	"github.com/chanzuckerberg/terraform-provider-snowflake/pkg/snowflake"
+	"github.com/stretchr/testify/require"
+)
+
+func TestMaskingPolicyCreate(t *testing.T) {
+	r := require.New(t)
+	m := snowflake.MaskingPolicy("test_masking_policy", "test_db", "test_schema")
+	r.NotNil(m)
+
+	m.WithValueDataType("string")
+	m.WithMaskingExpression(`case
+	when current_role() in ('ANALYST') then val
+	else '*********'
+end`)
+	m.WithReturnDataType("string")
+	m.WithComment("This is a test comment")
+
+	q := m.Create()
+	r.Equal(`CREATE MASKING POLICY "test_db"."test_schema"."test_masking_policy" AS (VAL string) RETURNS string -> case
+	when current_role() in ('ANALYST') then val
+	else '*********'
+end COMMENT = 'This is a test comment'`, q)
+}
+
+func TestMaskingPolicyDescribe(t *testing.T) {
+	r := require.New(t)
+	m := snowflake.MaskingPolicy("test_masking_policy", "test_db", "test_schema")
+	r.NotNil(m)
+
+	q := m.Describe()
+	r.Equal(`DESCRIBE MASKING POLICY "test_db"."test_schema"."test_masking_policy"`, q)
+}
+
+func TestMaskingPolicyDrop(t *testing.T) {
+	r := require.New(t)
+	m := snowflake.MaskingPolicy("test_masking_policy", "test_db", "test_schema")
+	r.NotNil(m)
+
+	q := m.Drop()
+	r.Equal(`DROP MASKING POLICY "test_db"."test_schema"."test_masking_policy"`, q)
+}
+
+func TestMaskingPolicyChangeComment(t *testing.T) {
+	r := require.New(t)
+	m := snowflake.MaskingPolicy("test_masking_policy", "test_db", "test_schema")
+	r.NotNil(m)
+
+	q := m.ChangeComment("test comment!")
+	r.Equal(`ALTER MASKING POLICY "test_db"."test_schema"."test_masking_policy" SET COMMENT = 'test comment!'`, q)
+}
+
+func TestMaskingPolicyRemoveComment(t *testing.T) {
+	r := require.New(t)
+	m := snowflake.MaskingPolicy("test_masking_policy", "test_db", "test_schema")
+	r.NotNil(m)
+
+	q := m.RemoveComment()
+	r.Equal(`ALTER MASKING POLICY "test_db"."test_schema"."test_masking_policy" UNSET COMMENT`, q)
+}
+
+func TestMaskingChangeMaskingExpression(t *testing.T) {
+	r := require.New(t)
+	m := snowflake.MaskingPolicy("test_masking_policy", "test_db", "test_schema")
+	r.NotNil(m)
+
+	q := m.ChangeMaskingExpression(`case
+	when current_role() in ('ANALYST') then val
+    else sha2(val, 512)
+end`)
+
+	r.Equal(`ALTER MASKING POLICY "test_db"."test_schema"."test_masking_policy" SET BODY -> case
+	when current_role() in ('ANALYST') then val
+    else sha2(val, 512)
+end`, q)
+}