> 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

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

```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 by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

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

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

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.