Re: Change table owner
Revival of an old question: can a table's owner be changed in Informix without dropping and recreating it? The thread confirms there is no supported command; the classic workaround remains recreating the table with the right owner, copying data, and rebuilding indexes/constraints. Participants discuss prevention instead — CREATE SCHEMA AUTHORIZATION (limited, doesn't cover ALTER TABLE), naming constraints explicitly so they get the table owner, doing all DDL as one application/schema user, and using myschema's -a/-O/--set-owner options when moving schemas between environments. Two IBM RFEs are cited for voting, DB2's TRANSFER OWNERSHIP is noted as precedent, and an unsupported hack (updating owner columns in systables/sysconstraints/sysobjstate as informix, then restarting) is mentioned with caution. No feature or official fix is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi everyone, I know this is an old thread, but I have just been terribly inconvenienced by not being able to easily change the owner of some tables to informix that were created by a developer in his own name. To help I went and looked up the 'Request For Enhancement' entries for it, I found two related requests if you felt like helping by voting: https://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=33828 Headline: Change owner of objects ID: 33828 Description: There should be a way to change the owner of database objects. For now, once a table, index or procedure is created, the owner is stored and cannot be changed. Votes: 4 https://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=91585 Headline: Informix should be able to change owner on tables and any other objects ID: 91585 Description: If user=informix creates all database and all tables, then programmer creates table but user=informix cannot change it. Only drop it. Votes: 15 (there was also a declined one ID:40195, hopefully they favourably consider the current requests, and possibly even amalgamate the votes). Thanks for your help. Regards, Bryce Stenberg. >On Tuesday, 8 April 2014 at 5:18 PM, Fernando Nunes wrote: > ..... >DON'T PLAY WITH FIRE! >Create an RFE for it, if it does not exist yet. > >Regards. > >> ..... >>> ..... >>>> >>>> On Monday, 19 January 2009 at 11:32 AM, Art Kagel wrote: >>>> >>>>No. Alternative: >>>> >>>>- create new table with temp name and correct owner >>>> >>>>- copy data >>>> >>>>- drop old table >>>> >>>>- rename new table >>>> >>>>- establish indexes, constraints >>>> >>>>- re-establish referencing constraints on other tables >>>> >>>>Art >>>> >>>>>On Monday, 19 January 2009 at 10:55 AM, Georges Martin < >>>>>georges_martin_1@hotmail.com> wrote: >>>>> >>>>> Hi Folks, >>>>> is there a fast way to change a table's owner without dropping and >>>>> recreating >>>>> the table? >>>>> Thanks
Hi,
I hope readers will excuse this longish post but this is an area of
frustration for me too. I had already voted for request #2 but have added my
vote to #1.
With an evolving schema, this whole area requires constant vigilance to keep
in order and in the worst case it can lead to security holes. Avoiding the
problem in the first place has to be part of the solution as well as providing
a means to fix it afterwards. In an RDBMS like Oracle where table ownership is
interpreted much more strictly than a non-ANSI Informix database, there is a
simple command you can run to ensure all objects in your session are created
with the correct owners: "alter session set current_schema=;". I have added a
comment to one of the RFEs to this effect.
There is a wrapper for "create table" statements "create schema authorization
<username>" which kind of does the same thing but it only works when creating
a table and its scope is the next statement only.
Instead you have to employ a lot of different methods to ensure that tables,
indices, constraints etc. have consistent ownership or you avoid the problem
by always deploying as the schema owner. (I don't consider deploying as the
"schema owner" a good solution because you ideally want separation between the
user ids for the apps, users and application schema.)
Another example is adding a column with a constraint. If I run under my own
user id:
alter table X add col1 int default 0 not null;This creates a "not null" constraint, where it can be seen in sysconstraints
that I am the owner and not the existing owner of the table. The only way
around this is a named constraint, e.g.
alter table X add col1 int default 0 not null constraintschemaowner.constrname;
There is a more subtle version of this where you modify a column that already
has a "not null" constraint.
The problem with such a feature is making a business case for it as the
product continues to work just fine despite these problems. I guess the main
one is the time I have to put into checking upgrade scripts.
Ben.
What's wrong with DBCREATE_PERMISSION?
Get Outlook for iOS<https://aka.ms/o0ukef>;
________________________________
From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of BENJAMIN
THOMPSON <benjamin.thompson@skybettingandgaming.com>
Sent: Wednesday, June 14, 2017 10:59:03 AM
To: ids@iiug.org
Subject: Re: Change table owner [39371]
Hi,
I hope readers will excuse this longish post but this is an area of
frustration for me too. I had already voted for request #2 but have added my
vote to #1.
With an evolving schema, this whole area requires constant vigilance to keep
in order and in the worst case it can lead to security holes. Avoiding the
problem in the first place has to be part of the solution as well as providing
a means to fix it afterwards. In an RDBMS like Oracle where table ownership is
interpreted much more strictly than a non-ANSI Informix database, there is a
simple command you can run to ensure all objects in your session are created
with the correct owners: "alter session set current_schema=;". I have added a
comment to one of the RFEs to this effect.
There is a wrapper for "create table" statements "create schema authorization
<username>" which kind of does the same thing but it only works when creating
a table and its scope is the next statement only.
Instead you have to employ a lot of different methods to ensure that tables,
indices, constraints etc. have consistent ownership or you avoid the problem
by always deploying as the schema owner. (I don't consider deploying as the
"schema owner" a good solution because you ideally want separation between the
user ids for the apps, users and application schema.)
Another example is adding a column with a constraint. If I run under my own
user id:
alter table X add col1 int default 0 not null;This creates a "not null" constraint, where it can be seen in sysconstraints
that I am the owner and not the existing owner of the table. The only way
around this is a named constraint, e.g.
alter table X add col1 int default 0 not null constraintschemaowner.constrname;
There is a more subtle version of this where you modify a column that already
has a "not null" constraint.
The problem with such a feature is making a business case for it as the
product continues to work just fine despite these problems. I guess the main
one is the time I have to put into checking upgrade scripts.
Ben.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Spokey, Unless I am missing something I don't see how it addresses the problem. It only controls who can create a database whereas I am discussing modifying an existing one. I am already controlling who can make schema changes through "grant DBA". I believe the only neat solution is to deploy the changes as the owner of the schema but this requires this user to have DBA privileges and means using a shared account which makes auditing harder. Ben.
Gotcha. Get Outlook for iOS<https://aka.ms/o0ukef>; ________________________________ From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of BENJAMIN THOMPSON <benjamin.thompson@skybettingandgaming.com> Sent: Wednesday, June 14, 2017 11:09:41 AM To: ids@iiug.org Subject: Re: Change table owner [39373] Spokey, Unless I am missing something I don't see how it addresses the problem. It only controls who can create a database whereas I am discussing modifying an existing one. I am already controlling who can make schema changes through "grant DBA". I believe the only neat solution is to deploy the changes as the owner of the schema but this requires this user to have DBA privileges and means using a shared account which makes auditing harder. Ben. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
My solution in production is to only allow informix to be dba in production
and require sudo to change your id which documents the changein status. Not
ideal, but ...
Moving the schema from dev to prod I fix using myschema rather than
dbschema. The -a, -O, and --set-owner options fix the owners in the schema
when it is exported to prod.
Art
On Jun 14, 2017 05:18, "BENJAMIN THOMPSON" <
benjamin.thompson@skybettingandgaming.com> wrote:
> Hi,
>
> I hope readers will excuse this longish post but this is an area of
> frustration for me too. I had already voted for request #2 but have added
> my
> vote to #1.
>
> With an evolving schema, this whole area requires constant vigilance to
> keep
> in order and in the worst case it can lead to security holes. Avoiding the
> problem in the first place has to be part of the solution as well as
> providing
> a means to fix it afterwards. In an RDBMS like Oracle where table
> ownership is
> interpreted much more strictly than a non-ANSI Informix database, there is
> a
> simple command you can run to ensure all objects in your session are
> created
> with the correct owners: "alter session set current_schema=;". I have
> added a
> comment to one of the RFEs to this effect.
>
> There is a wrapper for "create table" statements "create schema
> authorization
> <username>" which kind of does the same thing but it only works when
> creating
> a table and its scope is the next statement only.
>
> Instead you have to employ a lot of different methods to ensure that
> tables,
> indices, constraints etc. have consistent ownership or you avoid the
> problem
> by always deploying as the schema owner. (I don't consider deploying as the
> "schema owner" a good solution because you ideally want separation between
> the
> user ids for the apps, users and application schema.)
>
> Another example is adding a column with a constraint. If I run under my own
> user id:
> alter table X add col1 int default 0 not null;> This creates a "not null" constraint, where it can be seen in
> sysconstraints
> that I am the owner and not the existing owner of the table. The only way
> around this is a named constraint, e.g.
> alter table X add col1 int default 0 not null constraint> schemaowner.constrname;
>
> There is a more subtle version of this where you modify a column that
> already
> has a "not null" constraint.
>
> The problem with such a feature is making a business case for it as the
> product continues to work just fine despite these problems. I guess the
> main
> one is the time I have to put into checking upgrade scripts.
>
> Ben.
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
Note that DB2 has a TRANSFER OWNERSHIP statement: https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.sql.re f.doc/doc/r0021665.html?pos=2 There are ramifications to transferring ownership. That's not to say it shouldn't be done (it should), but there are more twists, knots, kinks involved than you'd expect at first glance. On Tue, Jun 13, 2017 at 5:35 PM, BRYCE STENBERG <bryce@hrnz.co.nz> wrote: > I know this is an old thread, but I have just been terribly > inconvenienced by not being able to easily change the owner of some > tables to informix that were created by a developer in his own name. > > To help I went and looked up the 'Request For Enhancement' entries > for it, I found two related requests if you felt like helping by voting: > > https://www.ibm.com/developerworks/rfe/execute? > use_case=viewRfe&CR_ID=33828 > Headline: Change owner of objects > ID: 33828 > Description: There should be a way to change the owner of database objects. > For now, once a table, index or procedure is created, the owner is stored > and cannot be changed. > Votes: 4 > > https://www.ibm.com/developerworks/rfe/execute? > use_case=viewRfe&CR_ID=91585 > Headline: Informix should be able to change owner on tables and any other > objects > ID: 91585 > Description: If user=informix creates all database and all tables, then > programmer > creates table but user=informix cannot change it. Only drop it. > Votes: 15 > > ( > âTâ > here was also a declined one ID:40195, hopefully they favourably consider > the > â â > current requests, and possibly even amalgamate the votes). > > -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2015.1101 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused."
I have mixed feelings about this one. To make it clear, I'd like to have
the feature... basically I'd like to have all the features :)
But I question myself on how we enter a state where we really nead it?:
1- Why would a normal user have privileges to create objects?
2- All DDL should be done by a specific (application) user. This should be
integrated in a "dev -> qa -> prod" process
I totally disagree with Art's suggestion of creating the objects with user
informix. User informix should barely be used.... and never for object
creation.
The rational behind this is that informix has too much power... so if
you're minimally security aware, you'd want to restrict informix usage to
the bare minimum. No DBA and very few DBSA activities should be done with
"informix" user...
Anyway... just my 2c... The reason why I'd love to have the feature would
be to move all objects created with Informix to an application user :)
Regards.
On Wed, Jun 14, 2017 at 10:59 AM, BENJAMIN THOMPSON <
benjamin.thompson@skybettingandgaming.com> wrote:
> Hi,
>
> I hope readers will excuse this longish post but this is an area of
> frustration for me too. I had already voted for request #2 but have added
> my
> vote to #1.
>
> With an evolving schema, this whole area requires constant vigilance to
> keep
> in order and in the worst case it can lead to security holes. Avoiding the
> problem in the first place has to be part of the solution as well as
> providing
> a means to fix it afterwards. In an RDBMS like Oracle where table
> ownership is
> interpreted much more strictly than a non-ANSI Informix database, there is
> a
> simple command you can run to ensure all objects in your session are
> created
> with the correct owners: "alter session set current_schema=;". I have
> added a
> comment to one of the RFEs to this effect.
>
> There is a wrapper for "create table" statements "create schema
> authorization
> <username>" which kind of does the same thing but it only works when
> creating
> a table and its scope is the next statement only.
>
> Instead you have to employ a lot of different methods to ensure that
> tables,
> indices, constraints etc. have consistent ownership or you avoid the
> problem
> by always deploying as the schema owner. (I don't consider deploying as the
> "schema owner" a good solution because you ideally want separation between
> the
> user ids for the apps, users and application schema.)
>
> Another example is adding a column with a constraint. If I run under my own
> user id:
> alter table X add col1 int default 0 not null;> This creates a "not null" constraint, where it can be seen in
> sysconstraints
> that I am the owner and not the existing owner of the table. The only way
> around this is a named constraint, e.g.
> alter table X add col1 int default 0 not null constraint> schemaowner.constrname;
>
> There is a more subtle version of this where you modify a column that
> already
> has a "not null" constraint.
>
> The problem with such a feature is making a business case for it as the
> product continues to work just fine despite these problems. I guess the
> main
> one is the time I have to put into checking upgrade scripts.
>
> Ben.
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
> On 16 June 2017 at 15:11 Fernando Nunes <domusonline@gmail.com> wrote:
>
>
> I have mixed feelings about this one. To make it clear, I'd like to have
> the feature... basically I'd like to have all the features :)
> But I question myself on how we enter a state where we really nead it?:
Bad practive over the last 20 years ;->>>>
Stuck in a pit, need a way out!
Regards,
David.
>
> 1- Why would a normal user have privileges to create objects?
> 2- All DDL should be done by a specific (application) user. This should be
> integrated in a "dev -> qa -> prod" process
>
> I totally disagree with Art's suggestion of creating the objects with user
> informix. User informix should barely be used.... and never for object
> creation.
> The rational behind this is that informix has too much power... so if
> you're minimally security aware, you'd want to restrict informix usage to
> the bare minimum. No DBA and very few DBSA activities should be done with
> "informix" user...
>
> Anyway... just my 2c... The reason why I'd love to have the feature would
> be to move all objects created with Informix to an application user :)
>
> Regards.
>
> On Wed, Jun 14, 2017 at 10:59 AM, BENJAMIN THOMPSON <
> benjamin.thompson@skybettingandgaming.com> wrote:
>
> > Hi,
> >
> > I hope readers will excuse this longish post but this is an area of
> > frustration for me too. I had already voted for request #2 but have added
> > my
> > vote to #1.
> >
> > With an evolving schema, this whole area requires constant vigilance to
> > keep
> > in order and in the worst case it can lead to security holes. Avoiding the
> > problem in the first place has to be part of the solution as well as
> > providing
> > a means to fix it afterwards. In an RDBMS like Oracle where table
> > ownership is
> > interpreted much more strictly than a non-ANSI Informix database, there is
> > a
> > simple command you can run to ensure all objects in your session are
> > created
> > with the correct owners: "alter session set current_schema=;". I have
> > added a
> > comment to one of the RFEs to this effect.
> >
> > There is a wrapper for "create table" statements "create schema
> > authorization
> > <username>" which kind of does the same thing but it only works when
> > creating
> > a table and its scope is the next statement only.
> >
> > Instead you have to employ a lot of different methods to ensure that
> > tables,
> > indices, constraints etc. have consistent ownership or you avoid the
> > problem
> > by always deploying as the schema owner. (I don't consider deploying as the
> > "schema owner" a good solution because you ideally want separation between
> > the
> > user ids for the apps, users and application schema.)
> >
> > Another example is adding a column with a constraint. If I run under my own
> > user id:
> > alter table X add col1 int default 0 not null;> > This creates a "not null" constraint, where it can be seen in
> > sysconstraints
> > that I am the owner and not the existing owner of the table. The only way
> > around this is a named constraint, e.g.
> > alter table X add col1 int default 0 not null constraint> > schemaowner.constrname;
> >
> > There is a more subtle version of this where you modify a column that
> > already
> > has a "not null" constraint.
> >
> > The problem with such a feature is making a business case for it as the
> > product continues to work just fine despite these problems. I guess the
> > main
> > one is the time I have to put into checking upgrade scripts.
> >
> > Ben.
> >
> >
> > ************************************************************
> > *******************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
> 1- Why would a normal user have privileges to create objects? > 2- All DDL should be done by a specific (application) user. This should be integrated in a "dev -> qa -> prod" process. Yes, these are great points but unfortunately not how we work. The users I refer to in (1) are not "normal" users: they are support users responsible for code deployment. If we moved to something like (2) it would only work straightforwardly if the objects were owned by the same user doing the DDL. We also ideally need to lock out the account when it's not in use and the audit trail is less clear because we're now dealing with a shared account. Ben.
So... do you know CREATE SCHEMA AUTHORIZATION? Regards. On Mon, Jun 19, 2017 at 9:49 AM, BENJAMIN THOMPSON < benjamin.thompson@skybettingandgaming.com> wrote: > > 1- Why would a normal user have privileges to create objects? > > 2- All DDL should be done by a specific (application) user. This should > be > integrated in a "dev -> qa -> prod" process. > > Yes, these are great points but unfortunately not how we work. The users I > refer to in (1) are not "normal" users: they are support users responsible > for > code deployment. If we moved to something like (2) it would only work > straightforwardly if the objects were owned by the same user doing the > DDL. We > also ideally need to lock out the account when it's not in use and the > audit > trail is less clear because we're now dealing with a shared account. > > Ben. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...
> So... do you know CREATE SCHEMA AUTHORIZATION? Yes, I've discussed it earlier in this thread. Ben.
Oh... I missed it. Just one note... You can have several instructions... But yes, ALTER TABLES are not allowed... That could be a nice RFE... On Mon, Jun 19, 2017 at 10:39 AM, BENJAMIN THOMPSON < benjamin.thompson@skybettingandgaming.com> wrote: > > So... do you know CREATE SCHEMA AUTHORIZATION? > > Yes, I've discussed it earlier in this thread. > > Ben. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...
Jonathan, > There are ramifications to transferring ownership. That's not to say it > shouldn't be done (it should), but there are more twists, knots, kinks > involved than you'd expect at first glance. I think even a supported offline way of doing this would be an improvement. I am assuming this would simplify things over a fully online DDL operation. To illustrate what might be possible, currently there is a totally unsupported way of resolving object ownership issues whereby the owner column on sysobjstate and systables/sysconstraints/whatever can be updated as user "informix" with the server restarted immediately afterwards. This is similar to the method of adding a disabled foreign key (FK) and then hacking sysobjstate.state which can avoid a long wait while the server validates the data. (This is no longer necessary now that the duration of adding validated FKs was much reduced in 11.70.FC8 or, for those that still like to live slightly dangerously, there is the NOVALIDATE option.) Ben.