'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.

stored-proceduressnowflake-cloud-data-platformarraysangularinput


Solution 1:[1]

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

Solution 2:[2]

Since we are not using [(ngModel)] to manage the FormControl, we ourselves have to take care of syncing the model and view data.

One possible solution would be to manually update the input value in case of error.

Within html file:

(focusout)="rangeChanged(fromRange, index, true)"

Within ts file:

rangeChanged(inputEle: HTMLInputElement, index: number, isFromRangeChanged: boolean) {
    let hasError: boolean;
    const rangeProperty = isFromRangeChanged ? 'fromRange' : 'toRange';
    // Validation logic that sets the hasError value

    if (hasError) {
      // Error logic goes here...

      inputEle.value = this.ranges[index][rangeProperty];
    } else {
      this.ranges[index][rangeProperty] = inputEle.value;
    }
  }

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Gokhan Atil
Solution 2 Siddhant

Related Questions