Friday, October 31, 2008

11.50.xC3: another pack of features...

IBM has just made available IDS 11.50.xC3. Traditionally, fix packs include bug fixes and eventually some minor improvements. I believe the concept of "minor" may have to be revised when it comes to IDS fix packs. Let's see what the good fellows of R&D decided to put into this release:

  • MAX OS X improvements:
    • Silent installation
    • Ability to create Data Source Names (DSN)
  • Enterprise Replication improvements:
    • Dynamically change Enterprise Replication (ER) configuration parameters in the Onconfig file
      We can now do this using commands like cdr add onconfig, cdr change onconfig, and cdr remove onconfig
    • Improved Consistency Reporting after Enterprise Replication Repair Operations
      The repair jobs have an option that forces the retrying if inconsistencies are found at the end of the jobs
    • Administering and Monitoring Enterprise Replication with SQL Admin API
      The SQL admin now allows us to run cdr commands through SQL
  • Ability to change ONCONFIG parameters with SQL Admin API
    SET ONCONFIG for session change and SET ONCONFIG PERMANENT for Onconfig and session change (session here means the current instance configuration and not client session)
  • Three new dynamically changeable ONCONFIG parameters:
    LTXEHWM, LTXHWM, and DYNAMIC_LOGS
  • Improved SQL Tracing with the SQL Admin API
    From now on we can define which databases, sessions and users will be traced. This gives higher control on the SQL tracing facility. We can also suspend and resume the SQL tracing (without loosing previously set specifications)
  • Changing the Size definition of the First Extent of a Table
    Now we can establish a new definition for the first extent size of a table. Note that this will not change it's physical layout. It will be used if you extract the table schema, rebuild the table (by specifying a new fragmentation clause) or if you truncate the table with the reuse clause
  • Implementation of SAVEPOINTs
    SAVEPOINTS are an SQL standard that allows the programmer to establish some points after the begin of a transaction to where he can ROLLBACK. Imagine that you're running a batch file, processing several customers in a FOREACH cycle. You may want to do all the customers as a unit, so you would COMMIT at the end. But also consider you want to do several DML operations for each customer and that you accept that you can skip a customer, but not one or more operations within a customer. To put it simpler, if one operation on a customer fails you want to skip it, but continue with the transaction. With this feature it's possible. You just have to set a SAVEPOINT after each customer. If something goes wrong in the next customer, you can ROLLBACK WORK TO SAVEPOINT and continue with the next customer (maintaining your main transaction open).
  • Basic Text Search DataBlade Module Supports High-Availability Clusters
    In previous versions, the indexes created by the BTS needed to be created in external dbapces. This would mean that you couldn't use BTS searches on secondary nodes unless you used some (officially unsupported) external way of synchronizing these external spaces within the cluster hosts. Now, you can create these indexes in smart blob spaces, which can be logged, and thus can be replicated to all the cluster nodes
  • Querying XML Attributes with the Basic Text DataBlade Module
    BTS now supports another index creation parameter that specifies that the XML attributes (on XML tags) should be indexed (and thus become available for searches)
  • Setting the Frequency of Error Checking for Smart Large Object Transmission
    A new environment variable called IFX_LOB_XFERSIZE was introduced to specify the interval (in bytes) that will trigger error checking for SLOBs. After this amount of bytes the data will be checked for transmission error (between client and the database server). If it finds any errors it aborts with an error. Without this variable the checking is made only after the end of the transmission
A final note to the IDS Information Center. It now contains three new features:
  • An RSS feed with updates to the Information Center
  • Search engine add-ons for browsers, so that you can directly search on IBM Informix Information Center for your specific version
  • An extended section with links for community sites. This includes forums, general sites, wikis and blogs about Informix. The Informix-Techonology blog is one of the additions to this list. I face this as an honor and a great responsibility. It's also a great incentive to keep the work that I started more than one year ago.
I keep repeating myself about this idea: The IBM Informix team (management, architects, developers etc.) is doing a terrific job improving IDS while maintaining the ease of administration, reliability and performance we all know and love! With new features come some complexity increase, but that doesn't mean the product has to be slower, harder to manage or more unstable.
I look forward to the next release/fix pack which I bet will bring some more great improvements!

Wednesday, October 29, 2008

Encryption: All your (data) base are NOT belong to us!

If you were growing up during the personal computer boom you may notice that the title of this post is a joke with another joke. For details on that check the Wikipedia or the flash animation.
This is just a pretext to introduce a very serious subject. You probably know that Informix IDS can encrypt column data, but you may also know that it has some disadvantages (you cannot index encrypted data, you may have to change the applications etc.). There's nothing wrong with column encryption, but as with most of the features is has advantages and disadvantages.

Another option for encryption is to encrypt the files containing the data (chunks in IDS language). This brings up several questions:

  • Performance
    If you're doing data encryption you're consuming CPU cycles
  • Ease of use
    Is it transparent to the database? And to the application?
  • Privilege users access
    Can root work around the encryption? Usually, being root is like being God...
IBM has a solution for these kind of transparent low level encryption. It's a partnership with Vormetric which resulted in a product called IBM Database Encryption Expert. It tries to solve all the questions above. It's fast and light, meaning you won't have too much performance impact, it's easy to use and configure, through an administrative console, and you can stop the root user from accessing your encrypted data files.

IBM has just released fixpack 1.1.3 of this product, which is the first version to support IDS (versions 11.10FC2 and 11.50). You can get more info about it at the product page ( http://www.ibm.com/software/data/db2imstools/database-encryption-expert/ ).
Up to now, it was certified for use with DB2, so it's probable that you find several references of the two products together. One of them which I find interesting, is a video showing it working with DB2, by Belal Tassi, a colleague from IBM Information Management:




As you can see from the product page, the fixpack Readme and the video above, it's transparent for the database, for the application and it can be setup to be safe even from root. It's also able to encrypt backups (it would be a breach of security to have your data encrypted and keep your backups without encryption). Hint: Version 11.10 introduced backup and restore filters...
The platforms supported at the moment are:
  • AIX (64-bit):
  • Linux (64-bit): Red Hat Advanced Server 4.0 Update 4
  • Solaris 9
  • Solaris 10 update 2
  • Windows

Supported file systems

  • Windows: NTFS
  • AIX: JFS, JFS2, NFS, VxFS
  • Solaris: UFS, NFS, VxFS
  • RedHat: EXT3, NFS, VxFS
This product can be a solution for some compliance requirements, specially for companies who have to comply with Payment Card Industry Data Security Standards (PCI DSS), or simply for paranoid DBAs

Monday, October 27, 2008

Too many subjects...

Yes... Fortunately it's true: I can't keep up with everything that's been hapening....
In this post I'll mix a lot of topics. They've been covered elsewhere, but for the record, a few comments:

  • Carlton Doe, a well know name within the Informix community, has published a new book. It's called Administering Informix Dynamic Server: Building the Foundation. You can find it at Amazon. I don't have a copy yet, but I look forward to check on the contents. Given the author's history on Informix and previous work this book should be a safe investment
  • IBM has made available an Informix Virtual Appliance. This is essentially a virtual machine pre-configured with a lot of Informix software (IDS of course, Client SDK, JDBC driver, Spatial Datablade, Open Admin Tool, Data Server Drivers - JDBC/ODBC, and finally Data Studio). All running on top of SUSE Linux Enterprise Server 10 SP2. All you need to run it is a copy of one off the VMWare players. You can get a complete explanation and download instructions on Guy Bowerman's site
  • IBM started another initiative. It's called the Data Champion Program. The idea is to publicly recognize individuals who have made strong contributions to the Data Management community. The program website is located at http://www.ibm.com/software/data/champion. Today, Mr. Arvind Krishan announced at IBM Information On Demand 2008 conference (IOD) the inaugural champions.
    I'd like to congratulate all of them, with just one special note to Eric Herber for his wonderful job at Informix Zone ( http://informix-zone.com).
    You can nominate other champions on the program's website above until January 31, 2009.
  • Speaking about IOD, the IBM Information On Demand 2008 conference, it started yesterday and you can expect some important news. For now, on the light side, there are a lot of Informix tattooed people there... Take a look at this photo for example, courtesy of Jean George Perrin, showing a familiar face for the Portuguese Informix community, with a "quality stamp on the forehead" :)
  • One of the IOD news is about SolidDB. As you probably already know, Solid was acquired by IBM, and produces a product called SolidDB that can act as a cache for DB2 and Informix. SolidDB is an in-memory database, meaning it can speed things quite a bit. In this press release, IBM announces that it now supports RDBMS from other vendors. Specifically, SolidDB cache now supports DB2, Informix, Oracle, Sybase and in a near future SQL Server
  • And finally, an announcement from Exact, about plans to support for Informix in two products: iReplay (capture and replay SQL), and iWatch (SQL monitoring). You can check the press release here: http://www.exact-solutions.com/aboutus/news/386
That's all for now. This post is just to keep you up to date. I'd love to return to long, boring, technical articles, but time is short... Hopefully soon...

Friday, October 17, 2008

Informix at VendorRate

If you follow some Informix blogs, groups or sites, you probably already know that Informix earned Top Customer Satisfaction Score on VendorRate survey in Q3. If you're into press releases you can learn more at Business Wire. If you prefer to check the report directly you can find it here.

Informix received the maximum score achieved by any vendor (91) and was rated by more than 120 organizations accordingly to the study. "O"ther RDBMS vendor got 81. The rating covers several aspects (Recommend, Integrity, Communication, Timeliness, Usability, Reliability, Customer Service, Budget, Expertise and Effectiveness). Informix got 9 in all of them except for Reliability (10) and Budget (8).

This rating reflects customer opinions. They are not marketing, although they're probably be used for marketing... I can't say I'm surprised. We all know that Informix customers are loyal, and they're loyal simply because they know they're using a solid product.

Obviously, we tend to compare "our" goods with the competition. And to be honest, we can't say the competition looks bad. As expected, a vendor who has the largest market share is well rated in general... But being a curious fellow I decided to dig a bit deeper... And if you follow me you can also check interesting points:

  • In the small companies category Informix is even better positioned. The direct competitor gets a lower rating than in the general rating. It doesn't even show on the table.
    This is not surprising considering the ease of administration and low total cost of ownership (TCO) of Informix
  • Informix continues to achieve top score in the medium size companies. In this category, the second RDBMS place doesn't belong to the biggest competitor
  • In the large enterprise category Informix still scores top ratings. The biggest competitor is in a close second place
This can lead to the conclusion that Informix is terribly good in all kinds of organizations. it simply does it's job, and you can count on top class vendor support.

Then, and just for fun, I decided to dig even deeper... I noticed that VendorRate was at the biggest competitor conference and issued a report based on data collected there.
Looking at this report we can also find very interesting (if not amusing) points:
  • Informix was rated exceptional by the attendees (page 5)
  • Informix was rated 2 points (93) above the quarterly VendorRate ratings (91) (page 10)
