Viewing Table structure alterations
Asked whether IDS 10 records when a table's structure was last altered or when privileges were granted/revoked. Answers: there is no built-in catalog record of this (Art Kagel: "No"), although some alters update the created date in systables (only when the server must rebuild rather than change in place). The suggested approaches are to enable auditing per the Trusted Facility Guide to log DDL, mine the logical logs, keep table definitions in version control, and restrict/discipline who may alter tables — none of which helps for changes already made.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
All, From an administrator point of view, is it possible to see the last time a table structure was altered, or when rights for particular users we assigned or revoked from a particular table? DB: IDS 10 Gath
Hi, There isn't a reliable way of accessing that information by default, however, it is possible to enable that kind of auditing. Details can be found in the trusted facility guide. Usually, however, when someone asks this kind of question, it is to find information about events that have already happened. Not much help there. Mark -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of PAUL GATHOGO Sent: 08 April 2009 10:04 AM To: ids@iiug.org Subject: Viewing Table structure alterations [15475] All, >From an administrator point of view, is it possible to see the last >time a table structure was altered, or when rights for particular users we assigned or revoked from a particular table? DB: IDS 10 Gath **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. ================== Please read our Email Disclaimer : http://www.thefuelgroup.com/disclaimer.html
Hi, Some table alters will update the created date on the systables catalogue, however not all changes. I think it has to do with whether the database server can get away with an inplace change or if it has to rebuild the table Not sure if that helps -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of PAUL GATHOGO Sent: 08 April 2009 10:04 AM To: ids@iiug.org Subject: Viewing Table structure alterations [15475] All, >From an administrator point of view, is it possible to see the last >time a table structure was altered, or when rights for particular users we assigned or revoked from a particular table? DB: IDS 10 Gath **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. ================== Please read our Email Disclaimer : http://www.thefuelgroup.com/disclaimer.html
No. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Apr 8, 2009 at 4:04 AM, PAUL GATHOGO <pgathogo@gmail.com> wrote: > All, > > >From an administrator point of view, is it possible to see the last time a > table structure was altered, or when rights for particular users we > assigned > or revoked from a particular table? > > DB: IDS 10 > > Gath > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016e644df4caf289604670cd86c
Am just wondering how hard is it to have such a feature in the IDS? or How do people control such alteration? You can imagine a situation when you have a pool of programmers, some might Alter tables without documenting there changes, as an administrator it would be good to have a log of such changes... Any ideas? Gath
On Wed, Apr 8, 2009 at 20:53, PAUL GATHOGO <pgathogo@gmail.com> wrote:
> Am just wondering how hard is it to have such a feature in the IDS? or How do
> people control such alteration?
People control those who have permission to alter tables - and impose
penalties on those who do undisciplined changes to tables. In many
production shops, especially those with critical data (such as
customer account data that must be protected - whether credit card
numbers or healthcare data or any other sensitive data), changing a
table without permission would be grounds for instant dismissal for
abuse of privileges.
> You can imagine a situation when you have a pool of programmers, some might
> Alter tables without documenting there changes, as an administrator it would
> be good to have a log of such changes...
As was already mentioned (by others), you could enable auditing and
track all DDL changes. This would put a finger on the culprit - if
you have sufficient control over your system that people don't all
login as root and informix to do the database administration work. If
they all login as generic user names, you have no trackability - don't
let them do it. You might want to add role separation to your setup.
Alternatively, you can grope through the logical logs to find when
tables are changed.
However, it is generally simpler to make it a discipline issue - you
change a table without permission, and you don't change any other
tables without permission because you aren't working here any more.
Clearly, if it is a development instance and the changes are required,
then that is a different issue. But the changes should be documented,
and undocumented changes should be an issue (unless the database is
the user's private copy - then they can do what the hell they like as
long as it doesn't break things when the changes are documented and
brought into the main development or production versions of the
database).
You should be keeping a record of the 'official structure' of tables
under some sort of VCS (version control system).
I've been toying with ideas for identifying when two tables have
equivalent (identical) structures. The problem is knowing which
aspects of the structure are critical. For many purposes (I'm
thinking I4GL programs here, though there are other places it could be
of relevance), the table name, sometimes with the owner name, and the
column list with the types of the columns is all that must be the
same. So, I've considered whether an MD5 checksum of the table name
and column list (each entry consisting of column name, coltype,
collength fields from syscolumns) is sufficient. For many of my
purposes, I think it would be. And MD5 is fine for this - I'm looking
for 'probably the same', not 'has definitively not been tampered
with'. This would identify reordered columns and renamed columns and
retyped columns. I'm not sure whether it is reliable for UDTs - the
numbers are not necessarily the same. So, it might be necessary to
use text names for UDTs (row types, distinct types, datablade types,
etc). And if that's the case, then maybe the hash should be over the
text representation of types (CHAR(40) instead of
coltype=0,collength=40) for the basic types.
Why this meditation - w? Well, packaged appropriately, it would give
you an easy mechanism to identify whether the crude table structure is
the same as it used to be. More refined versions might detect
differences in default values, check constraints, indexing
differences, primary key and foreign key constraints, and so on. Note
that it is more difficult to manage some of these - what is the
canonical ordering such that these two tables have the same checksum:
CREATE TABLE Sample1
(
Col01 SERIAL NOT NULL PRIMARY KEY,
Col02 DATETIME YEAR TO SECOND DEFAULT CURRENT YEAR TO SECOND NOT NULL,
Col03 INTEGER NOT NULL REFERENCES Sample3(Col1),
Col04 CHAR(1) NOT NULL CHECK(Col04 IN ('Y','N','M','-'))
CONSTRAINT c0_sample1
);
CREATE TABLE Sample
(
Col01 SERIAL NOT NULL,
Col02 DATETIME YEAR TO SECOND DEFAULT CURRENT YEAR TO SECOND NOT NULL,
Col03 INTEGER NOT NULL,
Col04 CHAR(1) NOT NULL,
PRIMARY KEY(Col01),
FOREIGN KEY(Col03) REFERENCES Sample3,
CHECK(Col04 IN ('-','Y','M','N')) CONSTRAINT c0_sample2
);
I'm assuming that the primary key of table Sample3 is Col1.
It means that constraint names (both implicit and explicit) have to be
disregarded.
In the case of the two check constraints, the sequence of the
alternative values has changed, but that's a set so there is no
difference in effect - does this matter? What about constraints such
as:
CHECK(Col0x >= 1 AND Col0x <= 20)
CHECK(Col0x <= 20 AND Col0x >= 1)
CHECK(Col0x BETWEEN 1 AND 20)
In these cases, I'd be inclined to say "The spelling of the CHECK
constraint must be identical between the two tables" as recognizing
all the alternatives and reducing them to some canonical form is an
open-ended nightmare.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/
"Blessed are we who can laugh at ourselves, for we shall never cease
to be amused."
NB: Please do not use this email for correspondence.
I don't necessarily read it every week, even.
Norman Mailer - "Writing books is the closest men ever come to
childbearing." -
http://www.brainyquote.com/quotes/authors/n/norman_mailer.html