DSXchange

The only mechanism you can use to update UV_USERS is to use GRANT and REVOKE statements. Nothing else will work. This means, in turn, that you need to be able to log in as the user who has been granted DBA privilege, as recorded in that UV_USERS table.

That having been done, you will also find that the other tables can not be updated using UPDATE. I do not believe that the owner of tables can be changed. Any tables, not just the ones in the CATALOG schema.

Ray,

When writing to a hashed file using the PATH method, what would datastage use as permissions? would it not just check file level permissions? or would it know that I am trying to write to the UV_USERS file which is one of it's system tables and prevent it?

If it is the case that DS knows this is a sytem table, could I not take a copy of this to another location and write to the copy, then move the copy back into $DSHOME/sql/catalog overwriting the current version?

Ray one other question

If I was to only update the prodUser so they have DBAUTH privaledges would that be sufficient for the system to work correctly, i.e. prodUser to be able to create/delete projects, or does the prodUser need to be owner for the Catalog tables?

The six tables in the CATALOG schema have an extra level of protection (a bit set in the file header) that prevents their being changed other than by appropriate DDL statements.

Copying these tables can not be guaranteed to work, because they contain an internal Security and Integrity Constraints Area (SICA). I am uncertain (and unable to check today) as to whether the SICA contains the expected pathname of the schema. Certainly the entries in UV_SCHEMA do; you may need to use VERIFY.SQL (as DBA) to repair the CATALOG schema after such a move.

It's probable that granting DBA privilege to all users would allow things to happen. But that's a dangerous course. Do you still have the old UV_USERS table? Can it be repaired? Could it be restored from a system backup (including its three subfiles DATA.30, OVER.30 and .Type30)?

nick.bond wrote:Unfortunately there is no system backup because against advice no backups were taken as it was deemed quicker to perform reinstall than get a system restore. I think the decision was based on the perceived service provided by the company that looks after the servers (no names but they also make sauce).

Now this is a good informational post, not something you see everyday. Thanks for the great info guys. This is definately going in my favorites bucket :D