Enforce field existence in DMS table schemas using security rules - Data Management

Data Management Service (DMS) lets you enforce specific field requirements in table schemas, simplifying data analysis and cleansing. Configure a security rule to require specific fields in all new tables.

Prerequisites

Your system role must be administrator or DBA. View my system role.

Overview

Organizations often enforce company- or business-line-specific rules when designing table schemas. Use the Security Rules feature in Data Management Service (DMS) to define these standards and apply them to each database instance.

Procedure

Log in to DMS 5.0.
Move the pointer over the icon in the upper-left corner and choose All Features > Security and disaster recovery (DBS) > Security Rules.

Note
If you use the DMS console in normal mode, choose Security and disaster recovery (DBS) > Security Rules in the top navigation bar.
On the Security Rules page, find the target rule and click Edit in the Actions column.
Click Schema Design in the left pane. Set Detection Point to Save edit and check table header.
Define the security rule in domain-specific language (DSL).
Example: require the id and gmt_create fields in all new tables:
```
if    
    @fac.table_kind == 'new' and    
    @fun.array_size(@fun.extract_column_by_name(@fac.table_column_array, ['id', 'gmt_create'])) < 2
then    
    @act.block_submit "[R&D Standard] New tables must contain the following fields: id, gmt_create. This is not compliant with R&D standards."
end
```
In the Edit Rule - Schema Design dialog box, set Detection Point to Save edit and check table header, enter a Rule Name, and paste the DSL in the code editor. The right panel provides the Factors, Actions, Functions, and Operators tabs. Click Submit.
Click Submit.
Click Enable, then click OK in the Notice dialog box.

Note
Rules are disabled by default to prevent misconfigurations from affecting production environments.
Verify the rule. This check runs when a developer creates a table from the console or imports a CREATE TABLE statement.

When you submit a new table request, the system runs a pre-check. If the schema violates the rule, the Pre-check Results dialog box displays an error such as "[R&D Standard] New tables must contain the following fields: id, gmt_create. This is not compliant with R&D standards."