Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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.
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.