So, if people using our competition loves us, why shouldn't we?!

Wednesday, October 08, 2008

IIUG 2008 survey: again, time to speak up!

International Informix Users Group ( IIUG ) has put up another user survey where you can classify several proposed IDS new features. You can also name other features not specified in the survey.
Please note, that the fact that the presented features are available for voting doesn't necessarily mean they're being implemented. But your feedback is very important and IIUG will take it to R&D.
Many of the most voted features of last survey were in fact implemented in IDS 11.10 and IDS 11.50. So now it's time to make your voice be heard, and request that those annoying things be fixed. Some of my favorites that are not in the survey are:

  • Limit locks per session
    This is really an annoyance... Give the DBA more control please!
  • Clarification on support for Encryption Expert (IBM and Vormetric)
  • Compression
  • Configurable system page size
    This could be a step for the next one
  • Portable dbspaces (at least for same-indian and same page size system)
  • HDR between heterogeneous systems (same as previous)
  • Fine grained auditing
    Some control per object. Probably a big change and probably big performance impact... Maybe a switch per user/mask to keep the lighter current implementation...
  • End of user in OS limitation (default user for procedures/query plan?)
  • Storage provisioning
  • Some DW features available on XPS (bitmap indexes, star index...?)
Well, looking at the list again, I'd say that "asking is easy!..."
You can find the survey here: http://www.iiug.org/2008_survey
Don't miss the opportunity to influence IDS future!

Tuesday, September 30, 2008

Community day: October 1 - What and why?

This post should have been written a few days ago, but as usual, time is precious and never enough...

As the title says, October 1 was defined as the "community day!"... but what Community you may wonder...? Well, it all started at Adam Gartenberg's Blog. Adam is involved in the IBM Information Management division and suggested we could do some coordinated actions to strength the various communities related to IBM Information Management products. So, the basic idea is that we all participate in any community we're involved on October 1.
This of course can lead to various questions... I've been thinking about this and the biggest question in my mind is this: "What is a community, and why do I care? Do I belong to any community?"

The answers to this would be big, probably boring and subjective, but I can't resist putting here some of my thoughts. In these (let me use a buzzword) Web 2.0 times, a community can be anything. A site, a forum, a newsgroup, a blog, a wiki, a group of people with whom I exchange emails etc. In fact, all Informix users worldwide can consider themselves as part of a community. We may never meet each other, but we probably read the same news, the same blog articles, the same forums, and most important, we share a common interest. We probably share some of our doubts, we eventually find similar solutions etc. There is a vast amount of information lying around in the Internet, our email systems, our corporate intranets etc. And then we have search engines, knowledge base systems etc. All this didn't materialize from nothing. It's the direct result of our participation in the community. And when I say "our" I don't mean just the people who write boring articles like this :) I'm also thinking about all the people that simply ask some questions or make some comments.
And now what about the "should I care" part? Well, to put it simply, yes, we should care, simply because it makes our job easier. I can never forget my first steps as an Internet user. I was doing some system/network administration and I had a problem with what we called ATS (Asynchronous Terminal Servers), which were simply network connected equipments with several RS232 ports that connected dumb VT100 terminals to the central system (running Informix Turbo v4 ;) ). My local supplier couldn't find an answer, and using Usenet news I got some help from some friendly guy from the USA. Was this a community? Yes. We both used the same kind of equipment, we had the same problem, and the guy from across the Atlantic had a solution!
Currently, besides my work with Informix, I try to help a customer with other IBM products (namely WebSphere Application Server and IBM Information Server ). As you can imagine I am not a specialist on this products. But I've been able to help this customer in issues and doubts. How, you may ask? Obviously taking advantage of the "community". This include the fine technical support staff, the contents (presentations and other documents) produced by product specialists, the internal and external forums etc. What does it translate into? Productivity. Without these communities I wouldn't have been able to help my customer.

These were just two examples of the importance of community. In 1994, the communities were smaller, there was less content and the search facilities were much less efficient. But the principles were the same as today.

So, hopefully i've showed why the community is so important today. Assuming you agree, I urge you to follow up on Adam's suggestion, and participate more in your community. You can do this by:

  • Answer some comp.databases.informix question(s)
  • Subscribe the IIUG mailing lists or answer some questions there
  • Leave some comment on one of your favorite blogs (if you don't have one, try checking the list on this page's right side) - for example suggesting topics for articles
  • Ask some question to the community or suggest and idea
  • Spread some links of community sites you visit to your co-workers, colleagues and friends
  • Start your own blog?
  • Do some work on a community wiki
The idea of doing it on October 1, is just to create a wave... Did you ever heard about the butterfly effect and the domino effect? ;)

Tuesday, September 16, 2008

Demos? Yes... we have them

There have been a lot of discussions on public newsgroups about the lack of nice and flashy IBM Informix demos. The truth is, IBM has them, but as so many other stuff it may be hard to find in the IBM website. So I decided to ease up your searches. Please follow this steps:

  1. Access IBM TV
  2. On the right side, there is a list of a lot of media titles. Scroll down until you find several Informix titles which include:

    - Mr. Ambuj Goyal (current IBM Information Management General Manager) message about IDS 11
    - A funny video explaining why you don't see much of Informix
    - A flash demo of IDS 11 (overview)
    - A flash demo of IDS 11 security features
I think there is also another flash demo about IDS high availability. I've seen it in some meetings, but apparently it didn't reach the IBM TV website, which by the way is a terrific source of information not only for IBM products but also for current IT trends.
I have the feeling that most Informix DBAs aren't too much into flash demos, but they're nice to show to customers and prospects. So, here they are... take advantage of them as you like.

Face lift

Very short post just to let you know I changed the page look a bit.
There were already too many links, so I tried to organize them into categories (blogs, documentation and just links).

I also added a gadget that allows you to view and listen to some Informix videos available on YouTube.

Monday, September 08, 2008

IDS 11.50xC2: new features

