> For the complete documentation index, see [llms.txt](https://docs.datapancake.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.datapancake.com/guides/miscellaneous-guides/json-tutorial-udfs-and-security-policies.md).

# 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
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## 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, and the optional `goal` query parameter:

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

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

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.