DataDeploy: override exists sql for IWOV_IDMAPS

tanguyr

Hello,

Is it possible to control the sql used by datadeploy to determine if the table IWOV_IDMAPS exists?

I have found the file create_table_overrides.xml which allows me to override the sql used to create this table if it doesn't exist, i'm looking for an equivalent file (or functionality) used to determine if it exists in the first place. I seem to remember reading something about this, either here or in the docco, but i can't find it...

Regs,
/t

tanguyr

OK,

A little more information on my problem, in case someone has another idea:

The company where i work has a strict policy regarding the setup of database apps: the user which is used to connect to the database should have the minimum privileges needed to carry out a task. Usually what we do is:

- each app has its own db user account - the "app owner". All app-related db objects (tables, views, etc) belong to this user. This user account is locked - you cannot use it to connect to the database.
- each app has its own role. The various privs needed to operate the app (select on table x, insert on table y, etc) are granted to this role.
- finally, this role is granted to a different user account - the "app user", which is allowed to connect to the db.

This way, even if you compromise the "app user" account, you can't do things like drop tables, delete records, etc.

To make a long story short [too late], i'm trying to use datadeploy to sync a whole workarea full of DCRs to the database. I'm using a custom schema, and i have created all the tables as the "app owner". The problem is that i want to run the datadeploy as the "app user". I've used the "exists-sql" element in the datadeploy to override the sql used to check if the custom tables exist, but i still have a problem with the table IWOV_IDMAPS. By default, the sql used to check if this table exists is something like

SELECT * FROM USER_TABLES WHERE TABLE_NAME LIKE 'IWOV_IDMAPS'

... which returns no rows (the app user account doesn't own anything). Even worse, DataDeploy then tries to create this table, and that fails as the app user isn't allowed to create tables. Now, i'm totally willing and able to create this table as the app owner - i need to figure out some way for DataDeploy to see this table when running as the app user, so that it doesn't try to recreate it.

If anyone here has ever come up against this before or has any ideas for what i could do to fix this problem, i'd really appreciate any light you could shed on the matter.

Regards,
/t