Help Center/ Data Warehouse Service / SQL Syntax Reference (9.1.0.x)/ DDL Syntax/ ALTER REDACTION POLICY
Updated on 2025-09-18 GMT+08:00
View PDF

ALTER REDACTION POLICY

Function

ALTER REDACTION POLICY modifies a data redaction policy applied to a specified table.

Precautions

Only the table object owner and users with the gs_redaction_policy preset role can modify the masking policy.

Syntax

  • Modify the expression used for a redaction policy to take effect. 
    1
    		 
    ALTER REDACTION POLICY policy_name ON table_name WHEN (when_expression);
  • Enable or disable a redaction policy. 
    1
    		 
    ALTER REDACTION POLICY policy_name ON table_name ENABLE | DISABLE;
  • Rename a redaction policy. 
    1
    		 
    ALTER REDACTION POLICY policy_name ON table_name RENAME TO new_policy_name;
  • Add, modify, or delete a column on which the redaction policy is used. 
    1
2
    		 
    ALTER REDACTION POLICY policy_name ON table_name 
    action;

    There are several clauses of action:

    1
2
3
    		 
    [INHERIT] ADD COLUMN column_name WITH redaction_function_name ( [ argument [, ...] ] )
  | [INHERIT] MODIFY COLUMN column_name WITH redaction_function_name ( [ argument [, ...] ] )
  | DROP COLUMN column_name

Parameter Description

Table 1 ALTER REDACTION POLICY parameters

Parameter

Description

Value Range

policy_name

Specifies the name of the redaction policy to be modified.

Name of an existing masking policy.

table_name

Specifies the name of the table to which the redaction policy is applied.

Name of an existing table.

when_expression

Specifies the new expression used for the redaction policy to take effect.

-

ENABLE | DISABLE

Specifies whether to enable or disable the current redaction policy.

  • ENABLE: makes the data masking policy of the table take effect again.
  • DISABLE: disables the masking policy applied to the table.

-

new_policy_name

Specifies the new name of the redaction policy.

A string compliant with the identifier naming rules.

column_name

Specifies the name of the table column to which the redaction policy is applied.

  • To add a column, use a column name that has not been bound to any redaction functions.
  • To modify a column, use the name of an existing column.
  • To delete a column, use the name of an existing column.

-

redaction_function_name

Specifies the name of a redaction function.

For details about the supported functions, see Data Redaction Functions.

arguments

Specifies the list of arguments of the masking function.

  • MASK_NONE: indicates that no masking is performed.
  • MASK_FULL: indicates that all data is masked to a fixed value.
  • MASK_PARTIAL: indicates that data of the specified character type, numeric type, or time type is partially masked.

For details about the supported functions, see Data Redaction Functions.

Examples

Create a user named test_role and an example table named emp, and insert data into the table. 
1
 
CREATE ROLE test_role PASSWORD '{Password}';

1
2
3
 
DROP TABLE IF EXISTS emp;
CREATE TABLE emp(id int, name varchar(20), salary NUMERIC(10,2));
INSERT INTO emp VALUES(1, 'July', 1230.10), (2, 'David', 999.99);
Define a masking policy mask_emp on the emp table that hides the salary column from the user test_role. 
1
 
CREATE REDACTION POLICY mask_emp ON emp WHEN(current_user = 'test_role') ADD COLUMN salary WITH mask_full(salary);

Modify the expression for the data masking policy to take effect for the specified role. (If no user is specified, the policy takes effect for the current user by default.)

1
2
 
ALTER REDACTION POLICY mask_emp ON emp WHEN (pg_has_role(current_user, 'redact_role', 'member'));
ALTER REDACTION POLICY mask_emp ON emp WHEN (pg_has_role('redact_role', 'member'));

Modify the expression for the masking policy to take effect for all users.

1
 
ALTER REDACTION POLICY mask_emp ON emp WHEN (1=1);

Disable the masking policy.

1
 
ALTER REDACTION POLICY mask_emp ON emp DISABLE;

Enable the masking policy again.

1
 
ALTER REDACTION POLICY mask_emp ON emp ENABLE;

Change the masking policy name to mask_emp_new.

1
 
ALTER REDACTION POLICY mask_emp ON emp RENAME TO mask_emp_new;

Add a column with the masking policy used.

1
 
ALTER REDACTION POLICY mask_emp_new ON emp ADD COLUMN name WITH mask_partial(name, '*', 1, length(name));

Modify the masking policy for the name column. Use the MASK_FULL function to redact all data in the name column.

1
 
ALTER REDACTION POLICY mask_emp_new ON emp MODIFY COLUMN name WITH mask_full(name);

Delete an existing column where the masking policy is used.

1
 
ALTER REDACTION POLICY mask_emp_new ON emp DROP COLUMN name;

Helpful Links

CREATE REDACTION POLICY and DROP REDACTION POLICY

Parent Topic: DDL Syntax