Database Autonomy Service:Best practices for automated management of column encryption rules

Use cases

Database security and compliance practices typically require the following capabilities:

• Bulk encryption: Configure column encryption rules for sensitive columns such as phone numbers, ID card numbers, and email addresses across multiple instances, databases, and tables.

• Query and audit: Query encrypted columns, instance activation status, quota usage, authorized users, and permission groups.

• Policy management: Delete or update encryption policies, batch-modify authorization permissions, change encryption algorithms, and manually synchronize policies.

• Security compliance: Replace manual console operations with auditable, traceable, and preview-enabled scripted workflows.

Toolchain architecture

The toolchain includes four Python scripts that call DAS OpenAPI through the Alibaba Cloud Python SDK V1 CommonRequest mechanism. All scripts share the same credential loading logic and support the --dry-run flag for dry-run mode.

Configuration files

The toolchain uses four types of configuration files. Each type provides a real file and a .example template. Each file type serves a distinct purpose:

Step 1: Download and extract the toolchain

Download the toolchain archive dbse-skills.tgz and extract it locally:

tar xzf dbsec-skills.tgz
cd dbsec-skills/skills/column-encryption-rule/scripts

Step 2: Install Python dependencies

Create a virtual environment and install dependencies:

python3 -m venv .venv
.venv/bin/python -m pip install -r requirements.txt

Contents of the dependency file (requirements.txt):

aliyun-python-sdk-core>=2.16.0
PyMySQL>=1.1.0

Use the project venv for all subsequent commands: .venv/bin/python.

Step 3: Configure cloud API credentials

Copy the template and enter your AccessKey pair:

cp .aliyun.env.example .aliyun.env

Example .aliyun.env content:

ALIBABA_CLOUD_ACCESS_KEY_ID=your_access_key_id
ALIBABA_CLOUD_ACCESS_KEY_SECRET=your_access_key_secret
# Required only for STS temporary credentials. Delete or leave empty for standard AccessKey pairs.
ALIBABA_CLOUD_SECURITY_TOKEN=your_sts_token

Step 4: Prepare the encryption policy template

Copy the rule template:

cp rule.example.json rule.json

Keep rule.json as a shared policy template and move all instance-specific information to columns.csv:

{
  "ProductId": 5,
  "EncAlgorithm": "AES_128_GCM",
  "EncryptionKeyMode": "client_key",
  "CooperationChannel": "DAS",
  "BackendRegionId": "cn-zhangjiakou",
  "UserList": [
    {
      "AccountId": "shared_user",
      "AuthRole": "fullAccess",
      "EngineType": "MySQL"
    },
    {
      "AccountId": "only_for_instanceB",
      "InstanceId": "rm-instanceB",
      "AuthRole": "restrictedAccess",
      "EngineType": "MySQL"
    }
  ],
  "ResourceGroupId": ""
}

Key fields:

AuthRole values:

Step 5: Prepare the column list

You can prepare the column list in either of the following ways:

Method 1: Manually create columns.csv

Copy the template:

cp columns.example.csv columns.csv

Enter data one column per row. Example:

RegionId,InstanceId,DbName,TableName,Column,EngineType,ProductId
cn-hangzhou,rm-instanceA,your_database,your_table,email,MySQL,
cn-hangzhou,rm-instanceA,your_database,your_table,phone,MySQL,
cn-beijing,rm-instanceB,another_db,users,id_card,MySQL,

To specify multiple columns for the same table, use the Columns field with comma-separated values in quotes:

RegionId,InstanceId,DbName,TableName,Columns,EngineType
cn-hangzhou,rm-instanceA,your_database,your_table,"email,phone",MySQL

The ProductId column is optional. Specify it for cross-product scenarios. If left empty, the top-level value from rule.json is used.

Method 2: Automatically export with fetch_schema.py

Copy the template and enter your database connection information:

cp mysql.example.conf mysql.conf

Example mysql.conf content (key:value format):

url:rm-xxxxxxxxxxxxxxxxx.rwlb.rds.aliyuncs.com
user:your_user
pass:your_password

View the database schema (text format):

.venv/bin/python fetch_schema.py --config mysql.conf --database your_database

Export a compatible columns.csv file:

.venv/bin/python fetch_schema.py --config mysql.conf --database your_database \
  --format csv --region-id cn-beijing --instance-id rm-xxxxxxxxxxxxxxxxx > columns.csv

Full parameter list for fetch_schema.py:

