CREATE-POLICY
CREATE-POLICY
Name
CREATE POLICY
Description
Create policies,such as:
- Create security policies(ROW POLICY) and explain to view the rewritten SQL.
- Create storage migration policy(STORAGE POLICY), used for cold and hot data transform
Grammar:
- ROW POLICY
CREATE ROW POLICY test_row_policy_1 ON test.table1
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
CREATE STORAGE POLICY test_storage_policy_1
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
Create a set of row security policies
CREATE ROW POLICY test_row_policy_1 ON test.table1
AS RESTRICTIVE TO test USING (c1 = 'a');CREATE ROW POLICY test_row_policy_2 ON test.table1
AS RESTRICTIVE TO test USING (c2 = 'b');CREATE ROW POLICY test_row_policy_3 ON test.table1
AS PERMISSIVE TO test USING (c3 = 'c');CREATE ROW POLICY test_row_policy_3 ON test.table1
AS PERMISSIVE TO test USING (c4 = 'd');When we execute the query on Table1, the rewritten SQL is
select * from (select * from table1 where c1 = 'a' and c2 = 'b' or c3 = 'c' or c4 = 'd')
Create policy for storage
- NOTE
- To create a cold hot separation policy, you must first create a resource, and then associate the created resource name when creating a migration policy
- Currently, the drop data migration policy is not supported to prevent data from being migrated. If the policy has been deleted, then the system cannot retrieve the data
- Create policy on cooldown_datetime
CREATE STORAGE POLICY testPolicy
PROPERTIES(
"storage_resource" = "s3",
"cooldown_datetime" = "2022-06-08 00:00:00"
);- Create policy on cooldown_ttl
CREATE STORAGE POLICY testPolicy
PROPERTIES(
"storage_resource" = "s3",
"cooldown_ttl" = "1d"
);Relevant parameters are as follows:
storage_resource
: the storage resource of createcooldown_datetime
: Data migration timecooldown_ttl
: Countdown of the distance between the migrated data and the current time
- NOTE
Keywords
CREATE, POLICY