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()');