JSON Tutorial UDFs & Security Policies

Copy and deploy these in a workspace for the JSON Tutorial.

CREATE OR REPLACE ROLE PLANT_ACCESS_NA;
CREATE OR REPLACE ROLE PLANT_ACCESS_EU;

CREATE OR REPLACE TABLE DEMO_PHARMA_MANUFACTURING_PLANT_ACCESS (
  role_name STRING,
  plant_code STRING);

-- Example grants
INSERT INTO DEMO_PHARMA_MANUFACTURING_PLANT_ACCESS(role_name, plant_code) VALUES
  ('PLANT_ACCESS_NA','PLT-AZ01'),
  ('PLANT_ACCESS_NA','PLT-NJ02'),
  ('PLANT_ACCESS_EU','PLT-IE03');
  

-- Row access policy using role() to look up entitlements
CREATE OR REPLACE ROW ACCESS POLICY rap_by_plant
AS (plant_code STRING) RETURNS BOOLEAN ->
  'ACCOUNTADMIN' = CURRENT_ROLE() 
  OR
  EXISTS (
    SELECT 1
    FROM DEMO_PHARMA_MANUFACTURING_PLANT_ACCESS pa
    WHERE pa.plant_code = plant_code
      AND UPPER(pa.role_name) IN (
        SELECT UPPER(value::string)
        FROM TABLE(
          FLATTEN(INPUT => PARSE_JSON(CURRENT_AVAILABLE_ROLES()))
        )
      )
  );



CREATE OR REPLACE FUNCTION mask_phone(raw STRING)
RETURNS STRING
LANGUAGE SQL
AS
$$
  CASE
    WHEN raw IS NULL THEN NULL
    WHEN LENGTH(raw) <= 4 THEN '****'
    ELSE CONCAT(REPEAT('*', GREATEST(LENGTH(raw)-4,0)), RIGHT(raw, 4))
  END
$$;

-- Policy for phone
CREATE OR REPLACE MASKING POLICY mp_customer_phone AS (val STRING) RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('ACCOUNTADMIN') THEN val
    ELSE mask_phone(val)
  END;

-- Policy for email
CREATE OR REPLACE MASKING POLICY mp_customer_email AS (val STRING) RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('ACCOUNTADMIN') THEN val
    ELSE REGEXP_REPLACE(val, '(^.).+(@.+$)', '\\1***\\2')  -- a***@domain
  END;

-------Data Enrichment
  

-- UDF: standardize part number to uppercase and zero-pad the middle block
CREATE OR REPLACE FUNCTION udf_std_part_number(pn STRING)
RETURNS STRING
LANGUAGE SQL
IMMUTABLE
AS
$$
  CASE
    WHEN pn IS NULL THEN NULL

    
    WHEN REGEXP_LIKE(pn, '^PN-(\d+)-([A-Z])$', 'i') THEN
      UPPER('PN-' || LPAD(REGEXP_SUBSTR(pn, 'PN-(\d+)-', 1, 1, 'i', 1), 4, '0') || '-' ||
            REGEXP_SUBSTR(pn, '-([A-Z])$', 1, 1, 'i', 1))
    ELSE UPPER(pn)
  END
$$;


CREATE OR REPLACE FUNCTION udf_email_domain(email STRING)
RETURNS STRING
LANGUAGE SQL
IMMUTABLE
AS
$$
  CASE
    WHEN email IS NULL THEN NULL
    -- trim whitespace and lowercase
    WHEN REGEXP_LIKE(LOWER(TRIM(email)), '^[^@]+@[^@]+$') THEN
      LOWER(SPLIT_PART(TRIM(email), '@', 2))
    ELSE NULL
  END
$$;