If you follow up some Informix related blogs and sites you probably already noticed that the latest IDS 11.50xC2 includes some surprises. Both Guy Bowerman and Eric Herbert mentioned it on their webpages.
I'd like to add a few more comments to their posts. I chose to do this, specially because the release notes don't emphasize enough all the changes. If you're on Windows, please refer to Guy's post, because it has a lot of features specific for Windows environment.
The features I'd like to mention are:

  • Installer improvements in Mac OS X
    The new installer can now configure some kernel parameters which are needed to take full advantage of IDS. Just recently a fellow IIUG mailing list user was asking about some issues. He tried the IDS 11.50.xC1 installation and hit some issues. After repeating it with IDS 11.50.xC2 everything was ok with the default instance setup
  • Reconfiguration of the Connection Manager without restart
    The new version of Connection Manager allows it's reconfiguration without restart. Just use the -r option. This is a nice feature, since the purpose of the Connection Manager is to collaborate in the increase of the availability of your cluster. So the need to restart it to change it's configuration was annoying. Nevertheless, you should always use more than one instance of the connection manager, so if you need to change it's configuration you should change one instance at a time. Doing this allows you to keep you connection infra-structure answering requests while you go through your CM instances, changing it's configuration
  • Several improvements in the Enterprise Replication facility
    You can check Guy's post and the release notes to check all the changes. They are well covered there
  • Limiting the number of sessions that can connect to IDS
    Once in a while a customer asks me if we have any way to control the maximum number of sessions that can connect to one instance... I usually ask why, and if they can control it on the application side. Sometimes I wonder I they need this, and sometimes they remember me that the most famous IDS competitor has this "feature". I even saw situations where they need to recycle an instance to increase it... Well, I must confess that I've seen situations (specially with Java applications using application servers and connection pools) where the application starts to fire an incredible number of connections (sometimes because there are problems in the database server). In these situations it's a good idea to have a limit of sessions. And after 11.50.xC2 we have a new parameter that allows this. It's called LIMITNUMSESSIONS. If you don't define it, the server ignores the limit. If you define it then the server only allows the specified number of sessions. After that number is reached the new connections will be refused and an error code (-25571) received.
    The best aspect of this feature is that (as many new features) it allows for easy online reconfiguration using onmode -wm and onmode -wf
    Also note that any DBSA user will be allowed to connect even if the maximum connections that we specify was already reached
  • New format for backup/restore filters
    The relatively new feature that allows us to put a whole backup through a custom filter program (BACKUP_FILTER and RESTORE_FILTER parameters) were changed to use another format for backup. This is pretty transparent to the user/dba, but you have to keep in mind that you can't restore a backup made in previous versions with this new release (and vice-versa). So the real impact of this is that you must keep a record of which version you used to make your backups if you use this functionality
  • New BTSCANNER option to specify the compression level
    There is a new option in the BTSCANNER parameter that allows us to define the level of data on the pages that trigger the compression of index pages. This can also be changed dynamically using onmode -C compression and an SQL API command SET INDEX COMPRESSION
  • Server specific audit configuration file
    I recently wrote in the Audit and Role Separation article that one of the problems was that the changes we made using the onaudit utility were written to an adtcfg. file but the instance alwasy looked at the adtcfg file. Well, finally it's fixed, and now the changes and the server look at the adtcfg. file
  • UPDATABLE_SECONDARY
    Well, if you already played with IDS 11.50.xC1 and redirected writes you probably know that the parameter to activate the redirected writes was called REDIRECTED_WRITES. Well, the parameter changed it's name. It's now called UPDATABLE_SECONDARY. This is not clearly stated in the release notes (although it's mentioned in the documentation notes). Eric Herbert and I hit this issue, so please be warned if you're going to play with redirected writes
  • COMMITTED READ/LAST COMMITTED on HDR and RSS servers
    Another feature that may be very useful and it's not well explained in the release notes. With this release, you can use the COMMITTED READ and the LAST COMMITTED isolation level on the HDR and RSS servers. This is a big change, since we all expect these servers to accept only DIRTY READ. Also, these isolation levels were already supported in SDS servers. The only thing you have to do it to activate the redirected writes. This will start a component called the lock manager that is needed to support these two isolation levels. So things like onmode -k start to make sense and be useful on secondary servers.
    Obviously the default isolation level is still the dirty read (even with redirecte writes active)
  • Other features...
    There are several other features, like improvements in the Open Admin Tool, longer (32K) return strings from several string related functions and subquery support in UPDATE and DELETE statements
To completely check all the features I recommend the following links:
So, if you're about to try this version, please check all this information, and enjoy the new features

Tuesday, July 29, 2008

MediaWiki with IDS (courtesy of Jacques Roy)

This is a very quick post, just to reference a blog article by old time IDS guru Jacques Roy.
Jacques Roy has years of experience with IDS, in particular in extensibility and he writes a blog.

In one of his articles he tells us that he manage to run MediaWiki with IDS 11.50.
This setup takes advantage of one of the new features of IDS 11.50, the Basic Text Datablade (BTS) that you get for free with it.

As you probably know, MediaWiki is the best known Wiki software, and you've probably used it in WikiPedia.
It's very nice to see that it's possible to use IDS with this kind of software, and if you'd like to use it, I'm sure Jacques would be glad to help you.
I took the opportunity to add Jacques's blog to the list of links.

Sunday, July 20, 2008

Compliance: Role Separation and Audit (part II)

On the first article about auditing, I tried to show how to configure an Informix Dynamic Server instance to use role separation. The purpose was to prepare the instance for implementation of the various roles involved in the management and audit of the instance.
In this article I will focus on the audit process. We will see how to setup the audit facility and how to define what and who is audited.
Keep in mind the previous article main point: The administration and auditing is prepared for several roles. Whether these roles are played by absolutely different sets of people is really a matter of why are you doing auditing. If you plan to setup a proper auditing environment, and want to be prepared for internal or external auditors than this role separation is crucial. On the other hand, if you're planning to implement auditing in order to gain more knowledge about your instance (what is being done, how often, by whom, from where...) then you can consider playing having each person play more than one role.

In the following paragraphs I will try to answer the following questions:

* What can be audited?
* Who can be audited?
* How do we define who/what is audited?
* How do we control the auditing facility?
* Where and how does the engine store the audit information?
* What do the audit logs contain?
* How painful is the auditing?

After these I will give examples and make a few considerations.
Let's start with the questions...


What can be audited?

Informix auditing facility permits us to activate audit per events. This means that we can audit actions. An action can be a command execution, a GRANT operation, execution of stored procedures, creation of tables etc. I have to be clear about one point: Currently, we cannot define audit based on objects. We can only define auditing on actions (and users as we will see next). This means you can activate auditing for GRANTs, but not for GRANTs on a specific table. You can activate audit for execution of stored procedure but not for a specific stored procedure. Some other RDBMS do exactly the opposite: You can audit objects and not actions. In a perfect world we'd probably like to have both. But if we think about it, we can understand Informix perspective. First we should keep in mind that a user can only do what he is allowed to do. If we're trying to answer the question "who did this?" a vague but truthfully answer would be "one of the persons who has the privileges to do that". This will not make auditors happy, but my point is that you can take two paths: define auditing for your sensitive objects, or otherwise define auditing for your sensitive users. Let's see some examples... In Informix you can audit actions like DROP CHUNK, DROP INDEX, DROP TABLE etc... Would you need to define the object in the auditing definition? Of course not... It's better to define the auditing on the action. The WHO and WHAT will be included in the audit log. You can also define auditing for CREATE TABLE, CREATE PROCEDURE etc. These ones can't also be established on objects, because they refer to new objects.
If you're thinking about using auditing to see who saw what in your database you probably have to reconsider your strategy. As I wrote before, people only do what they can. A user can't do more than their privileges let him. If you want to have finer control on what people can access you should probably implement Label Based Access Control (LBAC) to prevent the access instead to trying to check if people accessed what they should not.


Who can be audited?

In short: anyone. We activate the auditing for users. Users are anyone from the different roles. In this context a user can be a member of the DBSA group, a member of the users allowed to connect to the instance or a member of the DBSSO group. So, everybody that can access the instance, that can manage the instance, or that can define what is audited can be audited.
I would like to emphasize this: Without any other product, just with the bare Informix software, you can really establish a strong auditing facility that allows you to implement role separation, and you can effectively control your users actions and specially your power users, like the DBAs, DBSAs and DBSSOs. And besides this you also guarantee the integrity of the audit logs against your DBAs and DBSSOs, meaning that they can't change anything or do any cover up action without being spotted by the AAOs. This means more value for your money. Other RDBMs will require other products to implement this. Again, root is still a possible problem, and informix is a problem which you can control.

How do we define who/what is audited?

I hope you're ready for the technical part! Ok... In Informix, we have a long list of mnemonics that represent auditable actions. Theoretically, everything we can do within an instance is an action, and has a mnemonic for it. To give you a few examples, let's consider INSERTs, DELETEs, UPDATEs, CREATE TABLE etc. and a few more esoteric like CREATE CAST, onaudit, onbar....
Each of these have a mnemonic like INRW, DLRW, UPRW, CRTB, CRCT, ONAU, ONBR

So, as we saw before, we can audit actions and users. We specify the actions using a list of mnemonics but how do we specify the users? By using the concept of masks. Informix has three standard masks, usually called global masks and we can define the ones we want. Each mask will be defined with a list of mnemonics by the DBSSOs. The three global masks are:

  1. _default
    This mask will be applied to any user that doesn't have a specific mask
  2. _required
    This mask will be added to the user mask (or the _default if there is not a specific user mask)
  3. _exclude
    This mask will contain actions that should not be audited
The masks are applied in the order above meaning that first will be the user (or _default) mask, then the _required and then the _exclude. Any conflict will be resolved by the order of the masks, meaning for example that if you specify one action (mnemonic) in the _required and _exclude masks, that action will not be audited.

You probably noticed that these masks start with an underscore. All masks that start with an underscore are called template masks and you can then use them to create specific user masks.
Note that if you do this and later change the template it will not change the user's mask.

How do we control the auditing facility?

The auditing facility is mainly controlled using the onaudit command. With it DBSSOs can change/define the user masks and the AAOs can start, stop and reconfigure the audit facility.
Let's check the command usage:


Usage: onaudit [-f file] [-u name] [-r bmsk] [-e eset] [-y]
onaudit [-c] [-n] [-l lev] [-e err] [-p path] [-s size]
action: one of
-a -- add a mask
-d -- delete a mask
-m -- modify a mask
-o -- output a mask
-r bmsk -- name of basemask
-c -- print audit configuration
-n -- start new log file
-l lev -- set ADTMODE
-e err -- set ADTERR
-p path -- set ADTPATH
-s size -- set ADTSIZE
-f file -- include instruction file
-u mask -- name of target/mask
-e eset -- event set added to (+) or removed from (-) mask
-y -- respond yes to all prompts


So, we can create a mask (-a), we can drop a mask (-d), change a mask (-m) or show a mask (-o). These would be options for the DBSSOs.
For AAOs we can change the audit mode (-l), change the behavior in case of error (-e), name the audit log files location (-p) and each audit log size (-s). I will show some examples later, but it's important to explain some of the settings. First, let's take the audit level. We can define the following audit levels:
  • 0 - Means audit off
  • 1 - Audit is on. No DBSSOs or DBSAs actions are automatically audited
  • 3 - Audit is on and all the DBSSOs actions are automatically audited
  • 5 - Audit is on and all the DBSAs actions are automatically audited
  • 7 - Audit is on and all the DBSSOs and DBSAs actions are automatically audited
One very important option to note is that the AAOs can define a level that automatically audits the actions of the DBSAs and/or the DBSSOs. This prevents any DBSSOs to manipulate the audit masks to remove himself from the audited users. This also shows the importance of implementing real role separation.
Another note about the audit levels: You may notice that the levels (besides 0 which means off) are all odd numbers. If you're using older versions of IDS you may see references to even numbers. These were equivalents to the ones specified above, but instead of writing to log files the audit facility would send the audit info to the underlying operating system auditing facility. This was not standard, and didn't work on all platforms. In the latest IDS versions the support for those levels were discontinued.
Another important configuration is the audit error mode (-e). This defines the behavior of the instance when it need to audit something, but can't write to the audit log:

  • 0 - Continues the normal processing, without writing the audit log
  • 1 - Suspends the thread/session that need to be audited, and keeps trying to record the audit log
  • 3 - The instance shuts down
So, you have to choose how important the audit is for you... If you can live without it, set it to 0. If you think you can continue to work on the instance (other sessions may not being audited), set it to 1. If you consider auditing to be crucial, you can stop the instance by setting it to 3, meaning it will shutdown if it finds a problem when trying to write the audit log.

The other options are all relative to the audit masks creation and manipulation and are more or less self explanatory.

Where and how does the engine store the audit information?

There are three types of audit information in the auditing facility:

  1. The audit masks definition
  2. The audit facility configuration (level, path, file size and error mode)
  3. The audit logs or the audited information
The audit mask definitions are stored in the sysmaster database and it will survive any reboot. The sysmaster database has several tables containing audit configuration info: sysaudit, sysadtinfo, syscrtadt.

The audit facility configuration is stored in a file called adtcfg.<servernum> located in the $INFORMIXDIR/aaodir. Please note that there is an annoying bug relative to this file usage: Whenever you change the audit facility configuration this file will be updated, but when the server starts it looks at the contents of the file called adtcfg located in the same directory. So you must copy the adtcfg.<servernum> file to adtcfg. This is also one of the reasons why this directory should be unique for each $INFORMIXDIR you have on the same system. This bug did not happen in my testing environment with IDS 11.50.UC1. I hit it with previous versions (7.31.FD7 and 10.00.FC4).
This file contains the following configuration parameters:

  • ADTMODE
    The audit level
  • ADTPATH
    The path where the audit log files are created
  • ADTSIZE
    The size for each audit log file (in bytes)
  • ADTERR
    The error mode of the audit facility

Finally, the audit logs will be created in the path specified by the -p option of onaudit or by the ADTPATH configuration parameter of the adtcfg file.

What do the audit logs contain?

The audit logs are text files that contain entries for each action run from the list of audit actions.
The information for each line will have the same structure, but some fields contain different meanings for different mnemonics or actions.
IDS provides an utility called onshowaudit that will read these audit logs and will generate pipe separated files that you can load into a table with the following structure:


CREATE TABLE frag_logs (
adttag CHAR(4), -- The audited mnemonic
date_time DATETIME YEAR TO FRACTION(3), -- The action timestamp
hostname CHAR(18), -- The client hostname
pid INT, -- The client PID
server CHAR(18), -- The DB server name
username CHAR(8), -- The username
errno INT, -- The error number
code CHAR(4), -- The error code
dbname CHAR(18), -- The database name
tabid INT, -- The table or object ID
objname CHAR(18), -- The object name
extra_1 INT, -- Extra info specific for the mnemonic
partno INT, -- Partition number (usefull for partitioned tables)
row_num INT, -- The row ID
login CHAR(8), -- The database login
flags INT, -- Flags specific for each mnemonic
extra_2 VARCHAR(160,1) -- Extra info specifc for the mnemonic
);


The audit logs will have a name with the following nomenclature: <servername>.<log_number>. The log_number will be a sequential number. When the current file reaches the size of ADTSIZE, this number is incremented and a new file is created. Only the AAOs will be able to access these files, so once again, this will prevent any change by people who manage the instance (DBSSAs) or by people who define the audit masks (DBSSOs).


How painful is the auditing?

This is the million dollar question. Every customer that thinks about implementing the audit functionality asks how much impact will it have on their instances? And usually they want an answer specifying a percentage of performance impact... Unfortunately for me, I will never receive a million dollars by answering this question, simply because there is no answer!
Let's keep this topic on the technical level... How can we specify the impact if we don't know what (and who) will be audited? And even if we did, how could we specify the impact, if we don't know how many times the audited actions will happen?
Although I really think there is no answer, we can of course discuss this topic... For example, let's consider that we want to audit everybody, but we only want to audit the session creation (STSN), and the GRANTs and REVOKEs on tables (GRTB and RVTB). In a normal system you can do this and will probably don't even be able to measure a performance impact... I mean, how many CONNECTs and GRANT/REVOKEs do you do?
On the other hand, let's imagine you want to audit every table row that it read by everyone (RDRW). I'd bet your instance will suffer a very large impact.
So, regarding performance costs, what we need is to clearly define the needs of the auditing process, and to accept that some things just can't be audited (unless you reduce it to a few users for example). Establishing the RDRW mnemonic may be acceptable if you define it just for your DBAs... In short, the million dollar answer would be "it depends", but no one would pay so much money for such a poor answer...

Examples

I will try to show some very basic configuration of the audit facility, in order to demonstrate the concepts exposed above. I will implement role separation. Let's start with a fresh IDS 11.50 installation in /usr/informix:



[root@PacMan srvr1150uc1]# id
uid=0(root) gid=0(root) groups=0(root),1(bin),2(daemon),3(sys),4(adm),6(disk),10(wheel)
[root@PacMan srvr1150uc1]# pwd
/usr/informix/srvr1150uc1
[root@PacMan srvr1150uc1]# ls -lia
total 132
750742 drwxr-xr-x 25 informix informix 4096 Jul 13 17:25 .
456970 drwxr-xr-x 12 informix informix 4096 Jul 13 17:14 ..
750755 drwxrwxr-x 2 informix informix 4096 Jul 13 17:25 aaodir
750756 drwxr-xr-x 2 informix informix 4096 Jul 13 17:26 bin
750757 drwxrwxr-x 2 informix informix 4096 Jul 13 17:25 dbssodir
752279 drwxrwxr-x 4 root root 4096 Jul 13 17:25 demo
750767 drwxr-xr-x 3 informix informix 4096 Jul 13 17:25 doc
750760 drwxrwxr-x 4 informix informix 4096 Jul 13 17:26 etc
751451 drwxr-xr-x 12 informix informix 4096 Jul 13 17:25 extend
752645 drwxrwxr-x 2 root root 4096 Jul 13 17:25 forms
750774 drwxr-xr-x 7 informix informix 4096 Jul 13 17:25 gls
751483 drwxr-xr-x 3 informix informix 4096 Jul 13 17:25 gsk
751478 drwxr-xr-x 2 informix informix 4096 Jul 13 17:26 gskit
752401 drwxrwxr-x 4 root root 4096 Jul 13 17:25 help
750754 drwxr-xr-x 2 informix informix 4096 Jul 13 17:25 ids_license
750762 drwxr-xr-x 6 informix informix 4096 Jul 13 17:25 incl
752232 drwxrwxr-x 2 root root 4096 Jul 13 17:25 ism
750758 drwxr-xr-x 3 informix informix 4096 Jul 13 17:25 lib
750751 drwxr-xr-x 3 informix informix 4096 Jul 13 17:25 master_license
750771 drwxr-xr-x 3 informix informix 4096 Jul 13 17:25 msg
750764 drwxr-xr-x 3 informix informix 4096 Jul 13 17:25 release
752736 drwxrwxr-x 4 root root 4096 Jul 13 17:25 snmp
751482 drwxrwx--- 2 informix informix 4096 Jul 13 17:25 ssl
750749 drwxrwx--- 2 informix informix 4096 Jul 13 20:39 tmp
752765 drwxr-xr-x 2 informix informix 4096 Jul 13 17:25 uninstall_ids1150
751450 -rwxr-xr-x 1 root informix 29713 Apr 25 00:42 uninstallserver
[root@PacMan srvr1150uc1]#


Then, using the ixvirtdir script I'll create a new INFORMIXDIR called srvr1150uc1_cheetah2:


[root@PacMan srvr1150uc1]# ixvirtdir /usr/informix/srvr1150uc1/ /usr/informix/srvr1150uc1_cheetah2 11.5 ixdbsa ixaao ixdbsso


[root@PacMan srvr1150uc1]# ls -lia /usr/informix/srvr1150uc1_cheetah2/
total 24
456978 drwxr-xr-x 6 informix informix 4096 Jul 14 00:41 .
456970 drwxr-xr-x 13 informix informix 4096 Jul 14 00:41 ..
457097 drwxrwx--- 2 informix ixaao 4096 Jul 14 00:41 aaodir
457079 lrwxrwxrwx 1 informix informix 29 Jul 14 00:41 bin -> /usr/informix/srvr1150uc1/bin
457096 drwxrwx--- 2 informix ixdbsso 4096 Jul 14 00:41 dbssodir
457080 lrwxrwxrwx 1 informix informix 30 Jul 14 00:41 demo -> /usr/informix/srvr1150uc1/demo
457081 lrwxrwxrwx 1 informix informix 29 Jul 14 00:41 doc -> /usr/informix/srvr1150uc1/doc
457098 drwxrwxr-x 4 informix ixdbsa 4096 Jul 14 00:41 etc
457082 lrwxrwxrwx 1 informix informix 32 Jul 14 00:41 extend -> /usr/informix/srvr1150uc1/extend
457083 lrwxrwxrwx 1 informix informix 31 Jul 14 00:41 forms -> /usr/informix/srvr1150uc1/forms
457084 lrwxrwxrwx 1 informix informix 29 Jul 14 00:41 gls -> /usr/informix/srvr1150uc1/gls
457095 lrwxrwxrwx 1 informix informix 29 Jul 14 00:41 gsk -> /usr/informix/srvr1150uc1/gsk
457094 lrwxrwxrwx 1 informix informix 31 Jul 14 00:41 gskit -> /usr/informix/srvr1150uc1/gskit
457085 lrwxrwxrwx 1 informix informix 30 Jul 14 00:41 help -> /usr/informix/srvr1150uc1/help
457092 lrwxrwxrwx 1 informix informix 37 Jul 14 00:41 ids_license -> /usr/informix/srvr1150uc1/ids_license
457086 lrwxrwxrwx 1 informix informix 30 Jul 14 00:41 incl -> /usr/informix/srvr1150uc1/incl
457087 lrwxrwxrwx 1 informix informix 29 Jul 14 00:41 ism -> /usr/informix/srvr1150uc1/ism
457088 lrwxrwxrwx 1 informix informix 29 Jul 14 00:41 lib -> /usr/informix/srvr1150uc1/lib
457093 lrwxrwxrwx 1 informix informix 40 Jul 14 00:41 master_license -> /usr/informix/srvr1150uc1/master_license
457089 lrwxrwxrwx 1 informix informix 29 Jul 14 00:41 msg -> /usr/informix/srvr1150uc1/msg
457090 lrwxrwxrwx 1 informix informix 33 Jul 14 00:41 release -> /usr/informix/srvr1150uc1/release
457091 lrwxrwxrwx 1 informix informix 30 Jul 14 00:41 snmp -> /usr/informix/srvr1150uc1/snmp
457221 drwxr-xr-x 3 informix informix 4096 Jul 14 00:41 ssl




We need to make oninit executable to anyone:


cheetah2@PacMan.onlinedomus.net:informix-> ls -lia $INFORMIXDIR/bin/oninit
750793 -rwsr-sr-x 1 root informix 15274592 Jul 13 17:26 /usr/informix/srvr1150uc1_cheetah2/bin/oninit


Then I setup several users for several roles:
  • dbsauser belongs to ixdbsa group, will be the DBSA
  • ssouser belongs to ixdbsso group will be the DBSSO
  • aaouser belongs to ixaao group will be the AAO
I will not use user informix except for storage file creation.
So, as user dbsauser I will initialize the instance:


cheetah2@PacMan.onlinedomus.net:dbsauser-> id
uid=506(dbsauser) gid=506(ixdbsa) groups=506(ixdbsa)
cheetah2@PacMan.onlinedomus.net:dbsauser-> oninit -iwv

This action will initialize IBM Informix Dynamic Server;
any existing IBM Informix Dynamic Server databases will NOT be accessible -
Do you wish to continue (y/n)? y
Checking group membership to determine server run mode...succeeded
Reading configuration file '/usr/informix/srvr1150uc1_cheetah2/etc/onconfig.cheetah2'...succeeded
Creating /INFORMIXTMP/.infxdirs...succeeded
Creating infos file "/usr/informix/srvr1150uc1_cheetah2/etc/.infos.cheetah2"...succeeded
Linking conf file "/usr/informix/srvr1150uc1_cheetah2/etc/.conf.cheetah2"...succeeded
Checking config parameters...succeeded
Writing to infos file...succeeded
Allocating and attaching to shared memory...succeeded
Creating resident pool 4818 kbytes...succeeded
Allocating 50016 kbytes for buffer pool of 2K page size...succeeded
Initializing rhead structure...
succeeded
Initialization of Encryption...succeeded
Initializing ASF...succeeded
Initializing Dictionary Cache and SPL Routine Cache...succeeded
Bringing up ADM VP...succeeded
Creating VP classes...succeeded
Onlining 0 additional cpu vps...succeeded
Onlining 2 IO vps...succeeded
Forking main_loop thread...succeeded
Initializing DR structures...succeeded
Forking 1 'soctcp' listener threads...succeeded
Starting tracing...succeeded
Initializing 8 flushers...succeeded
Initializing log/checkpoint information...succeeded
Initializing dbspaces...succeeded
Opening primary chunks...succeeded
Opening mirror chunks...succeeded
Validating chunks...succeeded
Creating database partition...succeeded
Initialize Async Log Flusher...succeeded
Forking btree cleaner...succeeded
Initializing DBSPACETEMP list...succeeded
Checking database partition index...succeeded
Initializing dataskip structure...succeeded
Checking for temporary tables to drop...succeeded
Forking onmode_mon thread...succeeded
Starting scheduling system...succeeded
Verbose output complete: mode = 5
cheetah2@PacMan.onlinedomus.net:dbsauser->
cheetah2@PacMan.onlinedomus.net:dbsauser-> onstat -

IBM Informix Dynamic Server Version 11.50.UC1 -- On-Line -- Up 00:00:39 -- 88060 Kbytes

cheetah2@PacMan.onlinedomus.net:dbsauser-> onstat -m

IBM Informix Dynamic Server Version 11.50.UC1 -- On-Line -- Up 00:00:41 -- 88060 Kbytes

Message Log File: /usr/informix/logs/cheetah2.log
23:12:03 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog used 134, Llog used 1123

23:12:04 'sysmaster' database built successfully.
23:12:05 'sysutils' database built successfully.
23:12:05 'sysuser' database built successfully.
23:12:11 Building 'sysadmin' database ...
23:12:11 dynamically allocated 2000 locks
23:12:12 Loading Module
23:12:13 Unloading Module
23:12:13 Loading Module
23:12:13 'sysadmin' database built successfully.
23:12:13 Logical Log 3 Complete, timestamp: 0x20a1b.
23:12:13 SCHAPI: Started dbScheduler thread.
23:12:13 SCHAPI: Started 2 dbWorker threads.
23:12:13 Checkpoint Completed: duration was 1 seconds.
23:12:13 Wed Jul 16 - loguniq 4, logpos 0x206018, timestamp: 0x214ce Interval: 6

23:12:13 Maximum server connections 1
23:12:13 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 1, Plog used 177, Llog used 2086


Perfect... Instance up and running, without user Informix... ok... I cheated a little bit, because I had previously touched/chmod the rootdbs chunk file and gave group permissions for the $INFORMIXDIR/etc/$ONCONFIG file...


No I will create a normal dbspace and a temporary dbspace. Again, the file creation should be done as user informix. After this let's create the stores demo database...


cheetah2@PacMan.onlinedomus.net:informix-> id
uid=501(informix) gid=501(informix) groups=501(informix)
cheetah2@PacMan.onlinedomus.net:informix-> pwd
/usr/informix/discos/cheetah2
cheetah2@PacMan.onlinedomus.net:informix-> touch dbs1.c1 temp1.c1
cheetah2@PacMan.onlinedomus.net:informix-> chmod 660 dbs1.c1 temp1.c1
cheetah2@PacMan.onlinedomus.net:informix-> ls -l
total 150152
-rw-rw---- 1 informix informix 0 Jul 16 23:18 dbs1.c1
-rw-rw---- 1 informix informix 153600000 Jul 16 23:17 root_dbs.c1
-rw-rw---- 1 informix informix 0 Jul 16 23:18 temp1.c1
cheetah2@PacMan.onlinedomus.net:informix->


And again as user dbsauser:


cheetah2@PacMan.onlinedomus.net:dbsauser-> id
uid=506(dbsauser) gid=506(ixdbsa) groups=506(ixdbsa)
cheetah2@PacMan.onlinedomus.net:dbsauser-> onspaces -c -d dbs1 -p /usr/informix/discos/cheetah2/dbs1.c1 -s 50000 -o 0
Verifying physical disk space, please wait ...
Space successfully added.

** WARNING ** A level 0 archive of Root DBSpace will need to be done.
cheetah2@PacMan.onlinedomus.net:dbsauser-> onspaces -c -d temp1 -t -p /usr/informix/discos/cheetah2/temp1.c1 -s 10000 -o 0
Verifying physical disk space, please wait ...
Space successfully added.
cheetah2@PacMan.onlinedomus.net:dbsauser-> onstat -d

IBM Informix Dynamic Server Version 11.50.UC1 -- On-Line -- Up 00:09:00 -- 88060 Kbytes

Dbspaces
address number flags fchunk nchunks pgsize flags owner name
477787f8 1 0x60001 1 1 2048 N B informix rootdbs
4890fb40 2 0x60001 2 1 2048 N B informix dbs1
487c5e60 3 0x42001 3 1 2048 N TB informix temp1
3 active, 2047 maximum

Chunks
address chunk/dbs offset size free bpages flags pathname
47778958 1 1 0 75000 19181 PO-B /usr/informix/discos/cheetah2/root_dbs.c1
4890fca0 2 2 0 25000 24947 PO-B /usr/informix/discos/cheetah2/dbs1.c1
4889e778 3 3 0 5000 4947 PO-B /usr/informix/discos/cheetah2/temp1.c1
3 active, 32766 maximum

NOTE: The values in the "size" and "free" columns for DBspace chunks are
displayed in terms of "pgsize" of the DBspace to which they belong.

Expanded chunk capacity mode: always

cheetah2@PacMan.onlinedomus.net:dbsauser->


cheetah2@PacMan.onlinedomus.net:dbsauser-> dbaccessdemo stores_demo -log -dbspace dbs1

DBACCESS Demonstration Database Installation Script

Dropping existing stores_demo database ....

Creating stores_demo database ....


So, we have a functional instance with the usual stores_demo database.
Now we need to start the auditing facility... Let's try running onaudit with the dbsauser:


cheetah2@PacMan.onlinedomus.net:dbsauser-> id
uid=506(dbsauser) gid=506(ixdbsa) groups=506(ixdbsa)
cheetah2@PacMan.onlinedomus.net:dbsauser-> onaudit
Onaudit -- Audit Subsystem Configuration Utility


Must be an AAO or DBSSO to run this program.


So, as expected, the instance administrator which in this simple case is also the database administrator for stores_demo can't even run the onaudit utility... Let's change our identity to aaouser and activate the audit facility, but first, let's try to exceed our role... Let's stop the instance!


cheetah2@PacMan.onlinedomus.net:aaouser-> id
uid=508(aaouser) gid=507(ixaao) groups=507(ixaao)
cheetah2@PacMan.onlinedomus.net:aaouser-> onmode -ky

Must be a DBSA to run this program
cheetah2@PacMan.onlinedomus.net:aaouser->


Sorry... we can't... We're not a DBSA... :)

