How to Change Ownership of a Database
At times, you may need to change the ownership of an entire database, for example, when a database moves from development to production or when the current DBA moves to a different project.
To change the ownership of a database, you must have permission to impersonate another user and to update system catalogs.
To change the ownership of a database, follow this process:
Note: In this process, the user name of the current owner is user_old and user name of the new owner is user_new.
1. Be sure that there is a current backup of the database, preferably a checkpoint. For more information, see the chapter “Performing Backup and Recovery.” If there is a problem in changing ownership, restore the original database.
2. Log in as the current DBA of the database.
3. Create a temporary working directory to hold the files that can be created. Move to that directory. Be certain that the temporary directory is not in the path pointed to by ING_ABFDIR or you will lose your unloaded files during destroydb.
4. Create the unload and reload scripts using VDBA.
Note: If you are also moving the database to a machine with a different processor you must unload the database with the Create Printable Data Files option enabled. Doing so produces data files in a portable, ASCII format.
6. On Linux, change permissions, as follows, so the new database owner can work with these files:
chmod 744 *
7. Destroy the original database by dropping it from within VDBA. For more information, see online help.
8. Log in as the new database owner or impersonate the new owner by selecting the appropriate user name from the Users branch in the Virtual Nodes toolbar in VDBA.
9. Create a fresh database in VDBA, which can be owned by the user chosen is Step 8. For details, see online help.
The reload script contains a line for each user who owns objects (tables, indexes, or views).
11. Edit the reload script:
a. Change those lines that reload objects with the user flag of the old owner, so that they can load with the user flag of the new owner.
b. Take ownership of the database objects of any or all users by changing each user line so that it loads with the new user flag.
Caution: The user flag for user $ingres must never be changed. $ingres is a special user ID that is used internally for the system catalogs.
12. Reload the database by executing the reload script. For more information, see online help.
13. Run system modification to update the query optimizer information. For more information, see the chapter “Using the Query Optimizer.”
At this point all objects (including tables, indexes, and views) are owned by the new DBA; however, database objects (forms, reports, applications, and so on) need special attention to make them accessible to everyone, because they are still owned by their old owners.
14. Update the ii_objects catalog to change ownership of these objects to the new DBA:
a. Make sure that the new DBA does not already own any objects (forms, reports, and so on) with names identical to those you are about to reassign. If there are two identically named objects for the same owner, the original is overwritten and destroyed.
Run the following query to select the database objects for the old owner, user_old:
select object_id, object_owner
from ii_objects
where object_owner = 'user_old';
Run the following query to select the database objects for the new owner, user_new:
select object_id, object_owner
from ii_objects
where object_owner = 'user_new';
b. Compare the object list for the new owner with the list for the new owner. If duplicates are found, eliminate them by deleting or copying and renaming the objects.
c. After you have copied and renamed or destroyed any duplicates, rerun the queries to ensure that there are no longer any duplicate objects.
15. Execute the following query to transfer ownership of existing database objects, for example from the VDBA SQL Scratchpad window:
update ii_objects set object_owner = 'user_new'
where object_owner = 'user_old';
Note: You can execute this query from a terminal monitor only if you invoke it using the +U flag, which allows you to update the system catalogs and secondary indexes.
16. Test the database and remove the temporary working directory and the associated work files.