Stored Procedure fails with error “Requested information on the current user is not accessible in stored procedure”

When running a Stored Procedure that triggers a query to run against Views or Functions from INFORMATION_SCHEMA, it might fail with below error message:

Requested information on the current user is not accessible in stored procedure.

This can be easily reproduced using below code:

CREATE OR REPLACE PROCEDURE my_proc() 
RETURNS VARCHAR 
LANGUAGE JAVASCRIPT 
EXECUTE AS OWNER
AS 
$$ 
    var stmt = snowflake.createStatement( {sqlText: "SELECT * FROM INFORMATION_SCHEMA.LOAD_HISTORY limit 100"} ); 
    stmt.execute(); 
    
    return 1;
$$ ; 

call my_proc();

The reason for the failure is because for the owner’s right Stored Procedure, it will run using owner’s privilege, however, VIEWs and FUNCTIONs inside the INFORMATION_SCHEMA will require to check user’s privilege before data can be returned. Those two requirements conflict with each other. So Snowflake will not allow users to run query against INFORMATION_SCHEMA through Owner’s right Stored Procedure.

To workaround the issue, we have two options:

1. Switch to use ACCOUNT_USAGE instead. However, there are things to keep in mind:
a. ACCOUNT_USAGE will require special privilege, as by default, only ACCOUNTADMIN can access ACCOUNT_USAGE
b. There are latencies in ACCOUNT_USAGE, compared with INFORMATION_SCHEMA, so some updates in the system might not get reflected in the ACCOUNT_USAGE right away. Details can be found here: Differences Between Account Usage and Information Schema

2. If possible, change to CALLER’s right Stored Procedure will allow query to go through. This is my preferred option.

Loading

Leave a Reply

Your email address will not be published.