But we should be able to configure the audit... Check it:



cheetah2@PacMan.onlinedomus.net:aaouser-> id
uid=508(aaouser) gid=507(ixaao) groups=507(ixaao)
cheetah2@PacMan.onlinedomus.net:aaouser-> ls -l $INFORMIXDIR/aaodir
total 8
-rw-rw-r-- 1 informix informix 1120 Apr 25 00:42 adtcfg
-rw-r--r-- 1 informix informix 812 Apr 25 00:42 adtcfg.std
cheetah2@PacMan.onlinedomus.net:aaouser-> ls -lia /usr/informix/audit_logs
total 8
457224 d--xrwx--- 2 informix ixaao 4096 Jul 17 23:25 .
456970 drwxr-xr-x 15 informix informix 4096 Jul 17 00:13 ..


No specific ADTCFG file for the engine and no files in the /usr/informix/audit_logs directory, which will be the place to store the audit trail files.
Let's activate the audit facility:


cheetah2@PacMan.onlinedomus.net:aaouser-> onaudit -l 7 -e 0 -p /usr/informix/audit_logs -s 50000
Onaudit -- Audit Subsystem Configuration Utility

cheetah2@PacMan.onlinedomus.net:aaouser-> ls -l $INFORMIXDIR/aaodir
total 12
-rw-rw-r-- 1 informix informix 1120 Apr 25 00:42 adtcfg
-rw-rw-r-- 1 informix informix 1216 Jul 18 00:04 adtcfg.0
-rw-r--r-- 1 informix informix 812 Apr 25 00:42 adtcfg.std

