My Oracle Support Banner

How To Isolate a Table To Run Update Without Losing Granted Roles (Doc ID 1068753.6)

Last updated on APRIL 21, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 8.1.7.4 to 11.2.0.1 [Release 8.1.7 to 11.2]
Information in this document applies to any platform.
Checked for relevance on 04-Sep-2010


Purpose

- You need to update a table.
- You need to limit access to the table during the time of the update.
- You do not have a window to startup the database in restricted mode to do the update: users need to continue to access other tables in the application schema.
- The application tables are owned by a single schema/account.
- You have roles granted to roles and these roles are granted to users to allow users to access tables in the application schema.

How do you isolate this one table to perform the update and still allow other users to access tables in the application schema without disturbing users access/privileges?

Scope

You try to move the table to another schema, update the table and move the table back to the original schema. You first try to "create table as select" in another schema. This method is not successful because the original table is still accessible to the users.

You try to export the table into another schema, update the table, and import back into the original schema. However, after exporting the table you check the privileges views ... dba_tab_privs, role_role_privs,  session_roles, and session_privs. The roles and privileges were not exported. On table exports roles and role grants are not exported. When exporting at the table level only the owner's table grants are exported. You would need to rerun the scripts to recreate the appropriate privileges for the application user running the update. After rerunning scripts to recreate privileges, updating the table, you truncate the original table and import the updated table.

There a simpler way to do this: this bulletin explains the best method.

Details

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.