Re: Table Ownership, Changing

}I can hear Jonathon L. now. In fact, I'm probably hearing an echo }from the last time permissions and ownerships was 'discussed'. } }The ringing in my ears goes something like this. } }NO!!!!!!!!!!!!! }NOT!!!!!!!!!!!! }NEVER!!!!!!!!!!!!!! } }WHAT PART OF NO DON'T YOU UNDERSTAND???????? Hi, Actually, I told the guy by private email: }Date: Wed Aug 10 08:43:18 1994 }From: johnl (Jonathan Leffler) }To: brian@anasazi.com }Subject: Re: Table Ownership, Changing } }I didn't say it, but ... } }As the principal DBA (database owner/creator) or as Informix, }you can update the owner column in systables. Very convenient. It gets around problems caused by indisciplined control of who does what with the database design. Nowadays, I normally get around it by having .sql scripts which explicitly state the owner of everything as they create the tables (that means that the official DBA user for the database owns the table, the constraint names, and the indexes, and the table owner grants the privileges). But in less disciplined environments, and to clean up before going into production, then I will certainly change owners directly in SysTables. It can also be necessary to change the table owner if some user who is not supposed to be authorised creates a table and you need to prevent them from using the table because it simply shouldn't be in the database. I don't particularly encourage it, which is why I replied to him direct rather than through comp.databases.informix, but it can be done and there are sound reasons for occasionally wanting to do so. Dave Mascorro's refinement of changing the .dat and .idx files in SE was an important point I'd forgotten -- I work almost exclusively with OnLine (to the point where I have 3 different versions of OnLine running on my machine at the moment!). The distinction between the last set of permissions discussions and this one is that I'm assuming that the permissions on all the files are set correctly, given that 'fred', say, owns the table. But it should be owned by 'bill', so the permissions need to be changed so that 'bill' does own the table. This means that the SysTables entry does need to be changed, and so does the ownership of the .dat and .idx files if using SE, but the permissions on the file should simply change from 'fred informix -rw-rw----' to 'fred informix -rw-rw----'. Yours amusedly, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> }Date: Wed, 10 Aug 94 12:05:13 MDT }From: george@talon.ftc.scs.ag.gov }Subject: Re: Table Ownership, Changin }X-Informix-List-Id: <list.4498> } }} From: Brian Lee }} >I am looking for ideas on changing table ownerships. What I need to do is }} >change the ownership of all the tables in my database. }} >Does anyone have any comments on this subject? }} }} Brian, }} I don't recommend changing the ownership of tables, however, I have in the past }} done this by executing the following SQL statement: }} }} update systables set owner = "newusername" where tabname = "targettable" }} }} You must also change the file permissions of the .dat file found in the .dbs }} directory of your database to the newusername (i.e. chown targettable.dat }} newusername). }} }} Dave Mascorro }} dave@oncology.uthscsa.edu }} University of Texas Health Science Center at San Antonio } }I can hear Jonathon L. now. In fact, I'm probably hearing an echo }from the last time permissions and ownerships was 'discussed'. } }The ringing in my ears goes something like this. } }NO!!!!!!!!!!!!! }NOT!!!!!!!!!!!! }NEVER!!!!!!!!!!!!!! } }WHAT PART OF NO DON'T YOU UNDERSTAND???????? } }Sorry Jonathon, I could't resist. I have had similar problems with }folks I deal with. If the truth be told, I am sure that I have been }guilty of the same thing. Take a big enough club up along side my head }and I learn. ;-) } }George Teachman }USDA-SCS }Fort Collins, CO