0

I've doing some tests in SQL Server and Oracle.

When I created a temporary table in SQL Server I don't needed grant select on the temporary table for the specified user. Any user get to query the temporary table.

In Oracle I need to issue grant for the users.

Is this the behaviour? I always need to issue grant on the temporary table?

After this I thought that one way to have the same behaviour of SQL Server would be grant to public role.

3
  • 3
    Temporary tables in Oracle are only temporary with regards to the data. The definition of the table is globally visible and will stay even if the session disconnects that created the table
    – user1822
    Sep 24, 2019 at 13:55
  • It's globally visible, but I need grant for someone query the table, that is it?
    – Potter
    Sep 24, 2019 at 14:33
  • 4
    A global temp table has an owner. If you want other users to allow access to that table, then yes you need to grant privileges to those users.
    – user1822
    Sep 24, 2019 at 14:36

1 Answer 1

2

A global temporary table is global, because its definition is persisted an other sessions can use the table as well, given that they have the necessary privilege on it. The content of the table is of course specific to the session using it.

Creating Global Temporary Tables

Global temporary tables are permanent database objects that are stored on disk and visible to all sessions connected to the database.

Yes, you need to grant the privileges on it for other users or roles, if you want this to be accessible to them.

Oracle did not have other any other type of temporary table, up until 18c, which introduced Private Temporary Tables, which are similar to temporary tables in SQL Server:

Creating Private Temporary Tables

Private temporary tables are temporary database objects that are dropped at the end of a transaction or session. Private temporary tables are stored in memory and each one is visible only to the session that created it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.