--config          Database configuration file. Default: mysql.conf
--database        Query only the specified database. Repeatable. Omit to enumerate all business databases.
--include-system  Include system databases (information_schema, etc.)
--format          json | text | csv. Default: text
--region-id       Required for CSV: instance region, e.g. cn-beijing
--instance-id     Required for CSV: instance ID, e.g. rm-xxxx
--engine-type     EngineType for CSV. Default: MySQL

Step 6: Perform a dry run

Always perform a dry run before you submit changes to verify the request body and batch splitting.

Dry run for a single creation (without CSV):

.venv/bin/python create_data_masking_rule.py \
  --env-file .aliyun.env --config rule.json --dry-run

Dry run for batch creation (with CSV):

.venv/bin/python create_data_masking_rule.py \
  --env-file .aliyun.env --config rule.json --columns-csv columns.csv --dry-run

Example dry-run output:

BatchCount: number of batches
TotalSubRuleCount: aggregated table rule count
TotalUniqueColumnCount: unique column count
Batches: InstanceId, ServiceRegionId, SubRuleCount, UniqueColumnCount per batch

Optional: local quota precheck. For example, if the remaining quota is 50 columns:

.venv/bin/python create_data_masking_rule.py \
  --env-file .aliyun.env --config rule.json --columns-csv columns.csv \
  --max-new-columns 50 --dry-run

If the column count exceeds the --max-new-columns limit, the script stops locally without sending requests.

Step 7: Create column encryption rules

After the dry run succeeds, remove the --dry-run flag to execute the operation.

Single creation:

.venv/bin/python create_data_masking_rule.py \
  --env-file .aliyun.env --config rule.json

Batch creation:

.venv/bin/python create_data_masking_rule.py \
  --env-file .aliyun.env --config rule.json --columns-csv columns.csv

Optional: adjust the number of SubRules per batch (maximum 100):

.venv/bin/python create_data_masking_rule.py \
  --env-file .aliyun.env --config rule.json --columns-csv columns.csv --batch-size 50

Step 8: Query and verify

Use describe_data_masking.py to query column encryption status. Use --action to select a read-only API operation. All operations support --dry-run.

Examples:

# List authorized users
.venv/bin/python describe_data_masking.py --env-file .aliyun.env \
  --action DescribeDataMaskingUsers

# Query supported encryption algorithms for an instance
.venv/bin/python describe_data_masking.py --env-file .aliyun.env \
  --action DescribeDataMaskingAlgorithms \
  --product-id 5 --service-region-id cn-beijing --instance-id rm-xxxxxxxxxxxxxxxxx

# Query encrypted columns with pagination
.venv/bin/python describe_data_masking.py --env-file .aliyun.env \
  --action ListDataMaskingColumns \
  --product-ids 5 --db-name your_database --page-size 20 --current-page 1

# Get encrypted column quota statistics
.venv/bin/python describe_data_masking.py --env-file .aliyun.env \
  --action GetDataMaskingColumnCount --product-ids 5

Filter and pagination parameters for List operations: --masking-status, --template-id, --risk-level-id, --model-tag-id, --instance-id, --db-name, --table-name, --column-name, --current-page, --page-size, --product-id, --product-ids, --risk-level-ids, --template-rule-ids, --engine-type.

Step 9: Management operations

Use manage_data_masking.py with --action to select a write API operation. All operations support --dry-run to preview the request body. List parameters are provided through a JSON file specified by --config. Scalar parameters can be set in the config file or overridden by CLI options (CLI takes precedence).

Examples (run with --dry-run first to preview, then remove --dry-run to execute):

# Delete a policy: provide SubRuleList in config. ProductId is an integer.
.venv/bin/python manage_data_masking.py --env-file .aliyun.env \
  --action DeleteDataMaskingRule --config delete_rule.json --rule-name rule-demo --dry-run

# Batch-modify authorization permission groups
.venv/bin/python manage_data_masking.py --env-file .aliyun.env \
  --action BatchModifyDataMaskingUsers --config batch_users.json \
  --auth-role fullAccess --dry-run

# Change the encryption algorithm for an instance
.venv/bin/python manage_data_masking.py --env-file .aliyun.env \
  --action ModifyDataMaskingEncryptionAlgorithm \
  --instance-id rm-xxx --product-id 5 --service-region-id cn-beijing \
  --encryption-algorithm AES_256_GCM --dry-run

# Manually synchronize all encryption policies (no business parameters)
.venv/bin/python manage_data_masking.py --env-file .aliyun.env \
  --action ManualSyncMaskingRules --dry-run