So now, we have the adtcfg.0 (0 is the instance SERVERNUM).


cheetah2@PacMan.onlinedomus.net:aaouser-> ls -lia /usr/informix/audit_logs
total 8
457224 d--xrwx--- 2 informix ixaao 4096 Jul 18 00:04 .
456970 drwxr-xr-x 15 informix informix 4096 Jul 17 00:13 ..
459479 -rw-rw---- 1 informix ixaao 0 Jul 18 00:04 cheetah2.0


And we already have an audit trail file. The suffix is a number which will increment when we change the file (using onaudit or when one file reaches the size we specified.


cheetah2@PacMan.onlinedomus.net:aaouser-> onaudit -c
Onaudit -- Audit Subsystem Configuration Utility

Current audit system configuration:
ADTMODE = 7
ADTERR = 0
ADTPATH = /usr/informix/audit_logs
ADTSIZE = 50000
Audit file = 0
cheetah2@PacMan.onlinedomus.net:aaouser->


And we can see the current audit configuration. Take noticed that because we used audit level 7, DBSA and DBSSO actions will be automatically recorded into the audit log. Let's make a test with dbsauser. A simple connect to sysmaster:


cheetah2@PacMan.onlinedomus.net:dbsauser-> id
uid=506(dbsauser) gid=506(ixdbsa) groups=506(ixdbsa)
cheetah2@PacMan.onlinedomus.net:dbsauser-> dbaccess sysmaster <<EOF
select count(*) from sysdatabases;
> EOF

Database selected.



(count(*))

4

1 row(s) retrieved.



Database closed.

cheetah2@PacMan.onlinedomus.net:dbsauser->


and no, let's check the contents of the audit trail file:


cheetah2@PacMan.onlinedomus.net:dbsauser-> id
uid=506(dbsauser) gid=506(ixdbsa) groups=506(ixdbsa)
cheetah2@PacMan.onlinedomus.net:dbsauser-> ls -lia /usr/informix/audit_logs/
ls: /usr/informix/audit_logs/: Permission denied
cheetah2@PacMan.onlinedomus.net:dbsauser-> cat /usr/informix/audit_logs/cheetah2.0
cat: /usr/informix/audit_logs/cheetah.0: Permission denied
cheetah2@PacMan.onlinedomus.net:dbsauser->


Hmmm... what is a DBSA trying to check something only AAO should do?! :)
So, let's now do it with the correct user:


cheetah2@PacMan.onlinedomus.net:aaouser-> id
uid=508(aaouser) gid=507(ixaao) groups=507(ixaao)
cheetah2@PacMan.onlinedomus.net:aaouser-> ls -lia /usr/informix/audit_logs/
total 12
457224 d--xrwx--- 2 informix ixaao 4096 Jul 18 00:04 .
456970 drwxr-xr-x 15 informix informix 4096 Jul 17 00:13 ..
459479 -rw-rw---- 1 informix ixaao 937 Jul 18 00:14 cheetah2.0
cheetah2@PacMan.onlinedomus.net:aaouser-> cat /usr/informix/audit_logs/cheetah2.0
ONLN|2008-07-18 00:14:49.000|PacMan.onlinedomus.net|4386|cheetah2|dbsauser|0:STSN
ONLN|2008-07-18 00:14:49.000|PacMan.onlinedomus.net|4386|cheetah2|dbsauser|0:OPDB:sysmaster:0:-
ONLN|2008-07-18 00:14:49.000|PacMan.onlinedomus.net|4386|cheetah2|dbsauser|0:ACTB:sysmaster:informix:sysdatabases:247
ONLN|2008-07-18 00:14:49.000|PacMan.onlinedomus.net|4386|cheetah2|dbsauser|0:ACTB:sysmaster:informix:sysdbspartn:100
ONLN|2008-07-18 00:14:50.000|PacMan.onlinedomus.net|4386|cheetah2|dbsauser|0:RDRW:sysmaster:100:1048578:513
ONLN|2008-07-18 00:14:50.000|PacMan.onlinedomus.net|4386|cheetah2|dbsauser|0:RDRW:sysmaster:100:1048578:514
ONLN|2008-07-18 00:14:50.000|PacMan.onlinedomus.net|4386|cheetah2|dbsauser|0:RDRW:sysmaster:100:1048578:515
ONLN|2008-07-18 00:14:50.000|PacMan.onlinedomus.net|4386|cheetah2|dbsauser|0:RDRW:sysmaster:100:1048578:516
ONLN|2008-07-18 00:14:50.000|PacMan.onlinedomus.net|4386|cheetah2|dbsauser|0:CLDB:sysmaster
cheetah2@PacMan.onlinedomus.net:aaouser->


