Quick Synonym Question 2

[THoey]

Is there a privilege that can be assigned to one schema to allow it to modify, or drop and recreate a synonym in another schema?

If more detail is needed, it is a little convoluted, but here goes:

A developer created a schema for an 24 X 7 application. For security reasons, I have been asked to create another schema that has all the necessary roles and privileges to use these objects, without having complete control to drop all objects.

One of the objects created by the developer is a set of tables, say ConfigTable1 and ConfigTable2. On occaision, they need to update this table with a large data load. Since this is an always live system, and since they are worried about being able to fall back to the previous version of the data in case of error, that is the reason for ConfigTable1 and ConfigTable2.

While the application is using ConfigTable1, they can empty and load ConfigTable2, and then cutover to the new data. This cutover is done using a synonym called ConfigTable that points to the current version of the table for the application to use.

The problem goes back to the security issue and creating the user account. I am looking for a permission or privilege that can be granted to this "user" account that will allow it to drop and then recreate the synonym that is an object of the application owner account.

Told you it was convoluted. Any suggestions?

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[Beilstwh]

A much simplier way to do that is to do the following steps

1) delete from the table.
2) insert the new date
3) commit

Your users will never see the new data until you do the commit. If there is ANY errors during the load, simply issue a rollback and they will never see the new data and the old data will still be there.

Bill
Oracle DBA/Developer
New York State, USA

 

[carp]

Terry -
A possible problem you may run into with your approach is that when you drop and recreate the synonym, any code that is dependent on that synonym will immediately become invalid and require recompilation.
Bill's suggestion is quite valid - just don't do a commit until you are sure the load has completed successfully. If, on the other hand, you cannot convince your drivers that this is an acceptable approach, then I would recommend that you simply copy your table to another table, THEN do the updates. That way, if something goes wrong, you have your original data still intact (albeit any user activity that went on while you were doing your updates might be lost).
Another thing you might want to explore is Flashback, which allows you to restore a table to its state at a previous point in time (conceptually, it's like being able to do a rewind on your DML and DDL, even after a commit).

 

[THoey]

Thanks Y'all for your suggestions. I am going to have to take this back to the developers, as I don't get much say in how they do it. I didn't when they designed it in the first place, and I still don't now. Just my job to fix what they break or poorly design.

I love my job...

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...