'use temporary table without future grant

I would like to get rid of Teamporary tables without having to issue FUTURE GRANT on my schema. The schema "DB_DEV.Schema" is a "WITH MANAGED ACCESS" schema. But I do not succeed. Here is the code of my tests.

Case 1

create or replace temporary table DB_DEV.Schema.TempTable as select cast(null as date) Col1 ,cast(null as date) as Col2 from Dual ;

select * from DB_DEV.Schema.TempTable ;

--SQL compilation error: Object 'DB_DEV.Schema."TempTable"' does not exist or not authorized.

Case 2

grant SELECT,INSERT,UPDATE,DELETE,TRUNCATE  ON future TABLES  IN SCHEMA DB_DEV.Schema  to  ROLE My_User_Role ;

create or replace temporary table DB_DEV.Schema.TempTable as select cast(null as date) Col1 ,cast(null as date) as Col2 from Dual ;

select * from DB_DEV.Schema.TempTable ;

--it works

Did I miss something or do something wrong?



Sources

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

Source: Stack Overflow

Solution Source