1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
use role sysadmin;
use database ttips;
use schema udfs;

-- creating a secure SQL udf
create or replace secure function secure_sql_udf()
returns string
as
$$
    select 'wow! my secure scalar sql udf worked'
$$;

-- call the function and observer the behaviour
select secure_sql_udf();

-- same for javascript
create or replace secure function secure_js_udf()
returns string null
language javascript
as
$$
    return 'wow! my secure scalar JavaScript udf worked'
$$;

-- run them together the function
select secure_sql_udf(),secure_js_udf();

desc function secure_sql_udf();

show functions like 'SECURE%';

-- lets understand the privacy part
-- use role public; -- this will not work in snowsight under folder. 
select current_role();
select secure_sql_udf(),secure_js_udf();

-- lets create a non secure function first
create or replace function non_secure_udf()
returns string null
language javascript
as
$$
    return 'security breach! Not secure'
$$;

-- lets give usgae grant to public role
grant usage on database ttips to role "PUBLIC";
grant usage on schema ttips.udfs to role "PUBLIC";

grant usage on function secure_sql_udf() to role "PUBLIC";
grant usage on function secure_js_udf() to role "PUBLIC";
grant usage on function non_secure_udf() to role "PUBLIC";

grant USAGE on warehouse compute_wh to role "PUBLIC";

select current_role();
use WAREHOUSE COMPUTE_WH;
select secure_sql_udf(),secure_js_udf(),non_secure_udf();

desc function non_secure_udf();
desc function secure_js_udf();

-- let test the get_ddl with admin role
select get_ddl('function','non_secure_udf()');
select get_ddl('function','secure_js_udf()');

-- now change role to public who has usage access
select current_role();
select get_ddl('function','non_secure_udf()');
select get_ddl('function','secure_js_udf()');