CODEWITHSUNDEEP
stored-proceduressnowflake-cloud-data-platform
Ishani
Ishani

Reputation: 1

Snowflake procedure is running as caller but not as owner even though both are same

The definition of the procedure is as follows

CREATE OR REPLACE PROCEDURE "EMPLOYEE_TREND_TMP"()

RETURNS VARCHAR(16777216)

LANGUAGE JAVASCRIPT

EXECUTE AS OWNER

AS '

var rs = snowflake.execute( { sqlText: SELECT count(*) FROM "DATA_MART"."EMPLOYEE"."CCH_EMPLOYEE_COMP" ; } );

';

The owner of the procedure is HR_ROLE. When I specify "execute as caller" and call the procedure using HR_ROLE, it works fine. But "execute as owner" throws an error:

"Execution error in store procedure EMPLOYEE_TREND_TMP: Stored procedure execution error: Requested information on the current user is not accessible in stored procedure. At Snowflake.execute, line 4 position 21"

This is strange because both the owner and caller of the procedure is HR_ROLE itself.

The query inside the procedure "SELECT count(*) FROM "DATA_MART"."EMPLOYEE"."CCH_EMPLOYEE_COMP" ;" is also running fine using the HR_ROLE.

I want to execute the procedure as owner but I am confused as to why it's throwing an error, when both the caller and the owner are the same exact role (HR_ROLE). I have checked all the privileges and access to objects, everything seems to be in place.

Upvotes: 0

Views: 1016

Answers (1)

Gokhan Atil
Gokhan Atil

Reputation: 10134

As I see, this error occurs usually happens when accessing information_schema or session parameters. Are you sure that the SP contains only 1 line, and it's just calling this statement? Could there be other statements to access session variables or information schema?

Have you checked the limitations of the Owner’s Rights Stored Procedures?

https://docs.snowflake.com/en/sql-reference/stored-procedures-rights.html#owner-s-rights-stored-procedures

Upvotes: 0

Related Questions