Ah... Ok, now we have the audited data... Let's check it by mnemonic:
  1. STSN
    For STart SessioN, showing timestamp, instance, user, PID...
  2. OPDB
    For OPen DataBase, showing timestamp, instance, user, database, PID, return code...
  3. ACTB
    For ACess TaBle, showing the table, the table owner/schema, the database etc.
  4. RDRW
    For ReaD RoW, showing the timestamp, instance, database, table id and rowid
  5. CLDB
    For CLose DataBase, showing the timestamp, instance, PID and database...
This simple test shows you the potential that RDRW has to damage your instance performance... A full table scan on a big table will be enough to fill thounsands or millions of audit trail entry lines.

Now, let's create the _default global mask. I'll use user informix to test it.
To start, let's audit the same events we saw in the previous example, but first let's change the audit log:


cheetah2@PacMan.onlinedomus.net:aaouser-> id
uid=508(aaouser) gid=507(ixaao) groups=507(ixaao)
cheetah2@PacMan.onlinedomus.net:aaouser-> ls -ltr /usr/informix/audit_logs/
total 0
-rw-rw---- 1 informix ixaao 937 Jul 20 17:37 cheetah2.0
cheetah2@PacMan.onlinedomus.net:aaouser-> onaudit -n
Onaudit -- Audit Subsystem Configuration Utility

cheetah2@PacMan.onlinedomus.net:aaouser-> ls -ltr /usr/informix/audit_logs/
total 0
-rw-rw---- 1 informix ixaao 937 Jul 20 17:37 cheetah2.0
-rw-rw---- 1 informix ixaao 0 Jul 20 17:40 cheetah2.1
cheetah2@PacMan.onlinedomus.net:aaouser->


And now, with the ssouser, let's create the _default mask:


cheetah2@PacMan.onlinedomus.net:ssouser-> id
uid=507(ssouser) gid=508(ixdbsso) groups=508(ixdbsso)
cheetah2@PacMan.onlinedomus.net:ssouser-> ls -ltr /usr/informix/audit_logs/
ls: /usr/informix/audit_logs/: Permission denied
cheetah2@PacMan.onlinedomus.net:ssouser-> id
uid=507(ssouser) gid=508(ixdbsso) groups=508(ixdbsso)
cheetah2@PacMan.onlinedomus.net:ssouser-> onaudit -a -u _default -e STSN,OPDB,CLDB,ACTB,RDRW
Onaudit -- Audit Subsystem Configuration Utility

cheetah2@PacMan.onlinedomus.net:ssouser-> onaudit -o -y
Onaudit -- Audit Subsystem Configuration Utility

_default - ACTB,CLDB,OPDB,RDRW,STSN
cheetah2@PacMan.onlinedomus.net:ssouser->


And now, using another user, informix, let's do the same we did before and check the audit trail log:


cheetah2@PacMan.onlinedomus.net:informix-> id
uid=501(informix) gid=501(informix) groups=501(informix)
cheetah2@PacMan.onlinedomus.net:informix-> dbaccess sysmaster < select count(*) from sysdatabases;
> EOF

Database selected.



(count(*))

5

1 row(s) retrieved.



Database closed.

cheetah2@PacMan.onlinedomus.net:informix->


And now, the content of the audit trail:


cheetah2@PacMan.onlinedomus.net:aaouser-> id
uid=508(aaouser) gid=507(ixaao) groups=507(ixaao)
cheetah2@PacMan.onlinedomus.net:aaouser-> ls -ltr /usr/informix/audit_logs/
total 0
-rw-rw---- 1 informix ixaao 0 Jul 20 17:37 cheetah2.0
cheetah2@PacMan.onlinedomus.net:aaouser-> onaudit -n
Onaudit -- Audit Subsystem Configuration Utility

cheetah2@PacMan.onlinedomus.net:aaouser-> ls -ltr /usr/informix/audit_logs/
total 0
-rw-rw---- 1 informix ixaao 0 Jul 20 17:37 cheetah2.0
-rw-rw---- 1 informix ixaao 0 Jul 20 17:40 cheetah2.1
cheetah2@PacMan.onlinedomus.net:aaouser-> id
uid=508(aaouser) gid=507(ixaao) groups=507(ixaao)
cheetah2@PacMan.onlinedomus.net:aaouser-> cat /usr/informix/audit_logs/cheetah2.1
ONLN|2008-07-20 17:44:15.000|PacMan.onlinedomus.net|8059|cheetah2|ssouser|0:STSN
ONLN|2008-07-20 17:44:15.000|PacMan.onlinedomus.net|8059|cheetah2|ssouser|0:OPDB:sysmaster:0:-
ONLN|2008-07-20 17:44:15.000|PacMan.onlinedomus.net|8059|cheetah2|ssouser|0:ACTB:sysmaster:informix:sysadtinfo:214
ONLN|2008-07-20 17:44:15.000|PacMan.onlinedomus.net|8059|cheetah2|ssouser|0:RDRW:sysmaster:214:1025:0
ONLN|2008-07-20 17:44:15.000|PacMan.onlinedomus.net|8059|cheetah2|ssouser|0:ACTB:sysmaster:informix:sysaudit:261
ONLN|2008-07-20 17:44:15.000|PacMan.onlinedomus.net|8059|cheetah2|ssouser|0:RDRW:sysmaster:261:1048836:513
ONLN|2008-07-20 17:44:15.000|PacMan.onlinedomus.net|8059|cheetah2|ssouser|0:ACTB:sysmaster:informix:syscrtadt:215
ONLN|2008-07-20 17:44:15.000|PacMan.onlinedomus.net|8059|cheetah2|ssouser|0:LSAM:_default
ONLN|2008-07-20 17:44:15.000|PacMan.onlinedomus.net|8059|cheetah2|ssouser|0:INRW:sysmaster:215:1026:6913
ONLN|2008-07-20 17:44:15.000|PacMan.onlinedomus.net|8059|cheetah2|ssouser|0:ACTB:sysmaster:informix:syscrtadt:215
ONLN|2008-07-20 17:44:15.000|PacMan.onlinedomus.net|8059|cheetah2|ssouser|100:LSAM:_default
ONLN|2008-07-20 17:44:15.000|PacMan.onlinedomus.net|8059|cheetah2|ssouser|0:INRW:sysmaster:215:1026:6913
ONLN|2008-07-20 17:44:15.000|PacMan.onlinedomus.net|8059|cheetah2|ssouser|0:CLDB:sysmaster
ONLN|2008-07-20 17:48:24.000|PacMan.onlinedomus.net|8255|cheetah2|informix|0:STSN
ONLN|2008-07-20 17:48:24.000|PacMan.onlinedomus.net|8255|cheetah2|informix|0:OPDB:sysmaster:0:-
ONLN|2008-07-20 17:48:24.000|PacMan.onlinedomus.net|8255|cheetah2|informix|0:ACTB:sysmaster:informix:sysdatabases:247
ONLN|2008-07-20 17:48:24.000|PacMan.onlinedomus.net|8255|cheetah2|informix|0:ACTB:sysmaster:informix:sysdbspartn:100
ONLN|2008-07-20 17:48:24.000|PacMan.onlinedomus.net|8255|cheetah2|informix|0:RDRW:sysmaster:100:1048578:513
ONLN|2008-07-20 17:48:24.000|PacMan.onlinedomus.net|8255|cheetah2|informix|0:RDRW:sysmaster:100:1048578:514
ONLN|2008-07-20 17:48:24.000|PacMan.onlinedomus.net|8255|cheetah2|informix|0:RDRW:sysmaster:100:1048578:515
ONLN|2008-07-20 17:48:24.000|PacMan.onlinedomus.net|8255|cheetah2|informix|0:RDRW:sysmaster:100:1048578:516
ONLN|2008-07-20 17:48:24.000|PacMan.onlinedomus.net|8255|cheetah2|informix|0:RDRW:sysmaster:100:1048578:517
ONLN|2008-07-20 17:48:24.000|PacMan.onlinedomus.net|8255|cheetah2|informix|0:CLDB:sysmaster
cheetah2@PacMan.onlinedomus.net:aaouser->


You may notice that there are entries referring to two Process IDs. The first (8059) refers to the onaudit command we used first to define the _default mask. The other (8255) refers to the dbaccess process and is similar to the first test we did. So, any change to the audit masks, which must be done by a DBSSO user is automatically audited if we choose a correct audit level (as we did).

Again we see the RDRW mnemonic which is able to cause serious performance issues.
This is a nice opportunity to show you the effect of the _exclude mask. Let's include the RDRW in this mask. But first let's change the audit trail log file again with:


cheetah2@PacMan.onlinedomus.net:aaouser-> id
uid=508(aaouser) gid=507(ixaao) groups=507(ixaao)
cheetah2@PacMan.onlinedomus.net:aaouser-> onaudit -n
Onaudit -- Audit Subsystem Configuration Utility

Now, with ssouser, let's add the _exclude audit mask:


cheetah2@PacMan.onlinedomus.net:ssouser-> id
uid=507(ssouser) gid=508(ixdbsso) groups=508(ixdbsso)
cheetah2@PacMan.onlinedomus.net:ssouser-> onaudit -a -u _exclude -e RDRW
Onaudit -- Audit Subsystem Configuration Utility

cheetah2@PacMan.onlinedomus.net:ssouser->


Since we're using audit level 7, this action should have been logged. Let's see how:


