Re: Changing of owner of a table
Thanks to every one for their time to reply my query, however, I did a test to update systable in one of our test environment and guess what ??????? It changed the owner BUT I could not achived the desired result. Wait.. Wait !!!!!! I will explain "desired results". Infact I wanted to create a role and when I treid to create role, I get an error as: "19800: Role name already exists as a user or role." I then found that some table have the owner with the same name which I intend to setup as role. There are some reasons to setup role with that name only. Having done the update (owner.systables), I still get the same error. Then I found systabauth also have grantor as that user. The only thing left now is to bounce engine and check again. Interestingly, I dropped tables and re-created but still I get 19800 error (after owner.systables update) . Pl. note so far I have not bounced the engine. Anyway. I agree with all of you that updating system tables is NOT A GOOD IDEA indeed..... Thanks again. Hari "Hari Gupta" <hariog@yahoo.com> wrote in message news:SaEta.2$at2.88@news.optus.net.au... > Hi Folks, > > I am sure there have been many posts for this issue in the past but I could > not find answer to my query below. > With Informix 9.21 on Linux 6.2 - How safe is (in terms of integrity of > system catalogue) to update systables > for changing owner of a table ? Also is this update for changing owner of a > table supported by IBM ? > > Thanks in advance. > > Hari Gupta > >
I don't think that this has anything to do with the update on systables.owner. I tested the behaviour on Suse Linux 8.0 with IDS 9.30.UC3 and was able to change systables.owner and also to create a role with the same name, for example: update informix.systables set owner = 'mytest' where tabid > 99 and tabtype = 'T'; create role 'mytest'; Be aware of the following restrictions regarding 'create role': ***************************************************************************** From the Informix SQL Syntax-Guide: The role name is an authorization identifier. It cannot be a user name that is known to the database server or to the operating system of the database server. The role name cannot already be listed in the username column of the sysusers system catalog table, nor in the grantor or grantee columns of the systabauth, syscolauth, sysprocauth, and sysroleauth system catalog tables. Also, the role name cannot already be listed in the grantor or grantee columns of the sysfragauth system catalog table ***************************************************************************** -- Best regards Eric -- IT-Consulting Herber WWW: http://www.herber-consulting.de Email: eric@herber-consulting.de *********************************************** Download the IFMX Database-Monitor for free at: http://www.herber-consulting.de/BusyBee *********************************************** Hari Gupta wrote: > Thanks to every one for their time to reply my query, however, I did a test > to update systable in one of our test environment and guess what ??????? > It changed the owner BUT I could not achived the desired result. > > Wait.. Wait !!!!!! I will explain "desired results". > Infact I wanted to create a role and when I treid to create role, I get an > error as: > "19800: Role name already exists as a user or role." > I then found that some table have the owner with the same name which I > intend to setup as role. There are some reasons to setup role with that name > only. > Having done the update (owner.systables), I still get the same error. Then I > found systabauth also have grantor as that user. > The only thing left now is to bounce engine and check again. > > Interestingly, I dropped tables and re-created but still I get 19800 error > (after owner.systables update) . Pl. note so far I have not bounced the > engine. > Anyway. I agree with all of you that updating system tables is NOT A GOOD > IDEA indeed..... > > Thanks again. > > Hari > > "Hari Gupta" <hariog@yahoo.com> wrote in message > news:SaEta.2$at2.88@news.optus.net.au... > >>Hi Folks, >> >>I am sure there have been many posts for this issue in the past but I > > could > >>not find answer to my query below. >>With Informix 9.21 on Linux 6.2 - How safe is (in terms of integrity of >>system catalogue) to update systables >>for changing owner of a table ? Also is this update for changing owner of > > a > >>table supported by IBM ? >> >>Thanks in advance. >> >>Hari Gupta >> >> > > >
Thanks to Eric for clear & detailed explaination. It makes clear now. We have a unix user-id with the same name as role which was causing the problem. Hari "Eric Herber" <eric@herber-consulting.de> wrote in message news:3EB8ACD8.7020503@herber-consulting.de... > I don't think that this has anything to do with > the update on systables.owner. > > I tested the behaviour on Suse Linux 8.0 with IDS 9.30.UC3 > and was able to change systables.owner and also to create > a role with the same name, for example: > > update informix.systables > set owner = 'mytest' > where tabid > 99 > and tabtype = 'T'; > > create role 'mytest'; > > > Be aware of the following restrictions regarding 'create role': > > **************************************************************************** * > From the Informix SQL Syntax-Guide: > > The role name is an authorization identifier. It cannot be a user name > that is known to the database server or to the operating system of the > database server. The role name cannot already be listed in the username > column of the sysusers system catalog table, nor in the grantor or > grantee columns of the systabauth, syscolauth, sysprocauth, and > sysroleauth system catalog tables. Also, the role name cannot already be > listed in the grantor or grantee columns of the sysfragauth system > catalog table > **************************************************************************** * > > -- > > Best regards > > Eric > -- > IT-Consulting Herber > WWW: http://www.herber-consulting.de > Email: eric@herber-consulting.de > > *********************************************** > Download the IFMX Database-Monitor for free at: > http://www.herber-consulting.de/BusyBee > *********************************************** > > > Hari Gupta wrote: > > Thanks to every one for their time to reply my query, however, I did a test > > to update systable in one of our test environment and guess what ??????? > > It changed the owner BUT I could not achived the desired result. > > > > Wait.. Wait !!!!!! I will explain "desired results". > > Infact I wanted to create a role and when I treid to create role, I get an > > error as: > > "19800: Role name already exists as a user or role." > > I then found that some table have the owner with the same name which I > > intend to setup as role. There are some reasons to setup role with that name > > only. > > Having done the update (owner.systables), I still get the same error. Then I > > found systabauth also have grantor as that user. > > The only thing left now is to bounce engine and check again. > > > > Interestingly, I dropped tables and re-created but still I get 19800 error > > (after owner.systables update) . Pl. note so far I have not bounced the > > engine. > > Anyway. I agree with all of you that updating system tables is NOT A GOOD > > IDEA indeed..... > > > > Thanks again. > > > > Hari > > > > "Hari Gupta" <hariog@yahoo.com> wrote in message > > news:SaEta.2$at2.88@news.optus.net.au... > > > >>Hi Folks, > >> > >>I am sure there have been many posts for this issue in the past but I > > > > could > > > >>not find answer to my query below. > >>With Informix 9.21 on Linux 6.2 - How safe is (in terms of integrity of > >>system catalogue) to update systables > >>for changing owner of a table ? Also is this update for changing owner of > > > > a > > > >>table supported by IBM ? > >> > >>Thanks in advance. > >> > >>Hari Gupta > >> > >> > > > > > > >