RE: Change ownership of tables?

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"? --EEM > -----Original Message----- > From: Link, David A. [mailto:DALink@west.com] > Sent: Thursday, July 21, 2005 8:05 PM > To: Everett Mills; Neil Truby; informix-list@iiug.org > Subject: RE: Change ownership of tables? > > Everett, > > Neil was asking about if there was a way without exporting it. You should > be able to just issue a rename table statement from what I see in the > Guide to SQL - Reference... See the example below.... > > RENAME TABLE > Use the RENAME TABLE statement to change the name of a table. > Syntax > Usage > To rename a table, you must be the owner of the table, or have the ALTER > privilege on the table, or have the DBA privilege on the database. > An error occurs if old_table is a synonym, rather than the name of a > table. > You cannot change the table owner by renaming the table. An error occurs > if > you try to specify an owner. qualifier for the new name of the table. ♦ > A user with DBA privilege on the database can change the owner of a table, > if the table is local. Both the table name and owner can be changed using > one > command. > The following example uses the RENAME TABLE statement to change the > owner of a table: > RENAME TABLE tro.customer TO mike.customer > When the table owner is changed, you must specify both the old owner and > new owner. > + > Element Purpose Restrictions Syntax > new_table New name for old_table Cannot include an owner. qualifier here. > Identifier, p. 4-189 > old_table Name that new_table replaces Must be the name (not the synonym) > of a > table that exists in the current database. > Identifier, p. 4-189 > owner Current owner of the table Must be the owner of the table. Owner, p. > 4-234 > new_owner The new owner of the table Must have DBA privilege on the > database > (XPS) > Owner, p. 4-234 > old_table TO RENAME TABLE new_table > owner > > -----Original Message----- > From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] > On Behalf Of Everett Mills > Sent: Thursday, July 21, 2005 7:13 PM > To: Neil Truby; informix-list@iiug.org > Subject: RE: Change ownership of tables? > > Neil- > I had a similar situation some years back. What I did was to vi > the .sql file for the database (in the .exp directory) and type this: > > :g/\\"root\\"/s//\\"informix\\"/g > :wq > > That should do it, but make a backup of the .sql file first, just in > case... > > --EEM > > > -----Original Message----- > > From: Neil Truby [mailto:neil.truby@ardenta.com] > > Sent: Thursday, July 21, 2005 5:48 PM > > To: informix-list@iiug.org > > Subject: Change ownership of tables? > > > > IDS 9.40 on AIX > > > > Half the tables in the database are owned by root and half by > informix. > > > > Without an export/import is there any straightforward way of changing, > > say, > > the root ones to owner informix? > > > > thanks > > Neil > > > > > sending to informix-list sending to informix-list

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. >>-----Original Message----- >>From: Link, David A. [mailto:DALink@west.com] >>Sent: Thursday, July 21, 2005 8:05 PM >>To: Everett Mills; Neil Truby; informix-list@iiug.org >>Subject: RE: Change ownership of tables? >> >>Everett, >> >>Neil was asking about if there was a way without exporting it. You should >>be able to just issue a rename table statement from what I see in the >>Guide to SQL - Reference... See the example below.... >> >>RENAME TABLE >>Use the RENAME TABLE statement to change the name of a table. >>Syntax >>Usage >>To rename a table, you must be the owner of the table, or have the ALTER >>privilege on the table, or have the DBA privilege on the database. >>An error occurs if old_table is a synonym, rather than the name of a >>table. >>You cannot change the table owner by renaming the table. An error occurs >>if >>you try to specify an owner. qualifier for the new name of the table. ♦ >>A user with DBA privilege on the database can change the owner of a table, >>if the table is local. Both the table name and owner can be changed using >>one >>command. >>The following example uses the RENAME TABLE statement to change the >>owner of a table: >>RENAME TABLE tro.customer TO mike.customer >>When the table owner is changed, you must specify both the old owner and >>new owner. >>+ >>Element Purpose Restrictions Syntax >>new_table New name for old_table Cannot include an owner. qualifier here. >>Identifier, p. 4-189 >>old_table Name that new_table replaces Must be the name (not the synonym) >>of a >>table that exists in the current database. >>Identifier, p. 4-189 >>owner Current owner of the table Must be the owner of the table. Owner, p. >>4-234 >>new_owner The new owner of the table Must have DBA privilege on the >>database >>(XPS) >>Owner, p. 4-234 >>old_table TO RENAME TABLE new_table >>owner >> >>-----Original Message----- >>From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] >>On Behalf Of Everett Mills >>Sent: Thursday, July 21, 2005 7:13 PM >>To: Neil Truby; informix-list@iiug.org >>Subject: RE: Change ownership of tables? >> >>Neil- >> I had a similar situation some years back. What I did was to vi >>the .sql file for the database (in the .exp directory) and type this: >> >>:g/\\"root\\"/s//\\"informix\\"/g >>:wq >> >>That should do it, but make a backup of the .sql file first, just in >>case... >> >> --EEM >> >> >>>-----Original Message----- >>>From: Neil Truby [mailto:neil.truby@ardenta.com] >>>Sent: Thursday, July 21, 2005 5:48 PM >>>To: informix-list@iiug.org >>>Subject: Change ownership of tables? >>> >>>IDS 9.40 on AIX >>> >>>Half the tables in the database are owned by root and half by >> >>informix. >> >>>Without an export/import is there any straightforward way of changing, >>>say, >>>the root ones to owner informix? >>> >>>thanks >>>Neil >>> >> >> >>sending to informix-list > > > > sending to informix-list -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.01 -- http://dbi.perl.org/

"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)?