permissions - How to grant privileges to see Snowflake stored procedure DDL without granting privileges to execute it - Stack Ov

admin管理员组

文章数量:1023086

In Snowflake, it appears that a stored procedure USAGE privilege is required to see the stored procedure DDL, but the same privilege allows the user to execute the stored procedure. In our production environment, the developers need to be able to access stored procedure DDL (e.g., for troubleshooting purposes) but must not be able to execute stored procedures. Is there a clean way to grant one privilege without the other? The only approach I can think of is to create a "utility" stored procedure to be executed with owner rights that would accept stored procedure name as a parameter and return the DDL of this SP. This seems like a hack though - anyone have better suggestions?

In Snowflake, it appears that a stored procedure USAGE privilege is required to see the stored procedure DDL, but the same privilege allows the user to execute the stored procedure. In our production environment, the developers need to be able to access stored procedure DDL (e.g., for troubleshooting purposes) but must not be able to execute stored procedures. Is there a clean way to grant one privilege without the other? The only approach I can think of is to create a "utility" stored procedure to be executed with owner rights that would accept stored procedure name as a parameter and return the DDL of this SP. This seems like a hack though - anyone have better suggestions?

• permissions
• snowflake-cloud-data-platform

Share Improve this question Follow edited Nov 21, 2024 at 22:30 samhita 4,12522 gold badges1111 silver badges1818 bronze badges asked Nov 20, 2024 at 14:39 Vlad KupchanVlad Kupchan 15788 bronze badges 1

• You are right about imported privileges being used in shared databases, I was thinking more from inheriting privileges from a role.I was testing it but could not get to work,will keep looking – samhita Commented Nov 22, 2024 at 1:32

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

This should be possible using the snowflake get_ddl function, which uses the complete procedure name and definition. There is no need to provide usage permission on the procedure.

As explained in the below example

use role some_role;
create or replace procedure dev._neeru.test_1(arg_1 varchar)
    returns varchar
    language javascript
    strict
    execute as caller
AS
$$
    function formatError(error) {
        return "Error: " + error.message +
            "\nCode: " + error.code +
            "\nState: " + error.state +
            "\nStack Trace:\n" + error.stackTraceTxt
    }
    var sql = `SELECT 1`;
    var res = snowflake.execute({sqlText: sql});
    res.next();
    return res.getColumnValue(1);
$$;

use role public;

select current_role();
-- PUBLIC


select get_ddl('procedure', 'dev._neeru.test_1(varchar)');

/*
 CREATE OR REPLACE PROCEDURE "TEST_1"("ARG_1" VARCHAR(16777216))
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
STRICT
EXECUTE AS CALLER
AS '
    function formatError(error) {
        return "Error: " + error.message +
            "\\nCode: " + error.code +
            "\\nState: " + error.state +
            "\\nStack Trace:\\n" + error.stackTraceTxt
    }
    var sql = `SELECT 1`;
    var res = snowflake.execute({sqlText: sql});
    res.next();
    return res.getColumnValue(1);
';
 */

This worked for me if you are still running into issues. Try the below approach with Snowflake Metadata tables in the SNOWFLAKE_ACCOUNT_USAGE schema. Related Docs to grants and procedure view details

SELECT
PROCEDURE_NAME, ARGUMENT_SIGNATURE, PROCEDURE_DEFINITION
FROM SNOWFLAKE.ACCOUNT_USAGE.PROCEDURES
WHERE PROCEDURE_CATALOG = 'DEV' 
AND PROCEDURE_SCHEMA = '_NEERU'
AND PROCEDURE_NAME = 'TEST_1'
AND DELETED IS NULL LIMIT 100;

In Snowflake, it appears that a stored procedure USAGE privilege is required to see the stored procedure DDL, but the same privilege allows the user to execute the stored procedure. In our production environment, the developers need to be able to access stored procedure DDL (e.g., for troubleshooting purposes) but must not be able to execute stored procedures. Is there a clean way to grant one privilege without the other? The only approach I can think of is to create a "utility" stored procedure to be executed with owner rights that would accept stored procedure name as a parameter and return the DDL of this SP. This seems like a hack though - anyone have better suggestions?

In Snowflake, it appears that a stored procedure USAGE privilege is required to see the stored procedure DDL, but the same privilege allows the user to execute the stored procedure. In our production environment, the developers need to be able to access stored procedure DDL (e.g., for troubleshooting purposes) but must not be able to execute stored procedures. Is there a clean way to grant one privilege without the other? The only approach I can think of is to create a "utility" stored procedure to be executed with owner rights that would accept stored procedure name as a parameter and return the DDL of this SP. This seems like a hack though - anyone have better suggestions?

• permissions
• snowflake-cloud-data-platform

Share Improve this question Follow edited Nov 21, 2024 at 22:30 samhita 4,12522 gold badges1111 silver badges1818 bronze badges asked Nov 20, 2024 at 14:39 Vlad KupchanVlad Kupchan 15788 bronze badges 1

• You are right about imported privileges being used in shared databases, I was thinking more from inheriting privileges from a role.I was testing it but could not get to work,will keep looking – samhita Commented Nov 22, 2024 at 1:32

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

This should be possible using the snowflake get_ddl function, which uses the complete procedure name and definition. There is no need to provide usage permission on the procedure.

As explained in the below example

use role some_role;
create or replace procedure dev._neeru.test_1(arg_1 varchar)
    returns varchar
    language javascript
    strict
    execute as caller
AS
$$
    function formatError(error) {
        return "Error: " + error.message +
            "\nCode: " + error.code +
            "\nState: " + error.state +
            "\nStack Trace:\n" + error.stackTraceTxt
    }
    var sql = `SELECT 1`;
    var res = snowflake.execute({sqlText: sql});
    res.next();
    return res.getColumnValue(1);
$$;

use role public;

select current_role();
-- PUBLIC


select get_ddl('procedure', 'dev._neeru.test_1(varchar)');

/*
 CREATE OR REPLACE PROCEDURE "TEST_1"("ARG_1" VARCHAR(16777216))
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
STRICT
EXECUTE AS CALLER
AS '
    function formatError(error) {
        return "Error: " + error.message +
            "\\nCode: " + error.code +
            "\\nState: " + error.state +
            "\\nStack Trace:\\n" + error.stackTraceTxt
    }
    var sql = `SELECT 1`;
    var res = snowflake.execute({sqlText: sql});
    res.next();
    return res.getColumnValue(1);
';
 */

This worked for me if you are still running into issues. Try the below approach with Snowflake Metadata tables in the SNOWFLAKE_ACCOUNT_USAGE schema. Related Docs to grants and procedure view details

SELECT
PROCEDURE_NAME, ARGUMENT_SIGNATURE, PROCEDURE_DEFINITION
FROM SNOWFLAKE.ACCOUNT_USAGE.PROCEDURES
WHERE PROCEDURE_CATALOG = 'DEV' 
AND PROCEDURE_SCHEMA = '_NEERU'
AND PROCEDURE_NAME = 'TEST_1'
AND DELETED IS NULL LIMIT 100;

本文标签：