CREATE-POLICY

Name

CREATE POLICY

Description

Create policies,such as:

  1. Create security policies(ROW POLICY) and explain to view the rewritten SQL.
  2. Create storage migration policy(STORAGE POLICY), used for cold and hot data transform

Grammar:

  1. ROW POLICY
1. CREATE ROW POLICY test_row_policy_1 ON test.table1
2. AS {RESTRICTIVE|PERMISSIVE} TO test USING (id in (1, 2));

illustrate：

  • filterType：It is usual to constrict a set of policies through AND. PERMISSIVE to constrict a set of policies through OR
  • Configure multiple policies. First, merge the RESTRICTIVE policy with the PERMISSIVE policy
  • It is connected with AND between RESTRICTIVE AND PERMISSIVE

  • It cannot be created for users root and admin

  • STORAGE POLICY

1. CREATE STORAGE POLICY test_storage_policy_1
2. PROPERTIES ("key"="value", ...);

illustrate：

  • PROPERTIES has such keys:
  • storage_resource：storage resource name for policy
  • cooldown_datetime：cool down time for tablet, can’t be set with cooldown_ttl.
  • cooldown_ttl：hot data stay time. The time cost between the time of tablet created and the time of migrated to cold data, formatted as： 1d：1 day 1h：1 hour 50000: 50000 second

Example

  1. Create a set of row security policies

1. CREATE ROW POLICY test_row_policy_1 ON test.table1 2. AS RESTRICTIVE TO test USING (c1 = 'a');

1. CREATE ROW POLICY test_row_policy_2 ON test.table1 2. AS RESTRICTIVE TO test USING (c2 = 'b');

1. CREATE ROW POLICY test_row_policy_3 ON test.table1 2. AS PERMISSIVE TO test USING (c3 = 'c');

1. CREATE ROW POLICY test_row_policy_3 ON test.table1 2. AS PERMISSIVE TO test USING (c4 = 'd');

When we execute the query on Table1, the rewritten SQL is

1. select * from (select * from table1 where c1 = 'a' and c2 = 'b' or c3 = 'c' or c4 = 'd') 2. Create policy for storage

  1. Create policy on cooldown_datetime

1. CREATE STORAGE POLICY testPolicy 2. PROPERTIES( 3. "storage_resource" = "s3", 4. "cooldown_datetime" = "2022-06-08 00:00:00" 5. );

  1. Create policy on cooldown_ttl

1. CREATE STORAGE POLICY testPolicy 2. PROPERTIES( 3. "storage_resource" = "s3", 4. "cooldown_ttl" = "1d" 5. );

Keywords

1. CREATE, POLICY

Best Practice