Can we Use Snowflake Tables functions in the procedure?

Question

Experts,

I am trying to use tables function inside the snowflake procedure. However it throws me an error.

CREATE PROCEDURE ..... .... extcnt ="select count(*) from table(information_schema.external_table_files(table_name=>''MYTABLE''))";
snowflake.execute({sqlText: extcnt}); .....

I get below error message. Is there any restriction in using table function inside the procedure? Please Help.

"JavaScript execution error: Uncaught 100183-Stored procedure execution error: Requested information on the current user is not accessible in stored procedure"

Regards, Gopi

Answer 1

Check the "Caller’s Rights and Owner’s Rights Stored Procedures" - this is probably the source of that error message:

• https://docs.snowflake.com/en/sql-reference/stored-procedures-rights.html

When defining a stored procedure you can ask it to EXECUTE AS CALLER, which changes the access it has to different resources.

Create a stored procedure as an owner’s rights stored procedure if all of the following are true: You want to delegate a task(s) to another user(s) who will run with the owner’s privileges, not the caller’s own privileges [...]

Create a stored procedure as a caller’s rights stored procedure if the following are true: The stored procedure operates only on objects that the caller owns or has the required privileges on [...]

CREATE PROCEDURE sv_proc1()
    RETURNS VARCHAR
    LANGUAGE JAVASCRIPT
    EXECUTE AS CALLER
    AS
    $$
...