cheetah2@PacMan.onlinedomus.net:aaouser-> id
uid=508(aaouser) gid=507(ixaao) groups=507(ixaao)
cheetah2@PacMan.onlinedomus.net:aaouser-> cat /usr/informix/audit_logs/cheetah2.2
ONLN|2008-07-20 18:21:38.000|PacMan.onlinedomus.net|8478|cheetah2|aaouser|0:ACTB:sysmaster:informix:syscrtadt:215
ONLN|2008-07-20 18:21:38.000|PacMan.onlinedomus.net|8478|cheetah2|aaouser|0:CLDB:sysmaster
ONLN|2008-07-20 18:22:42.000|PacMan.onlinedomus.net|8484|cheetah2|ssouser|0:STSN
ONLN|2008-07-20 18:22:42.000|PacMan.onlinedomus.net|8484|cheetah2|ssouser|0:OPDB:sysmaster:0:-
ONLN|2008-07-20 18:22:42.000|PacMan.onlinedomus.net|8484|cheetah2|ssouser|0:ACTB:sysmaster:informix:sysadtinfo:214
ONLN|2008-07-20 18:22:42.000|PacMan.onlinedomus.net|8484|cheetah2|ssouser|0:RDRW:sysmaster:214:1025:0
ONLN|2008-07-20 18:22:42.000|PacMan.onlinedomus.net|8484|cheetah2|ssouser|0:ACTB:sysmaster:informix:sysaudit:261
ONLN|2008-07-20 18:22:42.000|PacMan.onlinedomus.net|8484|cheetah2|ssouser|0:ACTB:sysmaster:informix:syscrtadt:215
ONLN|2008-07-20 18:22:42.000|PacMan.onlinedomus.net|8484|cheetah2|ssouser|100:LSAM:_exclude
ONLN|2008-07-20 18:22:42.000|PacMan.onlinedomus.net|8484|cheetah2|ssouser|0:INRW:sysmaster:215:1026:6913
ONLN|2008-07-20 18:22:42.000|PacMan.onlinedomus.net|8484|cheetah2|ssouser|0:ACTB:sysmaster:informix:sysaudit:261
ONLN|2008-07-20 18:22:42.000|PacMan.onlinedomus.net|8484|cheetah2|ssouser|0:INRW:sysmaster:261:1048836:514
ONLN|2008-07-20 18:22:42.000|PacMan.onlinedomus.net|8484|cheetah2|ssouser|0:ACTB:sysmaster:informix:syscrtadt:215
ONLN|2008-07-20 18:22:42.000|PacMan.onlinedomus.net|8484|cheetah2|ssouser|0:CRAM:_exclude
ONLN|2008-07-20 18:22:42.000|PacMan.onlinedomus.net|8484|cheetah2|ssouser|0:INRW:sysmaster:215:1026:6913
ONLN|2008-07-20 18:22:42.000|PacMan.onlinedomus.net|8484|cheetah2|ssouser|0:CLDB:sysmaster



So the DBSSO action was fully logged. Now let's change to another audit log file, and repeat the same dbaccess we did previously with user dbsauser:


cheetah2@PacMan.onlinedomus.net:aaouser-> id
uid=508(aaouser) gid=507(ixaao) groups=507(ixaao)
cheetah2@PacMan.onlinedomus.net:aaouser-> onaudit -n
Onaudit -- Audit Subsystem Configuration Utility

cheetah2@PacMan.onlinedomus.net:aaouser->



and let's see the file contents:


ONLN|2008-07-20 18:23:00.000|PacMan.onlinedomus.net|8486|cheetah2|informix|0:STSN
ONLN|2008-07-20 18:23:00.000|PacMan.onlinedomus.net|8486|cheetah2|informix|0:OPDB:sysmaster:0:-
ONLN|2008-07-20 18:23:00.000|PacMan.onlinedomus.net|8486|cheetah2|informix|0:ACTB:sysmaster:informix:sysdatabases:247
ONLN|2008-07-20 18:23:00.000|PacMan.onlinedomus.net|8486|cheetah2|informix|0:ACTB:sysmaster:informix:sysdbspartn:100
ONLN|2008-07-20 18:23:00.000|PacMan.onlinedomus.net|8486|cheetah2|informix|0:CLDB:sysmaster
cheetah2@PacMan.onlinedomus.net:aaouser->


So, similar to the previous examples, but without the RDRW event.

These examples should give you a very basic feeling on how we set up and configure the auditing facility. Following, I will try to show how to process the audit files, and how we can use SQL to do it.

Using SQL to analyze your auditing information

I have created an SQL file with the CREATE TABLE above, and I will obtain a file in unload format (PIPE separated), with the contents of the audit logs we generated. To do this I will use the onshowaudit utility:


cheetah2@PacMan.onlinedomus.net:aaouser-> id
uid=508(aaouser) gid=507(ixaao) groups=507(ixaao)
cheetah2@PacMan.onlinedomus.net:aaouser-> onshowaudit -l -I -n 0 >audit_logs.unl

Program Over.

cheetah2@PacMan.onlinedomus.net:aaouser-> head -30 audit_logs.unl

ONSHOWAUDIT Secure Audit Utility
INFORMIX-SQL Version 11.50.UC1
ONLN|2008-07-20 17:44:15.000|PacMan.onlinedomus.net|8059|cheetah2|ssouser|0|STSN||||||||||
ONLN|2008-07-20 17:44:15.000|PacMan.onlinedomus.net|8059|cheetah2|ssouser|0|OPDB|sysmaster|||||||0|-|
ONLN|2008-07-20 17:44:15.000|PacMan.onlinedomus.net|8059|cheetah2|ssouser|0|ACTB|sysmaster|214|||||informix||sysadtinfo|
ONLN|2008-07-20 17:44:15.000|PacMan.onlinedomus.net|8059|cheetah2|ssouser|0|RDRW|sysmaster|214|||1025|0||||
ONLN|2008-07-20 17:44:15.000|PacMan.onlinedomus.net|8059|cheetah2|ssouser|0|ACTB|sysmaster|261|||||informix||sysaudit|
ONLN|2008-07-20 17:44:15.000|PacMan.onlinedomus.net|8059|cheetah2|ssouser|0|RDRW|sysmaster|261|||1048836|513||||
ONLN|2008-07-20 17:44:15.000|PacMan.onlinedomus.net|8059|cheetah2|ssouser|0|ACTB|sysmaster|215|||||informix||syscrtadt|
cheetah2@PacMan.onlinedomus.net:aaouser->


As you can see there are 3 annoying header lines at the top. I erased them with vi.
So now, we're prepared to load this into an audit table that we create with the SQL above:


cheetah2@PacMan.onlinedomus.net:aaouser-> dbaccess stores_demo frag_logs.sql

Database selected.


Table created.


Database closed.

cheetah2@PacMan.onlinedomus.net:aaouser-> dbaccess stores_demo -

Database selected.

> load from audit_logs.unl insert into frag_logs;

58 row(s) loaded.

> cheetah2@PacMan.onlinedomus.net:aaouser->



So now, you have a table with all the info collected by the auditing facility. You can then explore it using simple SQL. You can search for specific entries or simply create reports based on it.

Hopefully this will be enough to get you started with the auditing facility, but I'd like to point out several possible problems (with solutions) and a few other considerations.


Possible issues:
  1. Versions 9.x, 10.x and 11.x may raise an error like:

    Onaudit -- Audit Subsystem Configuration Utility

    Cannot set audit configuration parameters.
    Problem may be caused by lack of permission, bad
    parameter values, or bad parameter combinations.

    This error usually relates to the fact that the oninit processes are not running as user root.
    This was done in versions 7.x, but was changed in versions 9.x. If running versions 9.x and versions 10.x you may set a variable called NONROOT_OFF to value 1:

    NONROOT_OFF=1
    export NONROOT_OFF

    before starting the instance. This will force the oninit processes to run as root. The need to run as root derives from the fact that the engine must create the audit log files with specific user/group and root privileges are needed for this.
    At least in IDS 11.50 this doesn't happen if you start your intance using a DBSA user.
  2. In some IDS versions you may need to explicitly grant privileges on the sysmaster tables to the DBSSO and/or AAO users. The tables are sysmaster:sysadtinfo and sysmaster:sysaudit
  3. In some IDS versions, the audit facility will change/create the adtcfg.<servernum> file, but the instance will only look at the adtcfg file on startup. Be sure to confirm your version doesn't have this issue, or if it has, be sure to copy the file generated by the auditing facility to the one used by the instance startup process. In this case you have to do this after each change of the auditing facility configuration
Note that on version 11.50 which I used to generate the examples on this article, I didn't notice any of the above issues. I could reproduce the first one only if I start the engine with user informix which I didn't include in the group od DBSAs

Final recommendations
  • Test everything in a test environment using the same OS and IDS version you use in production. Some of the changes may cause your system to become unusable or unstable if something goes wrong
  • If you think about using the auditing facility for compliance reasons, don't do it without creating full role separation
  • If you implement full role separation, be sure that the different teams and/or users are able to work together, and that each one understands the impact of his activity in the overall use of your database instance(s)
  • Consider carefully the settings for ADTERR. Informix allows you to choose what to do if you can't register the events on the auditing log. This setting can decide the instance availability
  • Think carefully about how to use the auditing logs information. If you intend to use SQL for processing the information, be sure to guarantee that the information can only be managed by the AAO group users. Informix does everything to guarantee the audit logs information security, so you must be sure to guarantee this security after you extract the info and process it. Consider using a different instance and possibly a different machine to process this data
  • Understand that the auditing information can be useful for your database administration purposes. To give you an example I help managing a reasonable sized system and the audit facility was the best way to measure some things like:


    • We had 11 connections per second
    • We execute more than 90 stored procedures per second
    • We create more than 6 temporary tables per second

  • Think very carefully about activating some action mnemonics like RDRW, INRW and DLRW. This can have a dramatic impact on your intance performance. It really depends on which users will use masks with this mnemonics active, and how many DML operations they do.

References:

Friday, April 25, 2008

Workshop IDS 11.50 (Lisbon 26-28 May)

As the title suggests this post refers to a workshop that will be held in Lisbon on May 26-28 about IDS 11.50 (Cheetah 2).
As this will be a local Workshop the rest of the post will be in Portuguese. Information about other locations can be seen at the IIUG site

A IBM irá realizar um workshop para parceiros e clientes dedicado à versão 11.50 do IDS em Lisboa. Ao contrário de outras localizações, onde o workshop será de dois dias, Lisboa terá um evento de três dias, tendo como objectivo rever algumas das funcionalidades da versão 11.10 lançada o ano passado.

O workshop servirá para transmitir o posicionamento do produto e as novas funcionalidades desta versão que se encontra nesta altura em fase open beta. Os participantes poderão experimentar o produto e as novas funcionalidades em ambientes pré-configurados.
Será também uma excelente oportunidade para conhecer os serviços técnicos que a IBM disponibiliza.

A forma de registo deverá ser anunciada brevemente, e se participou em algum dos eventos anteriores relacionados com Informix é provável que receba um convite. De momento a única informação disponível publicamente está no site do IIUG (link acima), mas em caso de interesse reserve desde já os dias indicados: 26, 27 e 28 de Maio.

Wednesday, April 16, 2008

Certification: Test your knowledge for free...

Just a very quick post to echo an IIUG announcement. If you're a member of IIUG you can do an Informix V11 certification preparation test. This will be a test similar to what you'll find if you want to do the 918 exam, which will allow you to be certified on Informix.

The announcement is here. Note that the code will allow only one test per person... I've done mine, without preparation and I would pass (80.33% achieved, 70% needed), but I can say several questions are very tricky. So, if you plan to be certified be prepared to take some time studying. But this can be the first step in your preparation roadmap. A normal test would cost you $10 (American dollars).

Monday, March 10, 2008

Compliance: Role Separation and Audit (part I)

