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.
