你不能只用一个SQL查询就得到这个,但这是我的一个队友开发的存储过程:
/* This table will hold a list of all masking policies defined */
CREATE OR REPLACE TABLE SNAPSHOT_MASKING_POLICIES (
SNAPSHOT_DATE TIMESTAMP,
CREATED_ON TIMESTAMP,
POLICY_NAME VARCHAR,
POLICY_DB VARCHAR,
POLICY_SCHEMA VARCHAR,
POLICY_KIND VARCHAR,
OWNER VARCHAR,
COMMENT VARCHAR);
/* This table will hold a list of all columns that are masked by one of the policies */
create or replace table SNAPSHOT_MASKING_POLICY_REFERENCES (
SNAPSHOT_DATE TIMESTAMP,
CREATED_ON TIMESTAMP,
POLICY_DB VARCHAR,
POLICY_SCHEMA VARCHAR,
POLICY_NAME VARCHAR,
POLICY_KIND VARCHAR,
REF_DATABASE_NAME VARCHAR,
REF_SCHEMA_NAME VARCHAR,
REF_ENTITY_NAME VARCHAR,
REF_ENTITY_DOMAIN VARCHAR,
REF_COLUMN_NAME VARCHAR
);
/* Procedure will loop through each policy and get the policy_references */
CREATE OR REPLACE PROCEDURE UDP_GET_MASKING_POLICY_REFERENCES(V_POLICY_NAME STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
var return_val = "";
var query_id = "";
var sql_command = "SHOW MASKING POLICIES LIKE '" + V_POLICY_NAME + "'"
try {
var result_set = snowflake.execute ({sqlText: sql_command});
}
catch (err) {
return "Failed: "+ sql_command + ": " + err; // Return a success/error indicator.
}
var sql_command = "SELECT LAST_QUERY_ID()"
try {
var result_set = snowflake.execute ({sqlText: sql_command});
while (result_set.next()) {
query_id = result_set.getColumnValue(1);
}
}
catch (err) {
return "Failed: "+ sql_command + ": " + err; // Return a success/error indicator.
}
var sql_command = "DELETE FROM SNAPSHOT_MASKING_POLICIES WHERE POLICY_NAME LIKE ?"
try {
var result_set = snowflake.execute ({sqlText: sql_command,binds:[V_POLICY_NAME]});
}
catch (err) {
return "Failed: "+ sql_command + ": " + err; // Return a success/error indicator.
}
var sql_command = "insert into SNAPSHOT_MASKING_POLICIES \
SELECT CURRENT_TIMESTAMP() AS SNAPSHOT_DATE,* \
FROM TABLE(RESULT_SCAN('" + query_id + "'));"
try {
var result_set = snowflake.execute ({sqlText: sql_command});
}
catch (err) {
return "Failed: "+ sql_command + ": " + err; // Return a success/error indicator.
}
var sql_command = "DELETE FROM SNAPSHOT_MASKING_POLICY_REFERENCES WHERE POLICY_NAME LIKE ?";
try {
var result_set = snowflake.execute ({sqlText: sql_command,binds:[V_POLICY_NAME]});
}
catch (err) {
return "Failed: "+ sql_command + ": " + err; // Return a success/error indicator.
}
var sql_command = "select POLICY_DB||'.'||POLICY_SCHEMA||'.'||POLICY_NAME FROM SNAPSHOT_MASKING_POLICIES WHERE POLICY_NAME LIKE ?";
try {
var result_set = snowflake.execute ({sqlText: sql_command,binds:[V_POLICY_NAME]});
while (result_set.next()) {
policy_name = result_set.getColumnValue(1);
if (return_val == "") {
return_val = policy_name;
} else {
return_val = return_val + ", " + policy_name;
}
var sql_command2 = "INSERT INTO SNAPSHOT_MASKING_POLICY_REFERENCES SELECT CURRENT_TIMESTAMP() AS SNAPSHOT_DATE, * FROM table(information_schema.policy_references(policy_name => '" + policy_name + "'))"
var create_insert_stmt = snowflake.createStatement({ sqlText: sql_command2});
try {
var result_set2 = create_insert_stmt.execute ();
}
catch (err) {
return "Failed: "+ sql_command2 + ": " + err; // Return a success/error indicator.
}
}
return "Complete: " + return_val; // Return a success/error indicator.
}
catch (err) {
return "Failed: "+ sql_command + ": " + err; // Return a success/error indicator.
}
$$;
/* Call Procedure */
CALL UDP_GET_MASKING_POLICY_REFERENCES('%');
/* Display Results */
select * from SNAPSHOT_MASKING_POLICY_REFERENCES;
(感谢鲍勃·莫里森!)
