Permissions to allow users to drop tables created by others
After revoking DBA from PUBLIC for Sarbanes-Oxley compliance, Simon found a Sage EDI application broke: one user creates daily tables and a different user must drop them later. In Informix only the table owner (or a DBA) can drop a table, and no table-level grant (not even ALTER) confers drop rights. Suggestions included redesigning the app, using temp tables, pre-creating the tables each day under one fixed owner who also runs a scripted drop, or investigating roles; Clive floated a script that grants privileges once the table appears. No working solution was confirmed in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, We have been told by Sarbanes Oxley to tighten up our database permissions (ie revoke DBA from public !) and I have granted resource to all users for each individual database. I have also granted ALL on each table in each database to public. My problem now is that the application is trying to drop a table for one user, but the table was created by another user. Can anyone tell me if there is any other way to get this to work other than setting up each user as a DBA ? Regards - Simon
Simon Redesign the application. I would have serious issues with an app even creating permanent tables, let alone having one user create them and another deleting them! If the table is temporary then create it as a temp table, if it is permanent then the DBA should create it where he wants it with sensible extent sizes, indexes and constraints and the users sould just be adding, amending and removing the data. But basically, no Keith On 20/10/06, bondsfis <simon_bondsfield@nospam.yahoo.co.uk> wrote: > Hi, > > We have been told by Sarbanes Oxley to tighten up our database permissions > (ie revoke DBA from public !) and I have granted resource to all users for > each individual database. I have also granted ALL on each table in each > database to public. My problem now is that the application is trying to > drop a table for one user, but the table was created by another user. Can > anyone tell me if there is any other way to get this to work other than > setting up each user as a DBA ? > > Regards > > - Simon > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >
Keith, Unfortunately it's a Sage application. These are daily EDI tables which are created by the first person to raise an invoice each day but then processed and dropped by someone else at the end of each day. I agree it's a ridiculous way of working, but I've no control over it ! Cheers - Simon
bondsfis wrote: > Keith, > > Unfortunately it's a Sage application. These are daily EDI tables which > are created by the first person to raise an invoice each day but then > processed and dropped by someone else at the end of each day. I agree > it's a ridiculous way of working, but I've no control over it ! > I guess you could run a script that queries the systables/sleeps until the table is created, and then issues grants to all other potential 'dropees' - I have to say it's pretty yuk. Not sure what 'SO' would have to say either - we don't have it over here in the Free World ;-) -- Clive Eisen CTO Hildebrand Group
Still wouldn't work as you can only drop a table if you are the owner (or a DBA) and only one person can own a table, and can only create tables if you have RESOURCE permission on the database. You don't say which version you are using, but are roles an option (never used them myself, but might be worth investigating). Alternatively could you pre-create the table at the beginning of each day (I assume they are dated in some way and if the table already exixsts when an invoice is raised it just gets inserted), make it owed by a specific user who has permission to remove it and that only he runs the drop procedure at the end of the day (or even better the drop procedure is scripted in some way as well). Keith On 20/10/06, Clive Eisen <clive@serendipita.com> wrote: > bondsfis wrote: > > Keith, > > > > Unfortunately it's a Sage application. These are daily EDI tables which > > are created by the first person to raise an invoice each day but then > > processed and dropped by someone else at the end of each day. I agree > > it's a ridiculous way of working, but I've no control over it ! > > > > I guess you could run a script that queries the systables/sleeps until > the table is created, and then issues grants to all other potential > 'dropees' - I have to say it's pretty yuk. Not sure what 'SO' would have > to say either - we don't have it over here in the Free World ;-) > > -- > Clive Eisen > CTO Hildebrand Group > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >
What grant statement would you suggest issuing to potention "dropees" ? I can only see that granting DBA would allow users to drop other user's tables, not even "alter" at table level will allow other users to drop the table. Informix doesn't appear to allow you to grant a permission that lets anyone drop a table, other than setting DBA for each user.
We have the same problems with AppEngine tables in PeopleSoft.... --- bondsfis <simon_bondsfield@nospam.yahoo.co.uk> wrote: > Keith, > > Unfortunately it's a Sage application. These are > daily EDI tables which > are created by the first person to raise an invoice > each day but then > processed and dropped by someone else at the end of > each day. I agree > it's a ridiculous way of working, but I've no > control over it ! > > Cheers > > - Simon > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
What do Sage say about it ? Paul Watson Tel: +44 1414161772 Mob: +44 7818003457 Web: www.oninit.com GO FURTHER with DB2 GET THERE FASTER with Informix. Attend IDUG 2007 San Jose, North America May 6-10, 2006 Visit http://www.iiug.org/conf for more information. > -----Original Message----- > From: bondsfis [mailto:simon_bondsfield@nospam.yahoo.co.uk] > Posted At: 20 October 2006 06:19 > Posted To: comp.databases.informix > Conversation: Permissions to allow users to drop tables > created by others > Subject: Re: Permissions to allow users to drop tables > created by others > > > Keith, > > Unfortunately it's a Sage application. These are daily EDI > tables which are created by the first person to raise an > invoice each day but then processed and dropped by someone > else at the end of each day. I agree it's a ridiculous way > of working, but I've no control over it ! > > Cheers > > - Simon >