# JSON Tutorial UDFs & Security Policies

```sql
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
$$;
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.datapancake.com/guides/miscellaneous-guides/json-tutorial-udfs-and-security-policies.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.