How to Change Ownership of Tables
Follow these steps to change the ownership of a table:
1. Generate the executable scripts.
2. Execute the copy.out script, copying the table as the current owner.
3. Execute the copy.in script, copying the table back in as the new owner.
These tasks can be accomplished using the copydb and sql commands.
For details on performing these steps in VDBA, see online help.
For more information on copying tables and other database objects, see the chapter “Loading and Unloading Databases.”
Example: Change Ownership of Table
The following procedure changes the ownership of any table from the currently selected user, John, to the user named dba.
1. Create the executable scripts:
copydb -ujohn dbname tablename
(To do this in VDBA: Open the Generate copy.in and copy.out dialog box for the database in which the table is located. Click Tables to invoke the Specify Tables dialog box. Enable the check box for the table whose ownership you want to change and click OK. Click OK to create the copy scripts.)
2. At the operating system prompt, enter the following command to copy the table from the database mydb into an intermediate binary file in the current directory:
sql -ujohn mydb <copy.out
3. Edit the copy.in file to change the table reference in the GRANT statement from john.<table> to <dbaname>.<table>. If you do not do this, the GRANT statements refer to John's table.
Note: The GRANT statements are present only if grants are defined for the table being copied.
There are now two copies of the table, one owned by the user john and the other owned by the user dba. In the usual case, John’s version is no longer needed and can be removed. For example, the user john (or another user impersonating john) can easily drop the table in VDBA. For more information, see online help.
4. At the operating system prompt, enter the following command to copy the table from the intermediate binary file in the database as user dba:
sql -udba mydb <copy.in
You also need to grant access permissions to the new table owned by user dba. For more information, see the Security Guide.
Last modified date: 01/30/2023