Oracle Privileges to Use Global Temporary Table via Definer's Rights Stored Procedure

Question

Please assume:

User A creates global temporary table gtt.

User A creates stored procedure sp with definer's rights AUTHID DEFINER. For simplicity, assume this sp simply inserts a row into gtt and selects a value from the row in gtt.

User A grants user B execute on sp.

What additional grants, if any, need to be given to users A and B so that B can successfully execute sp?

I've heard that when a global temporary table is used (e.g. data inserted), that the user using the global temporary table needs create table privilege to create the instance of the globaly temporary table in their session's memory (even though the global temporary table itself was already created). Is that true? I assumed granting select and insert on the global temporary table would have been sufficient.

Because sp is defined by A does this mean A needs create any table, so the row of data can be inserted and selected from user B's session memory?

Sorry, I don't currently have access to an Oracle instance where I have enough privileges to try this myself.

Please note, I am not trying to create the global temporary table in the stored procedure.

Using Oracle 19c Enterprise Edition.

Thank you in advance for helping me understand the privileges involved here.

Answer 1

What additional grants, if any, need to be given to users A and B so that B can successfully execute sp?

None.

---

SQL> show user
USER is "SCOTT"
SQL> create global temporary table gtt (name varchar2(20));

Table created.

SQL> create or replace procedure sp
  2    authid definer
  3  as
  4  begin
  5    insert into gtt (name) values (user);
  6  end;
  7  /

Procedure created.

SQL> exec sp;

PL/SQL procedure successfully completed.

SQL> select * from gtt;

NAME
--------------------
SCOTT

SQL> grant execute on sp to mike;

Grant succeeded.

Everything works so far for the GTT and SP owner. Let's see the grantee.

SQL> connect mike/lion
Connected.
SQL> exec scott.sp;

PL/SQL procedure successfully completed.

SQL> select * From scott.gtt;
select * From scott.gtt
                    *
ERROR at line 1:
ORA-00942: table or view does not exist

Right; as I said, no other privileges are needed - stored procedure works (i.e. didn't fail), but - as scott didn't grant any additional privileges, mike can't check gtt table's contents.

Back to scott:

SQL> connect scott/tiger
Connected.
SQL> select * From scott.gtt;

no rows selected

SQL>

But of course; that's a global temporary table - scott sees only its own data (which is now lost).

---

[EDIT: to answer questions you posted as comments]

mike had create table privilege; now it doesn't:

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> revoke create table from mike;

Revoke succeeded.

This piece of code is a copy/paste from above:

SQL> connect scott/tiger
Connected.
SQL> create global temporary table gtt (name varchar2(20));

Table created.

SQL> create or replace procedure sp
  2    authid definer
  3  as
  4  begin
  5    insert into gtt (name) values (user);
  6  end;
  7  /

Procedure created.

SQL> exec sp;

PL/SQL procedure successfully completed.

SQL> select * from gtt;

NAME
--------------------
SCOTT

SQL> grant execute on sp to mike;

Grant succeeded.

Additionally, scott now grants select on gtt to mike:

SQL> grant select on gtt to mike;

Grant succeeded.

What does mike see now?

SQL> connect mike/lion
Connected.
SQL> exec scott.sp;

PL/SQL procedure successfully completed.

SQL> select * from scott.gtt;

NAME
--------------------
MIKE

SQL>