Depending on your geography and your business, you are probably at least well aware of the current government, legal and privacy regulations.
After some well noticed economical frauds, and also due to the constant fight between security and privacy, many companies had to increase their internal regulations and security measures, and they had to begin to comply with several national and international regulations. If you're in the USA, names like Sarbanes-Oxley Act of 2002 (SOX) and Health Insurance Portability and Accountability Act (HIPAA) have surely become part of your daily jargon. Other countries have similar national laws or must follow similar directives (EU countries). From a brief search Asian countries like Japan also have similar regulations. So, although not equal in every country, most of us have met the same or similar requirements to comply with security, accountability and privacy regulations.
The situations will vary but in general this means companies have to define, ensure and control who does what to their data. This directly implies that all IT departments must make sure they can answer their auditors (either internal or external) about the controls they implemented in their systems.

I will, in this and another article, try to explain some of the security, auditing and other control features IBM Informix Dynamic Server has to offer. In this first part I will show you how Informix can separate the various roles that are needed to install, manage and audit an Informix database instance. The second article will focus on the audit facility. I will leave out of these articles the features that allow you to control the user's access to data. This will be the privileges (database and table level) and the Label Based Access Control (LBAC). The reason for this is that both of these features are reasonable well known and also because when LBAC was introduced in Cheetah (IDS 11.10) it received a lot of coverage in several places (like IDS Experts Blog and Guy Bowerman's Blog).

The role separation and audit functionalities were traditionally covered in the Trusted Facility Manual (V7, V9, V10) , but since IDS 11.10 they were put together with LBAC and some other functionalities like encryption into a new manual, the Security Guide Manual.

If you start looking at the manuals and look for auditing facilities you'll first find references to "role separation". You may also notice that the IDS installer will ask you if you want to enable role separation. Please, don't mix up this "role" with the well known database roles that you can use to manage database and table level privileges. These "roles" in the audit facility context are different. IDS documentation identifies five roles regarding to database instance installation, administration, audit control and audit administration:

  1. Operating System Administrator(s) (OSA)
    These are responsible for the Operating System administration and they are needed for the IBM Informix Dynamic Server product installation
  2. DataBase System Administrators (DBSA)
    These are the database system administrators. They can do all maintenance tasks on the database instance (not the databases within the instance). These include starting, stopping, monitoring, executing backups etc. They cannot access the data inside the databases and they cannot control any of the auditing tasks of the instance. This means they cannot control what is audited, neither who is audited and they cannot access the audit logs. They can of course be audited!
  3. DataBase System Security Officers (DBSSO)
    These define what is audited. As you will see in the second article, they will define the audit masks. But they won't be able to manipulate the auditing facility (start, stop, configure...) or access/manipulate the audit logs
  4. Audit Analysis Officers (AAO)
    These will manage (start, stop, configure...) the auditing facility and will also be able to access the audit logs. They will not be able to define what is audited
  5. DataBase Administrators (DBA)
    These are the users with "GRANT DBA" on one or more databases inside an instance. Remember that the owner of the database is inherently a DBA of its database.
It also talks about another group or role, the instance users, which you can define in the $INFORMIXDIR/dbssodir/seccfg file. In this file you can define a group, and then only the members of that OS group will be able to connect to the instance. Unfortunately you can only define one group, and this file is not configurable per instance (as $ONCONFIG for example). So, if you have more than one instance in your server you'll need more than one $INFORMIXDIR.

Hopefully I will be able to explain the tasks of each of these roles, and it will be clear that by providing this separation ability IDS offers you tools to implement compliance that you'd have to buy in other databases. The decision to implement all these roles and to really keep them separate (by keeping each user in only one role) will depend on your organization objectives (and also the capabilities in terms of organization and staff).

I'd like to make one thing very clear: Although ideally these roles should be completely separated, organizations should be aware that they must team together. DBSSO and AAO can do certain tasks that will impact the database performance and availability. They should take this into consideration. Also, some tasks, usually attributed to DBSA can be impossible to achieve without giving them the DBA role also... So, you should really define your objectives, priorities and staff functions. Sometimes there will be a price to pay for some functionalities and this may need management approval. The good news is that everything I'll talk about here is possible to configure on the fly meaning you can dynamically change everything... from role separation to audit configuration and management. So, if as I mentioned earlier, your different departments work as a team you benefit from this ability to reconfigure without stop, if and when the need arises.

I'll focus on Unix/Linux, mainly because that's where I'm most experienced, but everything I'll talk about is possible to do on Windows. It may have to be done in a slightly different way. Please consult the manuals if I'm not specific about it here. There is also an article about it on Guy Bowerman's blog.

So, let's start by the beginning... Ideally it all starts at installation time. The installation itself needs to be done by an OSA role element. Typically this will be root on Linux/Unix and a member of Administrators group in Windows. The installation will ask you if you want to create role separation. If you answer "yes", it will ask you for OS group that will represent the DBSSO and the AAO roles. It assumes DBSA will be group informix (Linux/Unix) and Informix-Admin on Windows. I don't like to assume that DBSA will map to group informix. I'll explain it later...
So, the mapping between instance administrative roles and users will be implemented by OS groups. To be more specific, there are three directories inside your $INFORMIXDIR that will define the roles. The group owners of those directories will be the OS groups representing the roles. Specifically the mappings are:

  • DBSA
    Group owning $INFORMIXDIR/etc
  • DBSSO
    Group owning $INFORMIXDIR/dbssodir
  • AAO
    Group owning $INFORMIXDIR/aaodir
The OSA and DBA roles don't map like this. The OSA is defined by OS configuration, and DBAs are defined in each database by GRANTing privileges to specific users.

So, if you installed the product and you did not choose to create role separation, you can do it later. Let's assume we will have OS groups called ixdbsa, ixdbsso and ixaao that respectively map to DBSA, DBSSO and AAO roles. To implement role separation on an existing instance you should issue the following commands:


chgrp ixdbsa $INFORMIXDIR/etc
chgrp ixaao $INFORMIXDIR/aaodir
chgrp ixdbsso $INFORMIXDIR/ixdbsso

The directory permissions should also be changed:

chmod 770 $INFORMIXDIR/aaodir
chmod 770 $INFORMIXDIR/dbssodir
chmod g+w $INFORMIXDIR/etc

Note: Some documents may show 775 for aaodir and dbssodir, but I don't see the need for permissions in these directories outside the group they belong to.

And also you should give permissions for your DBSAs to alter $ONCONFIG file:

chgrp ixdbsa $INFORMIXDIR/etc/$ONCONFIG
chmod g+w $INFORMIXDIR/etc/$ONCONFIG


Let me make a few considerations before proceeding: As I wrote above, I personally don't like to use group informix for the DBSA role. Why? Well, you are probably aware that the IDS chunks must be owned by user informix and group informix and must have permissions 660. Now, if you use group informix as your DBSA group, then you have to include your instance administrators in this group. The problem with this is that then, theoretically, they will have read/write ability on the chunks. They could, using just OS commands, read data pages, overwrite data pages, eliminate chunks etc. Of course they would need deep technical knowledge about a chunk structure and IDS datatypes to be able to read any useful information, but it would be technically possible. The downside of having a different group for DBSA is that you must use the informix account (or an OSA) to add space to your instance. The upside would be that your team of DBSAs could do everything needed to manage the instance, but could not ever access your data. It could of course drop a chunk, but this would have to be done using IDS commands which can be (and should be) easily audited.

By now, some of you are probably thinking I'm being too though or too strict... I mean... we are DBSAs/DBAs and we are supposed to be trustworthy individuals... Yes, this is true. And yes, after the end of these two articles you'll be asking "and what about root?... He can still do rm -rf /*, can't he?"... Yes, we are trustworthy individuals, and yes, root is a problem, but when you are confronted with an auditor, you MUST prove that you can't do any harm without leaving a trace, and YOU don't have to take care of the root problem... The idea here is to show how the product can be configured to assure maximum security and accountability... then there are the implications, and organizational decisions...

So, first decision, use informix group for DBSA? Your choice... Or your organization's choice. Anyway, after putting your staff that will administer the IDS instance in the "DBSA" OS group, you should consider their access to the informix account. After doing the right configuration you can (and should) impose serious restrictions on the use of the informix account. The fact is, in general, access to it will not be needed. If you choose not to use group informix then it will be necessary for adding space to the instance, but that's the only thing I remember... everything else can be done using your individual accounts as long they belong to the DBSA group.
Some very important notes:
  1. If you choose to use a different DBSA group (not informix) your OSA must change permissions on the oninit executable file;

    chmod o+x $INFORMIXDIR/bin/oninit

    Before you shout "What?! Isn't that the most insecure thing to do?", I should say that oninit will check the identity of the invoking user, and will not run if he doesn't belong to the DBSA defined group
  2. Many customers usually have several instances in the same box, sharing the same product installation directory. IDS is prepared to do this, and most of its configuration and operation files ($ONCONFIG, MSGPATH etc.) are separated by instance. But if you're using role separation, this would mean you'd need to have the same groups/roles for all the instances. This may not be convenient. I suggest that you install your product just once, but then create separated $INFORMIXDIRs for your instances. The steps to achieve this are:
    1. Create one $INFORMIXDIR for each of your instances
    2. Inside each one, create symbolic links to the equivalent directory on the product installation directory except for $INFORMIXDIR/etc, $INFORMIXDIR/aaodir and $INFORMIXDIR/dbssodir
    3. For this three directories, copy their content from the product installation directory into each one of yours $INFORMIXDIR

      This process is well described in Jonathan Leffler's "Is your DBA paranoid enough" presentation... You can Google it...
      At the bottom of this article you can also find a link to a script called ixvirtdir that will help you in the process of creating these directories for each instance.
  3. If you're running versions 9+ and you use a specific group for AAO you may hit one problem: In these versions the majority of the oninit processes will not run as user root, but will use user informix. When you do certain audit commands the engine will try to create a file with the running user and then will try to change it's ownership (like chown...). This will fail in many, if not most platforms. There is an undocumented variable that you can use (NONROOT_OFF=1) in the server environment that will change the default behavior, and will force oninit to run as root. This has disadvantages (that's why it was changed), but may be the only way to create certain files with the desired user
By now you should have all the info needed for setting up role separation in your IBM Dynamic Server instances. This is the basic step to allow for a proper audit environment.
This will be the subject of the second article, which should be available soon.

You still have two problems to address: root and informix. These are privileged users, and you should restrict access to them. root is always a problem, because it really can do whatever he wants... and many times it's nearly impossible to completely audit its actions, because usually root will be able to circumvent all the restrictions you may implement. informix will be much easier, and you should avoid at all cost the use of this user, once you create and defined the DBSA group and you have your instances configured. There will be a few actions that you must do as informix but you should take measures like:
  • Avoid direct login as informix
  • Make it impossible to have more then one people at the same time using this login
  • Audit the actions of user informix using the audit facility
  • Audit everyone that accesses the informix account (logging su and/or sudo commands)
  • Eventually use the OS to log informix keystrokes

I recommend that you test all these steps and all the actions in test environments. A mistake or bad configuration options can really cause harm to your databases. Please note that many times functionality comes with a price... Please consider it before taking decisions.

To end this article, a few references: