Re: Change ownership of tables?

On 7/23/05, Neil Truby <neil.truby@ardenta.com> wrote: > "Jonathan Leffler" <jleffler@earthlink.net> wrote in message > news:o2iEe.2601$Uk3.2514@newsread1.news.pas.earthlink.net... > > Everett Mills wrote: > >> Sorry, guys I misread that. You could update the systables owner field, > >> but that's dangerous. How about a feature request for a new statement: > >> alter database/table/index/view/trigger/procedure owner "new_owner"? > > > > IIRC, there might be such a statement in DB2, called TRANSFER OWNER or > > thereabouts, that can change the ownership of any owned object in the > > DBMS. It isn't in IDS 10.00. It's on the list of 'nice to have but we do > > not have enough manpower to do it' features. > > Thanks Jonathan. As a matter of interest then, the implication is that it's > a more complex fetaure to engineer than it would appear (it appears you just > need to update a few system tables)? Well, yes...one of the main other problems is what to do with that rat's nest of permissions that were granted by the original owner and their delegates. Sometimes, it is easy, but the most general cases are pretty horrific. OK, that is still updating a few system tables, but the updates are hard. Another issue is permission to do the operation in the first place - who is authorized to change ownership? Can the change be made unilaterally by the old owner, or does the incoming owner have to agree to own the object? Come to that, does the old owner automatlcally have permission to change ownership, for example, of a DBA-privileged stored procedure? Can a DBA decide that the current owner is incorrect and transfer ownership without consulting the current owner? So, while the fundamentals are relatively straight-forward, though there is information in places other than the system catalog that also has to be changed. One of the main reasons why you are counselled not to try running an update on systables is because that can lead to inconsistencies in the data, which makes recovery later from a disaster unreliable. sending to informix-list