Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/). 

# Dynamic data masking
Dynamic data masking

**Note**  
Amazon Redshift automatically masks certain system table columns when logging information about queries made to Data Catalog views to prevent exposure of sensitive metadata. For more information, see [ Secure logging](https://docs.aws.amazon.com/redshift/latest/mgmt/db-auditing-secure-logging.html) in the *Amazon Redshift Management Guide*.

Using dynamic data masking (DDM) in Amazon Redshift, you can protect sensitive data in your data warehouse. You can manipulate how Amazon Redshift shows sensitive data to the user at query time, without transforming it in the database. You control access to data through masking policies that apply custom obfuscation rules to a given user or role. In that way, you can respond to changing privacy requirements without altering underlying data or editing SQL queries.

Dynamic data masking policies hide, obfuscate, or pseudonymize data that matches a given format. When attached to a table, the masking expression is applied to one or more of its columns. You can further modify masking policies to only apply them to certain users, or to user-defined roles that you can create with [Role-based access control (RBAC)](t_Roles.md). Additionally, you can apply DDM on the cell level by using conditional columns when creating your masking policy. For more information about conditional masking, see [Conditional dynamic data masking](t_ddm-conditional.md).

You can apply multiple masking policies with varying levels of obfuscation to the same column in a table and assign them to different roles. To avoid conflicts when you have different roles with different policies applying to one column, you can set priorities for each application. In that way, you can control what data a given user or role can access. DDM policies can partially or completely redact data, or hash it by using user-defined functions written in SQL, Python, or with AWS Lambda. By masking data using hashes, you can apply joins on this data without access to potentially sensitive information.

# SQL commands for managing dynamic data masking policies
SQL commands for DDM policies

You can perform the following actions to create, attach, detach, and delete dynamic data masking policies:
+ To create a DDM policy, use the [CREATE MASKING POLICY](r_CREATE_MASKING_POLICY.md) command.

  The following is an example of creating a masking policy using a SHA-2 hash function.

  ```
  CREATE MASKING POLICY hash_credit 
  WITH (credit_card varchar(256)) 
  USING (sha2(credit_card + 'testSalt', 256));
  ```
+ To alter an existing DDM policy, use the [ALTER MASKING POLICY](r_ALTER_MASKING_POLICY.md) command.

  The following is an example of altering an existing masking policy.

  ```
  ALTER MASKING POLICY hash_credit
  USING (sha2(credit_card + 'otherTestSalt', 256));
  ```
+ To attach a DDM policy on a table to one or more users or roles, use the [ATTACH MASKING POLICY](r_ATTACH_MASKING_POLICY.md) command.

  The following is an example of attaching a masking policy to a column/role pair.

  ```
   ATTACH MASKING POLICY hash_credit 
  ON credit_cards (credit_card) 
  TO ROLE science_role 
  PRIORITY 30;
  ```

  The PRIORITY clause determines which masking policy applies to a user session when multiple policies are attached to the same column. For example, if the user in the preceding example has another masking policy attached to the same credit card column with a priority of 20, science\$1role's policy is the one that applies, as it has the higher priority of 30.
+ To detach a DDM policy on a table from one or more users or roles, use the [DETACH MASKING POLICY](r_DETACH_MASKING_POLICY.md) command.

  The following is an example of detaching a masking policy from a column/role pair.

  ```
  DETACH MASKING POLICY hash_credit 
  ON credit_cards(credit_card) 
  FROM ROLE science_role;
  ```
+ To drop a DDM policy from all databases, use the [DROP MASKING POLICY](r_DROP_MASKING_POLICY.md) command.

  The following is an example of dropping a masking policy from all databases.

  ```
  DROP MASKING POLICY hash_credit;  
  ```

# Dynamic data masking policy hierarchy
DDM policy hierarchy

When attaching multiple masking policies, consider the following:
+ You can attach multiple masking policies to a single column.
+ When multiple masking policies are applicable to a query, the highest priority policy attached to each respective column applies. Consider the following example. 

  ```
  ATTACH MASKING POLICY partial_hash
  ON credit_cards(address, credit_card)
  TO ROLE analytics_role 
  PRIORITY 20;
  
  ATTACH MASKING POLICY full_hash
  ON credit_cards(credit_card, ssn)
  TO ROLE auditor_role 
  PRIORITY 30;
  
  SELECT address, credit_card, ssn
  FROM credit_cards;
  ```

  When running the SELECT statement, a user with both the analytics and auditor roles sees the address column with the `partial_hash` masking policy applied. They see the credit card and SSN columns with the `full_hash` masking policy applied because the `full_hash` policy has the higher priority on the credit card column.
+  If you don't specify a priority when attaching a masking policy, the default priority is 0. 
+ You can't attach two policies to the same column with equal priority. 
+ You can't attach two policies to the same combination of user and column or role and column.
+ When multiple masking policies are applicable along the same SUPER path while attached to the same user or role, only the highest priority attachment takes effect. Consider the following examples. 

  The first example shows two masking policies attached on the same path, with the higher priority policy taking effect. 

  ```
  ATTACH MASKING POLICY hide_name
  ON employees(col_person.name)
  TO PUBLIC
  PRIORITY 20;
  
  ATTACH MASKING POLICY hide_last_name
  ON employees(col_person.name.last)
  TO PUBLIC
  PRIORITY 30;
  
  --Only the hide_last_name policy takes effect.
  SELECT employees.col_person.name FROM employees;
  ```

  The second example shows two masking policies attached to different paths in the same SUPER object, with no conflict between the policies. Both attachments will apply at the same time.

  ```
  ATTACH MASKING POLICY hide_first_name
  ON employees(col_person.name.first)
  TO PUBLIC
  PRIORITY 20;
  
  ATTACH MASKING POLICY hide_last_name
  ON employees(col_person.name.last)
  TO PUBLIC
  PRIORITY 20;
  
  --Both col_person.name.first and col_person.name.last are masked.
  SELECT employees.col_person.name FROM employees;
  ```

To confirm which masking policy applies to a given user and column or role and column combination, users with the [https://docs.aws.amazon.com/redshift/latest/dg/r_roles-default.html](https://docs.aws.amazon.com/redshift/latest/dg/r_roles-default.html) role can look up the column/role or column/user pair in the [SVV\$1ATTACHED\$1MASKING\$1POLICY](r_SVV_ATTACHED_MASKING_POLICY.md) system view. For more information, see [Dynamic data masking system views](r_ddm-svv.md).

# Using dynamic data masking with SUPER data type paths
Using DDM with SUPER type paths

 Amazon Redshift supports attaching dynamic data masking policies to paths of SUPER type columns. For more information about the SUPER data type, see [Semi-structured data in Amazon Redshift](super-overview.md). 

When attaching masking policies to paths of SUPER type columns, consider the following.
+ When attaching a masking policy to a path on a column, that column must be defined as the SUPER data type. You can only apply masking policies to *scalar* values on the SUPER path. You can't apply masking policies to complex structures or arrays. 
+ You can apply different masking policies to multiple scalar values on a single SUPER column, as long as the SUPER paths don't conflict. For example, the SUPER paths `a.b` and `a.b.c` conflict because they are on the same path, with `a.b` being the parent of `a.b.c`. The SUPER paths `a.b.c` and `a.b.d` don’t conflict.
+ Amazon Redshift can’t check that the paths that a masking policy attaches to exist in the data and are of the expected type until the policy is applied at user query runtime. For example, when you attach a masking policy that masks TEXT values to a SUPER path that contains an INT value, Amazon Redshift will attempt to cast the type of the value at the path.

  In such situations, the behavior of Amazon Redshift at runtime depends on your configuration settings for querying SUPER objects. By default, Amazon Redshift is in lax mode, and will resolve missing paths and invalid casts as `NULL` for the given SUPER path. For more information about SUPER-related configuration settings, see [SUPER configurations](super-configurations.md).
+ SUPER is a schemaless type, which means that Amazon Redshift can’t confirm the existence of the value at a given SUPER path. If you attach a masking policy to a SUPER path that doesn’t exist and Amazon Redshift is in lax mode, Amazon Redshift will resolve the path to a `NULL` value. We recommend that you consider the expected format of SUPER objects and the likelihood of them having unexpected attributes when attaching masking policies to paths of SUPER columns. If you think there might be an unexpected schema in your SUPER column, consider attaching your masking policies directly to the SUPER column. You can use SUPER type information functions to check attributes and types, and using `OBJECT_TRANSFORM` to mask the values. For more information about SUPER type information functions, see [SUPER type information functions](c_Type_Info_Functions.md).

## Examples


**Attaching masking policies to SUPER paths**  
The following example attaches multiple masking policies onto multiple SUPER type paths in one column.

```
CREATE TABLE employees (
    col_person SUPER
);

INSERT INTO employees
VALUES
    (
        json_parse('
            {
                "name": {
                    "first": "John",
                    "last": "Doe"
                },
                "age": 25,
                "ssn": "111-22-3333",
                "company": "Company Inc."
            }
        ')
    ),
    (
        json_parse('
            {
                "name": {
                    "first": "Jane",
                    "last": "Appleseed"
                },
                "age": 34,
                "ssn": "444-55-7777",
                "company": "Organization Org."
            }
        ')
    )
;
GRANT ALL ON ALL TABLES IN SCHEMA "public" TO PUBLIC;

-- Create the masking policies.

-- This policy converts the given name to all uppercase letters.
CREATE MASKING POLICY mask_first_name
WITH(first_name TEXT)
USING ( UPPER(first_name) );

-- This policy replaces the given name with the fixed string 'XXXX'.
CREATE MASKING POLICY mask_last_name
WITH(last_name TEXT)
USING ( 'XXXX'::TEXT );

-- This policy rounds down the given age to the nearest 10.
CREATE MASKING POLICY mask_age
WITH(age INT)
USING ( (FLOOR(age::FLOAT / 10) * 10)::INT );

-- This policy converts the first five digits of the given SSN to 'XXX-XX'.
CREATE MASKING POLICY mask_ssn
WITH(ssn TEXT)
USING ( 'XXX-XX-'::TEXT || SUBSTRING(ssn::TEXT FROM 8 FOR 4) );

-- Attach the masking policies to the employees table.
ATTACH MASKING POLICY mask_first_name
ON employees(col_person.name.first)
TO PUBLIC;

ATTACH MASKING POLICY mask_last_name
ON employees(col_person.name.last)
TO PUBLIC;

ATTACH MASKING POLICY mask_age
ON employees(col_person.age)
TO PUBLIC;

ATTACH MASKING POLICY mask_ssn
ON employees(col_person.ssn)
TO PUBLIC;

-- Verify that your masking policies are attached.
SELECT
    policy_name,
    TABLE_NAME,
    priority,
    input_columns,
    output_columns
FROM
    svv_attached_masking_policy;

   policy_name   | table_name | priority |           input_columns           |          output_columns
-----------------+------------+----------+-----------------------------------+-----------------------------------
 mask_age        | employees  |        0 | ["col_person.\"age\""]            | ["col_person.\"age\""]
 mask_first_name | employees  |        0 | ["col_person.\"name\".\"first\""] | ["col_person.\"name\".\"first\""]
 mask_last_name  | employees  |        0 | ["col_person.\"name\".\"last\""]  | ["col_person.\"name\".\"last\""]
 mask_ssn        | employees  |        0 | ["col_person.\"ssn\""]            | ["col_person.\"ssn\""]
(4 rows)

-- Observe the masking policies taking effect.
SELECT col_person FROM employees ORDER BY col_person.age;

-- This result is formatted for ease of reading.
         col_person
--------------------------------
{
    "name": {
        "first": "JOHN",
        "last": "XXXX"
    },
    "age": 20,
    "ssn": "XXX-XX-3333",
    "company": "Company Inc."
}
{
    "name": {
        "first": "JANE",
        "last": "XXXX"
    },
    "age": 30,
    "ssn": "XXX-XX-7777",
    "company": "Organization Org."
}
```

Following are some examples of invalid masking policy attachments to SUPER paths.

```
-- This attachment fails because there is already a policy
-- with equal priority attached to employees.name.last, which is
-- on the same SUPER path as employees.name.
ATTACH MASKING POLICY mask_ssn
ON employees(col_person.name)
TO PUBLIC;
ERROR:  DDM policy "mask_last_name" is already attached on relation "employees" column "col_person."name"."last"" with same priority
               
-- Create a masking policy that masks DATETIME objects.
CREATE MASKING POLICY mask_date
WITH(INPUT DATETIME)
USING ( INPUT );
               
-- This attachment fails because SUPER type columns can't contain DATETIME objects.
ATTACH MASKING POLICY mask_date
ON employees(col_person.company)
TO PUBLIC;
ERROR:  cannot attach masking policy for output of type "timestamp without time zone" to column "col_person."company"" of type "super
```

Following is an example of attaching a masking policy to a SUPER path that doesn’t exist. By default, Amazon Redshift will resolve the path to `NULL`.

```
ATTACH MASKING POLICY mask_first_name
ON employees(col_person.not_exists)
TO PUBLIC;

SELECT col_person FROM employees LIMIT 1;

-- This result is formatted for ease of reading.
         col_person
-----------------------------------
{
    "name": {
        "first": "JOHN",
        "last": "XXXX"
    },
    "age": 20,
    "ssn": "XXX-XX-3333",
    "company": "Company Inc.",
    "not_exists": null
}
```

# Conditional dynamic data masking
Conditional dynamic data masking

You can mask data at the cell level by creating masking policies with conditional expressions in the masking expression. For example, you can create a masking policy that applies different masks to a value, depending on another column's value in that row.

The following is an example of using conditional data masking to create and attach a masking policy that partially redacts credit card numbers involved in fraud, while completely hiding all other credit card numbers. You must be a superuser or have the [https://docs.aws.amazon.com/redshift/latest/dg/r_roles-default.html](https://docs.aws.amazon.com/redshift/latest/dg/r_roles-default.html) role to run this example.

```
--Create an analyst role.
CREATE ROLE analyst;

--Create a credit card table. The table contains an is_fraud boolean column,
--which is TRUE if the credit card number in that row was involved in a fraudulent transaction.
CREATE TABLE credit_cards (id INT, is_fraud BOOLEAN, credit_card_number VARCHAR(16));

--Create a function that partially redacts credit card numbers.
CREATE FUNCTION REDACT_CREDIT_CARD (credit_card VARCHAR(16))
RETURNS VARCHAR(16) IMMUTABLE
AS $$
    import re
    regexp = re.compile("^([0-9]{6})[0-9]{5,6}([0-9]{4})")
 
    match = regexp.search(credit_card)
    if match != None:
        first = match.group(1)
        last = match.group(2)
    else:
        first = "000000"
        last = "0000"
    
    return "{}XXXXX{}".format(first, last)
$$ LANGUAGE plpythonu;

--Create a masking policy that partially redacts credit card numbers if the is_fraud value for that row is TRUE,
--and otherwise blanks out the credit card number completely.
CREATE MASKING POLICY card_number_conditional_mask
    WITH (fraudulent BOOLEAN, pan varchar(16)) 
    USING (CASE WHEN fraudulent THEN REDACT_CREDIT_CARD(pan)
                ELSE Null
           END);

--Attach the masking policy to the credit_cards/analyst table/role pair. 
ATTACH MASKING POLICY card_number_conditional_mask ON credit_cards (credit_card_number)
 USING (is_fraud, credit_card_number)
 TO ROLE analyst PRIORITY 100;
```

# Dynamic data masking system views
DDM system views

Superusers, users with the `sys:operator` role, and users with the ACCESS SYSTEM TABLE permission can access the following DDM-related system views.
+  [SVV\$1MASKING\$1POLICY](r_SVV_MASKING_POLICY.md) 

   Use SVV\$1MASKING\$1POLICY to view all masking policies created on the cluster or workgroup. 
+  [SVV\$1ATTACHED\$1MASKING\$1POLICY](r_SVV_ATTACHED_MASKING_POLICY.md) 

  Use SVV\$1ATTACHED\$1MASKING\$1POLICY to view all the relations and users or roles with policies attached on the currently connected database.
+  [SYS\$1APPLIED\$1MASKING\$1POLICY\$1LOG](SYS_APPLIED_MASKING_POLICY_LOG.md) 

  Use SYS\$1APPLIED\$1MASKING\$1POLICY\$1LOG to trace the application of masking policies on queries that reference DDM-protected relations.

Following are some examples of the information that you can find using system views.

```
--Select all policies associated with specific users, as opposed to roles
SELECT policy_name,
       schema_name,
       table_name,
       grantee
FROM svv_attached_masking_policy
WHERE grantee_type = 'user';     

--Select all policies attached to a specific user
SELECT policy_name,
       schema_name,
       table_name,
       grantee
FROM svv_attached_masking_policy
WHERE grantee = 'target_grantee_name'            
            
--Select all policies attached to a given table
SELECT policy_name,
       schema_name,
       table_name,
       grantee
FROM svv_attached_masking_policy
WHERE table_name = 'target_table_name'
      AND schema_name = 'target_schema_name';            
            
--Select the highest priority policy attachment for a given role
SELECT samp.policy_name,
       samp.priority,
       samp.grantee,
       smp.policy_expression
FROM svv_masking_policy AS smp
JOIN svv_attached_masking_policy AS samp
    ON samp.policy_name = smp.policy_name
WHERE
    samp.grantee_type = 'role' AND
    samp.policy_name = mask_get_policy_for_role_on_column(
        'target_schema_name', 
        'target_table_name', 
        'target_column_name', 
        'target_role_name')
ORDER BY samp.priority desc
LIMIT 1;         

--See which policy a specific user will see on a specific column in a given relation
SELECT samp.policy_name,
       samp.priority,
       samp.grantee,
       smp.policy_expression
FROM svv_masking_policy AS smp
JOIN svv_attached_masking_policy AS samp
    ON samp.policy_name = smp.policy_name
WHERE
    samp.grantee_type = 'role' AND
    samp.policy_name = mask_get_policy_for_user_on_column(
        'target_schema_name',
        'target_table_name',
        'target_column_name',
        'target_user_name')
ORDER BY samp.priority desc; 
         
 --Select all policies attached to a given relation.
SELECT policy_name,
schema_name,
relation_name,
database_name
FROM sys_applied_masking_policy_log
WHERE relation_name = 'relation_name'
AND schema_name = 'schema_name';
```

# Considerations when using dynamic data masking
Considerations

When using dynamic data masking, consider the following: 
+  When querying objects created from tables, such as views, users will see results based on their own masking policies, not the policies of the user who created the objects. For example, a user with the analyst role querying a view created by a secadmin would see results with masking policies attached to the analyst role. 
+  To prevent the EXPLAIN command from potentially exposing sensitive masking policy filters, only users with the SYS\$1EXPLAIN\$1DDM permission can see masking policies applied in EXPLAIN outputs. Users don't have the SYS\$1EXPLAIN\$1DDM permission by default.

  The following is the syntax for granting the permission to a role.

  ```
  GRANT EXPLAIN MASKING TO ROLE rolename
  ```

   For more information about the EXPLAIN command, see [EXPLAIN](r_EXPLAIN.md). 
+  Users with different roles can see differing results based on the filter conditions or join conditions used. For example, running a SELECT command on a table using a specific column value will fail if the user running the command has a masking policy applied that obfuscates that column. 
+  DDM policies must be applied ahead of any predicate operations, or projections. Masking polices can include the following:
  + Low cost constant operations such as converting a value to null
  + Moderate cost operations such as HMAC hashing
  + High cost operations such as calls to external Lambda user defined functions

  As such, we recommend that you use simple masking expressions when possible. 
+  You can use DDM policies for roles with row-level security policies, but note that RLS policies are applied before DDM. A dynamic data masking expression won't be able to read a row that was protected by RLS. For more information about RLS, see [Row-level security](t_rls.md). 
+  When using the [COPY](r_COPY.md) command to copy from parquet to protected target tables, you should explicitly specify columns in the COPY statement. For more information about mapping columns with COPY, see [Column mapping options](copy-parameters-column-mapping.md). 
+  DDM policies can't attach to the following relations:
  +  System tables and catalogs 
  +  External tables 
  +  Datasharing tables
  +  Cross-DB relations 
  +  Temporary tables 
  +  Correlated queries 
+  DDM policies can contain lookup tables. Lookup tables can be present in the USING clause. The following relation types can’t be used as lookup tables:
  +  System tables and catalogs 
  +  External tables 
  +  Datasharing tables 
  +  Views, materialized views, and late-binding views 
  +  Cross-DB relations 
  +  Temporary tables 
  +  Correlated queries 

  Following is an example of attaching a masking policy to a lookup table.

  ```
  --Create a masking policy referencing a lookup table
  CREATE MASKING POLICY lookup_mask_credit_card WITH (credit_card TEXT) USING (
    CASE
      WHEN
        credit_card IN (SELECT credit_card_lookup FROM credit_cards_lookup)      
      THEN '000000XXXX0000'
      ELSE REDACT_CREDIT_CARD(credit_card)
      END
    ); 
    
  --Provides access to the lookup table via a policy attached to a role
  GRANT SELECT ON TABLE credit_cards_lookup TO MASKING POLICY lookup_mask_credit_card;
  ```
+  You can't attach a masking policy that would produce an output incompatible with the target column's type and size. For example, you can’t attach a masking policy that outputs a 12 character long string to a VARCHAR(10) column. Amazon Redshift supports the following exceptions: 
  +  A masking policy with the input type INTN can be attached to a policy with size INTM as long as M < N. For example, a BIGINT (INT8) input policy can be attached to a smallint (INT4) column. 
  +  A masking policy with the input type NUMERIC or DECIMAL can always be attached to a FLOAT column. 
+ You can't use DDM policies with data sharing. If the datashare's data producer attaches a DDM policy to a table in the datashare, the table becomes inaccessible to users from the data consumer who are trying to query the table. Attempting to add the relation to a datashare fails on the producer-side cluster or namespace with the following error:

  ```
  <ddm_protected_relation> or a relation dependent on it is protected by a masking policy and cannot be added to a datashare
  ```

  If you attach a masking policy to a relation on the producer side and the relation is already included in a datashare, attempting to query the relation on the consumer side fails with the following error:

  ```
  cross-cluster query of the masked relation <ddm_protected_relation> is not supported.
  ```

  You can turn off DDM for datashares using the ALTER TABLE command with the MASKING OFF FOR DATASHARES parameter. For more information, see [ALTER TABLE](r_ALTER_TABLE.md).
+ You can't query relations that have attached DDM policies if your values for any of the following configuration options don't match the default value of the session:
  +  `enable_case_sensitive_super_attribute` 
  +  `enable_case_sensitive_identifier` 
  +  `downcase_delimited_identifier` 

  Consider resetting your session’s configuration options if you attempt to query a relation with a DDM policy attached and see the message "DDM protected relation does not support session level config on case sensitivity being different from its default value."
+  When your provisioned cluster or serverless namespace has any dynamic data masking policies, the following commands are blocked for regular users: 

  ```
  ALTER <current_user> SET enable_case_sensitive_super_attribute/enable_case_sensitive_identifier/downcase_delimited_identifier
  ```

  When you create DDM policies, we recommend that you change the default configuration option settings for regular users to match the session’s configuration option settings at the time the policy was created. Superusers and users with the ALTER USER privilege can do this by using parameter group settings or the ALTER USER command. For information about parameter groups, see [Amazon Redshift parameter groups](https://docs.aws.amazon.com/redshift/latest/mgmt/working-with-parameter-groups.html) in the *Amazon Redshift Management Guide*. For information about the ALTER USER command, see [ALTER USER](r_ALTER_USER.md).
+ Views and late-binding views with attached DDM policies can't be replaced by regular users using the [CREATE VIEW](r_CREATE_VIEW.md) command. To replace views or LBVs with DDM policies, first detach any DDM policies attached to them, replace the views or LBVs, and reattach the policies. Superusers and users with the `sys:secadmin` permission can use CREATE VIEW on views or LBVs with DDM policies without detaching the policies.
+ Views with attached DDM policies can't reference system tables and views. Late-binding views can reference system tables and views.
+ Late-binding views with attached DDM policies can't reference nested data in data lakes, such as JSON documents.
+  Late-binding views can't have DDM policies attached if that late-binding view is referenced by any view.
+  DDM policies attached to late-binding views are attached by column name. At query time, Amazon Redshift validates that all masking policies attached to the late-binding view have been applied successfully, and that the late-binding view's output column type matches the types in the attached masking policies. If the validation fails, Amazon Redshift returns an error for the query.
+ You can use customized session context variables when creating DDM policies. The following example sets session context variables for a DDM policy.

  ```
  -- Set a customized context variable.
  SELECT set_config('app.city', 'XXXX', FALSE);
  
  -- Create a MASKING policy using current_setting() to get the value of a customized context variable.
  CREATE MASKING POLICY city_mask
  WITH (city VARCHAR(30))
  USING (current_setting('app.city')::VARCHAR(30));
  
  -- Attach the policy on the target table to one or more roles.
  ATTACH MASKING POLICY city_mask 
  ON tickit_users_redshift(city) 
  TO ROLE analyst, ROLE dbadmin;
  ```

  For details on how to set and retrieve customized session context variables, go to [SET](r_SET.md), [SET\$1CONFIG](r_SET_CONFIG.md), [SHOW](r_SHOW.md), [CURRENT\$1SETTING](r_CURRENT_SETTING.md), and [RESET](r_RESET.md). For more information on modifying the server configuration in general, go to [Modifying the server configuration](cm_chap_ConfigurationRef.md#t_Modifying_the_default_settings).
**Important**  
 When using session context variables within DDM policies, the security policy is reliant on the user or role that invokes the policy. Be careful to avoid security vulnerabilities when using session context variables in DDM policies. 

# Dynamic data masking end-to-end example
End-to-end example

The following is an end-to-end example showing how you can create and attach masking policies to a column. These policies let users access a column and see different values, depending on the degree of obfuscation in the policies attached to their roles. You must be a superuser or have the [https://docs.aws.amazon.com/redshift/latest/dg/r_roles-default.html](https://docs.aws.amazon.com/redshift/latest/dg/r_roles-default.html) role to run this example.

## Creating a masking policy


First, create a table and populate it with credit card values.

```
--create the table         
CREATE TABLE credit_cards (
  customer_id INT,
  credit_card TEXT
);

--populate the table with sample values
INSERT INTO credit_cards
VALUES
  (100, '4532993817514842'),
  (100, '4716002041425888'),
  (102, '5243112427642649'),
  (102, '6011720771834675'),
  (102, '6011378662059710'),
  (103, '373611968625635')
;

--run GRANT to grant permission to use the SELECT statement on the table
GRANT SELECT ON credit_cards TO PUBLIC;

--create two users
CREATE USER regular_user WITH PASSWORD '1234Test!';

CREATE USER analytics_user WITH PASSWORD '1234Test!';

--create the analytics_role role and grant it to analytics_user
--regular_user does not have a role
CREATE ROLE analytics_role;

GRANT ROLE analytics_role TO analytics_user;
```

Next, create a masking policy to apply to the analytics role.

```
--create a masking policy that fully masks the credit card number
CREATE MASKING POLICY mask_credit_card_full
WITH (credit_card VARCHAR(256))
USING ('000000XXXX0000'::TEXT);

--create a user-defined function that partially obfuscates credit card data
CREATE FUNCTION REDACT_CREDIT_CARD (credit_card TEXT)
RETURNS TEXT IMMUTABLE
AS $$
    import re
    regexp = re.compile("^([0-9]{6})[0-9]{5,6}([0-9]{4})")
 
    match = regexp.search(credit_card)
    if match != None:
        first = match.group(1)
        last = match.group(2)
    else:
        first = "000000"
        last = "0000"
    
    return "{}XXXXX{}".format(first, last)
$$ LANGUAGE plpythonu;

--create a masking policy that applies the REDACT_CREDIT_CARD function
CREATE MASKING POLICY mask_credit_card_partial
WITH (credit_card VARCHAR(256))
USING (REDACT_CREDIT_CARD(credit_card));

--confirm the masking policies using the associated system views
SELECT * FROM svv_masking_policy;

SELECT * FROM svv_attached_masking_policy;
```

## Attaching a masking policy


Attach the masking policies to the credit card table.

```
--attach mask_credit_card_full to the credit card table as the default policy
--all users will see this masking policy unless a higher priority masking policy is attached to them or their role
ATTACH MASKING POLICY mask_credit_card_full
ON credit_cards(credit_card)
TO PUBLIC;

--attach mask_credit_card_partial to the analytics role
--users with the analytics role can see partial credit card information
ATTACH MASKING POLICY mask_credit_card_partial
ON credit_cards(credit_card)
TO ROLE analytics_role
PRIORITY 10;

--confirm the masking policies are applied to the table and role in the associated system view
SELECT * FROM svv_attached_masking_policy;

--confirm the full masking policy is in place for normal users by selecting from the credit card table as regular_user
SET SESSION AUTHORIZATION regular_user;

SELECT * FROM credit_cards;

--confirm the partial masking policy is in place for users with the analytics role by selecting from the credit card table as analytics_user
SET SESSION AUTHORIZATION analytics_user;

SELECT * FROM credit_cards;
```

## Altering a masking policy


The following section shows how to alter a dynamic data masking policy.

```
--reset session authorization to the default
RESET SESSION AUTHORIZATION;

--alter the mask_credit_card_full policy
ALTER MASKING POLICY mask_credit_card_full
USING ('00000000000000'::TEXT);	
	
--confirm the full masking policy is in place after altering the policy, and that results are altered from '000000XXXX0000' to '00000000000000'
SELECT * FROM credit_cards;
```

## Detaching and dropping a masking policy


The following section shows how to detach and drop masking policies by removing all dynamic data masking policies from the table.

```
--reset session authorization to the default
RESET SESSION AUTHORIZATION;

--detach both masking policies from the credit_cards table
DETACH MASKING POLICY mask_credit_card_full 
ON credit_cards(credit_card) 
FROM PUBLIC;

DETACH MASKING POLICY mask_credit_card_partial 
ON credit_cards(credit_card) 
FROM ROLE analytics_role;

--drop both masking policies
DROP MASKING POLICY mask_credit_card_full;

DROP MASKING POLICY mask_credit_card_partial;
```