Create Snowflake user network policies table

get_user_netpolicy.js

CREATE OR REPLACE PROCEDURE get_user_netpolicy()
  RETURNS variant NOT null
  LANGUAGE javascript
  EXECUTE as CALLER
  AS     
  $$
    var METRICS_SCHEMA = 'SNOWFLAKE_METRICS';
    var TABLE_NAME = 'NETWORK_POLICIES';
    var SCHEMA_AND_TABLE = `${METRICS_SCHEMA}.${TABLE_NAME}`;
    try {
      snowflake.execute(
          {
           sqlText: `CREATE SCHEMA IF NOT EXISTS IDENTIFIER(?);`,
           binds: [METRICS_SCHEMA]
          }
      );
      snowflake.execute(
          {
           sqlText: `CREATE OR REPLACE TABLE IDENTIFIER(?) (username varchar(256), network_policy varchar(256))`,
           binds: [SCHEMA_AND_TABLE]
          }
      );
    }
    catch(err){
        return "Failed: " + err;
    };
  
    // Define initial command to get all user names
    var selectUsers = "SELECT name, disabled FROM snowflake.account_usage.users where deleted_on is null and name != 'SNOWFLAKE';";                                                  
    var selectUsersCommand = snowflake.createStatement( {sqlText: selectUsers} );
    
    // Execute the SQL command
    var userNames = selectUsersCommand.execute();
    
    // Loop through the results, processing one row at a time... 
    while (userNames.next())  {
       var userName = userNames.getColumnValue(1);
       
       
       // Create SQL statement to show network policy
       var showPolicy = `SHOW PARAMETERS like 'NETWORK_POLICY' for user "` + userName + `";`;
       var showPolicyCommand = snowflake.createStatement( {sqlText: showPolicy} );
       
       showPolicyCommand.execute();
       
       // Use result scan to get the data and account for not truly null values. 
       // This SQL uses the UNICODE function to workaround a user with no network   
       // policy not returning a true null value
       var getPolicy = `SELECT case when unicode("value") = '0' then 'no_policy' else "value" end FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));`;
       var getPolicyCommand = snowflake.createStatement( {sqlText: getPolicy} );
       
       // Execute command to get network policy
       var netPolicy = getPolicyCommand.execute();
       netPolicy.next();
       var netPolicyName = netPolicy.getColumnValue(1);
       
       try {
          snowflake.execute(
              {
               sqlText: `INSERT INTO IDENTIFIER(?) VALUES (?, ?);`,
               binds: [SCHEMA_AND_TABLE, userName, netPolicyName]
              }
          );
        }
        catch(err){
            return "Failed: " + err;
        };
     }
  $$
;
CALL get_user_netpolicy();