CODEWITHSUNDEEP
pythonsnowflake-schemasnowflake-cloud-data-platform
Jeyavel
Jeyavel

Reputation: 3030

GRANT EXECUTE permission to ALL STORED PROCEDURES in snowflake

GRANT EXECUTE permission to ALL STORED PROCEDURES in snowflake.

I have create a stored procedure in the snowflake database but I am getting error while trying to execute that stored procedure.

create or replace procedure get_column_scale(column_index float)
    returns float not null
    language javascript
    as
    $$
    var stmt = snowflake.createStatement(
        {sqlText: "select EmployeeKey, EmployeeCode from stproc_test_employees;"}
        );
    stmt.execute();  // ignore the result set; we just want the scale.
    return stmt.getColumnScale(COLUMN_INDEX); // Get by column index (1-based)
    $$
    ;

i am executing like below 

CALL get_column_scale(1);

I'm getting this error when trying to execute the stored procedure with Snowflake

Error [100183] [P0000]: Execution error in stored procedure GET_COLUMN_SCALE:
compilation error:
'SYEMPLOYEES' does not exist or not authorized.
Statement.execute, line 5 position 9

I am thinking it's execute permission i need to add but I don't have idea where need to configure stored procedure permission in Snowflake.

Is anyone have idea about to give permission for stored procedure/table?

Upvotes: 3

Views: 12337

Answers (2)

Yaakov Bressler
Yaakov Bressler

Reputation: 12018

For those reading this answer in 2022, the correct syntax for giving permission to execute a procedure is as follows:

GRANT USAGE ON PROCEDURE
    get_column_scale(float) 
    TO ROLE other_role_name_here;

Upvotes: 2

Rich Murnane
Rich Murnane

Reputation: 2920

A few things that might help you.

  1. I'd recommend fully-qualifying that table name in the SELECT statement, this way whenever the stored procedure is called, the "context" of the user's session will not matter, as long as the session's current role has access to the table and schema you should be good.
    A fully-qualified table has the form: database_name.schema_name.object_name

    Example: hr_prod_db.hr_schema.employees

You can read more about object name resolution at this link: https://docs.snowflake.net/manuals/sql-reference/name-resolution.html

  1. I'd recommend you spend a little bit of time reading about "Session State", at the following link, as this link discuses "Caller's rights" vs. "Owner's rights" stored procedures. If your procedure is only going to be called from a session with the role of the stored procedure owner, this shouldn't matter, but if you are granting USAGE on the procedure to another role, it's very important to understand this and set this properly. https://docs.snowflake.net/manuals/sql-reference/stored-procedures-usage.html#session-state

  2. If your procedure is going to be called by a session that has it's current role set to a different role than the "owning role, you'll need to ensure the proper grants on the procedure (and schema + database) to the role that is going to be executing the procedure. This is all outlined here in this document quite thoroughly, pay particular attention to this as in your example code you have a table or view name that is different than what your error message is reporting, so perhaps stproc_test_employees is a view on top of SYEMPLOYEES: https://docs.snowflake.net/manuals/sql-reference/stored-procedures-usage.html#access-control-privileges Note: When/if you grant usage on this procedure to another role, you will need to include the datatype of the arguments, example:

    GRANT USAGE ON database_name.schema_name.get_column_scale(float) TO ROLE other_role_name_here;

I hope this helps...Rich

Upvotes: 2

Related Questions